On December 3rd, 2024, Amazon announced the preview of Amazon Aurora DSQL, which is introduced as "the fastest serverless distributed SQL database for always available applications".

From all the sources I have read so far, it looks to have been something that the team has had in the works for a long time. It comes in a few different flavours, but this blog post will focus more so on playing around with DSQL and seeing it in action.

To keep things simple, we will be using the AWS console to setup a new cluster and then connecting to it with Bun, TypeScript and Drizzle.

First, let's configure the project with Bun and Drizzle.

# Configure git, initialise the project
$ mkdir demo-dsql-drizzle
$ cd demo-dsql-drizzle
$ bun init
$ bun add drizzle-orm pg dotenv
$ bun add -D drizzle-kit @types/pg
$ mkdir -p src/db drizzle
$ touch src/index.ts src/db/schema.ts drizzle.config.ts
copy

We're going to follow a slightly modified version of Drizzle's getting started guide for Postgres. Please note that DSQL is only Postgres-compatible at the time of the announcement.

First, we need to configure our basic schema. Add the following to src/db/schema.ts:

import { integer, pgTable, varchar } from "drizzle-orm/pg-core";

export const usersTable = pgTable("users", {
  id: integer().primaryKey().unique(),
  name: varchar({ length: 255 }).notNull(),
  age: integer().notNull(),
  email: varchar({ length: 255 }).notNull().unique(),
});
copy

DSQL has limitations around constraints that I won't dive too deep into here, just note that you cannot use the generatedAlwaysAsIdentity() constraint as expected. AWS will respond with an error during the migration. You can read more about the unsupported features here.

At this point, we can pivot to the AWS console and create a new Aurora DSQL cluster. This will be required for filling out the remaining files as we will need some configuration setup.

Through the AWS console, you can search and navigate to Aurora DSQL.

Creating a cluster via the console

If you are not currently in a supported region, AWS will prompt you to change regions.

Change region request

Once you are in a supported region, you can select to create a cluster to move forward. Once it's created, go into the details for the cluster and you will see a "connect" button that will give you the endpoint and other details required to connect to the cluster.

Highlighted connect button within the UI

Auth details modal

At this point, we'll have a cluster running with some details about the endpoint and cluster ID, etc. Collect the details for the endpoint and we can move forward.

Back in our project, we can configure out connection. AWS has some more details about using the PostgreSQL protocol that you can read, but in our case I will walk use through what we need.

We'll need to configure our drizzle.config.ts file in the root of the project in order to run our migration. Given that this is just a walkthrough, I won't be using any .env configuration files, but the security is back on you to manage. We'll be using the admin configuration in our case, so the default password limit is 15 minutes to add some sense of security. In practice, you'll either need to manage IAM or make use of APIs to generate the configuration required to connect.

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./src/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    host: "<add in the endpoint from the console here>",
    port: 5432,
    user: "admin",
    password: "<add in the entire password string from the console here>",
    database: "postgres",
    ssl: true,
  },
});
copy

By default, the user will be admin and the database will be postgres. The password will be the one you set when creating the cluster.

The password itself includes the host as well. It's a very long string but just note that you need the entire thing in the password field.

Please note that ssl is required for the connection to work.

Once this is all configured, we can run the migration:

$ bunx drizzle-kit push
copy

You can read up more on Drizzle kit migrations on the Drizzle migrations documentation.

If everything is successful, you should the following:

$ bunx drizzle-kit push
No config path provided, using default 'drizzle.config.ts'
Reading config file '/Users/dennis.okeeffe/code/okeeffed/demo-dsql-drizzle/drizzle.config.ts'
Using 'pg' driver for database querying
[✓] Pulling schema from database...
[✓] Changes applied
copy

Let's finish by seeding some data and applying some basics calls against the database.

We'll modify an example script from Drizzle to run against the database slightly as we will need to provide the integer IDs ourselves. Not having certain key constraints means that we will need to enforce it at the application layer.

In src/index.ts, add the following:

import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import { eq } from "drizzle-orm";
import { usersTable } from "./db/schema";

const db = drizzle({
  connection: {
    host: "<add in the endpoint from the console here>",
    port: 5432,
    user: "admin",
    password: "<add in the entire password string from the console here>",
    database: "postgres",
    ssl: true,
  },,
});

async function main() {
  const user: typeof usersTable.$inferInsert = {
    id: 1234,
    name: "John",
    age: 30,
    email: "john@example.com",
  };
  const user2: typeof usersTable.$inferInsert = {
    id: 5678,
    name: "Tracy",
    age: 48,
    email: "tracy@example.com",
  };

  await db.insert(usersTable).values([user, user2]);
  console.log("New user created!");

  const users = await db.select().from(usersTable);
  console.log("Getting all users from the database: ", users);
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
  }[]
  */

  await db
    .update(usersTable)
    .set({
      age: 31,
    })
    .where(eq(usersTable.email, user.email));
  console.log("User info updated!");

  await db.delete(usersTable).where(eq(usersTable.email, user.email));
  console.log("User deleted!");
}

main();
copy

Similar to before, I'm just dumping in the credentials here for the sake of the walkthrough, but the connection object will look just like what we configured previously in the drizzle.config.ts file.

Running bun src/index.ts, we can finally test our code against the DSQL cluster:

$ bun src/index.ts
New user created!
Getting all users from the database:  [
  {
    id: 1234,
    name: "John",
    age: 30,
    email: "john@example.com",
  }, {
    id: 5678,
    name: "Tracy",
    age: 48,
    email: "tracy@example.com",
  }
]
User info updated!
User deleted!
copy

This blog post was more "show" than "tell". I have plenty more about Aurora DSQL that I need to dive into and understand, but hopefully this will give you some insight into how you can setup and connect to your own DSQL cluster.

Don't forget to tear down your cluster once you are done to avoid any unnecessary costs. As far as I can tell, costs are free during this preview period, but it's always good to be cautious.

Photo credit: joelfilip