Using Drizzle with SST Ion and Postgres

So this is how I'm working with Drizzle, SST Ion and Postgres. This is for SST Ion, if you want SST v2 checkout my previous post.

Initializing the project

I've just set up a super simple api to interact with the database using Hono to start. See the SST docs: Hono on AWS with SST for how to set this up. This is my sst.config.ts and a handler in src/index.ts.

sst.config.ts

async run() {
    const hono = new sst.aws.Function("Hono", {
      url: true,
      handler: "index.handler",
    });
 
    return {
      api: hono.url,
    };
}

src/index.ts

import { Hono } from "hono";
import { handle } from "hono/aws-lambda";
 
const app = new Hono().get("/", async (c) => {
  return c.text("hello world!");
});
 
export const handler = handle(app);

Setting up Postgres on RDS

Ok so we're going to set up a Postgres database and use Drizzle as our ORM. We're also going to take full advantage of SST Ion's new linking capabilities and use sst shell to manage our migrations. Previously, we had to set up a migration endpoint to invoke the migration function, but using sst shell we can inject our SST resources into locally run scripts. sst shell docs.

We can add add our database to our SST config like so:

sst.config.ts

export default $config({
  app(input) {
    return {
      name: "drizzle-sst3",
      removal: input?.stage === "production" ? "retain" : "remove",
      home: "aws",
      providers: {
        aws: {
          region: "eu-central-1",
        },
      },
    };
  },
  async run() {
    const db = new sst.aws.Postgres("TestDb", {
      scaling: {
        min: "0.5 ACU",
        max: "2 ACU",
      },
    });
    const hono = new sst.aws.Function("Hono", {
      url: true,
      handler: "index.handler",
      link: [db],
    });
 
    return {
      api: hono.url,
    };
  },
});

I've set the scaling to the smallest as AWS does charge for Postgres and I do not fancy a huge bill, but feel free to configure this however you want.

Now it's important that we run sst dev to create our resources and deploy to our AWS account. You can define a custom stage here if you want, otherwise SST will default to a personal stage in your default AWS account. If you don't run sst dev, when we run sst shell later we won't be able to access the resources. Deploying the database take a little bit of time so go touch grass while it's running.

Correctly setting the region

It's also important that we're set up in the correct region. I would normally use eu-west-2 but need to use eu-central-1 to get access to the RDS data-api which is what Drizzle uses to connect to and work with your database. To follow with the rest of hte tutorial you'll need to make sure you're set up in one of the Aurora Data-API Regions:

If you don't do this you'll get the following error:

BadRequestException: HttpEndpoint is not enabled for cluster ${PROJECT_NAME}-${STAGE}-${DBNAME}cluster. Please refer to https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html#data-api.troubleshooting

I know this because this ruined my life for a 24 hour period.

Setting up Drizzle

Once the database is set up we can setup Drizzle. You can put your folders wherever you want in your project, just make sure you change your paths. I'm going to put a drizzle folder at the root of my project and create three files within it: index.ts, schema.ts, migrate.ts, as well as a migrations folder. (I'm just referencing the Drizzle docs on how to setup Postgres with the AWS Data API).

It should look like the following:

drizzle
├── index.ts
├── migrate.ts
├── migrations
└── schema.ts

In the index.ts we'll initialise the database connection:

drizzle/index.ts

import { drizzle } from "drizzle-orm/aws-data-api/pg";
import { RDSDataClient } from "@aws-sdk/client-rds-data";
import { Resource as SSTResource } from "sst";
 
import * as schema from "./schema";
 
const rdsClient = new RDSDataClient({});
 
export const db = drizzle(rdsClient, {
  database: SSTResource.TestDb.database,
  secretArn: SSTResource.TestDb.secretArn,
  resourceArn: SSTResource.TestDb.clusterArn,
  schema,
});

Make sure you target the correct resource here, I had it as SSTResource.Postgres and spent 4 hours trying to debug a Resource 'Postgres' is not linked error.

You should also probably install drizzle-orm, aws-sdk and sst from your fav package manager at this point.

Then we'll make our schema. I'm just going to create a users table but you do you.

drizzle/schema.ts

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

And finally we create our migration script:

drizzle/migrate.ts

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

Also make sure your drizzle config is set up correctly:

drizzle.config.ts

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

Generating and running migrations

Now we're all set up we can run our migrations.

Firstly we generate a migration:

npx drizzle-kit generate:pg

If you've set up your config correctly you should now have some files in your previously empty migrations directory.

To apply the migration, we run the migration script using sst shell. SST shell allows us to bind our AWS resources to locally run scrips which is pretty big improvement on SST v2. I'm pretty excited to work more with this. The SST docs go into much more detail about linking and sst shell.

sst shell --stage=dev tsx ./src/drizzle/migrate.ts

And our migrations will run.

If you want you could also add this to your package.json

package.json

"scripts": {
	"shell": "sst shell tsx",
	"migrate": "sst shell tsx ./drizzle/migrate.ts"
}

The Drizzle migration docs are a great reference on all the ins and outs of generating and applying migrations.

Adding the database to the API

Finally, we can finish up our api endpoint: