Prisma ORM Basics in nextjs 15

Learn All you need to know about prisma

Table of Contents

Prisma is an open-source ORM for Node.js and TypeScript. It is used as an alternative to writing plain SQL, or using another database access tool such as SQL query builders

Installation

npm install prisma --save-dev

{/* Optional */}
npm install typescript ts-node @types/node --save-dev

Initailize prisma with sqlite

npx prisma init --datasource-provider sqlite

Now a prisma folder would be created and you'll have schema.prisma file in it.

Model your data in the Prisma schema


model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  password String
  name  String?
}

Models in the Prisma schema have two main purposes:

Represent the tables in the underlying database
Serve as foundation for the generated Prisma Client API

Run a migration to create your database tables with Prisma Migrate

npx prisma migrate dev --name init

This command created a new migration, applied it to the database, and updated the Prisma Client API.

Know in detail

this command did 3 things

It created a new SQL migration file for this migration in the prisma/migrations directory.
It executed the SQL migration file against the database.
It ran prisma generate under the hood (which installed the @prisma/client package and generated a tailored Prisma Client API based on your models)

🎉 Congratulations, you now have your database and tables ready.

Now you can interact with the prisma client to interact with db, but while using Nextjs you can encounter problem connection pooling which means since nextjs is a edge time framework so it is not actively running all the time, and if you don't use global prisma client (which I'll show below) it will create unnecessary connections with the database 4. Create a @/lib/db.ts file and create a global Client reference -> https://www.prisma.io/docs/orm/more/help-and-troubleshooting/help-articles/nextjs-prisma-client-dev-practices

import { PrismaClient } from "@prisma/client";

const prismaClientSingleton = () => {
  return new PrismaClient();
};

declare const globalThis: {
  prismaGlobal: ReturnType<typeof prismaClientSingleton>;
} & typeof global;

const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();

export default prisma;

if (process.env.NODE_ENV !== "production") globalThis.prismaGlobal = prisma;

Problem: Many users see a warning about too many Prisma Client instances running in Next.js development. This is due to hot reloading creating new instances with each run.

Solution: Create a single Prisma Client instance globally and reuse it to avoid unnecessary connections.

Basic CRUD operations queries:

Create

Create a single record

await prisma.user.create({
  data: {
    email: "faizan@unicornspace.tech",
    password: "hashedP$#$word",
  },
});

Create a many records

await prisma.user.createMany({
  data: [
    {
      email: "faizan@unicornspace.tech",
      password: "hashedP$#$word",
    },
    {
      email: "unicornspace@gmail.com",
      password: "hashedP$#$word",
    },
  ],
});

Read

Find all users

const users = await prisma.user.findMany();

Find a specific user

const users = await prisma.user.findUnique({
  where: {
    email: "faizan@unicornspace.tech",
  },
});

Find users, sort them

const sortedUsers = await prisma.user.findMany({
  orderBy: {
    name: `asc`, // 'desc'
  },
});

Filters

// * FILTERS
// * not
const notFilter = await prisma.user.findMany({
  where: {
    name: { not: "Pam" },
  },
});

// * in, notIn
const inFilter = await prisma.user.findMany({
  where: {
    name: { in: ["Pam", "Dwight"] },
  },
});

// * lt, lte, gt, gte
const ltFilter = await prisma.user.findMany({
  where: {
    age: { lt: 30 },
  },
});

// * contains, startsWith, endsWith
const containsFilter = await prisma.user.findMany({
  where: {
    name: { contains: "a" },
  },
});

// * AND, OR, NOT
const andFilter = await prisma.user.findMany({
  where: {
    AND: [{ name: "Pam" }, { age: { lt: 30 } }],
  },
});

// ARRAY FILTERING
// * some, none, every
// ! hypothetical example
// const someFilter = await prisma.user.findMany({
//   where: {
//     posts: {
//       some: {
//         title: 'Hello World',
//       },
//     },
//   },
// })

How to add multiple values or loop in the query

tasks.forEach(async (task) => {
  await prisma.task.create({
    data: {
      title: task.title,
      description: task.description,
    },
  });
});

// OR
for (const task of tasks) {
  await prisma.task.create({
    data: {
      title: task.title,
      description: task.description,
    },
  });
}

Enum

enum Role {
  USER
  ADMIN
}

model User {
  id String @id @default(uuid())
  role Role @default(USER)
}

Enums are useful for determining the role of a user, or the status of a post (draft, published, etc...)

Note: Enums are not supported by Sqlite

a gradient effect for website aesthetics