PostgreSQL with an ORM — Prisma
Model tables in a schema, run migrations, query and relate records, and avoid N+1 queries.
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.
| Entity | Important fields | Relationship |
|---|---|---|
| Student | id, email, createdAt | One student can have many plans |
| StudyPlan | id, ownerId, title, examDate | Belongs to one student |
| StudySession | id, planId, title, scheduledAt, completedAt | Belongs to one plan |
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.
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.
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.
# 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 generateCommands 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.
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.
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.
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.
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.
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.
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.
- Store connection strings in the deployment secret manager or local ignored environment file.
- Use a least-privilege database role for the application.
- Review migration SQL and test it against a representative database.
- Set connection pooling appropriate for serverless or long-lived runtimes.
- Monitor query duration, database connections, and migration outcome.
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.
Persist the Planner Safely
Practice
A relational plan model with a migration
- Model Student, StudyPlan, and StudySession with keys, relations, and appropriate constraints.
- Create and review a migration that adds plans and sessions without deleting existing data.
- Implement createPlan and listPlansForOwner using explicit fields and an authenticated owner ID.
- Load a plan with a bounded list of sessions and compare it with a per-plan query loop.
- 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.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
