Stop 06 · Databases (SQL & NoSQL)Topics · 4/8

ORMs (Prisma/Mongoose)

Part of the Full Stack Developer Roadmap

In short

Writing raw SQL strings for every query gets tiring, and typos only show up at runtime. An ORM lets you query with normal TypeScript objects and gives you autocomplete for every table and column.

Libraries that let you interact with your database using your programming language's objects instead of writing raw queries by hand — Prisma for SQL, Mongoose for MongoDB.

The mascot sips chai while a travel agent fills booking forms for it.

Think of it like this

The travel agent

You tell the travel agent 'Goa, two people, next Friday'. She fills the long railway and hotel forms in the right format. You never touch those forms, but you can still check her work. An ORM is that agent between your code and the database.

Watch it happen

One Prisma call, start to finish

Your code

ORM client

SQL

Database

Typed result

1/5You write prisma.user.findUnique({ where: { id }, include: { posts: true } }).

The big ideas

What you need to know

Schema is the source of truth

In Prisma you describe models in schema.prisma. Migrations and the typed client are generated from that one file.

Mongoose for MongoDB

Mongoose adds schemas, validation and models on top of MongoDB documents, which otherwise accept any shape.

Watch for N+1 queries

Looping over 50 users and fetching posts one by one fires 51 queries. Use include or a join instead.

Raw SQL is still allowed

For a tricky report, drop to prisma.$queryRaw. ORMs cover the common 90%, not every query.

See it in code

Walk through the code

1import { prisma } from "./db"; // your shared PrismaClient instance2 3// Create a user and their first post in one call4const user = await prisma.user.create({5  data: { email: "riya@example.com", posts: { create: { title: "Hello" } } },6});7 8// Fetch with a relation, fully typed9const withPosts = await prisma.user.findUnique({10  where: { id: user.id },11  include: { posts: true },12});13withPosts?.posts[0]?.title; // string, with autocomplete14 15// Escape hatch for complex queries16const rows = await prisma.$queryRaw`SELECT count(*) FROM "User" WHERE "createdAt" > now() - interval '7 days'`;

1/4Create one PrismaClient for the whole app and import it everywhere.

No SQL strings for everyday work, but the database underneath is still plain Postgres.

Save yourself the pain

Common mistakes

Oops

Creating a new PrismaClient inside every request handler.

Do this instead

Create one client at startup and reuse it. Each client opens its own pool of connections.

Oops

Treating the ORM as magic and never looking at the SQL it runs.

Do this instead

Turn on query logging in development, so you can spot slow or repeated queries early.

Your mission

Swap raw SQL for an ORM

~40 min

Quick check

You loop over 100 orders and call prisma.user.findUnique for each order's customer. What's the problem?

Go deeper

1Define a schema in Prisma (or Mongoose) and generate/connect the client.
2Perform basic CRUD operations through the ORM instead of raw queries.
3Compare the developer experience to writing the equivalent raw SQL or MongoDB queries.