Hide navigation sidebar
Hide table of contents sidebar
Skip to content
Back to top
Toggle table of contents sidebar

# `crdb` – CockroachDB support

Added in version 3.1.

CockroachDB is a distributed database using the same fronted-backend protocol of PostgreSQL. As such, Psycopg can be used to write Python programs interacting with CockroachDB.

Opening a connection to a CRDB database using `psycopg.connect()` provides a largely working object. However, using the `psycopg.crdb.connect()` function instead, Psycopg will create more specialised objects and provide a types mapping tweaked on the CockroachDB data model.

## Main differences from PostgreSQL

CockroachDB behaviour is different from PostgreSQL: please refer to the database documentation for details. These are some of the main differences affecting Psycopg behaviour:

- `cancel()` doesn’t work before CockroachDB 22.1. On older versions, you can use CANCEL QUERY instead (but from a different connection).

- Server-side cursors are well supported only from CockroachDB 22.1.3.

- `backend_pid` is only populated from CockroachDB 22.1. Note however that you cannot use the PID to terminate the session; use SHOW session_id to find the id of a session, which you may terminate with CANCEL SESSION in lieu of PostgreSQL’s `pg_terminate_backend()`.

- Several data types are missing or slightly different from PostgreSQL (see `adapters` for an overview of the differences).

- The two-phase commit protocol is not supported.

- `LISTEN` and `NOTIFY` are not supported. However the CHANGEFEED command, in conjunction with `stream()`, can provide push notifications.

## CockroachDB-specific objects

psycopg.crdb.connect(conninfo: str = '', *, autocommit: bool = False, prepare_threshold: int | None = 5, context: AdaptContext | None = None, row_factory: RowFactory[Row] | None = None, cursor_factory: type[Cursor[Row]] | None = None, **kwargs: str | int | None) → Self

  Connect to a database server and return a new `Connection` instance.

  This is an alias of the class method `CrdbConnection.connect`.

  If you need an asynchronous connection use the `AsyncCrdbConnection.connect()` method instead.

class psycopg.crdb.CrdbConnection(pgconn: PGconn, row_factory: RowFactory[Row] = <function tuple_row>)

  Wrapper for a connection to a CockroachDB database.

  `psycopg.Connection` subclass.

  classmethod is_crdb(conn: Connection[Any] | AsyncConnection[Any] | PGconn) → bool

    Return `True` if the server connected to `conn` is CockroachDB.

    Parameters:

      conn (`Connection`, `AsyncConnection`, `PGconn`) – the connection to check

class psycopg.crdb.AsyncCrdbConnection(pgconn: PGconn, row_factory: AsyncRowFactory[Row] = <function tuple_row>)

  Wrapper for an async connection to a CockroachDB database.

  `psycopg.AsyncConnection` subclass.

class psycopg.crdb.CrdbConnectionInfo(pgconn: PGconn)

  `ConnectionInfo` subclass to get info about a CockroachDB database.

  The object is returned by the `info` attribute of `CrdbConnection` and `AsyncCrdbConnection`.

  The object behaves like `ConnectionInfo`, with the following differences:

  vendor

    The `CockroachDB` string.

  server_version

    Return the CockroachDB server version connected.

    Return a number in the PostgreSQL format (e.g. 21.2.10 -> 210210).

psycopg.crdb.adapters

  The default adapters map establishing how Python and CockroachDB types are converted into each other.

  The map is used as a template when new connections are created, using `psycopg.crdb.connect()` (similarly to the way `psycopg.adapters` is used as template for new PostgreSQL connections).

  This registry contains only the types and adapters supported by CockroachDB. Several PostgreSQL types and adapters are missing or different from PostgreSQL, among which:

  - Composite types

  - `range`, `multirange` types

  - The `hstore` type

  - Geometric types

  - Nested arrays

  - Arrays of `jsonb`

  - The `cidr` data type

  - The `json` type is an alias for `jsonb`

  - The `int` type is an alias for `int8`, not `int4`.