How to use Prisma

A Prisma tutorial

Prisma is an interesting ORM.

An ORM is an abstraction layer over a database.

I’ve had the pleasure to use Prisma in a few different projects in the past months, and here I want to show you how easy it is to get started (and keep going).

I’m going to use Prisma in a React application based on Next.js.

You can create one new Next.js app in a folder with

npx create-next-app

First thing you have to do to add Prisma is to include prisma in your dev dependencies:

npm install -D prisma

Now you have access to the Prisma CLI utility by using npx. Try running:

npx prisma

and you’ll see the instructions on how to use it.

Now run this to setup Prisma for your project:

npx prisma init

This will create a prisma folder, and inside it, a schema.prisma file:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

It also created a .env file, in case you didn’t have one already, with the DATABASE_URL environment variable:

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

This has to point to your database.

Let’s get this point out of the way first. Prisma supports many different kinds of (relational) databases. I’ve used it with PostgreSQL and SQLite, but it supports also MySQL, AWS Aurora, MariaDB.

In production I like to use DigitalOcean’s managed database (obligatory referral link for a $100 free credit here), but for a quick example app, Railway.app (referral link) is a cool and free choice.

After you sign up you can provision a PostgreSQL database with a click:

and immediately after, you’ll get the connection URL:

Copy that to your .env file as the DATABASE_URL value.

Now it’s time to add a model to the schema, which will be translated to a database table.

Note that you can also do the opposite, if you have a database already populated with tables, by running npx prisma introspect. Prisma will generate the schema from the database.

Let’s say we are a fancy billionaire that likes to collect cars. We create a Car model to store the list of cars we want to buy:

model Car {
  id Int @id @default(autoincrement())
  brand String
  model String
  created_at DateTime @default(now())
  bought Boolean @default(false)
}

I highly recommend you to look up everything in the Prisma schema reference documentation.

This model defines 5 fields: id, brand, model, created_at, bought, each with its type, be it Int, String, DataTime, or Boolean.

id has the @id attribute which means it’s the primary key, which tells the database management system to make it unique. And it defaults to a value that’s automatically incremented, so whenever we add one new item, it always have a unique integer number that increments: 1, 2, 3, 4…

Note that you can also use a unique value with @default(cuid()) or @default(uuid()).

created_at defaults to the current datetime with @default(now()), and bought defaults to false.

Now we need to sync the database with our schema. We do that by running the command npx prisma migrate to create our first migration:

npx prisma migrate dev

Now you can see in the database, there’ll be a Car table:

and a file in your codebase in the prisma/migrations folder with the commands used to create those tables, in this case:

-- CreateTable
CREATE TABLE "Car" (
    "id" SERIAL NOT NULL,
    "brand" TEXT,
    "model" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "bought" BOOLEAN NOT NULL DEFAULT false,

    PRIMARY KEY ("id")
);

Any time you change the schema you need to run this npx prisma migrate dev command to apply the changes.

Great! Now we can use Prisma to insert data into the database, retrieve data, delete data.. and all that.

Now install the @prisma/client package with

npm install @prisma/client

Create a lib folder, and inside it a prisma.js file. In there, we initialize the PrismaClient object:

import { PrismaClient } from '@prisma/client'

let global = {}

const prisma = global.prisma || new PrismaClient()

if (process.env.NODE_ENV === 'development') global.prisma = prisma

export default prisma

This piece of code is needed to avoid excessive instantiations of Prisma when we run in development mode, with frequent refreshes due to hot module reloading. We’re basically adding prisma to a global variable first time we run this, and reuse that variable the next times.

Now in any file you want to use Prisma, you can add

import prisma from 'lib/prisma'

and you’re ready to go.

To retrieve all the cars, you use prisma.car.findMany():

const cars = await prisma.car.findMany()

You can pass an object to filter the data, for example by selecting all Ford cars:

const cars = await prisma.car.findMany({
  where: {
    brand: 'Ford',
  },
})

You can look for a single car by its id value, using prisma.car.findUnique():

const car = await prisma.car.findUnique({
  where: {
    id: 1,
  },
})

You can add a new car using prisma.car.create():

const car = await prisma.car.create({
  brand: 'Ford',
  model: 'Fiesta',
})

You can delete a car by using prisma.car.delete():

await prisma.job.delete({
  where: { id: 1 },
})

You can update the data of a car using prisma.car.update():

await prisma.job.delete({
  where: { id: 1 },
  data: {
    bought: true,
  },
})

You can do a whole lot more, but those are the basics, all you need to get started and 95% of what you need in a simple CRUD application.