AWS Builder Center

Builder Center

Run Postgres migrations from anywhere using AWS RDS Data API

How to apply SQL migration files to an Aurora Serverless PostgreSQL cluster with the Data API — no VPC access required.

Abdelrazek Rizk

Cloud and Data Analytics Specialist

Introduction

When you host your front end on Vercel and your database in a private VPC (Aurora Serverless), running SQL migrations from CI or a developer laptop can be painful: network access is usually blocked and opening the DB to the Internet is unsafe. The RDS Data API provides a secure HTTPS-based way to run SQL statements against an Aurora Serverless cluster. In this article I walk through a pragmatic, production-aware approach to run versioned migrations and seeds using the Data API.
This article was created for the purposes of entering the H0 hackathon. The examples and scripts come from the project repository: https://github.com/abdelrazekrizk/h0-serverless-content

Why this approach helps

  • No need to expose the database to the public Internet.
  • CI (or a developer machine) can run migrations over HTTPS using the AWS CLI or SDK.
  • Works well with Aurora Serverless clusters where Data API support is available.

High-level flow

  1. Enable the RDS Data API on your Aurora Serverless cluster.
  2. Store DB credentials in AWS Secrets Manager (JSON containing username and password).
  3. Add migration SQL files to your repo (e.g. drizzle/migrations/0001_init.sql).
  4. Use a small runner script that reads sorted migration files, splits statements, and executes them through the Data API.
  5. Mark migrations idempotent (IF NOT EXISTS / ON CONFLICT) and snapshot the DB before running migrations in prod.

Secrets Manager minimal shape

Store a JSON secret like:
1
{"username":"app_user","password":"S3cureP@ssw0rd"}
The Data API call references the cluster ARN and the secret ARN. The secret does not have to be encrypted with a special rotation if you rotate manually; for production consider Secrets Manager rotation.

Example: small runner (concept)

Use the AWS CLI to run a single statement:
1
2
3
4
5
aws rds-data execute-statement \
--resource-arn $CLUSTER_ARN \
--secret-arn $SECRET_ARN \
--sql "CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY, email varchar(255));" \
--database h0_content --region us-east-1
For multiple files, the runner should:
  • Read files in lexical order (0001_..., 0002_...)
  • For each file, split statements safely (avoid naive semicolon splits if you have procedural SQL)
  • Execute statements one at a time and stop on error (or capture and continue based on policy)
A small Node.js helper (conceptual) is included in the H0 repo: scripts/run-migrations-dataapi.js.

Practical tips

  • Keep migrations small and idempotent. Use IF NOT EXISTS and ON CONFLICT so re-running is safe.
  • Snapshot or take a manual backup before running migrations in production.
  • If you need to run complex migrations (long-running ALTERs), consider performing them inside the VPC (EC2/SSM or a self-hosted runner) to avoid Data API timeouts.
  • Use role-based access for CI: an IAM role with permission to call rds-data:ExecuteStatement and secretsmanager:GetSecretValue is typical.

Rolling back

  • Provide a tested rollback script or rely on DB snapshots. Data API executes statements; rollbacks should be planned and tested.

Example: CI step (GitHub Actions snippet)

1
2
3
4
5
6
7
8
9
10
11
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Run migrations via AWS CLI
env:
CLUSTER_ARN: ${{ secrets.CLUSTER_ARN }}
SECRET_ARN: ${{ secrets.SECRET_ARN }}
run: |
node scripts/run-migrations-dataapi.js
Make the migration job manual (workflow_dispatch) or protected with environment approvals for production.

Conclusion

The Data API gives a secure path to run SQL migrations against Aurora Serverless from CI or other environments that cannot reach the VPC. The H0 project demonstrates this approach with a simple runner and runbook (EC2/SSM alternative).
"This article was created for the purposes of entering the AWS & Vercel/v0 #H0hackathon."
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article