Nitric SQL Databases with Prisma

This guide demonstrates how to use Prisma 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 Prisma

npm install prisma @prisma/client
npx prisma init

We install prisma for database schema and migration management, and @prisma/client for database interactions.

Create a Nitric SQL Database and Connect Prisma to it

import { sql } from '@nitric/sdk'
import { PrismaClient } from '@prisma/client'

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

let prisma: PrismaClient

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

    prisma = new PrismaClient({
      datasources: {
        db: {
          url: connectionString,
        },
      },
    })
  }
  return prisma
}

// export our getClient function, which will be used to get the prisma 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 and configure PrismaClient to connect to the database using Nitric's connection string.

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 . .

# Generate Prisma client
RUN npx prisma generate

# Run the migrations and start the application when the Docker container starts
ENTRYPOINT ["sh", "-c", "npx prisma migrate deploy"]

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

Setup Prisma schema

Next, set up the Prisma schema file. This file contains the database schema and configurations. The DB_URL environment variable is used by Nitric during cloud migrations; however, during local development, we can add it via a .env file (we will do that later).

datasource db {
  provider = "postgresql"
  url      = env("DB_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Todo {
  id    Int     @id @default(autoincrement())
  text  String
  done  Boolean @default(false)
}

This schema defines a Todo model with fields id, text, and done.

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'

const mainApi = api('main')

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

  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.todo.create({
    data: {
      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.todo.update({
    where: { id: parseInt(id) },
    data: { text },
  })
})

// 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()
  const todo = await db.todo.findUnique({
    where: { id: parseInt(id) },
  })

  if (todo) {
    await db.todo.update({
      where: { id: parseInt(id) },
      data: { done: !todo.done },
    })
  }
})

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

  await db.todo.delete({
    where: { 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, and generate the Prisma client using the prisma migrate command:

npx prisma migrate dev --name init

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 an 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 set up your AWS credentials correctly.

nitric up

Tear down

To 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 Drizzle.