PostgreSQL with an ORM — Prisma

Model tables in a schema, run migrations, query and relate records, and avoid N+1 queries.

Est. 35 min Updated Suggest a fix
// Part 01 — Relational model

Plan Data as Tables with Clear Relationships

PostgreSQL is a relational database: data is stored in tables, rows have columns, and constraints protect important rules. A study planner can have students, plans, and sessions. One student can own multiple plans, and one plan can contain many sessions. Model those relationships explicitly rather than storing a comma-separated list inside one text column.

Every row should have an identifier. Add constraints for required values and relationships, and consider which queries need indexes. A database constraint protects data even if two requests arrive at the same time or a bug bypasses the user interface.

EntityImportant fieldsRelationship
Studentid, email, createdAtOne student can have many plans
StudyPlanid, ownerId, title, examDateBelongs to one student
StudySessionid, planId, title, scheduledAt, completedAtBelongs to one plan
💡 Note
An ORM maps application objects to relational data, but it does not remove the need to understand SQL, constraints, transactions, indexes, or query costs.
// Part 02 — Prisma schema

Describe Models, Fields and Relations

Prisma's schema is a declarative description of models and their database mapping. A relation field in the schema corresponds to a foreign key in the database. Keep ownership fields explicit so queries can scope records to the signed-in student. Configure the database connection through an environment variable that is not committed to source control.

prisma/schema.prisma
typescript
generator client {
  provider = "prisma-client-js"
}

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

model Student {
  id        String     @id @default(cuid())
  email     String     @unique
  createdAt DateTime   @default(now())
  plans     StudyPlan[]
}

model StudyPlan {
  id        String        @id @default(cuid())
  ownerId   String
  title     String
  examDate  DateTime?
  createdAt DateTime      @default(now())
  owner     Student       @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  sessions  StudySession[]
  @@index([ownerId, createdAt])
}

model StudySession {
  id          String    @id @default(cuid())
  planId      String
  title       String
  subject     String
  scheduledAt DateTime?
  completedAt DateTime?
  plan        StudyPlan @relation(fields: [planId], references: [id], onDelete: Cascade)
  @@index([planId, scheduledAt])
}

The unique email constraint prevents duplicate accounts, while the foreign keys ensure a plan cannot refer to a missing owner. Cascade deletion is a product decision: if deleting a student should preserve records for retention or audit, choose a different lifecycle. Think through privacy and recovery requirements before selecting cascading behavior.

⚠️ Important
A schema edit is not itself a safe production deployment. Review the generated migration, backups, locking behavior, and whether old and new application versions can both run during rollout.
// Part 03 — Migrations

Evolve the Database with Reviewed Migrations

A migration is a versioned database change that can be reviewed and applied consistently. In local development, create a migration after changing the schema, inspect the SQL it will run, and commit the migration files with the application. In production, apply committed migrations through the deployment process rather than editing a live database by hand.

migration-workflow.sh
bash
# Create a development migration after editing schema.prisma
npx prisma migrate dev --name add-study-plans

# Inspect the generated migration before committing it
# Apply committed migrations in a deployment environment
npx prisma migrate deploy

# Regenerate the client when the schema changes
npx prisma generate

Commands shown here are common Prisma CLI workflows; use the project manifest and official docs for the exact command available in the installed Prisma version. Do not run a development migration command against production. A deployment migration should be repeatable as part of a controlled release, and the team should know how to recover from a failed migration.

// Part 04 — Queries

Create and Read Data Through the Generated Client

Prisma Client provides typed query methods generated from the schema. Keep a reusable client instance in a server-only module, then call it from server code or API services. The example deliberately accepts an owner ID supplied by the caller: your application must obtain that ID from a verified authentication session, never blindly trust a request field.

src/data/plans.ts
typescript
import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

export async function createPlan(input: {
  ownerId: string
  title: string
  examDate?: Date
}) {
  return prisma.studyPlan.create({
    data: {
      ownerId: input.ownerId,
      title: input.title,
      examDate: input.examDate,
    },
    select: { id: true, title: true, examDate: true, createdAt: true },
  })
}

export async function listPlansForOwner(ownerId: string) {
  return prisma.studyPlan.findMany({
    where: { ownerId },
    orderBy: { createdAt: "desc" },
    select: { id: true, title: true, examDate: true },
  })
}

Choose only the fields the caller needs. An explicit select reduces accidental exposure of private fields and makes the data shape clear. For a list that can grow, add pagination and a stable ordering rather than loading every plan forever.

// Part 05 — Relations

Load Related Sessions Deliberately

Relations let a query include data from related tables. If the page needs each plan with its next few sessions, request that shape deliberately and select only the relevant columns. Avoid a loop that queries sessions once per plan; that N+1 pattern can turn one page request into many database round trips.

src/data/plan-details.ts
typescript
export async function listPlanSummaries(ownerId: string) {
  return prisma.studyPlan.findMany({
    where: { ownerId },
    orderBy: { createdAt: "desc" },
    select: {
      id: true,
      title: true,
      sessions: {
        orderBy: { scheduledAt: "asc" },
        take: 5,
        select: { id: true, title: true, scheduledAt: true, completedAt: true },
      },
    },
  })
}

This is one logical query request with a bounded related collection. Inspect actual query behavior and database plans when performance matters; the ORM's API shape does not guarantee a particular query strategy in every configuration. Add indexes that match frequent filters and sort orders, then verify them against real workload patterns.

🎯 Pro Tip
Select a focused read model for the screen rather than returning an entire database record graph. Smaller responses are easier to secure and faster to render.
// Part 06 — Transactions and operations

Protect Multi-Step Changes

Use a transaction when several database writes must succeed or fail together. For example, creating a plan and its initial sessions should not leave a half-created plan if one session insert fails. Transactions do not automatically prevent every concurrency bug, so choose constraints and isolation behavior appropriate to the business rule.

src/data/create-plan-with-sessions.ts
typescript
export async function createPlanWithSessions(input: {
  ownerId: string
  title: string
  sessions: { title: string; subject: string }[]
}) {
  return prisma.studyPlan.create({
    data: {
      ownerId: input.ownerId,
      title: input.title,
      sessions: {
        create: input.sessions.map((session) => ({
          title: session.title, subject: session.subject,
        })),
      },
    },
    include: { sessions: true },
  })
}

Nested writes are useful for closely related records. For more complex workflows, use an explicit transaction API, but keep external network calls outside a database transaction where possible: a slow AI provider call can hold locks open and exhaust connections.

  1. Store connection strings in the deployment secret manager or local ignored environment file.
  2. Use a least-privilege database role for the application.
  3. Review migration SQL and test it against a representative database.
  4. Set connection pooling appropriate for serverless or long-lived runtimes.
  5. Monitor query duration, database connections, and migration outcome.
// Part 07 — Pitfalls

Common Mistakes

Changing a production schema manually

Why: The application and migration history no longer describe the same database.

Fix: Create a reviewed migration and apply it through a release process.

Returning all columns and relations by default

Why: Sensitive fields and unnecessary data can escape the server.

Fix: Use focused select clauses and a purpose-built response shape.

Querying child records inside a loop

Why: A growing list creates many database round trips.

Fix: Load related records in a bounded query and inspect query behavior.

Opening a database connection per request without limits

Why: Concurrent app instances can exhaust database connections.

Fix: Use a suitable client lifecycle, pooling, and platform-specific guidance.

// Part 08 — Practice

Persist the Planner Safely

Practice

A relational plan model with a migration

  1. Model Student, StudyPlan, and StudySession with keys, relations, and appropriate constraints.
  2. Create and review a migration that adds plans and sessions without deleting existing data.
  3. Implement createPlan and listPlansForOwner using explicit fields and an authenticated owner ID.
  4. Load a plan with a bounded list of sessions and compare it with a per-plan query loop.
  5. Write down how the deployment will apply migrations and where database credentials will be stored.

Hint: Keep all database calls on the server. Add authorization before any query keyed by a plan ID.

🎯 Key Takeaways

  • ✓Model related concepts as relational tables with keys, constraints, and explicit ownership.
  • ✓Prisma schema describes models; migrations evolve the real database over time.
  • ✓Review migration SQL and use a controlled production deployment process.
  • ✓Use generated client queries with explicit selection and authorization-scoped filters.
  • ✓Bound related records and avoid N+1 query patterns.
  • ✓Consider pooling, backups, least privilege, and transaction scope in production.
  • Consult the official PostgreSQL and Prisma documentation for schema, migration, and connection guidance. Database URLs are secrets and should never be committed.

Next

What comes next

Connect student identity to database ownership with password hashing, safe cookies, and authorization checks.

Lesson 08 · Authentication — Sessions, JWTs and OAuth →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...