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
db.ts
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
migrate.dockerfile
# 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).
prisma/schema.prisma
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.
services/todos.ts
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:
.env
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.
Then add some content to the body of the request, stating the text key and value of the todo task and click send.
Let's check our todo got created by calling the GET /todos endpoint, which will list all todos.
Let's toggle our todo as done, navigate to the PATCH /todos/{id}/toggle and enter the correct id to toggle.
Finally, let's check our todo got toggled to done by calling the GET /todos endpoint.
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.
nitric.staging-aws.yaml
# 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