Amazon Aurora DSQL dialect for SQLAlchemy

Introduction

The Aurora DSQL dialect for SQLAlchemy provides integration between SQLAlchemy ORM and Aurora DSQL. This dialect enables Python applications to leverage SQLAlchemy's powerful object-relational mapping capabilities while taking advantage of Aurora DSQL's distributed architecture and high availability.

Sample Application

There is an included sample application in examples/pet-clinic-app that shows how to use Aurora DSQL with SQLAlchemy. To run the included example please refer to the sample README.

Prerequisites

• Python 3.10 or higher
• SQLAlchemy 2.0.0 or higher
• One of the following drivers:
  • psycopg 3.2.0 or higher
  • psycopg2 2.9.0 or higher

Installation

Install the packages using the commands below:

pip install aurora-dsql-sqlalchemy

# driver installation (in case you opt for psycopg)
# DO NOT use pip install psycopg-binary
pip install "psycopg[binary]"

# driver installation (in case you opt for psycopg2)
pip install psycopg2-binary

Dialect Configuration

After installation, you can connect to an Aurora DSQL cluster using the create_dsql_engine helper function:

from aurora_dsql_sqlalchemy import create_dsql_engine

engine = create_dsql_engine(
    host="<CLUSTER_ENDPOINT>",
    user="<CLUSTER_USER>",
    driver="psycopg",  # or "psycopg2"
)

The helper function handles:

• IAM authentication via the Aurora DSQL Python Connector
• SSL configuration with certificate verification
• Direct SSL negotiation optimization (when supported by libpq >= 17)
• Connection pooling with sensible defaults

For more control, you can customize additional parameters:

engine = create_dsql_engine(
    host="<CLUSTER_ENDPOINT>",
    user="<CLUSTER_USER>",
    driver="psycopg",
    pool_size=10,
    max_overflow=20,
)

Note: Each connection has a maximum duration limit. See the Maximum connection duration time limit in the Cluster quotas and database limits in Amazon Aurora DSQL page.

SSL/TLS Configuration

Aurora DSQL requires TLS for all connections. Plaintext connections are not supported. Enabling certificate verification protects against on-path and impersonation attacks.

create_dsql_engine defaults to:

• sslmode="verify-full" - verifies the server certificate and hostname
• sslrootcert="system" - uses the default certificate authority (CA) trust defined by libpq’s TLS backend

See SSL Configuration for detailed setup instructions.

Best Practices

Primary Key Generation

UUID

Server-generated UUIDs are the recommended choice for primary key columns. The following column definition can be used to define a UUID primary key column.

Column(
    "id",
    UUID(as_uuid=True),
    primary_key=True,
    default=text('gen_random_uuid()')
)

gen_random_uuid() returns an UUID version 4 as the default value.

Sequence and identity-based keys

Sequence and identity-based keys are also supported in DSQL and can be used for integer primary keys. The following column definitions can be used to define sequence and identity-based keys column.

Column(
    "id",
    BIGINT, 
    primary_key=True,
    default=Sequence("bigint_seq")
)

Column(
    "id", 
    BigInteger, 
    primary_key=True,
    Identity(always=True)
)

Column(
    "id", 
    BigInteger, 
    primary_key=True, 
    autoincrement=True
)

The dialect uses a default CACHE parameter of 65536 in sequence and identity definitions. A different value can be passed directly in column definitions.

Sequence("bigint_seq", cache=<cache_size>)

Identity(always=True, cache=<cache_size>)

See the Working with sequences and identity columns page for more information.

Dialect Features

• Foreign Keys: The dialect emits foreign keys inline with CREATE TABLE. Aurora DSQL supports NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT, plus MATCH SIMPLE, MATCH FULL, and deferrable constraints. RESTRICT checks are always immediate. A foreign key added later with AddConstraint is automatically marked NOT VALID; validate existing rows separately with ALTER TABLE ASYNC <table> VALIDATE CONSTRAINT <name>, capture the returned job_id, and wait with CALL sys.wait_for_job(job_id). Cascading actions count toward transaction row-modification limits, and foreign-key conflicts can produce retryable serialization failures.

• Check Constraints: CHECK constraints are supported both inline at CREATE TABLE and when added to an existing table. Because DSQL requires a CHECK constraint added via ALTER TABLE to be marked NOT VALID, the dialect automatically appends NOT VALID to ADD CONSTRAINT ... CHECK statements. To validate the constraint against rows that already exist in the table, run ALTER TABLE ASYNC <table> VALIDATE CONSTRAINT <name> as a separate statement (for example, op.execute(...) in an Alembic migration). The constraint is enforced on all new writes immediately; validation of existing rows runs as an asynchronous DSQL DDL job. See ALTER TABLE for details.

• Index Creation: The dialect uses CREATE INDEX ASYNC and CREATE UNIQUE INDEX ASYNC commands. See the Asynchronous indexes in Aurora DSQL page for more information.

  The following parameters are used for customizing index creation

  • auroradsql_include - specifies which columns to includes in an index by using the INCLUDE clause:

    Index(
        "include_index",
        table.c.id,
        auroradsql_include=['name', 'email']
    )
    

    Generated SQL output:

    CREATE INDEX ASYNC include_index ON table (id) INCLUDE (name, email)
    

  • auroradsql_nulls_not_distinct - controls how NULL values are treated in unique indexes:

    Index(
        "idx_name",
        table.c.column,
        unique=True,
        auroradsql_nulls_not_distinct=True
    )
    

    Generated SQL output:

    CREATE UNIQUE INDEX idx_name ON table (column) NULLS NOT DISTINCT
    

• Index Interface Limitation: NULLS FIRST | LAST - SQLalchemy's Index() interface does not have a way to pass in the sort order of null and non-null columns. (Default: NULLS LAST). If NULLS FIRST is required, please refer to the syntax as specified in Asynchronous indexes in Aurora DSQL and execute the corresponding SQL query directly in SQLAlchemy.

• Psycopg (psycopg3) support: When connecting to DSQL using the default postgresql dialect with psycopg, a SAVEPOINT error occurs during initialization. The DSQL dialect addresses this by disabling SAVEPOINT during connection.

For the full list of Aurora DSQL SQL compatibility details, see the PostgreSQL compatibility reference.

Developer instructions

Instructions on how to build and test the dialect are available in the Developer Instructions.

Security

See CONTRIBUTING for more information.

License

This project is licensed under the Apache-2.0 License.