# Aurora DSQL adapter for Tortoise ORM
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm"></a>

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](https://github.com/awslabs/aurora-dsql-orms/tree/main/python/tortoise-orm) and the [Tortoise ORM pet clinic example application](https://github.com/awslabs/aurora-dsql-orms/tree/main/python/tortoise-orm/example) on the GitHub website.

## About the adapter
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-about-the-adapter"></a>

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?
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-what-is-aurora-dsql-authentication"></a>

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](#SECTION_program-with-dsql-adapter-for-tortoise-orm-ssl-configuration).

### Features
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-features"></a>

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](working-with-foreign-key-constraints.md).
+ **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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-prerequisites"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-installation"></a>

```
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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-configuration"></a>

### Tortoise.init() — programmatic
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-tortoise-init-programmatic"></a>

```
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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-ssl-configuration"></a>

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](configure-root-certificates.md). 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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-url-format"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-connection-duration"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-schema-per-user"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-defining-models"></a>

### UUID primary key (recommended)
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-uuid-primary-key-recommended"></a>

```
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)
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-bigint-identity-column-opt-in"></a>

```
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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-foreign-key-relations"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-many-to-many"></a>

```
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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-schema-generation"></a>

`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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-basic-usage"></a>

```
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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-occ-retry-pattern"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-aerich-migrations"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-sql-splitting"></a>

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
<a name="SECTION_program-with-dsql-adapter-for-tortoise-orm-adapter-features-reference"></a>


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