Prisma ORM

Learn how to use Prisma ORM in Sushify Next.js

Prisma is the default ORM in Sushify Next.js, providing a type-safe and intuitive way to interact with your PostgreSQL database.

What is Prisma?

Prisma is a next-generation ORM that makes database access easy with an auto-generated and type-safe query builder. It consists of:

Prisma Client: Auto-generated database client
Prisma Migrate: Declarative data modeling and migrations
Prisma Studio: Visual database browser and editor

Setup

1. Schema Location

The Prisma schema is located at:

packages/database/prisma/schema.prisma

This file defines your database structure, models, and relationships.

The schema starts with the datasource configuration:

packages/database/prisma/schema.prisma

datasource db {
  provider = "postgresql"  // ⚠️ Must match Better Auth adapter provider
  url      = env("DATABASE_URL")
}

Important: The provider value here must match the provider in your Better Auth configuration (packages/auth/auth.ts). See the Database Overview for more details.

2. Generate Prisma Client

After any schema changes, regenerate the Prisma Client:

pnpm --filter database generate

This command:

Generates the type-safe Prisma Client
Creates Zod schemas for validation
Updates TypeScript types

3. Push Schema to Database

For development, push your schema directly to the database:

pnpm --filter database push

This syncs your Prisma schema with the database without creating migration files.

4. Create Migrations (Production)

For production environments, create and apply migrations:

pnpm --filter database migrate

This creates migration files in packages/database/prisma/migrations/ and applies them to your database.

Using Prisma Client

Importing the Client

Import the database client from the package:

import { db } from "@repo/database";

The client is a singleton, ensuring you reuse the same connection across your application.

Basic Queries

Find Many Records

// Get all users
const users = await db.user.findMany();

// Get users with filtering
const verifiedUsers = await db.user.findMany({
  where: {
    emailVerified: true,
  },
});

// Pagination
const users = await db.user.findMany({
  take: 10,    // Limit
  skip: 20,    // Offset
});

// Search with contains
const users = await db.user.findMany({
  where: {
    name: {
      contains: "john",
      mode: "insensitive", // Case-insensitive search
    },
  },
});

Find Unique Record

// Find by unique field
const user = await db.user.findUnique({
  where: {
    id: "user-id",
  },
});

// Find by email
const user = await db.user.findUnique({
  where: {
    email: "user@example.com",
  },
});

// Throw error if not found
const user = await db.user.findUniqueOrThrow({
  where: {
    id: "user-id",
  },
});

Find First Record

const user = await db.user.findFirst({
  where: {
    emailVerified: true,
  },
  orderBy: {
    createdAt: "desc",
  },
});

Creating Records

// Create a single record
const user = await db.user.create({
  data: {
    email: "user@example.com",
    name: "John Doe",
    emailVerified: true,
    createdAt: new Date(),
    updatedAt: new Date(),
  },
});

// Create with relations
const user = await db.user.create({
  data: {
    email: "user@example.com",
    name: "John Doe",
    emailVerified: true,
    createdAt: new Date(),
    updatedAt: new Date(),
    accounts: {
      create: {
        providerId: "github",
        accountId: "github-user-id",
        createdAt: new Date(),
        updatedAt: new Date(),
      },
    },
  },
  include: {
    accounts: true,
  },
});

Updating Records

// Update a record
const user = await db.user.update({
  where: {
    id: "user-id",
  },
  data: {
    name: "Jane Doe",
    updatedAt: new Date(),
  },
});

// Update many records
const result = await db.user.updateMany({
  where: {
    emailVerified: false,
  },
  data: {
    banned: true,
  },
});

console.log(`Updated ${result.count} users`);

Deleting Records

// Delete a record
const user = await db.user.delete({
  where: {
    id: "user-id",
  },
});

// Delete many records
const result = await db.user.deleteMany({
  where: {
    banned: true,
  },
});

console.log(`Deleted ${result.count} users`);

Counting Records

// Count all records
const count = await db.user.count();

// Count with filter
const verifiedCount = await db.user.count({
  where: {
    emailVerified: true,
  },
});

Relations

Prisma makes working with related data intuitive.

Include Relations

// Include related accounts
const user = await db.user.findUnique({
  where: {
    id: "user-id",
  },
  include: {
    accounts: true,
    sessions: true,
  },
});

// Nested includes
const organization = await db.organization.findUnique({
  where: {
    id: "org-id",
  },
  include: {
    members: {
      include: {
        user: true,
      },
    },
  },
});

Select Specific Fields

// Select only needed fields
const user = await db.user.findUnique({
  where: {
    id: "user-id",
  },
  select: {
    id: true,
    name: true,
    email: true,
    accounts: {
      select: {
        providerId: true,
      },
    },
  },
});

Filter by Relations

// Find users who have GitHub accounts
const usersWithGithub = await db.user.findMany({
  where: {
    accounts: {
      some: {
        providerId: "github",
      },
    },
  },
});

// Find organizations with admin members
const orgs = await db.organization.findMany({
  where: {
    members: {
      some: {
        role: "admin",
      },
    },
  },
});

Transactions

Prisma supports transactions to ensure data consistency.

Interactive Transactions

const result = await db.$transaction(async (tx) => {
  // Create user
  const user = await tx.user.create({
    data: {
      email: "user@example.com",
      name: "Sushify",
      emailVerified: true,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
  });

  // Create organization
  const org = await tx.organization.create({
    data: {
      name: "Sushify Dev",
      createdAt: new Date(),
    },
  });

  // Add user as member
  await tx.member.create({
    data: {
      organizationId: org.id,
      userId: user.id,
      role: "admin",
      createdAt: new Date(),
    },
  });

  return { user, org };
});

Sequential Transactions

const [user, updatedOrg] = await db.$transaction([
  db.user.create({
    data: {
      email: "user@example.com",
      name: "Sushify",
      emailVerified: true,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
  }),
  db.organization.update({
    where: { id: "org-id" },
    data: { name: "Updated Name" },
  }),
]);

Pre-built Queries

Sushify Next.js includes pre-built query functions in packages/database/prisma/queries/:

User Queries

import {
  getUsers,
  getUserById,
  getUserByEmail,
  createUser,
  updateUser,
} from "@repo/database";

// Get paginated users
const users = await getUsers({
  limit: 10,
  offset: 0,
  query: "john", // Optional search term
});

// Get user by ID
const user = await getUserById("user-id");

// Get user by email
const user = await getUserByEmail("user@example.com");

// Create user
const newUser = await createUser({
  email: "user@example.com",
  name: "Sushify",
  role: "user",
  emailVerified: true,
  onboardingComplete: false,
});

// Update user
const updatedUser = await updateUser({
  id: "user-id",
  name: "Sushify Dev",
});

Prisma Studio

Prisma Studio is a visual database browser that allows you to:

View and edit data
Filter and sort records
Explore relationships
Run queries

Open Prisma Studio

pnpm --filter database studio

This opens Prisma Studio at http://localhost:5555.

Best Practices

1. Select Only Needed Fields

Reduce data transfer by selecting only required fields:

const users = await db.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

2. Use Pagination

For large datasets, always use pagination:

const users = await db.user.findMany({
  take: 50,
  skip: page * 50,
});

3. Handle Errors

Always handle database errors appropriately:

try {
  const user = await db.user.create({ ... });
} catch (error) {
  if (error.code === "P2002") {
    // Unique constraint violation
    throw new Error("User already exists");
  }
  throw error;
}

4. Use Indexes

Define indexes in your schema for frequently queried fields:

model User {
  id    String @id @default(cuid())
  email String @unique

  @@index([email])
}

Prisma ORM

On this page