Peewee ORM + CockroachDB

Peewee ORM + CockroachDB

This article was originally posted on the personal blog of the Peewee ORM founder, Charles Leifer. Peewee is a simple and small Python ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use. And as of Peewee's most recent release (3.13.0), it supports CockroachDB!


I'm pleased to announce that Peewee now supports CockroachDB (CRDB), the distributed, horizontally-scalable SQL database. I'm excited about this release, because it's now quite easy to get up-and-running with a distributed SQL database that can scale out with minimal effort (documentation).

Minimal example of configuring a CockroachDatabase instance:

from playhouse.cockroachdb import CockroachDatabase

db = CockroachDatabase('my_app', user='root', host='10.1.0.8', port=26257)

CRDB conveniently exposes a very similar SQL API to Postgres, which has been well-supported for many years, allowing you to use features like jsonb and arrays, in addition to the regular complement of field-types. Additionally, CRDB speaks the same wire-protocol as Postgres, so it works out-of-the-box using the popular psycopg2 driver.

CRDB provides a client-side transaction retry API, which Peewee supports using a helper-method. To use client-side retries with Peewee and CRDB, provide a callable that executes your transactional SQL and pass it to the run_transaction() method:

from playhouse.cockroachdb import CockroachDatabase

db = CockroachDatabase('my_app')

def create_user_and_token(username, timestamp):
    # The run_transaction() helper accepts a callable that contains your
    # transactional SQL statements. The transaction will be automatically
    # retried until it can be committed.
    def callback(db_ref):
        user = User.create(username=username, created=timestamp)
        token = Token.create(
            user=user,
            expires=timestamp + timedelta(days=1),
            code=generate_random_token())
        return user, token.code

    # Upon success, the return-value of the callback is passed back to
    # the caller. If an error, such as a constraint violation, occurs,
    # the error is propagated back up the call-stack.
    return db.run_transaction(callback, max_attempts=10)

user, code = create_user_and_token('huey@kitten', datetime.now())

Links