Metadata-Version: 2.4
Name: aurora-dsql-sqlalchemy
Version: 1.2.0
Summary: Amazon Aurora DSQL dialect for SQLAlchemy
Project-URL: Repository, https://github.com/awslabs/aurora-dsql-orms
Project-URL: Documentation, https://github.com/awslabs/aurora-dsql-orms/tree/main/python/sqlalchemy
Author: Amazon Web Services
License-Expression: Apache-2.0
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Python: >=3.10
Requires-Dist: aurora-dsql-python-connector>=0.1.0
Requires-Dist: sqlalchemy>=2.0.0
Provides-Extra: psycopg
Requires-Dist: psycopg[binary]>=3.2.0; extra == 'psycopg'
Provides-Extra: psycopg2
Requires-Dist: psycopg2-binary>=2.9.0; extra == 'psycopg2'
Description-Content-Type: text/markdown

# Amazon Aurora DSQL dialect for SQLAlchemy

[![GitHub](https://img.shields.io/badge/github-awslabs/aurora--dsql--orms-blue?logo=github)](https://github.com/awslabs/aurora-dsql-orms)
[![License](https://img.shields.io/badge/license-Apache--2.0-brightgreen)](https://github.com/awslabs/aurora-dsql-orms/blob/main/LICENSE)
[![PyPI - Version](https://img.shields.io/pypi/v/aurora-dsql-sqlalchemy)](https://pypi.org/project/aurora-dsql-sqlalchemy)
[![Discord chat](https://img.shields.io/discord/1435027294837276802.svg?logo=discord)](https://discord.com/invite/nEF6ksFWru)

## 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](https://github.com/awslabs/aurora-dsql-orms/blob/python/sqlalchemy/v1.2.0/python/sqlalchemy/examples/pet-clinic-app) that shows how to use Aurora DSQL
with SQLAlchemy. To run the included example please refer to the [sample README](https://github.com/awslabs/aurora-dsql-orms/blob/python/sqlalchemy/v1.2.0/python/sqlalchemy/examples/pet-clinic-app#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:

```bash
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:

```python
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:

```python
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](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html) 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](https://github.com/awslabs/aurora-dsql-orms/blob/python/sqlalchemy/v1.2.0/python/sqlalchemy/docs/SSL_CONFIGURATION.md) 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.

```python
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.

```python
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.

```python

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

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

```

See the [Working with sequences and identity columns](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/sequences-identity-columns-working-with.html) page for more information.

## Dialect Features

- **Foreign Keys**: The dialect disables foreign key constraint generation. Referential integrity should be maintained at the application level.
- **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](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-sql-subsets.html#alter-table-syntax-support) for details.
- **Index Creation**: The dialect uses `CREATE INDEX ASYNC` and `CREATE UNIQUE INDEX ASYNC` commands. See the [Asynchronous indexes in Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-create-index-async.html) 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:

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

    Generated SQL output:

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

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

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

    Generated SQL output:

    ```sql
    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](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-create-index-async.html) 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](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility.html).

## Developer instructions

Instructions on how to build and test the dialect are available in the [Developer Instructions](https://github.com/awslabs/aurora-dsql-orms/blob/python/sqlalchemy/v1.2.0/python/sqlalchemy/aurora_dsql_sqlalchemy/README.md).

## Security

See [CONTRIBUTING](https://github.com/awslabs/aurora-dsql-orms/blob/python/sqlalchemy/v1.2.0/python/sqlalchemy/../../CONTRIBUTING.md#security-issue-notifications) for more information.

## License

This project is licensed under the Apache-2.0 License.
