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 disables foreign key constraint generation. Referential integrity should be maintained at the application level.
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.

FilesExpand file tree

README.md

Latest commit

History