Skip to content
@aws/nx-plugin
Blog

Python Relational Database

This generator creates a new Python relational database project backed by Amazon Aurora (PostgreSQL or MySQL), SQLModel for data modelling, and Alembic for schema migrations. It generates the application code and infrastructure needed to provision and manage a database using AWS CDK or Terraform, with declarative schema definition, automatic migration deployment, and a database client.

Usage

Section titled “Usage”

Generate a Relational Database

Section titled “Generate a Relational Database”

Run this generator@aws/nx-plugin:py#rdb

pnpm nx g @aws/nx-plugin:py#rdb
Build your command10

Required

You can also perform a dry-run to see what files would be changed

Options

Section titled “Options”
Generator Options10 options
nameRequiredstring

Name of the database project to generate

directorystringDefault: packages

The directory to store the application in.

infraenumDefault: aurora

Relational database service to provision.

auroranone
engineenumDefault: postgres

Database engine to use with the selected service.

postgresmysql
frameworkenumDefault: sqlmodel

ORM framework to use for the generated project.

sqlmodel
iacenumDefault: inherit

The preferred IaC provider. By default this is inherited from your initial selection.

inheritcdkterraform
subDirectorystring

The sub directory the project is placed in. By default this is the project name.

databaseUserstringDefault: dbadmin

Database admin username. Defaults to 'dbadmin'.

databaseNamestring

Initial database name. Defaults to the project name.

preferInstallDependenciesbooleanDefault: true

Whether to prefer installing dependencies after the generator runs. Set to false to defer installing when batching multiple generators (an install still runs if needed so subsequent generators can compute the Nx project graph); install once at the end.

Generator Output

Section titled “Generator Output”

The generator creates the following project structure in the <directory>/<name> directory:

  • Directory<name>
    • __init__.py Package exports
    • connection.py Database engine and session factory with IAM authentication
    • utils.py Runtime config and local development helpers
    • migration_handler.py Lambda handler that runs Alembic migrations during deployment
    • create_db_user_handler.py Lambda handler that creates the application database user during deployment
    • Directorymodels
      • __init__.py Model exports, imported by Alembic to discover your tables
      • example.py Example SQLModel table definition
  • Directorymigrations
    • versions Alembic-generated migration scripts
    • env.py Alembic environment (connects to the database)
    • script.py.mako Alembic migration script template
  • Directorytests
    • __init__.py Module initialisation
    • conftest.py Test configuration
    • test_noop.py Placeholder test
  • alembic.ini Alembic configuration
  • config.json Local development connection details and runtime config key
  • Dockerfile.migration Container image for the migration handler
  • Dockerfile.create-db-user Container image for the create-db-user handler
  • project.json Project configuration and build targets
  • pyproject.toml Packaging configuration file used by UV
  • README.md Project README
  • .python-version Contains the project’s Python version
  • .gitignore Files excluded from version control

Local development scripts are shared across all database projects and generated into packages/common/scripts/:

  • Directorypackages/common/scripts/src/rdb
    • pull-image.ts Pulls the database container image
    • start-container.ts Starts a local database container
    • wait-for-postgres-db.ts Waits for the local database to be ready (PostgreSQL)
    • wait-for-mysql-db.ts Waits for the local database to be ready (MySQL)

Infrastructure

Section titled “Infrastructure”

Since this generator vends infrastructure as code based on your chosen iac, it will create a project in packages/common which includes the relevant CDK constructs or Terraform modules.

The common infrastructure as code project is structured as follows:

  • Directorypackages/common/constructs
    • Directorysrc
      • Directoryapp/ Constructs for infrastructure specific to a project/generator
        • …
      • Directorycore/ Generic constructs which are reused by constructs in app
        • …
      • index.ts Entry point exporting constructs from app
    • project.json Project build targets and configuration
  • Directorypackages/common/constructs/src
    • Directoryapp
      • Directorydbs
        • <name>.ts Infrastructure specific to your database
    • Directorycore
      • Directoryrdb
        • aurora.ts Generic Aurora database construct

Architecture

Section titled “Architecture”

The deployed database has the following architecture. By default, an Amazon RDS Proxy sits in front of the Aurora cluster to pool connections and to enable IAM authentication — see Disable RDS Proxy for the alternative. The architecture is the same whether you select the PostgreSQL or MySQL engine; only the Aurora engine flavor differs.

Loading the diagram…

Local Development

Section titled “Local Development”

Data Modelling

Section titled “Data Modelling”

The generated project uses SQLModel to define your database schema. The workflow is model-first: add or update SQLModel table classes under your database project’s <name>/models/ directory, then generate a migration from those model changes.

Example model:

packages/my_db/my_db/models/example.py
from sqlalchemy import Column, String
from sqlmodel import Field, SQLModel




class ExampleModel(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(sa_column=Column(String(255), nullable=False))
    description: str | None = Field(default=None, sa_column=Column(String(255), nullable=True))

Import your models in <name>/models/__init__.py so Alembic can discover them during autogeneration.

Creating Migrations

Section titled “Creating Migrations”

After adding or updating models, use Alembic to generate and apply migration scripts. The generated alembic target automatically starts a local database container before running:

Terminal window
pnpm nx run <project>:alembic revision --autogenerate -m "describe your change"

This generates a new migration script under migrations/versions/. Review the generated script before applying it.

Apply the migration to your local database:

Terminal window
pnpm nx run <project>:migrate

When you deploy the AWS stack, the generated infrastructure automatically applies the generated migrations to the deployed database.

Applying Existing Migrations

Section titled “Applying Existing Migrations”

When you pull migration files created by other developers, apply them to your local database:

Terminal window
pnpm nx run <project>:migrate

Running Alembic Commands

Section titled “Running Alembic Commands”

The generated alembic target exposes the Alembic CLI, so you can run any Alembic command against the local database. See the Alembic command reference for available commands.

Terminal window
pnpm nx run <project>:alembic <alembic-command>

Stopping the Local Database

Section titled “Stopping the Local Database”

Stopping dev (e.g. with Ctrl+C) automatically removes the local database container, but preserves the named volume so your data persists across restarts.

Connecting to the Database

Section titled “Connecting to the Database”

Import session_context from your database package and use it as an async context manager to obtain an AsyncSession:

from sqlmodel import select


from my_scope_my_db import session_context
from my_scope_my_db.models.example import ExampleModel


async def example():
    async with session_context() as session:
        results = (await session.execute(select(ExampleModel))).scalars().all()

The database client automatically:

  • Retrieves database configuration from AWS AppConfig at runtime
  • Generates temporary authentication tokens via boto3 RDS Signer for IAM authentication
  • Establishes TLS connections using ssl.create_default_context()

Deploying your Database

Section titled “Deploying your Database”

The relational database generator creates CDK or Terraform infrastructure based on your selected iac.

The CDK construct is created in common/constructs. Example usage:

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


export class ApplicationStack extends Stack {
  constructor(scope: Construct, id: string, props?: StackProps) {
    super(scope, id, props);
    ...
    const db = new MyDatabase(this, 'Db', {
      vpc,
      vpcSubnets: {
        subnetType: SubnetType.PRIVATE_ISOLATED,
      }
    });
  }
}

This provisions an Aurora cluster with RDS Proxy, admin credentials, application database user, runtime config registration, and migration handler.

The generated infrastructure creates two database users:

  • Admin user - Created during cluster provisioning with credentials stored in AWS Secrets Manager
  • Application user - Created via a Lambda custom resource with IAM authentication enabled and DML privileges (SELECT, INSERT, UPDATE, DELETE) on the application database

The application user is automatically created with a random name and IAM authentication. The generated database client is already configured to authenticate as this user using short-lived RDS tokens, so your application code never handles database passwords.

Your VPC should include public subnets, private subnets with egress, and private isolated subnets. The database can run in private isolated subnets, while application Lambda functions should run in private subnets with egress so they can reach AWS services such as AppConfig.

Click here for an example VPC configuration.

Use the connection generator to connect a project to this database — see the connection guide for the relevant compute type (e.g. FastAPI, MCP server, agent) for the infrastructure wiring required to reach it.

Image Scanning

Section titled “Image Scanning”

The Docker image built for this project can be scanned for vulnerabilities using Trivy, running from the ECR-hosted Trivy image.

A trivy target is added to your project which scans the built image and exits non-zero if any HIGH or CRITICAL severity vulnerability is found. The generated Dockerfile uses a base image with no known fixable vulnerabilities of these severities at time of generation, and upgrades bundled tooling (such as npm) to keep it that way.

The scan uses the same container engine as your image build (docker or finch), so no additional tooling is required. The scan is not cached, since the image it reads lives in the container engine rather than on disk — so it always scans the real image, and fails loudly rather than reporting a cached pass for an image that is no longer there. Each run therefore takes tens of seconds per image and refreshes Trivy’s vulnerability database, so it needs network access. The vended trivy root script scans every image in the workspace:

Terminal window
pnpm trivy

Suppressing Trivy Findings

Section titled “Suppressing Trivy Findings”

There may be instances where you want to suppress a specific vulnerability, for example when no fix is yet available and you have assessed the risk as acceptable.

Add the vulnerability ID (one per line) to the .trivyignore file in the root of your project (i.e. next to your project.json):

.trivyignore
# node-tar arbitrary file write - not exploitable in our usage
CVE-2024-XXXXX

For more details on filtering findings, refer to the Trivy filtering documentation.

RDS Proxy Configuration

Section titled “RDS Proxy Configuration”

The generated infrastructure includes an RDS Proxy by default, which sits between your application and the Aurora cluster. RDS Proxy provides several benefits:

  • Connection pooling - Maintains a pool of database connections that can be shared across application instances, reducing the overhead of establishing new connections
  • Connection resilience - Automatically handles failovers and reconnects during Aurora instance replacements or maintenance
  • IAM authentication - Supports IAM-based database authentication, eliminating the need to manage database credentials in your application code
  • Improved security - Enforces TLS encryption for all connections

Disable RDS Proxy

Section titled “Disable RDS Proxy”

You can disable the RDS proxy as follows:

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  enableRdsProxy: false,
});

When RDS Proxy is disabled, your application connects directly to the Aurora cluster endpoint.

SSL Requirements When Connecting Without RDS Proxy

Section titled “SSL Requirements When Connecting Without RDS Proxy”

The Amazon RDS CA bundle must be in the runtime’s system trust store.

Container Runtimes
Section titled “Container Runtimes”
ADD https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem /etc/pki/ca-trust/source/anchors/global-bundle.pem
RUN update-ca-trust
Zip Lambda Functions
Section titled “Zip Lambda Functions”

For zip-deployed Lambda functions (such as a py#api FastAPI), the Amazon Linux 2023 Lambda execution environment’s built-in CA trust store includes the Amazon Root CAs used by RDS.

When using RDS Proxy, you do not need to configure the RDS CA bundle in the runtime that connects to the database.

Cluster Instances

Section titled “Cluster Instances”

Configure the writer and reader instances for your Aurora cluster.

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  writer: ClusterInstance.serverlessV2('writer'),
  readers: [ClusterInstance.serverlessV2('reader')],
});

Serverless Capacity

Section titled “Serverless Capacity”

Control Aurora Serverless v2 scaling limits to match your workload. Both providers default to a minimum of 0.5 ACUs and a maximum of 4.

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  serverlessV2MinCapacity: 0.5,
  serverlessV2MaxCapacity: 8,
});

Engine Version

Section titled “Engine Version”

Pin a specific Aurora engine version.

By default, the generated local database container image matches the default Aurora engine version. If you change the Aurora engine version, it’s recommended to also use a matching local container image version for maximum compatibility. See the AWS release notes for Aurora PostgreSQL versions and Aurora MySQL versions to identify the corresponding community database version.

The local database image is configured in the localDev.image field of the generated config.json file in your database project root. Update that value when you change engine versions.

engine = postgres
packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  engineVersion: AuroraPostgresEngineVersion.VER_17_7,
});
engine = mysql
packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  engineVersion: AuroraMysqlEngineVersion.VER_3_12_0,
});

Deletion Protection

Section titled “Deletion Protection”

The Aurora cluster is protected by two independent guards, so that turning off either one alone cannot delete your data:

  • deletionProtection, enforced by RDS.
  • RemovalPolicy.RETAIN, enforced by CloudFormation, which leaves the cluster in place when it is removed from the stack.

Deleting the Database

Section titled “Deleting the Database”

You can disable protection for environments where database deletion is expected, such as short-lived development or preview stacks.

packages/infra/src/stacks/application-stack.ts
import { RemovalPolicy } from 'aws-cdk-lib';
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  deletionProtection: false,
  removalPolicy: RemovalPolicy.DESTROY,
});

Removal Policy

Section titled “Removal Policy”

The CDK construct retains the Aurora cluster by default (removalPolicy: RemovalPolicy.RETAIN). Change this when you want CDK stack deletion to snapshot or destroy the cluster instead.

When using RemovalPolicy.DESTROY, deletion protection must also be disabled before the cluster can be deleted.

packages/infra/src/stacks/application-stack.ts
import { RemovalPolicy } from 'aws-cdk-lib';
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  removalPolicy: RemovalPolicy.SNAPSHOT,
});

For an ephemeral environment where the database should be deleted with the stack:

packages/infra/src/stacks/application-stack.ts
import { RemovalPolicy } from 'aws-cdk-lib';
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  deletionProtection: false,
  removalPolicy: RemovalPolicy.DESTROY,
});

Logging and Monitoring

Section titled “Logging and Monitoring”
engine = postgres

The postgresql log is exported for Aurora PostgreSQL, with logging scoped to DDL statements only (log_statement=ddl) so statement parameter values are never logged — as long as each statement is sent on its own. log_statement=ddl logs the entire raw text of a multi-statement batch (e.g. a single psql -c "a;b;c" call) verbatim if any statement in it is DDL, including any DML values in that same batch.

engine = mysql

The audit and error logs are exported for Aurora MySQL — general and slowquery are deliberately excluded since they log full statement text, including DML values. Advanced Auditing is scoped to connections and DDL (server_audit_events=CONNECT,QUERY_DDL), so statement parameter values are never logged.

Performance Insights is enabled on the Aurora writer instance by default (encrypted with the cluster’s KMS key). Aurora engine logs are also exported to CloudWatch Logs by default, configured to surface schema-level activity without leaking row data.

Exported log volume — and so CloudWatch Logs ingestion cost — scales with schema changes and connections rather than query traffic, since the log types that carry full statement text are excluded. On Aurora MySQL the CONNECT audit event is emitted per connection, so workloads that open a connection per request produce more than ones that pool.

Disable log export per database if not required:

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  enableCloudwatchLogs: false,
  enablePerformanceInsights: false,
});

Encryption Key Rotation

Section titled “Encryption Key Rotation”

The KMS key used to encrypt the Aurora cluster and its credentials secret has automatic key rotation enabled by default. Disable it if your security policy manages rotation externally.

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', {
  ...
  enableKeyRotation: false,
});

Admin Credentials

Section titled “Admin Credentials”

The Aurora admin (master) user’s password is generated and rotated by Aurora itself in AWS Secrets Manager, using RDS-managed master user passwords. Your infrastructure code never receives the password, so it cannot leak into a CloudFormation template or Terraform state file. Aurora rotates the secret every 7 days without any rotation function to deploy or maintain.

The secret is encrypted with the same customer-managed KMS key as the cluster.

Your application never uses these credentials. It connects as a least-privilege database user using IAM authentication — only the migration and create-db-user handlers read the admin secret, and each is granted access to just that secret and its KMS key.

The admin secret is exposed as secret.secretArn on the underlying cluster, and grantSecretRead grants a consumer read access to it:

packages/infra/src/stacks/application-stack.ts
import { MyDatabase } from '@my-scope/common-constructs';


const db = new MyDatabase(this, 'Db', { ... });


db.grantSecretRead(myFunction);

Connections

Section titled “Connections”

Use the connection generator to integrate this project with others in your workspace. The following connections involve this project:

Strands AgentsPythonAmazon AuroraPython
Python Agent to Relational DatabaseConnect a Python Agent to an Aurora relational database
FastAPIAmazon AuroraPython
FastAPI to Relational DatabaseConnect a FastAPI to an Aurora relational database
Model Context ProtocolPythonAmazon AuroraPython
Python MCP Server to Relational DatabaseConnect a Python MCP Server to an Aurora relational database