Skip to content

asyncpg

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

asyncpg

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
src
src
 
 
test
test
 
 
README.md
README.md
 
 
pyproject.toml
pyproject.toml
 
 
requirements.txt
requirements.txt
 
 

README.md

Aurora DSQL with asyncpg

This example demonstrates how to use the Aurora DSQL Python Connector with asyncpg to connect to Amazon Aurora DSQL clusters and perform basic database operations.

Aurora DSQL is a distributed SQL database service that provides high availability and scalability for your PostgreSQL-compatible applications. Asyncpg is a popular PostgreSQL database library for Python that allows you to interact with PostgreSQL databases using Python code.

About the code example

The example demonstrates a flexible connection approach that works for both admin and non-admin users:

  • When connecting as an admin user, the example uses the public schema and generates an admin authentication token.
  • When connecting as a non-admin user, the example uses a custom myschema schema and generates a standard authentication token.

The code automatically detects the user type and adjusts its behavior accordingly.

⚠️ Important

  • Running this code might result in charges to your AWS account.
  • We recommend that you grant your code least privilege. At most, grant only the minimum permissions required to perform the task. For more information, see Grant least privilege.
  • This code is not tested in every AWS Region. For more information, see AWS Regional Services.

TLS connection configuration

This example uses direct TLS connections where supported, and verifies the server certificate is trusted. Verified SSL connections should be used where possible to ensure data security during transmission.

  • Driver versions following the release of PostgreSQL 17 support direct TLS connections, bypassing the traditional PostgreSQL connection preamble
  • Direct TLS connections provide improved connection performance and enhanced security
  • Not all PostgreSQL drivers support direct TLS connections yet, or only in recent versions following PostgreSQL 17
  • Ensure your installed driver version supports direct TLS negotiation, or use a version that is at least as recent as the one used in this sample
  • If your driver doesn't support direct TLS connections, you may need to use the traditional preamble connection instead

Prerequisites

Download the Amazon root certificate from the official trust store

Download the Amazon root certificate from the official trust store:

wget https://www.amazontrust.com/repository/AmazonRootCA1.pem -O root.pem

Set up environment for examples

  1. Create and activate a Python virtual environment:
python3 -m venv .venv
source .venv/bin/activate  # Linux, macOS
# or
.venv\Scripts\activate     # Windows
  1. Install the required packages for running the examples:
pip install -e .

# Install optional dependencies for tests
pip install -e ".[test]"

Run the code

The example demonstrates the following operations:

  • Opening a connection to an Aurora DSQL cluster
  • Creating a table
  • Inserting and querying data

The example is designed to work with both admin and non-admin users:

  • When run as an admin user, it uses the public schema
  • When run as a non-admin user, it uses the myschema schema

Note: running the example will use actual resources in your AWS account and may incur charges.

The connetion pool examples demonstrate:

  • Creating a connection pool for Aurora DSQL
  • Using async context managers for connection management
  • Performing database operations through the pool
  • Running multiple concurrent database operations
  • Using asyncio.gather() for parallel execution
  • Proper resource management with connection pools

Set environment variables for your cluster details:

# e.g. "admin"
export CLUSTER_USER="<your user>"

# e.g. "foo0bar1baz2quux3quuux4.dsql.us-east-1.on.aws"
export CLUSTER_ENDPOINT="<your endpoint>"

Run the example:

# Run example directly
python src/example.py

# Run example using pytest
pytest ./test/test_example.py

# Run all using pytest
pytest ./test

The example contains comments explaining the code and the operations being performed.

Connection defaults

The connector automatically handles the following parameters:

Parameter Default Notes
database postgres Aurora DSQL's default database
port 5432 Standard PostgreSQL port
region Extracted from endpoint Parsed from *.dsql.<region>.on.aws

You can override any of these defaults by explicitly passing them in your connection parameters.

Additional resources

Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.

SPDX-License-Identifier: MIT-0