About the adapter Prerequisites Installation Configuration Defining models Schema generation Basic usage OCC retry pattern aerich migrations SQL splitting Adapter features reference

Aurora DSQL adapter for Tortoise ORM

Like the other adapters and dialects for Amazon Aurora DSQL, the Aurora DSQL adapter for Tortoise ORM (aurora-dsql-tortoise-orm) integrates an object-relational mapping (ORM) framework with Aurora DSQL. It provides custom backends for both asyncpg and psycopg drivers, with Aurora DSQL-aware schema generators for asynchronous index creation, IDENTITY columns, and foreign key enforcement through optimistic concurrency control (OCC), and IAM authentication, so you can use the full Tortoise ORM API against a distributed Aurora DSQL cluster. For more information, see the Aurora DSQL adapter for Tortoise ORM and the Tortoise ORM pet clinic example application on the GitHub website.

About the adapter

Amazon Aurora DSQL is a serverless, distributed relational database service optimized for transactional workloads. Aurora DSQL has specific requirements, including IAM-based authentication, asynchronous index creation, universally unique identifier (UUID) primary keys by default, and OCC-based foreign key enforcement. Foreign key (FK) conflicts raise a serialization error that your application can retry.

With the Aurora DSQL adapter for Tortoise ORM, you can use the full Tortoise ORM API against Aurora DSQL without manually accounting for its behavioral differences. The adapter addresses these requirements with custom Tortoise ORM backends and schema generators.

What is Aurora DSQL authentication?

In Aurora DSQL, authentication involves:

IAM authentication: All connections use IAM-based authentication with time-limited tokens.
Token generation: The connector generates authentication tokens from your AWS credentials. Tokens have configurable lifetimes.

The adapter uses a password factory that calls aurora-dsql-python-connector for each connection to generate IAM tokens automatically. Connections use Secure Sockets Layer (SSL), but the connector doesn't verify the server certificate by default. To turn on verification, see SSL configuration.

Features

The adapter provides the following features:

Automatic IAM authentication – Generates IAM tokens automatically from your AWS credentials.
Driver support – Provides custom backends for both the asyncpg and psycopg drivers.
Async index creation – Creates all indexes with CREATE INDEX ASYNC.
Foreign key support – The adapter emits REFERENCES constraints in generated DDL, with OCC-based enforcement. For the foreign key options that Aurora DSQL supports, see Working with foreign key constraints in Aurora DSQL.
UUID primary keys – UUIDField with uuid.uuid4 is the recommended primary key strategy.
BigInt identity columns – BigIntField primary keys use GENERATED BY DEFAULT AS IDENTITY with CACHE 65536.
aerich migrations – Works with aerich migration tooling.
Connection uniform resource locator (URL) support – Supports the dsql+asyncpg:// and dsql+psycopg:// URL schemes.
OCC error handling – Surfaces OC000 (data conflict) and OC001 (schema conflict) so that your application can retry.

Prerequisites

Before you use the adapter, make sure that you have the following:

Python 3.10 or higher
AWS credentials with dsql:DbConnectAdmin permission (admin) or dsql:DbConnect (non-admin)
Your cluster endpoint and user — the code examples below read these from CLUSTER_ENDPOINT and CLUSTER_USER environment variables, but you can supply them any way you prefer

Installation


PROMPT> pip install "aurora-dsql-python-connector>=0.2.7"  # provides is_occ_error for the retry example

# Install the adapter with one or both drivers:
PROMPT> pip install "aurora-dsql-tortoise-orm[asyncpg]"  # asyncpg backend
PROMPT> pip install "aurora-dsql-tortoise-orm[psycopg]"  # psycopg backend

Configuration

Tortoise.init() — programmatic


import os
from tortoise import Tortoise

CLUSTER_ENDPOINT = os.environ["CLUSTER_ENDPOINT"]
CLUSTER_USER = os.environ.get("CLUSTER_USER", "admin")

await Tortoise.init(
    config={
        "connections": {
            "default": {
                "engine": "aurora_dsql_tortoise.asyncpg",   # or .psycopg
                "credentials": {
                    "host": CLUSTER_ENDPOINT,
                    "user": CLUSTER_USER,
                },
            }
        },
        "apps": {
            "models": {
                "models": ["myapp.models"],
                "default_connection": "default",
            }
        },
    }
)

SSL configuration

Aurora DSQL requires TLS for all connections. To verify the server certificate, set sslmode to verify-full and sslrootcert to a certificate authority (CA) certificate file, such as Amazon Root CA 1. For more information, see Configuring SSL/TLS certificates for Aurora DSQL connections. The asyncpg backend takes these keys in credentials and doesn't accept sslrootcert="system". The psycopg backend takes them in server_settings:


# asyncpg
"credentials": {
    "host": CLUSTER_ENDPOINT,
    "user": CLUSTER_USER,
    "sslmode": "verify-full",
    "sslrootcert": "/path/to/custom-ca.pem",
},

# psycopg
"credentials": {
    "host": CLUSTER_ENDPOINT,
    "user": CLUSTER_USER,
    "server_settings": {"sslmode": "verify-full", "sslrootcert": "/path/to/custom-ca.pem"},
},

URL format

Before use, call register_asyncpg() to register the dsql+asyncpg:// URL scheme, or register_psycopg() to register dsql+psycopg://. register_backends() registers both and requires both drivers. In the following example, replace cluster-id and region with the values for your cluster:


from aurora_dsql_tortoise import register_asyncpg
register_asyncpg()

await Tortoise.init(
    db_url="dsql+asyncpg://admin@cluster-id.dsql.region.on.aws/postgres",
    modules={"models": ["myapp.models"]},
)
# or, after register_psycopg():
db_url = "dsql+psycopg://admin@cluster-id.dsql.region.on.aws/postgres"

The adapter type-casts URL query parameters automatically (for example, ?max_size=10&timeout=30).

Connection duration

Aurora DSQL enforces a one-hour maximum connection duration. The cluster closes long-lived connections.

asyncpg: max_inactive_connection_lifetime controls how long an idle connection stays in the pool before the pool closes it. It applies only to idle connections, so a continuously busy connection can still reach the one-hour limit. For workloads with long-running or continuously-used connections, recycle connections in application logic. Alternatively, switch to the psycopg backend. Its pool supports a max_lifetime parameter that caps total connection age regardless of activity.

psycopg backend

psycopg_pool defaults max_lifetime to 3600.0 seconds — exactly Aurora DSQL's hard limit. Set it explicitly to a lower value (for example, 3300) to give the pool time to recycle connections before the cluster closes them:


"credentials": {
    "host": CLUSTER_ENDPOINT,
    "user": CLUSTER_USER,
    "max_lifetime": 3300,  # recycle before the 1-hour hard limit
}

Schema per user

For non-admin users, set CLUSTER_USER to the non-admin user name. The adapter uses dsql:DbConnect instead of dsql:DbConnectAdmin for token generation automatically.

Defining models

UUID primary key (recommended)


import uuid
from tortoise import fields
from tortoise.models import Model

class Owner(Model):
    id   = fields.UUIDField(primary_key=True, default=uuid.uuid4)
    name = fields.CharField(max_length=30)
    city = fields.CharField(max_length=80)

    class Meta:
        table = "owner"

BigInt identity column (opt-in)


from tortoise import fields
from tortoise.models import Model

class Owner(Model):
    id   = fields.BigIntField(primary_key=True)   # → GENERATED BY DEFAULT AS IDENTITY (CACHE 65536)
    name = fields.CharField(max_length=30)

    class Meta:
        table = "owner"

Important

Use UUIDField or BigIntField as primary keys. IntField and SmallIntField primary keys raise a ValueError at schema generation time.

Foreign key relations

Aurora DSQL supports NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT, MATCH SIMPLE, MATCH FULL, and deferrable foreign keys, and supports SET CONSTRAINTS for foreign keys only. Enforcement is OCC-based: FK conflicts raise a serialization error (OC000 / SQLSTATE 40001), which you can detect with is_occ_error and retry. FK constraint violations (referencing a non-existent row) raise a distinct exception and aren't retryable. All DML on referenced or referencing tables incurs extra reads, and constraints added to existing tables use ALTER TABLE ... ADD CONSTRAINT ... NOT VALID followed by ALTER TABLE ASYNC ... VALIDATE CONSTRAINT; the adapter's aerich integration handles this automatically.


class Pet(Model):
    id     = fields.UUIDField(primary_key=True, default=uuid.uuid4)
    name   = fields.CharField(max_length=30)
    owner  = fields.ForeignKeyField("models.Owner", related_name="pets")

    class Meta:
        table = "pet"

The generated DDL includes an owner_id column with a REFERENCES owner(id) constraint. Enforcement is OCC-based — FK conflicts raise OC000, which your application can retry.

Many-to-many


class Vet(Model):
    id          = fields.UUIDField(primary_key=True, default=uuid.uuid4)
    name        = fields.CharField(max_length=30)
    specialties = fields.ManyToManyField("models.Specialty", related_name="vets")

    class Meta:
        table = "vet"

class Specialty(Model):
    id   = fields.UUIDField(primary_key=True, default=uuid.uuid4)
    name = fields.CharField(max_length=80)

    class Meta:
        table = "specialty"

Schema generation

generate_schemas() splits the DDL on ; (respecting quoted strings, dollar-quoted blocks, and comments) and executes each statement individually:


await Tortoise.generate_schemas()

This is safe to call — the splitter is quote/comment/dollar-quote aware.

Basic usage


from tortoise import Tortoise
import uuid

# Create
owner = await Owner.create(name="John Doe", city="Seattle")
pet   = await Pet.create(name="Buddy", owner=owner)

# Query
found = await Pet.get(id=pet.id).prefetch_related("owner")
print(found.owner.name)

# Filter
owners = await Owner.filter(city="Seattle").all()

# Update
await Owner.filter(id=owner.id).update(city="Portland")

# Delete
await pet.delete()
await owner.delete()

# Close
await Tortoise.close_connections()

OCC retry pattern

Aurora DSQL uses optimistic concurrency control (OCC). Conflicting concurrent transactions raise a serialization failure with SQLSTATE 40001. The error message contains OC000 (data conflict: two transactions modified the same row) or OC001 (schema conflict: the transaction's cached schema catalog has become out of date). Wrap write operations in a retry loop:


import asyncio
import random
from aurora_dsql_asyncpg import is_occ_error  # requires aurora-dsql-python-connector >=0.2.7
# psycopg backend: from aurora_dsql_psycopg import is_occ_error

async def with_retry(coro_fn, max_retries=5, base_delay=0.1):
    for attempt in range(max_retries):
        try:
            return await coro_fn()
        except Exception as e:
            if not is_occ_error(e):
                raise
            if attempt == max_retries - 1:
                raise
            await asyncio.sleep(random.uniform(0, base_delay * 2 ** attempt))  # exponential backoff with jitter

# Usage
result = await with_retry(lambda: Owner.create(name="Jane", city="Seattle"))

aerich migrations

aerich works with the Aurora DSQL backends. Install aerich, and then run aerich init to point aerich at an Aurora DSQL backend:


PROMPT> pip install "aerich>=0.9.2"
PROMPT> aerich init -t myapp.config.TORTOISE_ORM


# myapp/config.py
TORTOISE_ORM = {
    "connections": {
        "default": {
            "engine": "aurora_dsql_tortoise.asyncpg",
            "credentials": {"host": CLUSTER_ENDPOINT, "user": CLUSTER_USER},
        }
    },
    "apps": {
        "models": {
            "models": ["myapp.models", "aerich.models", "aurora_dsql_tortoise.aerich"],
            "default_connection": "default",
        }
    },
}

Include the aurora_dsql_tortoise.aerich module in the models list so that migrations succeed on Aurora DSQL. It applies the Aurora DSQL-specific patches to aerich: UUID primary key for the Aerich table, RUN_IN_TRANSACTION=False, and single-DDL statement splitting.

SQL splitting

The adapter includes a split_sql(sql: str) -> list[str] utility. It correctly handles the following SQL elements when it splits statements:

Single-quoted strings (including escaped '' quotes)
Double-quoted identifiers
Dollar-quoted strings ($$...$$ and $tag$...$tag$)
Single-line comments (-- ...)
Multi-line comments (/* ... */)
Trailing/consecutive semicolons

The following Python code example splits a multi-statement SQL script into individual statements:


from aurora_dsql_tortoise.common.config import split_sql

statements = split_sql("""
    CREATE TABLE owner (id UUID PRIMARY KEY DEFAULT gen_random_uuid());
    CREATE TABLE pet   (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), owner_id UUID);
    CREATE INDEX ASYNC idx_pet_owner ON pet (owner_id);
""")
# → ['CREATE TABLE owner ...', 'CREATE TABLE pet ...', 'CREATE INDEX ASYNC ...']

Adapter features reference

Capability	Supported	Notes
asyncpg backend	Yes	`aurora_dsql_tortoise.asyncpg`
psycopg backend	Yes	`aurora_dsql_tortoise.psycopg`
`CREATE INDEX ASYNC`	Yes	All indexes use async creation
`REFERENCES` constraints	Yes	The adapter emits these in generated DDL. Aurora DSQL supports referential actions, match types, and deferrable foreign keys, and OCC-based enforcement raises `OC000`, which your application can retry
UUID primary keys	Yes	Recommended — `UUIDField(primary_key=True, default=uuid.uuid4)`
BigInt identity primary keys (PKs)	Yes	`BigIntField(primary_key=True)` → `GENERATED BY DEFAULT AS IDENTITY (CACHE 65536)`
IntField / SmallIntField PKs	No	Raises `ValueError` at schema generation
`generate_schemas()`	Yes	The adapter splits the SQL into single statements before execution
aerich migrations	Yes	Works with the standard aerich config
URL scheme	Yes	`dsql+asyncpg://` and `dsql+psycopg://`
URL parameter type casting	Yes	int, float, bool params cast automatically
`SELECT ... FOR UPDATE`	Yes	Adds the selected rows to the commit-time conflict check instead of locking them. The transaction that commits second fails with SQLSTATE `40001`, so retry the whole transaction. Added in v0.3.0
OCC error codes	`OC000` (data conflict), `OC001` (schema conflict)	SQLSTATE `40001`; catch and retry
Python version	3.10+	Not applicable

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

SQLAlchemy dialect

Accessing Aurora DSQL