Cockroach Database

CockroachDB (CRDB) is well supported bypeewee.

from playhouse.cockroachdb import CockroachDatabase
 
db = CockroachDatabase('my_app', user='root', host='10.1.0.8')

The playhouse.cockroachdb extension module provides the following classesand helpers:

• CockroachDatabase - a subclass of PostgresqlDatabase,designed specifically for working with CRDB.
• PooledCockroachDatabase - like the above, but implementsconnection-pooling.
• run_transaction() - runs a function inside atransaction and provides automatic client-side retry logic.

Special field-types that may be useful when using CRDB:

• UUIDKeyField - a primary-key field implementation that usesCRDB’s UUID type with a default randomly-generated UUID.
• RowIDField - a primary-key field implementation that uses CRDB’sINT type with a default unique_rowid().
• JSONField - same as the Postgres BinaryJSONField, asCRDB treats JSON as JSONB.
• ArrayField - same as the Postgres extension (but does not supportmulti-dimensional arrays).

CRDB is compatible with Postgres’ wire protocol and exposes a very similarSQL interface, so it is possible (though not recommended) to usePostgresqlDatabase with CRDB:

• CRDB does not support nested transactions (savepoints), so theatomic() method has been implemented to enforce this whenusing CockroachDatabase. For more info CRDB Transactions.
• CRDB may have subtle differences in field-types, date functions andintrospection from Postgres.
• CRDB-specific features are exposed by the CockroachDatabase,such as specifying a transaction priority or the AS OF SYSTEM TIMEclause.

CRDB Transactions

CRDB does not support nested transactions (savepoints), so theatomic() method on the CockroachDatabase hasbeen modified to raise an exception if an invalid nesting is encountered. Ifyou would like to be able to nest transactional code, you can use thetransaction() method, which will ensure that the outer-mostblock will manage the transaction (e.g., exiting a nested-block will not causean early commit).

Example:

@db.transaction()def create_user(username):    return User.create(username=username)
def some_other_function():    with db.transaction() as txn:
    # do some stuff...
    # This function is wrapped in a transaction, but the nested
    # transaction will be ignored and folded into the outer
    # transaction, as we are already in a wrapped-block (via the
    # context manager).
    create_user('some_user@example.com')
    # do other stuff.
# At this point we have exited the outer-most block and the transaction
# will be committed.
return

CRDB provides client-side transaction retries, which are available using aspecial run_transaction() helper. This helpermethod accepts a callable, which is responsible for executing any transactionalstatements that may need to be retried.

Simplest possible example of run_transaction():

def create_user(email):
    # Callable that accepts a single argument (the database instance) and
    # which is responsible for executing the transactional SQL.
    def callback(db_ref):
        return User.create(email=email)
 
    return db.run_transaction(callback, max_attempts=10)
 
huey = create_user('huey@example.com')

Note

The cockroachdb.ExceededMaxAttempts exception will be raised if thetransaction cannot be committed after the given number of attempts. If theSQL is mal-formed, violates a constraint, etc., then the function willraise the exception to the caller.

Example of using run_transaction() to implementclient-side retries for a transaction that transfers an amount from one accountto another:

from playhouse.cockroachdb import CockroachDatabase
 
db = CockroachDatabase('my_app')
 
 
def transfer_funds(from_id, to_id, amt):
    """
    Returns a 3-tuple of (success?, from balance, to balance). If there are
    not sufficient funds, then the original balances are returned.
    """
    def thunk(db_ref):
        src, dest = (Account
                     .select()
                     .where(Account.id.in_([from_id, to_id])))
        if src.id != from_id:
            src, dest = dest, src  # Swap order.
 
        # Cannot perform transfer, insufficient funds!
        if src.balance < amt:
            return False, src.balance, dest.balance
 
        # Update each account, returning the new balance.
        src, = (Account
                .update(balance=Account.balance - amt)
                .where(Account.id == from_id)
                .returning(Account.balance)
                .execute())
        dest, = (Account
                 .update(balance=Account.balance + amt)
                 .where(Account.id == to_id)
                 .returning(Account.balance)
                 .execute())
        return True, src.balance, dest.balance
 
    # Perform the queries that comprise a logical transaction. In the
    # event the transaction fails due to contention, it will be auto-
    # matically retried (up to 10 times).
    return db.run_transaction(thunk, max_attempts=10)

CRDB APIs

• class CockroachDatabase(database[, **kwargs])
• CockroachDB implementation, based on the PostgresqlDatabase andusing the psycopg2 driver.

Additional keyword arguments are passed to the psycopg2 connectionconstructor, and may be used to specify the database user, port,etc.

• runtransaction(_callback[, max_attempts=None[, system_time=None[, priority=None]]])

Parameters:

- **callback** – callable that accepts a single <code>db</code> parameter (whichwill be the database instance this method is called from).
- **max_attempts** (_int_) – max number of times to try before giving up.
- **system_time** (_datetime_) – execute the transaction <code>AS OF SYSTEM TIME</code>with respect to the given value.
- **priority** (_str_) – either “low”, “normal” or “high”.Returns:

returns the value returned by the callback.Raises:ExceededMaxAttempts if max_attempts is exceeded.

Run SQL in a transaction with automatic client-side retries.

User-provided callback:

- **Must** accept one parameter, the <code>db</code> instance representing theconnection the transaction is running under.
- **Must** not attempt to commit, rollback or otherwise manage thetransaction.
- **May** be called more than one time.
- **Should** ideally only contain SQL operations.

Additionally, the database must not have any open transactions at thetime this function is called, as CRDB does not support nestedtransactions. Attempting to do so will raise a NotImplementedError.

Simplest possible example:

def create_user(email):
    def callback(db_ref):
        return User.create(email=email)
 
    return db.run_transaction(callback, max_attempts=10)
 
user = create_user('huey@example.com')

• class PooledCockroachDatabase(database[, **kwargs])
• CockroachDB connection-pooling implementation, based onPooledPostgresqlDatabase. Implements the same APIs asCockroachDatabase, but will do client-side connection pooling.

• runtransaction(_db, callback[, max_attempts=None[, system_time=None[, priority=None]]])
• Run SQL in a transaction with automatic client-side retries. SeeCockroachDatabase.run_transaction() for details.

Parameters:

• db (CockroachDatabase) – database instance.
• callback – callable that accepts a single db parameter (whichwill be the same as the value passed above).

Note

This function is equivalent to the identically-named method onthe CockroachDatabase class.

• class UUIDKeyField
• UUID primary-key field that uses the CRDB gen_random_uuid() function toautomatically populate the initial value.

• class RowIDField
• Auto-incrementing integer primary-key field that uses the CRDBunique_rowid() function to automatically populate the initial value.

See also:

• BinaryJSONField from the Postgresql extension (available in thecockroachdb extension module, and aliased to JSONField).
• ArrayField from the Postgresql extension.