Exploring Aurora DSQL with TypeScript, Drizzle, Lambda, and AWS CDK

Does Amazon's fancy new serverless PostgreSQL-compatible database work with modern TypeScript tooling?

Amazon recently introduced Aurora DSQL in preview:

Amazon Aurora DSQL is a serverless distributed SQL database with virtually unlimited scale, the highest availability, and zero infrastructure management. Aurora DSQL offers the fastest distributed SQL reads and writes and makes it effortless for you to scale to meet any workload demand without database sharding or instance upgrades. With its active-active distributed architecture, Aurora DSQL ensures strong data consistency designed for 99.99% single-Region and 99.999% multi-Region availability. Its serverless design eliminates the operational burden of patching, upgrades, and maintenance downtime. Aurora DSQL is PostgreSQL-compatible and provides an easy-to-use developer experience.

What does all of that mean in practice? DSQL sounds like a truly serverless SQL database - we only pay for what we use and don't need to worry about things like connection pooling. Previous "serverless" SQL database offerings from AWS have left a lot to be desired in terms of both capabilities and cost, so this is a pretty exciting release if it works as promised and the final pricing ends up being reasonable. Let's try it out!

Prerequisites

I'm assuming you have some basic familiarity with the following:

• AWS CDK and AWS infrastructure as code
• SQL databases
• TypeScript
• Drizzle ORM

Create AWS CDK project

Let's init a new TypeScript CDK project:

mkdir drizzle-dsql-cdk-lambda && drizzle-dsql-cdk-lambda
cdk init app --language typescript

Create Aurora DSQL Cluster

...and we immediately run into a roadblock. Ideally we would create and manage our DSQL cluster in our CDK project. The CDK compiles to a CloudFormation template to create our AWS resources. Unfortunately Aurora DSQL doesn't support CloudFormation while it is in preview so we will need to create the cluster manually 🤦‍♂️. This is pretty easy to do in the AWS DSQL console with the "Create Cluster" button.

After you've created the cluster, take note of the cluster endpoint as we'll need it in a bit.

Create a Lambda function that connects to the DSQL cluster

We're going to be deploying a Node.js Lambda function that is bundled by esbuild. We need to first add a few dependencies to our project:

npm i esbuild @types/pg -D
npm i @aws-sdk/dsql-signer pg

Add a Lambda function to your CDK stack that looks something like:

const handler = new NodejsFunction(this, 'DSQLHandler', {
  runtime: Runtime.NODEJS_22_X,
  handler: 'handler',
  entry: 'lambda/handler.ts',
  memorySize: 1024,
  bundling: {
    bundleAwsSDK: true,
  },
});

handler.addToRolePolicy(
  new cdk.aws_iam.PolicyStatement({
    effect: cdk.aws_iam.Effect.ALLOW,
    actions: ['dsql:DbConnectAdmin'],
    resources: ['*'],
  })
);

Why do we need that bundleAwsSDK flag if the Lambda Node.js runtime already includes the AWS SDK? Apparently the @aws-sdk/dsql-signer is so new and/or experimental that it doesn't ship with the runtime yet so we'll need to include it in our deployed bundle. For the sake of simplicity the role that we're also giving the function permission to connect to any DSQL cluster - ideally you should lock that down by replacing the resources wildcard with your cluster ARN.

Let's add our Lambda handler code, heavily inspired by an example from the Aurora DSQL docs:

import { DsqlSigner } from '@aws-sdk/dsql-signer';
import { Client } from 'pg';
export const handler = async () => {
  const clusterEndpoint = YOUR_CLUSTER_ENDPOINT;
  let client;
  const region = 'us-east-1';
  try {
    // The token expiration time is optional, and the default value 900 seconds
    const signer = new DsqlSigner({
      hostname: clusterEndpoint,
      region,
    });
    const token = await signer.getDbConnectAdminAuthToken();
    client = new Client({
      host: clusterEndpoint,
      user: 'admin',
      password: token,
      database: 'postgres',
      port: 5432,
      ssl: true,
    });
    // Connect
    await client.connect();
    // Create a new table
    await client.query(`CREATE TABLE IF NOT EXISTS owner (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(30) NOT NULL,
      city VARCHAR(80) NOT NULL,
      telephone VARCHAR(20)
    )`);
    // Insert some data
    await client.query(
      'INSERT INTO owner(name, city, telephone) VALUES($1, $2, $3)',
      ['John Doe', 'Anytown', '555-555-1900']
    );
    // Check that data is inserted by reading it back
    const result = await client.query(
      "SELECT id, city FROM owner where name='John Doe'"
    );
    await client.query("DELETE FROM owner where name='John Doe'");
    return result;
  } catch (error) {
    console.error(error);
    return error;
  } finally {
    client?.end();
  }
};

This handler generates an authentication token, uses the token to connect to the cluster, then executes some simple queries. After building, deploying, and testing your function you should see the Lambda function return the result of the SELECT query.

Add Drizzle

Our Lambda function is currently using raw SQL queries which are not the most fun to write and maintain. Let's replace them with Drizzle ORM, a popular TypeScript ORM.

npm i drizzle-orm

Let's add a simple Drizzle schema to our handler that mimics the database table that we created in the previous step and replace the other queries with the equivalent Drizzle code:

import { DsqlSigner } from '@aws-sdk/dsql-signer';
import { Client } from 'pg';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import { text, pgTable, uuid } from 'drizzle-orm/pg-core';

const owner = pgTable('owner', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  city: text('city').notNull(),
  telephone: text('telephone'),
});

export const handler = async () => {
  const clusterEndpoint = YOUR_CLUSTER_ENDPOINT;
  let client;
  const region = 'us-east-1';
  try {
    // The token expiration time is optional, and the default value 900 seconds
    const signer = new DsqlSigner({
      hostname: clusterEndpoint,
      region,
    });
    const token = await signer.getDbConnectAdminAuthToken();
    client = new Client({
      host: clusterEndpoint,
      user: 'admin',
      password: token,
      database: 'postgres',
      port: 5432,
      ssl: true,
    });

    await client.connect();

    const db = drizzle(client, { schema: { owner } });
    await db
      .insert(owner)
      .values({ name: 'John Doe', city: 'Anytown', telephone: '555-555-1900' });
    const owners = await db.select().from(owner);

    await db.delete(owner).where(eq(owner.name, 'John Doe'));

    return owners;
  } catch (error) {
    console.error(error);
    return error;
  } finally {
    client?.end();
  }
};

Much cleaner! For simplicity here I'm declaring the Drizzle schema in the same file. In a real project you will probably want that in a dedicated schema file so that it can be shared and/or used in migrations.

After building, deploying, and testing our function again... it just works!

It's great to see this new service working out of the box with common tooling. This is obviously an extremely simple example but the response time seems quite promising as well.

What About Relationships and Migrations?

I'll write a part 2 of this in the future with a more complex database structure including joins across multiple tables created via drizzle-kit migrations. Aurora DSQL has a pretty extensive list of unsupported PostgreSQL features including foreign key constraints so it should be interesting to see what we can do!

The full code for this project is available on GitHub.