Here I dive in Prisma. Discussing the basics of what Prisma is, the functionality and how it could be intergrated into a node.js application.

What is Prisma?

Essentially it’s an ORM for Node.js and TypeScript. What’s an ORM you ask? Well, ORM stands for object relational model, this means it can connect your application to a database.

By creating a prisma schema, a bit like a database schema, here you can define a database model and relations. To connect a database you will need a prisma client. To update your schema, you will need to run the prisma mirgrate command: npx prisma migrate dev. prisma studio is an admin UI to view and edit data inside your database.

Prisma Schema

This is the main config file. In your application it will be called schema.prisma.

When a prisma command is called, the cli reads information from the schema. There are two examples of when this may be invooked:

• prisma generate: reads all information from the schema to generate the correct data source for prisma client
• prisma migrate dev: reads all data sources and data model defintion to create a new migration

You can also use environment variables within schema files to provide config for CLI command.

Data Sources

A data source determines how Prisma connects to a database.

Within the schema the following will be able to determine this.

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

Prisma can only have one data source but the data source url can be overridden with prisma client.

Prisma resolves SSL certificates within the ./prisma directory, so all certificates files should be within that folder.

Generators

A schema can have one or more generators. When the prisma generate command is rum, the generator determines assets created.

enerator client {
  provider = "prisma-client-js"
  output = "./generated/prisma-client-js"
}

Data Model

This defines the application models, which are also known as prisma models.

Models represetn the entites of the application, map to tables/collections(MongoDB) within a database and form the foundation of the queries available in the generated prisma client api.

model User {
  id          String      @id @default(auto()) @map("_id") @db.ObjectId
  createdAt   DateTime    @default(now())
  name        String?
  email       String      @unique
  role        Role        @default(USER)
}

Relations

One-to-one relations

One record can be connect to both sides of the relation.

model User {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
  profile Profile?
}

model Profile {
  id     String @id @default(auto()) @map("_id") @db.ObjectId
  user   User   @relation(fields: [userId], references: [id])
  userId String @unique @db.ObjectId // relation scalar field (used in the `@relation` attribute above)
}

• A user can have zero or one profiles
• A profile must always be connected to one user

Optional

model User {
  id      Int      @id @default(autoincrement())
  profile Profile? // No relation scalar - must be optional
}

One-to-many relations

One relation on onside of the relation can be connected to zero or more records on the other side.

model User {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  posts Post[]
}

model Post {
  id       String @id @default(auto()) @map("_id") @db.ObjectId
  author   User   @relation(fields: [authorId], references: [id])
  authorId String @db.ObjectId
}

• A user can have zero or more posts
• A post must always have an author

Many-to-one relations

Zero or more records on one side of the relation can be connected to zero or more records on the other side.

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "Category" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,

    CONSTRAINT "Category_pkey" PRIMARY KEY ("id")
);


-- Relation table + indexes --

CREATE TABLE "CategoriesOnPosts" (
    "postId" INTEGER NOT NULL,
    "categoryId" INTEGER NOT NULL,
    "assignedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "assignedBy" TEXT NOT NULL,

    CONSTRAINT "CategoriesOnPosts_pkey" PRIMARY KEY ("postId","categoryId")
);

ALTER TABLE "CategoriesOnPosts" ADD CONSTRAINT "CategoriesOnPosts_postId_fkey" FOREIGN KEY ("postId") REFERENCES "Post"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "CategoriesOnPosts" ADD CONSTRAINT "CategoriesOnPosts_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Complete Example of Schema

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

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

model User {
  id          String      @id @default(auto()) @map("_id") @db.ObjectId
  createdAt   DateTime    @default(now())
  name        String?
  email       String      @unique
  role        Role        @default(USER)
}

enum Role {
  USER
}

Although the default schema name is schema.prisma, if it has a different name you can do the following: prisma generate --schema ./database/myschema.prisma

Prisma Client

To generate a prisma client, you will first need to have set up a prisma schema file, with a generator and at least one model.

Install Prisma CLI

npx install prisma --save-dev
npx prisma

Install Prisma Client in your project

npm install @prisma/client

Use Prisma Client to send queries to your database

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()
// use `prisma` in your application to read and write data in your DB

// run inside `async` function
const newUser = await prisma.user.create({
  data: {
    name: 'example',
    email: 'example@prisma.io',
  },
})

const users = await prisma.user.findMany()

When you make changes to your database, which are reflect in the schema, you need to re-generate the Prisma client to update the code within the Node modules for prisma client.

prisma generate

Prisma Migrate

Does not apply to MongoDB. Instead of migrate dev, use db push for MongoDB.

Create the first migration

prisma migrate dev --name init

The schema should be in sync with your database schema and here is the initialised migration history:

migrations/
  └─ 20210313140442_init/
    └─ migration.sql

Now add any additional fields to the schema

model User {
  id       Int    @id @default(autoincrement())
  jobTitle String
  name     String
  posts    Post[]
}

Create the second migration

prisma migrate dev --name added_job_title

Here is what the migration history contains:

migrations/
  └─ 20210313140442_init/
    └─ migration.sql
  └─ 20210313140442_added_job_title/
    └─ migration.sql

Stack Support

REST