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.0.x:

pip install django-cockroachdb==3.0.*

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': 'require',
            '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

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

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

Notes on Django QuerySets

1. 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.

Known issues and limitations (as of CockroachDB 20.2.4)

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

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

   1. changing column type
   2. dropping or changing a table's primary key

3. Known Bugs:

   1. Timezones after 2038 use incorrect DST settings.

4. Unsupported queries:

   1. Mixed type addition in SELECT: unsupported binary operator: <int> + <float>
   2. UPDATE float column with integer column: value type int doesn't match type FLOAT8 of column <name>
   3. Division that yields a different type: unsupported binary operator: <int> / <int> (desired <int>)
   4. The power() database function doesn't accept negative exponents: power(): integer out of range
   5. sum() doesn't support arguments of different types: sum(): unsupported binary operator: <float> + <int>
   6. greatest() doesn't support arguments of different types: greatest(): expected <arg> to be of type <type>, found type <other type>
   7. SmallAutoField generates values that are too large for any corresponding foreign keys.
   8. The SHA224 and SHA384 database functions aren't supported.

Additional limitations in CockroachDB 20.1.x

• The StdDev and Variance aggregates aren't supported.

Additional issues and limitations in CockroachDB 19.2.x

1. Savepoints aren't supported. This means a few things:

   1. Django's transaction.atomic() can't be nested.
   2. Django's TestCase works like TransactionTestCase. That is, transactions aren't used to speed up the former.

2. Known Bugs:

   1. The extract() function doesn't respect the time zone.
   2. Interval math across daylight saving time is incorrect.
   3. date_trunc('week', <value>) truncates to midnight Sunday rather than Monday
   4. date_trunc() results are incorrectly localized.
   5. cast() timestamptz to time doesn't respect the time zone.
   6. Interval math may be incorrect on date columns.
   7. CharField is introspected by inspectdb as TextField.

3. Unsupported queries:

   1. extract() doesn't support interval columns (DurationField): unknown signature: extract(string, interval)
   2. The log() function doesn't support custom bases: unknown signature: extract(string, interval)
   3. timezone() doesn't support UTC offsets: timezone(): unknown time zone UTC...