SST + Drizzle ORM example project

---

Current example was inspired by and created from twitch stream -> https://www.twitch.tv/videos/1704293186

In this stream @thdr tried to use Prisma + Kysely to setup automatic database migrations using sst

---

We have prepared simple MVP example of how to achieve both migrations and ORM functionality using just Drizzle ORM

Note: Current example just showing a possibility to use drizzle orm in serverless environment using AWS Lambda/Custom Resources/etc. In next versions of those examples we can setup Custom resources, by examples how SST Team already did here

Initial setup

We used create-sst bootstrap script

npx create-sst@rc

For Drizzle ORM versions we are currently using internal(alfa) version, where we have just added AWS DataApi driver support

pnpm i drizzle-orm@latest drizzle-orm-pg@0.15.2-2b4d90d
pnpm i drizzle-kit -D

Project sctructure

In this repo you may found same project structure as create-sst script will generate. Just few things were added:

1. packages/core/sql/index.ts

Current file contain drizzle AwsDataApi connection setup + drizzle migrations setup. You can check more about drizzle-orm and drizzle-kit usage

import { drizzle } from "drizzle-orm-pg/aws-datapi";
import { migrate as mig } from "drizzle-orm-pg/aws-datapi/migrator";
import { RDS } from "sst/node/rds";
import { RDSDataClient } from "@aws-sdk/client-rds-data";

export const db = drizzle(new RDSDataClient({}), {
  database: RDS.rds.defaultDatabaseName,
  secretArn: RDS.rds.secretArn,
  resourceArn: RDS.rds.clusterArn,
});

export const migrate = async (path: string) => {
  return mig(db, { migrationsFolder: path });
};

2. packages/core/sql/schema.ts

Current file contains basic table schema definition, that can be used in further queries

import { integer, pgTable, text } from "drizzle-orm-pg";

export const users = pgTable('users', {
    id: integer('id').primaryKey(),
    name: text('name')
})

3. We have added 2 Lambda Functions
   • lambda.ts -> check, that query to newly created database was invoked without errors
   • migrator.ts -> lambda, that currently invoked using API, to simulate migration process from lambda code

How to run this example

1. Run cdk bootstrap command

pnpm run cdk:bootstrap aws://<account-id>/<region> --profile <profile>

2. Run sst deploy

pnpm run deploy

3. From outputs use ApiGateway base url
   • /migrate - to simulate migration process
   • / - to check if query is working as expected

Add new migrations

Core package has npm script migrate. By running this script drizzle-kit will generate new sql file in output folder, that was chosen in cli params

For more information you could check drizzle-kit docs and drizzle-orm docs