Connecting Drizzle and RDS within SST

Ever wondered how to set up drizzle with RDS deployed with SST? Probably not but here it is anyway.

Setup

Firstly, setup a drizzle folder with your endpoints, I went for /drizzle.

Then define two files and a folder: index.ts, schema.ts, /migrations.

Here's a bash command to do that if you like copying and pasting random snippets from the internet:

touch drizzle/index.ts drizzle/schema.ts && mkdir -p drizzle/migrations/

We now want to create an RDS client, a drizzle client and a wee migration function.

As we're using SST we have to bind RDS to our api routes:

app.stack(function Site({stack}){
//...
      const rds = new RDS(stack, "db", {
        engine: "postgresql11.16",
        defaultDatabaseName: "uploader",
      });
 
      const api = new Api(stack, "api", {
        defaults: {
          function: {
			//...
            bind: [bucket, rds],
			//...
          },
        },
        routes: {
			//...
		},
      });
//...
}
 
)

which then allows us to do:

import { drizzle } from "drizzle-orm/aws-data-api/pg";
import { RDSDataClient } from "@aws-sdk/client-rds-data";
import { RDS } from "sst/node/rds";
 
const rdsClient = new RDSDataClient({});
 
export const db = drizzle(rdsClient, {
  database: RDS.db.defaultDatabaseName,
  secretArn: RDS.db.secretArn,
  resourceArn: RDS.db.clusterArn,
});

Now we can define a schema, in this case we're just going to define a table for users:

import { pgTable, serial, varchar } from "drizzle-orm/pg-core";
 
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
});

Migrations

Now to add this to our table we're gonna need to generate some migrations and apply them, we need to create a drizzle config file so we can use drizzle-kit

import type { Config } from "drizzle-kit";
 
export default {
  schema: "./src/drizzle/schema.ts",
  out: "./src/drizzle/migrations/",
  driver: "pg", 
} satisfies Config;

Note: you can put these files basically anywhere you like

Then just run pnpm drizzle-kit generate:pg and your migrations folder should now populate. If it doesn't, try experimenting with relative/absolute filepaths in your drizzle config until it works.

Ok so now we write our migrator function. In this case, we'll be putting it in an ApiHandler that we can then hit to run the migrations. You could just use drizzle-kit migrate but the issue is that we'd need to define our connection parameters in our drizzle config. In previous Drizzle setups I've created a migration function and added it to package.json, but we need to have SST running or we can't bind the RDS resource to Drizzle so the recommended method is to just create a migration endpoint.

I've just dropped the migration function into the index.ts:

//...
import { migrate as migratePostgres } from "drizzle-orm/aws-data-api/pg/migrator";
//...
 
export const db = drizzle(...)
 
export const migrate = async (path: string) => {
  console.log("running migrations...");
  await migratePostgres(db, { migrationsFolder: path });
  console.log("migrations done.");
};

Now we simply write an endpoint that we can hit:

import { ApiHandler } from "sst/node/api";
import { migrate } from "./drizzle";
 
export const handler = ApiHandler(async (_evt) => {
  const pathToMigrations = "src/drizzle/migrations/";
 
  await migrate(pathToMigrations);
 
  return {
    body: "Migrated!",
  };
});

and add it to our sst.config.ts

{
...
  "POST /migrate": "src/handlers/migrator.handler",
...
}

Smash that with postman or whatever and you'll get a successful migration. You can then query your db, how exciting!!

Sources

This config was heavily inspired by the sst-drizzle-example which itself was inspired by a @thdxr twitch stream.