CockroachDB backend for Django

Prerequisites

You must install 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 3.2.x:

pip install django-cockroachdb==3.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 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

Why do I get the error psycopg2.errors.InvalidName: no database specified?

You may need to create the database. You can use cockroach sql --insecure on the command line to get a SQL prompt.

GIS support

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

You must install GEOS rather than have Django use the libgeos_c.so bundled with CockroachDB. In other words, if you try this Django setting:

GEOS_LIBRARY_PATH = '/usr/local/lib/cockroach/libgeos_c.so'

It will fail with OSError: libgeos.so.3.8.1: cannot open shared object file: No such file or directory.

Known issues and limitations (as of CockroachDB 20.2.7)

• 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
  • dropping or changing a table's primary key
  • indexes on expressions (Django's Index.expressions)

• Known Bugs:

  • Timezones after 2038 use incorrect DST settings.

• Unsupported queries:

  • Mixed type addition in SELECT: unsupported binary operator: <int> + <float>
  • UPDATE float column with integer column: value type int doesn't match type FLOAT8 of column <name>
  • 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.
  • The SHA224 and SHA384 database functions aren't supported.

• GIS:

  • Some database functions aren't supported: AsGML, AsKML, AsSVG, BoundingCircle, GeometryDistance, LineLocatePoint, and MemSize.
  • 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).
  • 3D storage isn't supported.
  • These lookups aren't supported:
    • contained (@)
    • exact/same_as (~=)
    • left (<<) and right (>>)
    • overlaps_left (&<), overlaps_right (&>), overlaps_above (&<|), overlaps_below (&>|)
    • strictly_above (|>>), strictly_below (<<|)