{"id":4775,"library":"sqlalchemy-cockroachdb","title":"SQLAlchemy CockroachDB Dialect","description":"The `sqlalchemy-cockroachdb` library provides a database dialect for SQLAlchemy, enabling Python applications to connect and interact with CockroachDB. It currently supports SQLAlchemy 2.0 and later, adapting SQLAlchemy's ORM and SQL Expression Language to CockroachDB's distributed SQL features. The library maintains an active release schedule, often aligning with updates to CockroachDB and SQLAlchemy.","status":"active","version":"2.0.3","language":"en","source_language":"en","source_url":"https://github.com/cockroachdb/sqlalchemy-cockroachdb","tags":["sqlalchemy","database","cockroachdb","orm","sql","dialect"],"install":[{"cmd":"pip install sqlalchemy-cockroachdb psycopg2-binary","lang":"bash","label":"Install with recommended psycopg2 driver"}],"dependencies":[{"reason":"Core ORM and SQL toolkit that this library extends.","package":"SQLAlchemy"},{"reason":"Recommended database driver for connecting to CockroachDB (PostgreSQL wire-compatible). `pg8000` is another option.","package":"psycopg2-binary","optional":false}],"imports":[{"note":"The CockroachDB dialect auto-registers when `sqlalchemy-cockroachdb` is installed. You create the engine using a `cockroachdb://` URL.","symbol":"create_engine","correct":"from sqlalchemy import create_engine"},{"note":"Used for executing raw SQL statements.","symbol":"text","correct":"from sqlalchemy import text"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text\n\n# CockroachDB connection string (replace with your actual connection string)\n# For a local insecure instance: \"cockroachdb://root@localhost:26257/defaultdb?sslmode=disable\"\n# For CockroachDB Cloud: \"cockroachdb://<username>:<password>@<host>:<port>/<database>?sslmode=require\"\nDATABASE_URL = os.environ.get(\n    \"COCKROACHDB_URL\",\n    \"cockroachdb://root@localhost:26257/defaultdb?sslmode=disable\"\n)\n\ntry:\n    engine = create_engine(DATABASE_URL)\n\n    with engine.connect() as connection:\n        # Verify connection\n        result = connection.execute(text(\"SELECT 1+1\"))\n        print(f\"Result of 1+1: {result.scalar()}\")\n\n        # Example: Create a table with UUID primary key (recommended for CockroachDB)\n        connection.execute(text(\"\"\"\n            CREATE TABLE IF NOT EXISTS accounts (\n                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n                balance INT\n            )\n        \"\"\"))\n        connection.commit()\n        print(\"Table 'accounts' checked/created.\")\n\n        # Example: Insert data\n        connection.execute(text(\"INSERT INTO accounts (balance) VALUES (:balance)\"), {\"balance\": 100})\n        connection.commit()\n        print(\"Inserted a new account.\")\n\n        # Example: Select data\n        accounts_result = connection.execute(text(\"SELECT id, balance FROM accounts LIMIT 5\"))\n        for row in accounts_result:\n            print(f\"Account ID: {row.id}, Balance: {row.balance}\")\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    print(\"Please ensure CockroachDB is running and your connection string is correct.\")\n","lang":"python","description":"Demonstrates connecting to CockroachDB, executing a simple query, creating a table with a UUID primary key, inserting data, and selecting data. It uses an environment variable for the connection URL, falling back to a common local insecure setup."},"warnings":[{"fix":"Ensure your `sqlalchemy-cockroachdb` version matches your installed `SQLAlchemy` major version (e.g., `sqlalchemy-cockroachdb==2.x` for `SQLAlchemy==2.x`).","message":"SQLAlchemy 2.0 Compatibility: `sqlalchemy-cockroachdb` v2.x is specifically designed for SQLAlchemy v2.x. Attempting to use a v1.x dialect with SQLAlchemy v2.x, or vice-versa, will result in errors.","severity":"breaking","affected_versions":"<2.0 (with SQLAlchemy 2.x), >=2.0 (with SQLAlchemy <2.x)"},{"fix":"Implement application-level transaction retry logic, often by wrapping transaction blocks in a loop. Refer to CockroachDB documentation for Python transaction retry examples, which typically involve catching specific error codes and retrying.","message":"Transaction Retries: CockroachDB frequently requires client-side transaction retry logic due to its distributed nature, which can lead to `SQLSTATE 40001` (serialization_failure) errors. SQLAlchemy does not inherently provide this.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Define primary keys using `UUID` (e.g., `id UUID PRIMARY KEY DEFAULT gen_random_uuid()`) or `UNIQUE_ROWID()` in your table schemas for better scalability in a distributed environment.","message":"Primary Key Strategy (`SERIAL` vs `UUID`/`UNIQUE_ROWID()`): Using `SERIAL` or `AUTOINCREMENT` for primary keys in CockroachDB can create hotspots and performance bottlenecks. `UUID` or `UNIQUE_ROWID()` are generally preferred for distributed performance.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always explicitly set `sslmode` in your CockroachDB connection string to match your database configuration (e.g., `sslmode=require` for secure connections, `sslmode=disable` for insecure local setups).","message":"Connection String `sslmode`: CockroachDB Cloud typically requires `sslmode=require` in the connection string. Local insecure instances might use `sslmode=disable`. Misconfiguring `sslmode` is a common cause of connection failures.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}