Nitric SQL Databases with Drizzle

This guide demonstrates how to use Drizzle ORM with Nitric's SQL database feature. For this guide we will be creating a to-do app. The finished source can be found here.

SQL databases are currently in preview and only support PostgreSQL deployed to AWS.

Create a new nitric project

The first step is to create a new Nitric TypeScript project using the Nitric CLI.

nitric new todo-app ts-starter

Enabling SQL databases

SQL databases are currently in Preview. To enable this feature in your project add the following to your nitric.yaml file

preview:
  - sql-databases

Ensure your CLI is at least v1.42 and your AWS provider is at least v1.6.2.

Install Drizzle Kit, Drizzle ORM and postgres

npm i drizzle-orm postgres
npm i -D drizzle-kit

We install drizzle-orm for database interactions, postgres for database management, and drizzle-kit as a dev-dependency for configuration and migrations.

Create a Nitric SQL Database and Connect Drizzle ORM to it

import { sql } from '@nitric/sdk'
import { type PostgresJsDatabase, drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

const db = sql('todos', {
  // points to our custom drizzle migration dockerfile
  migrations: 'dockerfile://migrate.dockerfile',
})

let drizzleClient: PostgresJsDatabase

const getClient = async () => {
  // ensure we only create the client once
  if (!drizzleClient) {
    const connectionString = await db.connectionString()

    const queryClient = postgres(connectionString)
    drizzleClient = drizzle(queryClient)
  }
  return drizzleClient
}

// export our getClient function, which will be used to get the drizzle client during runtime
export default getClient

In this snippet, we declare a new database called todos with the sql resource from @nitric/sdk, then we import functionality from postgres and drizzle-orm, connect to the database using Nitric's connection string, and initialize Drizzle.

Add the migrations dockerfile for cloud deployments

# Use an official Node.js runtime as the base image
FROM node

ENV DB_URL=""
ENV NITRIC_DB_NAME=""

# Copy package.json and package-lock.json into the Docker image
COPY package*.json ./

# Install the application's dependencies inside the Docker image
RUN npm ci

# Copy the rest of the application into the Docker image
COPY . .

# Build the Prisma client
RUN npm run drizzle-kit generate

# Run the migrations and start the application when the Docker container starts
# We define the entrypoint like this so we can correctly copy it out when running it
# in the various clouds (e.g. AWS Codebuild does not respect ENTRYPOINT and CMD)
ENTRYPOINT ["sh", "-c", "npm run drizzle-kit migrate"]

This will be used during deployment to apply migrations using drizzle-kit before any code changes are applied, ensuring no breakages in code due to a schema change.

Setup Drizzle config file

Next, set up the Drizzle configuration. This file, used by Drizzle Kit, contains information about your database connection, migration folder, and schema files. The DB_URL environment variable is used by Nitric during cloud migrations, however during local development we can can add it via a .env file (we will do that later).

import 'dotenv/config'
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DB_URL,
  },
})

This configuration file specifies the database schema location, the migration generation path, and the database connection via the connection string.

Declare todos schema

import { integer, text, boolean, pgTable } from 'drizzle-orm/pg-core'

export const todo = pgTable('todo', {
  id: integer('id').primaryKey(),
  text: text('text').notNull(),
  done: boolean('done').default(false).notNull(),
})

Here we define the todo table with fields id, text, and done, using data types from Drizzle ORM.

Now we’re ready to start developing an API.

Developing the app

Building the API

Delete the services/hello.ts file and create a new one called todos.ts, this will house our APIs.

Let's create some endpoints for creating, retrieving, editing and deleting todo tasks.

import { api } from '@nitric/sdk'
import getClient from '../db'
import { todo } from '../schema'
import { asc, eq, not } from 'drizzle-orm'

const mainApi = api('main')

// fetch all todos, sorted by their identifier
mainApi.get('/todos', async (ctx) => {
  const db = await getClient()
  const data = await db.select().from(todo).orderBy(asc(todo.id))

  return ctx.res.json(data)
})

// inserts a new record into the todo table
mainApi.post('/todos/:id', async (ctx) => {
  const { id } = ctx.req.params
  const { text } = ctx.req.json()
  const db = await getClient()

  await db.insert(todo).values({
    id: parseInt(id),
    text,
  })
})

// updates the text of a todo by its identifier
mainApi.patch('/todos/:id', async (ctx) => {
  const { id } = ctx.req.params
  const { text } = ctx.req.json()
  const db = await getClient()

  await db
    .update(todo)
    .set({
      text: text,
    })
    .where(eq(todo.id, parseInt(id)))
})

// toggles the status of a todo to its opposite state
mainApi.patch('/todos/:id/toggle', async (ctx) => {
  const { id } = ctx.req.params
  const db = await getClient()

  await db
    .update(todo)
    .set({
      done: not(todo.done),
    })
    .where(eq(todo.id, parseInt(id)))
})

// removes a todo by its identifier
mainApi.delete('/todos/:id', async (ctx) => {
  const { id } = ctx.req.params
  const db = await getClient()

  await db.delete(todo).where(eq(todo.id, parseInt(id)))
})

Start testing with nitric start

Create a .env file with your local database url, so you can develop against it during nitric start. By default this will be:

DB_URL="postgresql://postgres:localsecret@localhost:5432/todos"

Run nitric start to start your local database and apis.

nitric start

With your local database now running, create and run your first migration using the drizzle-kit generate and drizzle-kit migrate commands:

npx drizzle-kit generate --name init
npx drizzle-kit migrate

Add some todos using the Nitric dashboard

Open the local dashboard at localhost:49152, then navigate to the POST /todos/{id} endpoint and fill in an id as the path param.

Add id param for creating a todo

Then add some content to the body of the request, stating the text key and value of the todo task and click send.

Add text content for the todo

Let's check our todo got created by calling the GET /todos endpoint, which will list all todos.

Check that our todo get created

Let's toggle our todo as done, navigate to the PATCH /todos/{id}/toggle and enter the correct id to toggle.

Toggle our todo as done

Finally, let's check our todo got toggled to done by calling the GET /todos endpoint.

View the todo change

Feel free to test the other endpoints to update or delete the todo items.

Deploying to AWS

Create your stack

Create a AWS stack called aws-staging for your staging environment.

nitric stack new aws-staging aws

Inside the stack file, ensure you set your region.

# The nitric provider to use
provider: nitric/aws@1.6.2
# The target aws region to deploy to
# See available regions:
# https://docs.aws.amazon.com/general/latest/gr/lambda-service.html
region: us-east-2

Deploy

Go ahead and deploy to AWS using the nitric up command. Ensure you have setup your AWS credentials correctly.

nitric up

Tear down

The avoid unwanted costs of running your test app, you can tear down the stack using the nitric down command.

nitric down

Prefer to use a different ORM? We also have these guides:

Nitric SQL with Prisma.