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

Generate a Relational Database

VSCode

1. Install the Nx Console VSCode Plugin if you haven't already
2. Open the Nx Console in VSCode
3. Click Generate (UI) in the "Common Nx Commands" section
4. Search for @aws/nx-plugin - py#rdb
5. Fill in the required parameters
6. Click Generate

CLI

pnpm

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

yarn

yarn nx g @aws/nx-plugin:py#rdb

npm

npx nx g @aws/nx-plugin:py#rdb

bun

bunx nx g @aws/nx-plugin:py#rdb

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

pnpm

pnpm nx g @aws/nx-plugin:py#rdb --dry-run

yarn

yarn nx g @aws/nx-plugin:py#rdb --dry-run

npm

npx nx g @aws/nx-plugin:py#rdb --dry-run

bun

bunx nx g @aws/nx-plugin:py#rdb --dry-run

Options

Parameter	Type	Default	Description
name Required	string	-	Name of the database project to generate
directory	string	packages	The directory to store the application in.
subDirectory	string	-	The sub directory the project is placed in. By default this is the project name.
infra	aurora | none	aurora	Relational database service to provision.
engine	postgres | mysql	postgres	Database engine to use with the selected service.
databaseUser	string	dbadmin	Database admin username. Defaults to 'dbadmin'.
databaseName	string	-	Initial database name. Defaults to the project name.
framework	sqlmodel	sqlmodel	ORM framework to use for the generated project.
iac	inherit | cdk | terraform	inherit	The preferred IaC provider. By default this is inherited from your initial selection.
preferInstallDependencies	boolean	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

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

• Directory<name>

  • __init__.py Package exports (get_engine, session_context)
  • 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

    • 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
• 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

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

Since this generator vends infrastructure as code based on your chosen iacProvider, 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:

CDK

• 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

Generated Project

This project is generated using the ts#project generator and therefore configures the same build targets.

Terraform

• Directorypackages/common/terraform

  • Directorysrc

    • Directoryapp/ Terraform modules for infrastructure specific to a project/generator

      • …
    • Directorycore/ Generic modules which are reused by modules in app

      • …
  • project.json Project build targets and configuration

Generated Project

This project is generated using the terraform#project generator and therefore configures the same build targets.

CDK

• Directorypackages/common/constructs/src

  • Directoryapp

    • Directorydbs

      • <name>.ts Infrastructure specific to your database
  • Directorycore

    • Directoryrdb

      • aurora.ts Generic Aurora database construct

Terraform

• Directorypackages/common/terraform/src

  • Directoryapp

    • Directorydbs

      • Directory<name>

        • <name>.tf Module specific to your database
  • Directorycore

    • Directoryrdb

      • Directoryaurora

        • aurora.tf Generic Aurora module

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.

Local Development

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

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:

pnpm

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

yarn

yarn nx run <project>:alembic revision --autogenerate -m "describe your change"

npm

npx nx run <project>:alembic revision --autogenerate -m "describe your change"

bun

bunx 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:

pnpm

pnpm nx run <project>:migrate

yarn

yarn nx run <project>:migrate

npm

npx nx run <project>:migrate

bun

bunx 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

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

pnpm

pnpm nx run <project>:migrate

yarn

yarn nx run <project>:migrate

npm

npx nx run <project>:migrate

bun

bunx nx run <project>:migrate

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.

pnpm

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

yarn

yarn nx run <project>:alembic <alembic-command>

npm

npx nx run <project>:alembic <alembic-command>

bun

bunx nx run <project>:alembic <alembic-command>

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.

Windows

Due to limitations with signal handling on Windows, the container is not automatically removed when dev is stopped. You will need to remove it manually:

<engine> rm -f <scope>-<db-name>

Replace <engine> with your container engine (docker or finch), <scope> with your Nx workspace scope (e.g. proj), and <db-name> with your database project name (e.g. my-db).

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))).all()

The database client automatically:

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

Deploying your Database

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

CDK

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

Terraform

The Terraform module is created in common/terraform. Example usage:

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"

  # Database subnets have no internet route; Lambda subnets need NAT egress.
  vpc_id              = aws_vpc.main.id
  database_subnet_ids = aws_subnet.database[*].id
  lambda_subnet_ids   = aws_subnet.private[*].id

  tags = local.common_tags
}

This provisions an Aurora cluster with RDS Proxy, admin credentials, create-db-user Lambda, runtime config registration, migration Lambda, and container registry resources.

The database module registers its connection details under the database runtime configuration namespace. Include this namespace when instantiating the shared runtime configuration AppConfig application:

packages/infra/src/main.tf

module "runtime_config_appconfig" {
  source = "../../common/terraform/src/core/runtime-config/appconfig"

  application_name = "my-app-runtime-config"
  namespaces       = ["connection", "agentcore", "database"]
}

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 function 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.

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

Additional Cost

RDS Proxy is enabled by default but incurs additional charges on top of the Aurora cluster cost. See AWS RDS Proxy pricing for details. If you would prefer to disable the proxy, see the section below.

Disable RDS Proxy

You can disable the RDS proxy as follows:

CDK

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.

Terraform

By default, RDS Proxy is enabled. You can disable it if needed:

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  enable_rds_proxy = false
}

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

SSL Requirements When Connecting Without RDS Proxy

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

Container Runtimes

Amazon Linux

ADD https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem /etc/pki/ca-trust/source/anchors/global-bundle.pem
RUN update-ca-trust

Debian

ADD https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem /usr/local/share/ca-certificates/rds-global-bundle.crt
RUN update-ca-certificates

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

Configure the writer and reader instances for your Aurora cluster.

CDK

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')],
});

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  instance_count = 2  # 1 writer + 1 reader
}

Serverless Capacity

Control Aurora Serverless v2 scaling limits to match your workload.

CDK

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,
});

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  serverless_min_capacity = 0.5
  serverless_max_capacity = 8
}

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

CDK

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,
});

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  engine_version = "17.7"
}

engine = mysql

CDK

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,
});

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  engine_version = "8.0.mysql_aurora.3.12.0"
}

Deletion Protection

Deletion protection is enabled by default (deletionProtection: true in CDK, deletion_protection = true in Terraform) to protect the Aurora cluster from accidental deletion.

Disable Deletion Protection

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

CDK

packages/infra/src/stacks/application-stack.ts

import { MyDatabase } from ':my-scope/common-constructs';

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

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  deletion_protection = false
}

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.

CDK

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,
});

Data Loss

RemovalPolicy.DESTROY will permanently erase all data when the stack is deleted. Use only in ephemeral environments (such as automated tests). Use RETAIN or SNAPSHOT for all persistent and production stacks to prevent data loss.

Terraform

Terraform does not use CDK removal policies. By default, the module creates a final snapshot on deletion (skip_final_snapshot = false). To skip the final snapshot for an ephemeral environment:

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  deletion_protection = false
  skip_final_snapshot = true
}

Data Loss

skip_final_snapshot = true will permanently erase all data when the cluster is deleted. Use only in ephemeral environments (such as automated tests). Keep skip_final_snapshot = false for all persistent and production stacks to prevent data loss.

Logging and Monitoring

Performance Insights is enabled on the Aurora writer instance by default (encrypted with the cluster’s KMS key). You can also export the Aurora engine logs to CloudWatch Logs (postgresql for Aurora PostgreSQL; audit, error, general and slowquery for Aurora MySQL). Enable log export per database:

CDK

packages/infra/src/stacks/application-stack.ts

import { MyDatabase } from ':my-scope/common-constructs';

const db = new MyDatabase(this, 'Db', {
  ...
  enableCloudwatchLogs: true,
  enablePerformanceInsights: false, // disable if not required
});

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  enable_cloudwatch_logs      = true
  enable_performance_insights = false # disable if not required
}

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.

CDK

packages/infra/src/stacks/application-stack.ts

import { MyDatabase } from ':my-scope/common-constructs';

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

Terraform

packages/infra/src/main.tf

module "my_database" {
  source = "../../common/terraform/src/app/dbs/my-database"
  ...
  enable_key_rotation = false
}

Connections

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

Python Agent to Relational DatabaseConnect a Python Agent to an Aurora relational database

FastAPI to Relational DatabaseConnect a FastAPI to an Aurora relational database

Python MCP Server to Relational DatabaseConnect a Python MCP Server to an Aurora relational database