DSQL

Serverless Postgres on Aurora DSQL. IAM auth over TLS, schemas and roles declared in code, and optional multi-region clusters.

Database · anvil:aws:DSQL

What codegen gives you

anvil generate reads the schemas you declare on DSQL and writes a typed client: a Row, Insert and Update type for each table, plus a query helper per schema. Every anvil deploy runs it before building, so functions always bundle code that matches the schemas. You can also run it by hand.

Running it

Point the output next to the code that uses it. Discovery runs a preview, so nothing in AWS changes.

anvil generate --lang typescript --path functions/items/gen

The language defaults to your entry point's and the path to gen/. Both are remembered per stage, so a plain anvil generate — and the generate step of every anvil deploy — writes to the same place. Generated files are marked do not edit: change the schemas on the DSQL component instead.

  • The query helper takes a connected pg client. With a Pool, pass a client from await pool.connect() rather than the pool itself, and release it afterwards.
  • The generated findMany, findById, update and delete don't filter by owner. For per-user data, keep your own WHERE clause and type the result with the generated Row type, e.g. pool.query<OrdersRow>(...).

Declare the tables on the DSQL component. anvil generate reads this and nothing else.

import * as anvil from '@anvil-cloud/sdk';

const db = new anvil.aws.DSQL('db', {
  schemas: [
    {
      name: 'app',
      tables: [
        {
          name: 'items',
          columns: [
            { name: 'id', type: 'uuid' },
            { name: 'name', type: 'text', notNull: true },
          ],
          primaryKey: ['id'],
        },
      ],
      roles: [{ name: 'app_read', grants: ['SELECT'] }],
    },
  ],
});

import anvil_cloud as anvil

db = anvil.aws.DSQL("db",
    schemas=[{
        "name": "app",
        "tables": [{
            "name": "items",
            "columns": [
                {"name": "id", "type": "uuid"},
                {"name": "name", "type": "text", "not_null": True},
            ],
            "primary_key": ["id"],
        }],
        "roles": [{"name": "app_read", "grants": ["SELECT"]}],
    }],
)

db, err := anvilaws.NewDSQL(ctx.PulumiCtx(), "db", &anvilaws.DSQLArgs{
    Schemas: anvilaws.DSQLSchemaArray{
        anvilaws.DSQLSchemaArgs{
            Name: pulumi.String("app"),
            Tables: anvilaws.DSQLTableArray{
                anvilaws.DSQLTableArgs{
                    Name: pulumi.String("items"),
                    Columns: anvilaws.DSQLColumnArray{
                        anvilaws.DSQLColumnArgs{Name: pulumi.String("id"), Type: anvilaws.DSQLColumnType("uuid")},
                        anvilaws.DSQLColumnArgs{Name: pulumi.String("name"), Type: anvilaws.DSQLColumnType("text"), NotNull: pulumi.Bool(true)},
                    },
                    PrimaryKey: pulumi.StringArray{pulumi.String("id")},
                },
            },
            Roles: anvilaws.DSQLRoleArray{
                anvilaws.DSQLRoleArgs{
                    Name:   pulumi.String("app_read"),
                    Grants: anvilaws.DSQLGrantArray{anvilaws.DSQLGrant("SELECT")},
                },
            },
        },
    },
})

Using it in a Lambda

A Node.js handler is TypeScript. Both versions below use the same connection and the same table. For a Rust handler, see Using it in a Rust Lambda.

Coming soon. Python Lambda runtimes aren't available yet. Anvil's Lambda runs Node.js and Rust today, so this shows how the Python handler is expected to look.

Coming soon. Go Lambda runtimes aren't available yet. Anvil's Lambda runs Node.js and Rust today, so this shows how the Go handler is expected to look.

Write the SQL yourself and read the rows directly.

import { DsqlSigner } from '@aws-sdk/dsql-signer';
import { Client } from 'pg';

const databaseEndpoint = process.env.DSQL_ENDPOINT!;
const awsRegion = process.env.AWS_REGION!;

export async function handler() {
  const tokenSigner = new DsqlSigner({ hostname: databaseEndpoint, region: awsRegion });
  const authToken = await tokenSigner.getDbConnectAuthToken();

  const dbClient = new Client({
    host: databaseEndpoint,
    port: 5432,
    user: 'app_read',
    password: authToken,
    database: 'postgres',
    ssl: { rejectUnauthorized: true },
  });
  await dbClient.connect();

  const { rows } = await dbClient.query('SELECT id, name FROM app.items');
  await dbClient.end();

  return { statusCode: 200, body: JSON.stringify(rows) };
}

import json
import os

import asyncpg
import boto3

DSQL_ENDPOINT = os.environ["DSQL_ENDPOINT"]
REGION = os.environ["AWS_REGION"]


async def handler(event, context):
    token = boto3.client("dsql", region_name=REGION).generate_db_connect_auth_token(DSQL_ENDPOINT, REGION)
    conn = await asyncpg.connect(
        host=DSQL_ENDPOINT, port=5432, user="app_read", password=token, database="postgres", ssl="require"
    )
    try:
        rows = await conn.fetch("SELECT id, name FROM app.items")
    finally:
        await conn.close()

    return {"statusCode": 200, "body": json.dumps([dict(r) for r in rows])}

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"os"

	"github.com/aws/aws-lambda-go/lambda"
	_ "github.com/lib/pq"
)

func handler(ctx context.Context) (map[string]any, error) {
	endpoint := os.Getenv("DSQL_ENDPOINT")
	token, err := dsqlConnectToken(ctx, endpoint) /* IAM token for dsql:DbConnect */
	if err != nil {
		return nil, err
	}

	conn, err := sql.Open("postgres", "host="+endpoint+" port=5432 user=app_read password="+token+" dbname=postgres sslmode=require")
	if err != nil {
		return nil, err
	}
	defer conn.Close()

	rows, err := conn.QueryContext(ctx, "SELECT id, name FROM app.items")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []map[string]any
	/* scan rows into items */
	body, _ := json.Marshal(items)
	return map[string]any{"statusCode": 200, "body": string(body)}, nil
}

func main() { lambda.Start(handler) }

Using it in a Rust Lambda

For Rust, the client is a crate rather than a file. Whenever the project has a Rust Lambda and a schema, anvil generate writes it to gen/db, whatever language your config is in, and adds it to the Cargo workspace. Its Cargo.toml lists exactly the crates and features your column types need, so adding a uuid or timestamptz column never leaves it out of date. New Rust functions depend on it automatically.

Grant the function a role with grantConnect and pass it DSQL_ENDPOINT, exactly as for a Node.js handler. The handler signs an IAM token for that role and connects over TLS once, during init, so every invocation reuses the connection:

use std::sync::Arc;

use aws_config::BehaviorVersion;
use aws_sdk_dsql::auth_token::{AuthTokenGenerator, Config};
use db::app::ItemsQueries;
use lambda_runtime::{run, service_fn, tracing, Error, LambdaEvent};
use serde_json::{json, Value};
use tokio::sync::Mutex;
use tokio_postgres::config::SslMode;

async fn handler(db: &Mutex<tokio_postgres::Client>, _event: LambdaEvent<Value>) -> Result<Value, Error> {
    let client = db.lock().await;
    let items = ItemsQueries::new(&client).find_many().await?;
    Ok(json!({ "count": items.len() }))
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    tracing::init_default_subscriber();
    let _ = rustls::crypto::aws_lc_rs::default_provider().install_default();

    // Init: connect once per cold start, as the role grantConnect mapped.
    let aws = aws_config::load_defaults(BehaviorVersion::latest()).await;
    let endpoint = std::env::var("DSQL_ENDPOINT")?;
    let signer = AuthTokenGenerator::new(Config::builder().hostname(&endpoint).build()?);
    let token = signer.db_connect_auth_token(&aws).await?;

    let mut roots = rustls::RootCertStore::empty();
    roots.extend(webpki_roots::TLS_SERVER_ROOTS.iter().cloned());
    let tls = rustls::ClientConfig::builder().with_root_certificates(roots).with_no_client_auth();

    let (client, connection) = tokio_postgres::Config::new()
        .host(&endpoint)
        .port(5432)
        .dbname("postgres")
        .user("app_read")
        .password(token.to_string())
        .ssl_mode(SslMode::Require)
        .connect(tokio_postgres_rustls::MakeRustlsConnect::new(tls))
        .await?;
    tokio::spawn(connection);

    let db = Arc::new(Mutex::new(client));
    run(service_fn(move |event| {
        let db = db.clone();
        async move { handler(&db, event).await }
    }))
    .await
}

It needs a few crates beyond a new Rust Lambda's own:

cargo add aws-config --features behavior-version-latest
cargo add aws-sdk-dsql tokio-postgres tokio-postgres-rustls rustls webpki-roots
cargo add tokio --features sync

DSQL closes a connection after 60 minutes, and a frozen execution environment can drop one. In production, check client.is_closed() and the connection's age before using it, and reconnect when either says so.