SQLAlchemy CockroachDB Dialect

2.0.3 · active · verified Sun Apr 12

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.

Warnings

breaking 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.
Fix: Ensure your `sqlalchemy-cockroachdb` version matches your installed `SQLAlchemy` major version (e.g., `sqlalchemy-cockroachdb==2.x` for `SQLAlchemy==2.x`).
gotcha 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.
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.
gotcha 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.
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.
gotcha 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.
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).

Install

pip install sqlalchemy-cockroachdb psycopg2-binary Install with recommended psycopg2 driver

Imports

create_engine
```
from sqlalchemy import create_engine
```
The CockroachDB dialect auto-registers when `sqlalchemy-cockroachdb` is installed. You create the engine using a `cockroachdb://` URL.
text
```
from sqlalchemy import text
```
Used for executing raw SQL statements.

Quickstart

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.

import os
from sqlalchemy import create_engine, text

# CockroachDB connection string (replace with your actual connection string)
# For a local insecure instance: "cockroachdb://root@localhost:26257/defaultdb?sslmode=disable"
# For CockroachDB Cloud: "cockroachdb://<username>:<password>@<host>:<port>/<database>?sslmode=require"
DATABASE_URL = os.environ.get(
    "COCKROACHDB_URL",
    "cockroachdb://root@localhost:26257/defaultdb?sslmode=disable"
)

try:
    engine = create_engine(DATABASE_URL)

    with engine.connect() as connection:
        # Verify connection
        result = connection.execute(text("SELECT 1+1"))
        print(f"Result of 1+1: {result.scalar()}")

        # Example: Create a table with UUID primary key (recommended for CockroachDB)
        connection.execute(text("""
            CREATE TABLE IF NOT EXISTS accounts (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                balance INT
            )
        """))
        connection.commit()
        print("Table 'accounts' checked/created.")

        # Example: Insert data
        connection.execute(text("INSERT INTO accounts (balance) VALUES (:balance)"), {"balance": 100})
        connection.commit()
        print("Inserted a new account.")

        # Example: Select data
        accounts_result = connection.execute(text("SELECT id, balance FROM accounts LIMIT 5"))
        for row in accounts_result:
            print(f"Account ID: {row.id}, Balance: {row.balance}")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure CockroachDB is running and your connection string is correct.")

view raw JSON →