Store data permanently in PostgreSQL through an ORM. Set up Prisma, define a model, run a migration, do full CRUD, and connect tables with relations — reading related rows back as nested objects instead of writing JOINs by hand.
Why: NoSQL databases (like MongoDB) store flexible, JSON-like documents — easy to start with. Relational/SQL databases (like PostgreSQL or MySQL) store data in tables with strict columns and relationships. You usually talk to either through a library, not raw queries. In this lesson we practice with PostgreSQL.
Why: we talk to PostgreSQL through Prisma — a modern toolkit that lets you query with simple, typed method calls instead of writing SQL by hand. The install adds: prisma (the CLI), @prisma/client (the typed client you import), pg (the actual PostgreSQL driver), and @prisma/adapter-pg (the adapter that lets Prisma use pg). Note: you need a running PostgreSQL server. You can install PostgreSQL locally — the installer runs the server on port 5432, asking you to set a password for the default "postgres" user.
$ pnpm add prisma @prisma/client @prisma/adapter-pg pg$ pnpm exec prisma init --datasource-provider postgresqlWhy: a Prisma "model" describes one table and its columns. The datasource block tells Prisma which database to use. @id marks the primary key, @default(autoincrement()) auto-numbers each new row, and Int? means the column is optional.
# .env (keep this out of version control)
# replace "secret" with the password you set when installing PostgreSQL
DATABASE_URL="postgresql://postgres:secret@localhost:5432/myapp"// schema.prisma
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "postgresql"
}
model User {
id Int @id @default(autoincrement())
name String
email String @unique
age Int?
}Why: a "migration" turns your schema into real tables in PostgreSQL. "prisma migrate dev" creates the database if it does not exist yet, builds the table, and generates the typed client you import in code. Re-run it whenever you change the schema.
$ pnpm exec prisma migrate dev --name initWhy: "prisma generate" builds the typed client into the output folder from your schema, so the import in your code resolves. Migrate runs this for you, but run it directly any time you change the schema without migrating. Note: the client is TypeScript, so run your file on Node 23.6+ (older 22.6+ needs node --experimental-strip-types), and add "type": "module" to package.json.
$ pnpm dlx prisma generateWhy: CRUD = Create, Read, Update, Delete — the four basic things you do with data. Prisma gives you a typed method for each on prisma.user, and they all return Promises. This runs against your PostgreSQL table.
// app.js
// Create
import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client.ts";
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });
// Create (upsert so the script is safe to re-run)
await prisma.user.upsert({
where: { email: "ada@x.com" },
update: {},
create: { name: "Ada", email: "ada@x.com" },
});
// Read
const all = await prisma.user.findMany();
console.log("all:", all);
const one = await prisma.user.findUnique({ where: { email: "ada@x.com" } });
console.log("one:", one);// app.js
// Update
import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client.ts";
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });
// Update
await prisma.user.update({
where: { email: "ada@x.com" },
data: { age: 36 },
});
// Read
const all = await prisma.user.findMany();
console.log("all:", all);
const one = await prisma.user.findUnique({ where: { email: "ada@x.com" } });
console.log("one:", one);// app.js
import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client.ts";
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });
// Delete
await prisma.user.delete({ where: { email: "ada@x.com" } });
// Read
const all = await prisma.user.findMany();
console.log("all:", all);
const one = await prisma.user.findUnique({ where: { email: "ada@x.com" } });
console.log("one:", one);Why: the real power of an ORM is relations — linking tables and reading them back as nested objects instead of writing JOINs by hand. Declare the relation in the schema (a User has many Posts), run prisma migrate dev again, then create related rows in one nested write and fetch them together with include. Prisma turns the foreign key and the JOIN into plain objects.
// schema.prisma — a User has many Posts
model User {
id Int @id @default(autoincrement())
email String @unique
posts Post[] // one-to-many: this user's posts
}
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id])
authorId Int // the foreign key back to User
}// Create a user AND their first post in one nested write:
await prisma.user.create({
data: {
email: "ada@x.com",
posts: { create: [{ title: "Hello world" }] },
},
});
// Read users WITH their posts attached — no manual JOIN:
const usersWithPosts = await prisma.user.findMany({
include: { posts: true },
});
// -> [{ id, email, posts: [{ id, title, authorId }] }]