🗃️

Prisma Database Guide

Jun 10, 2025

Overview

This lecture provides a complete introduction to Prisma, covering setup, schema design, relationships, queries, updates, and deletions for SQL-based databases, especially with Node.js.

Getting Started with Prisma

  • Node.js and a database (e.g., Postgres) must be installed before using Prisma.
  • Initialize your project with npm init -y and install dependencies: prisma, typescript, ts-node, @types/node, and nodemon.
  • Create a tsconfig.json for TypeScript configuration.
  • Initialize Prisma with npx prisma init --datasource-provider postgresql to create the basic schema and .env file.

Setting Up the Schema

  • The schema.prisma file defines your data models, generators, and data source.
  • Use the Prisma VS Code extension for syntax highlighting and formatting.
  • The generator (usually prisma-client-js) produces a TypeScript client for database interaction.
  • Data source specifies the database type and URL (from .env).

Defining Models and Fields

  • Models represent tables; each model must have a unique id field.
  • Field types include: Int, String, Boolean, Float, Decimal, DateTime, Json, Bytes.
  • Relationships are handled through referencing other models.
  • Run migrations (npx prisma migrate dev --name <name>) to sync schema changes to the database.

Relationships in Prisma

  • One-to-Many: e.g., a user has many posts.
  • Many-to-Many: e.g., posts can have multiple categories; categories can have multiple posts.
  • One-to-One: e.g., each user has a unique preference record.
  • Use relation attributes and field modifiers ([] for arrays, ? for optional fields).

Attributes and Indexes

  • Use @unique for unique fields, @default for default values, @updatedAt for automatic timestamping.
  • Block-level attributes: @@unique([field1, field2]) for composite uniqueness, @@index([field]) for indexes, @@id([field1, field2]) for composite primary keys.

Enums

  • Define enums for fields with limited possible values using enum.
  • Example: enum Role { BASIC ADMIN EDITOR }.

CRUD Operations with Prisma Client

  • Install Prisma Client: npm i @prisma/client.
  • Use TypeScript for type safety and autocompletion.
  • Create records with create or createMany.
  • Read records using findUnique, findFirst, findMany, with support for filtering, pagination, ordering, and distinct.
  • Update with update or updateMany, supporting field-level operations (increment, decrement, etc.).
  • Delete with delete or deleteMany.
  • Nested create, connect, and disconnect allow managing related records in one operation.

Advanced Querying

  • Complex filters: AND, OR, NOT, plus operators like in, notIn, contains, startsWith, etc.
  • Relationship queries: filter based on related models using some, every, none, or is, isNot.

Key Terms & Definitions

  • Model — A schema object representing a database table.
  • Field — A property/column in a model/table.
  • Migration — A change applied to the database schema.
  • Generator — Defines what code is generated from the Prisma schema.
  • Data Source — Configuration for your database connection.
  • Enum — A field type with a fixed set of values.
  • Relation Attribute — Syntax to define relationships between models.

Action Items / Next Steps

  • Review and experiment with your schema.prisma file, defining relationships and field types.
  • Practice running migrations and CRUD operations with Prisma Client.
  • Explore additional Prisma documentation for advanced features as needed.

Full transcript