# Amazon Aurora DSQL dialect for SQLAlchemy
<a href="https://pypi.org/project/aurora-dsql-sqlalchemy"><img alt="PyPI - Version" src="https://img.shields.io/pypi/v/aurora-dsql-sqlalchemy?style=for-the-badge"></a>
## 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-sqlalchemy/tree/main/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-sqlalchemy/tree/main/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 SQLAlchemy's `create_engine`:
The connection parameter `auroradsql+psycopg` specifies to use the `auroradsql` dialect with the driver `psycopg` (psycopg3).
To use the driver `psycopg2`, change the connection parameter to `auroradsql+psycopg2`.
```python
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
url = URL.create(
"auroradsql+psycopg",
username=<CLUSTER_USER>,
host=<CLUSTER_ENDPOINT>,
database='postgres',
)
engine = create_engine(
url,
connect_args={"sslmode": "verify-full", "sslrootcert": "<ROOT_CERT_PATH>"},
pool_size=5,
max_overflow=10
)
```
**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.
## Best Practices
### Primary Key Generation
SQLAlchemy applications connecting to Aurora DSQL should use UUID for the primary key column since auto-incrementing integer keys (sequences or serial) are not supported in DSQL. The following column definition can be used to define an 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.
## Dialect Features and Limitations
- **Column Metadata**: The dialect fixes an issue related to `"datatype json not supported"` when calling SQLAlchemy's metadata() API.
- **Foreign Keys**: Aurora DSQL does not support foreign key constraints. The dialect disables these constraints, but be aware that referential integrity must be maintained at the application level.
- **Index Creation**: Aurora DSQL does not support `CREATE INDEX` or `CREATE UNIQUE INDEX` commands. The dialect instead 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, an unsupported `SAVEPOINT` error occurs. The DSQL dialect addresses this issue by disabling the `SAVEPOINT` during connection.
## Developer instructions
Instructions on how to build and test the dialect are available in the [Developer Instructions](https://github.com/awslabs/aurora-dsql-sqlalchemy/tree/main/aurora_dsql_sqlalchemy#readme).
## Security
See [CONTRIBUTING](https://github.com/awslabs/aurora-dsql-sqlalchemy/blob/main/CONTRIBUTING.md#security-issue-notifications) for more information.
## License
This project is licensed under the Apache-2.0 License.
Raw data
{
"_id": null,
"home_page": null,
"name": "aurora-dsql-sqlalchemy",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": null,
"author": "Amazon Web Services",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/9f/36/d8065b6d3fa329a323cf05d67ede899719490998a5abf820bca66b8f6cbb/aurora_dsql_sqlalchemy-1.0.2.tar.gz",
"platform": null,
"description": "# Amazon Aurora DSQL dialect for SQLAlchemy\n\n<a href=\"https://pypi.org/project/aurora-dsql-sqlalchemy\"><img alt=\"PyPI - Version\" src=\"https://img.shields.io/pypi/v/aurora-dsql-sqlalchemy?style=for-the-badge\"></a>\n\n## Introduction\n\nThe Aurora DSQL dialect for SQLAlchemy provides integration between SQLAlchemy ORM and Aurora DSQL. This dialect enables\nPython applications to leverage SQLAlchemy's powerful object-relational mapping capabilities while taking advantage of\nAurora DSQL's distributed architecture and high availability.\n\n## Sample Application\n\nThere is an included sample application in [examples/pet-clinic-app](https://github.com/awslabs/aurora-dsql-sqlalchemy/tree/main/examples/pet-clinic-app) that shows how to use Aurora DSQL\nwith SQLAlchemy. To run the included example please refer to the [sample README](https://github.com/awslabs/aurora-dsql-sqlalchemy/tree/main/examples/pet-clinic-app#readme).\n\n## Prerequisites\n\n- Python 3.10 or higher\n- SQLAlchemy 2.0.0 or higher\n- One of the following drivers:\n - psycopg 3.2.0 or higher\n - psycopg2 2.9.0 or higher\n\n## Installation\n\nInstall the packages using the commands below:\n\n```bash\npip install aurora-dsql-sqlalchemy\n\n# driver installation (in case you opt for psycopg)\n# DO NOT use pip install psycopg-binary\npip install \"psycopg[binary]\"\n\n# driver installation (in case you opt for psycopg2)\npip install psycopg2-binary\n```\n\n## Dialect Configuration\n\nAfter installation, you can connect to an Aurora DSQL cluster using SQLAlchemy's `create_engine`:\n\nThe connection parameter `auroradsql+psycopg` specifies to use the `auroradsql` dialect with the driver `psycopg` (psycopg3).\nTo use the driver `psycopg2`, change the connection parameter to `auroradsql+psycopg2`.\n\n```python\nfrom sqlalchemy import create_engine\nfrom sqlalchemy.engine.url import URL\n\nurl = URL.create(\n \"auroradsql+psycopg\",\n username=<CLUSTER_USER>,\n host=<CLUSTER_ENDPOINT>,\n database='postgres',\n)\n\nengine = create_engine(\n url,\n connect_args={\"sslmode\": \"verify-full\", \"sslrootcert\": \"<ROOT_CERT_PATH>\"},\n pool_size=5,\n max_overflow=10\n)\n```\n\n**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.\n\n## Best Practices\n\n### Primary Key Generation\n\nSQLAlchemy applications connecting to Aurora DSQL should use UUID for the primary key column since auto-incrementing integer keys (sequences or serial) are not supported in DSQL. The following column definition can be used to define an UUID primary key column.\n\n```python\nColumn(\n \"id\",\n UUID(as_uuid=True),\n primary_key=True,\n default=text('gen_random_uuid()')\n)\n```\n\n`gen_random_uuid()` returns an UUID version 4 as the default value.\n\n## Dialect Features and Limitations\n\n- **Column Metadata**: The dialect fixes an issue related to `\"datatype json not supported\"` when calling SQLAlchemy's metadata() API.\n- **Foreign Keys**: Aurora DSQL does not support foreign key constraints. The dialect disables these constraints, but be aware that referential integrity must be maintained at the application level.\n- **Index Creation**: Aurora DSQL does not support `CREATE INDEX` or `CREATE UNIQUE INDEX` commands. The dialect instead 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.\n\n The following parameters are used for customizing index creation\n\n - `auroradsql_include` - specifies which columns to includes in an index by using the `INCLUDE` clause:\n\n ```python\n Index(\n \"include_index\",\n table.c.id,\n auroradsql_include=['name', 'email']\n )\n ```\n\n Generated SQL output:\n\n ```sql\n CREATE INDEX ASYNC include_index ON table (id) INCLUDE (name, email)\n ```\n\n - `auroradsql_nulls_not_distinct` - controls how `NULL` values are treated in unique indexes:\n\n ```python\n Index(\n \"idx_name\",\n table.c.column,\n unique=True,\n auroradsql_nulls_not_distinct=True\n )\n ```\n\n Generated SQL output:\n\n ```sql\n CREATE UNIQUE INDEX idx_name ON table (column) NULLS NOT DISTINCT\n ```\n\n- **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.\n- **Psycopg (psycopg3) support**: When connecting to DSQL using the default postgresql dialect with psycopg, an unsupported `SAVEPOINT` error occurs. The DSQL dialect addresses this issue by disabling the `SAVEPOINT` during connection.\n\n## Developer instructions\n\nInstructions on how to build and test the dialect are available in the [Developer Instructions](https://github.com/awslabs/aurora-dsql-sqlalchemy/tree/main/aurora_dsql_sqlalchemy#readme).\n\n## Security\n\nSee [CONTRIBUTING](https://github.com/awslabs/aurora-dsql-sqlalchemy/blob/main/CONTRIBUTING.md#security-issue-notifications) for more information.\n\n## License\n\nThis project is licensed under the Apache-2.0 License.\n",
"bugtrack_url": null,
"license": "Apache License 2.0",
"summary": "Amazon Aurora DSQL dialect for SQLAlchemy",
"version": "1.0.2",
"project_urls": {
"Repository": "https://github.com/awslabs/aurora-dsql-sqlalchemy"
},
"split_keywords": [],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "c6b06489fdd72517f21d56fd5563d829e39bf3f0a72648d5e209d4dbeb0e6184",
"md5": "a2d738da4775b9ff638f3fb9fb1eb789",
"sha256": "b1cdd3b78d7ce1ed02757abc33b90fcfb213ef2baf1e0ea0d80732899495d411"
},
"downloads": -1,
"filename": "aurora_dsql_sqlalchemy-1.0.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "a2d738da4775b9ff638f3fb9fb1eb789",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 11374,
"upload_time": "2025-07-22T20:16:18",
"upload_time_iso_8601": "2025-07-22T20:16:18.762070Z",
"url": "https://files.pythonhosted.org/packages/c6/b0/6489fdd72517f21d56fd5563d829e39bf3f0a72648d5e209d4dbeb0e6184/aurora_dsql_sqlalchemy-1.0.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "9f36d8065b6d3fa329a323cf05d67ede899719490998a5abf820bca66b8f6cbb",
"md5": "74741611a1fee825b6960620a78559a3",
"sha256": "6b00983f49ecb55f15282ad4809c65c5705303d4c44880450729c8d5460d773c"
},
"downloads": -1,
"filename": "aurora_dsql_sqlalchemy-1.0.2.tar.gz",
"has_sig": false,
"md5_digest": "74741611a1fee825b6960620a78559a3",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 12374,
"upload_time": "2025-07-22T20:16:19",
"upload_time_iso_8601": "2025-07-22T20:16:19.824844Z",
"url": "https://files.pythonhosted.org/packages/9f/36/d8065b6d3fa329a323cf05d67ede899719490998a5abf820bca66b8f6cbb/aurora_dsql_sqlalchemy-1.0.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-07-22 20:16:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "awslabs",
"github_project": "aurora-dsql-sqlalchemy",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "aurora-dsql-sqlalchemy"
}
JSON
