CockroachDB backend for Django

Prerequisites

You must install:

• psycopg, which may have some prerequisites depending on which version you use.

You can also use either:

• psycopg2, which has some prerequisites of its own.

• psycopg2-binary

The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources.

Install and usage

Use the version of django-cockroachdb that corresponds to your version of Django. For example, to get the latest compatible release for Django 4.2.x:

pip install django-cockroachdb==4.2.*

The minor release number of Django doesn't correspond to the minor release number of django-cockroachdb. Use the latest minor release of each.

Configure the Django DATABASES setting similar to this:

DATABASES = {
    'default': {
        'ENGINE': 'django_cockroachdb',
        'NAME': 'django',
        'USER': 'myprojectuser',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '26257',
        # If connecting with SSL, include the section below, replacing the
        # file paths as appropriate.
        'OPTIONS': {
            'sslmode': 'verify-full',
            'sslrootcert': '/certs/ca.crt',
            # Either sslcert and sslkey (below) or PASSWORD (above) is
            # required.
            'sslcert': '/certs/client.myprojectuser.crt',
            'sslkey': '/certs/client.myprojectuser.key',
            # If applicable
            'options': '--cluster={routing-id}',
        },
    },
}

If using Kerberos authentication, you can specify a custom service name in 'OPTIONS' using the key 'krbsrvname'.

Notes on Django fields

• IntegerField uses the same storage as BigIntegerField so IntegerField is introspected by inspectdb as BigIntegerField.

• AutoField and BigAutoField are both stored as integer (64-bit) with DEFAULT unique_rowid().

Notes on Django QuerySets

• QuerySet.explain() accepts verbose, types, opt, vec, and distsql options which correspond to CockroachDB's parameters. For example:

  >>> Choice.objects.explain(opt=True, verbose=True)
  'scan polls_choice\n ├── columns: id:1 question_id:4 choice_text:2 votes:3\n ├── stats: [rows=1]\n ├── cost: 1.1\n ├── key: (1)\n ├── fd: (1)-->(2-4)\n └── prune: (1-4)'

FAQ

GIS support

To use django.contrib.gis with CockroachDB, use 'ENGINE': 'django_cockroachdb_gis' in Django's DATABASES setting.

Disabling CockroachDB telemetry

By default, CockroachDB sends the version of django-cockroachdb that you're using back to Cockroach Labs. To disable this, set DISABLE_COCKROACHDB_TELEMETRY = True in your Django settings.

Known issues and limitations in CockroachDB 22.2.x and earlier

• CockroachDB can't disable constraint checking, which means certain things in Django like forward references in fixtures aren't supported.

• Migrations have some limitations. CockroachDB doesn't support:

  • changing column type if it's part of an index
  • dropping or changing a table's primary key
  • CockroachDB executes ALTER COLUMN queries asynchronously which is at odds with Django's assumption that the database is altered before the next migration operation begins. CockroachDB will give an error like unimplemented: table <...> is currently undergoing a schema change if a later operation tries to modify the table before the asynchronous query finishes. A future version of CockroachDB may fix this.

• The Field.db_comment and Meta.db_table_comment options aren't supported due to poor performance.

• Unsupported queries:

  • Mixed type addition in SELECT: unsupported binary operator: <int> + <float>
  • Division that yields a different type: unsupported binary operator: <int> / <int> (desired <int>)
  • The power() database function doesn't accept negative exponents: power(): integer out of range
  • sum() doesn't support arguments of different types: sum(): unsupported binary operator: <float> + <int>
  • greatest() doesn't support arguments of different types: greatest(): expected <arg> to be of type <type>, found type <other type>
  • SmallAutoField generates values that are too large for any corresponding foreign keys.

• GIS:

  • Some database functions aren't supported: AsGML, AsKML, AsSVG, and GeometryDistance.
  • Some 3D functions or signatures aren't supported: ST_3DPerimeter, ST_3DExtent, ST_Scale, and ST_LengthSpheroid.
  • The Length database function isn't supported on geodetic fields: st_lengthspheroid(): unimplemented.
  • Union may crash with unknown signature: st_union(geometry, geometry).
  • The spheroid argument of ST_DistanceSpheroid isn't supported: unknown signature: st_distancespheroid(geometry, geometry, string).
  • These lookups aren't supported:
    • contained (@)
    • exact/same_as (~=)
    • left (<<) and right (>>)
    • overlaps_left (&<), overlaps_right (&>), overlaps_above (&<|), overlaps_below (&>|)
    • strictly_above (|>>), strictly_below (<<|)

Known issues and limitations in CockroachDB 22.1.x and earlier

• QuerySet.select_for_update(skip_locked=True) isn't supported.