README.md

Aurora DSQL Adapter for Tortoise ORM

An adapter for building Tortoise ORM applications with Amazon Aurora DSQL.

Requirements

• Tortoise ORM: 0.25 or later
• Python: 3.10 or later (installation guide)
• AWS Credentials: Valid credentials configured for IAM database authentication. The adapter generates a new auth token for each connection.
• Aerich (optional): 0.9.2 or later, if using Aerich for migrations

Getting Started

Install the adapter with your preferred async driver:

# asyncpg
pip install aurora-dsql-tortoise-orm[asyncpg]

# psycopg
pip install aurora-dsql-tortoise-orm[psycopg]

Configuration

Configure your connection using the DSQL engine:

TORTOISE_ORM = {
    "connections": {
        "default": {
            "engine": "aurora_dsql_tortoise.asyncpg",  # or "aurora_dsql_tortoise.psycopg"
            "credentials": {
                "host": "<cluster_id>.dsql.<region>.on.aws",
                "user": "admin",
            },
        }
    },
    "apps": {
        "models": {
            "models": ["your.models"],
            "default_connection": "default",
        }
    },
}

The adapter accepts all parameters supported by the underlying asyncpg or psycopg driver, as well as the Aurora DSQL Connector for Python.

Or use a connection URL (requires registering the backend first):

from aurora_dsql_tortoise import register_backends

register_backends()

TORTOISE_ORM = {
    "connections": {"default": "dsql+asyncpg://admin@<cluster_id>.dsql.<region>.on.aws/postgres"},
    "apps": {
        "models": {
            "models": ["your.models"],
            "default_connection": "default",
        }
    },
}

Defining Models

UUID primary keys are recommended for optimal performance with Aurora DSQL:

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=100)

    class Meta:
        table = "owner"

Aerich Migrations

For database migrations with Aerich, include the compatibility module:

TORTOISE_ORM = {
    "connections": {"default": {...}},
    "apps": {
        "models": {
            "models": [
                "your.models",
                "aerich.models",
                "aurora_dsql_tortoise.aerich_compat",
            ],
            "default_connection": "default",
        }
    },
}

The compatibility module patches Aerich to:

• Use UUID primary keys for migration tracking
• Execute DDL statements individually (DSQL transactions support only one DDL statement)
• Add incremental foreign keys with NOT VALID and wait for asynchronous validation to finish

Databases created with aurora-dsql-tortoise-orm 0.2.0 or earlier need a one-time foreign-key backfill migration. See Adapter Behavior.

SELECT FOR UPDATE

Use select_for_update() inside in_transaction() and bind the queryset to that connection. Non-key predicates are supported. Tortoise relation filters and select_related() emit LEFT OUTER JOIN; do not combine them with untargeted select_for_update(). Use explicit SQL with an INNER JOIN for joined locking queries. Aurora DSQL does not take blocking row locks; rows targeted by the locking clause participate in commit-time optimistic conflict checks. Each targeted row's primary key counts toward the 10 MiB transaction-size limit. Retry the whole transaction with backoff when a conflict returns SQLSTATE 40001, and keep external side effects outside the retried callback or make them idempotent.

Tortoise no_key=True falls back to regular FOR UPDATE because Aurora DSQL does not support FOR NO KEY UPDATE. update_or_create() is supported because its existing-row path uses SELECT FOR UPDATE; both create and update paths can encounter commit-time conflicts.

Features and Limitations

• Adapter Behavior - How the adapter modifies Tortoise ORM behavior for Aurora DSQL compatibility
• Known Issues - Known limitations and workarounds

Development

Install uv, then:

git clone https://github.com/awslabs/aurora-dsql-orms
cd aurora-dsql-orms/python/tortoise-orm
uv sync

Running Tests

⚠️ Running integration tests may result in charges to your AWS account.

Unit tests:

uv run unit

Integration tests (requires a DSQL cluster):

cp .env.example .env
# Edit .env with your cluster endpoint
uv run integration

Getting Help

• Open a support ticket with AWS Support
• Report bugs via GitHub Issues

Additional Resources

• Amazon Aurora DSQL Documentation
• Tortoise ORM Documentation
• Aerich Migration Tool

Opening Issues

If you encounter a bug, please search existing issues before opening a new one. GitHub issues are intended for bug reports and feature requests.

License

This library is licensed under the Apache 2.0 License.