Skip to content
Tutorials Center
My learning

Topics

Lesson 72 of 133 ยท Data and Storage

ORMs: Prisma and Sequelize

Understand ORMs in Node.js - Prisma schemas and migrations, Sequelize models and associations, queries and relations, the N+1 trap, raw SQL and the trade-offs, with runnable stand-ins.

Writing SQL by hand is precise but repetitive. For every table you write the same INSERT, SELECT ... WHERE id = ?, UPDATE and DELETE, convert rows to objects, and keep your code and your schema in sync as both change. An ORM (object-relational mapper) automates that work. You describe your tables as models, and the library generates SQL, runs it, and hands back plain objects. Many also generate migrations, the versioned schema changes covered in Databases from Node.js.

Two ORMs dominate the Node.js world: Prisma, which is schema-first and generates a fully typed client, and Sequelize, a long-established library where you define models in JavaScript. This lesson shows how each one looks and how they work in practice, and then discusses the trade-offs that decide whether an ORM is right for a project. As in the previous data lessons, the driver code is read-only (our editor has no npm packages or database servers), and runnable stand-ins show the ideas behind it.

What an ORM actually does

An ORM sits between your code and the database driver. It turns method calls into SQL, sends the SQL through a driver and a connection pool, and maps the result rows back to objects.

flowchart LR
    A["Your code<br/>user.findMany(...)"] --> B["ORM<br/>query builder + mapper"]
    B -->|"SELECT ... WHERE ..."| C["Driver + pool"]
    C --> D[("Database")]
    D -->|"rows"| C
    C --> B
    B -->|"plain objects"| A

The ORM builds parameterized SQL, delegates the network work to a driver, and converts rows to objects.

Because values are sent as parameters, an ORM protects you from SQL injection as long as you use its API (and its safe raw-query helpers, covered below). Because it knows your schema, it can also give you validation, relations and, in Prisma's case, precise TypeScript types for every query result.

Prisma: schema first

With Prisma you write one file, prisma/schema.prisma, that describes your data. The CLI then generates a typed client and the SQL migrations from it.

PRISMA
// read-only: prisma/schema.prisma
datasource db {
  provider = "postgresql"        // also mysql, sqlite, sqlserver, mongodb, ...
}

generator client {
  provider = "prisma-client"     // the generator name and options differ between major versions
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?                       // the ? makes the column nullable
  posts     Post[]                        // one user, many posts
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int

  @@index([authorId])
}

The relation between the two models is an ordinary foreign key. This is how the same schema looks as an entity diagram:

erDiagram
    USER ||--o{ POST : writes
    USER {
        int id PK
        string email UK
        string name
    }
    POST {
        int id PK
        string title
        boolean published
        int authorId FK
    }

One user has many posts; each post stores its author's id.

The everyday workflow uses a few commands:

Terminal
# read-only: run these in your own project
npm install prisma @prisma/client
npx prisma migrate dev --name init     # create a migration from schema changes, apply it, regenerate the client
npx prisma migrate deploy              # apply existing migrations in production (never creates new ones)
npx prisma generate                    # regenerate the typed client after changing the schema

migrate dev compares your schema with the database history, writes a new SQL file into prisma/migrations/, and applies it. You commit those SQL files to Git. migrate deploy runs them, in order, on the production database. That is the same forward-only, recorded-in-a-table design as the migration runner you built earlier.

Using the generated client looks like this:

JavaScript
// read-only: requires the generated Prisma client and a database
import { PrismaClient } from '@prisma/client';   // the import path depends on your generator settings

const prisma = new PrismaClient();                // create ONE per process

const ada = await prisma.user.create({
  data: { email: '[email protected]', name: 'Ada', posts: { create: [{ title: 'Notes on engines' }] } },
});

const publishedByAda = await prisma.post.findMany({
  where: { published: true, author: { email: '[email protected]' } },
  orderBy: { id: 'desc' },
  take: 10,
});

const withPosts = await prisma.user.findUnique({
  where: { id: ada.id },
  include: { posts: true },                       // load the relation too
});

await prisma.post.updateMany({ where: { authorId: ada.id }, data: { published: true } });

// several writes that must succeed or fail together
await prisma.$transaction([
  prisma.post.deleteMany({ where: { authorId: ada.id } }),
  prisma.user.delete({ where: { id: ada.id } }),
]);

await prisma.$disconnect();

Notice the vocabulary: where, orderBy, take/skip for pagination, select to choose columns, include to load relations. In TypeScript, withPosts.posts[0].title is checked by the compiler, and a typo in a column name is an error before the code ever runs. That type safety is Prisma's biggest selling point. TypeScript itself is covered in TypeScript with Node.js.

Sequelize: models in code

Sequelize (version 6 is the long-standing stable line) takes the opposite approach. You define models in JavaScript, and associations are declared with method calls.

JavaScript
// read-only: requires "npm install sequelize pg"
import { Sequelize, DataTypes, Model } from 'sequelize';

const sequelize = new Sequelize(process.env.DATABASE_URL, {
  logging: false,
  pool: { max: 10, idle: 10_000 },
});

class User extends Model {}
User.init(
  {
    email: { type: DataTypes.STRING, allowNull: false, unique: true, validate: { isEmail: true } },
    name: { type: DataTypes.STRING },
  },
  { sequelize, modelName: 'User' },
);

class Post extends Model {}
Post.init(
  {
    title: { type: DataTypes.STRING, allowNull: false },
    published: { type: DataTypes.BOOLEAN, defaultValue: false },
  },
  { sequelize, modelName: 'Post' },
);

User.hasMany(Post, { foreignKey: 'authorId' });
Post.belongsTo(User, { as: 'author', foreignKey: 'authorId' });

const ada = await User.create({ email: '[email protected]', name: 'Ada' });
await Post.create({ title: 'Notes on engines', authorId: ada.id });

const posts = await Post.findAll({
  where: { published: false },
  include: [{ model: User, as: 'author' }],       // JOIN, so there is no extra query per post
  order: [['id', 'DESC']],
  limit: 10,
});

await sequelize.transaction(async (t) => {
  await Post.destroy({ where: { authorId: ada.id }, transaction: t });
  await ada.destroy({ transaction: t });
});

Differences worth knowing:

  • Sequelize returns model instances with methods like save() and destroy(); Prisma returns plain objects. Instances are convenient, but it is easy to forget to pass { transaction: t }, which silently runs a statement outside the transaction. Prisma's interactive prisma.$transaction(async (tx) => ...) hands you a tx client, so you cannot forget it.
  • sequelize.sync() creates tables from your models, which is fine for tests and prototypes. For real databases, use migrations (sequelize-cli migration:generate), because sync({ alter: true }) can drop data.
  • Sequelize works in plain JavaScript without a build step. Prisma needs a generation step, which is why it integrates best with TypeScript projects but also works in JavaScript.

Seeing what an ORM does: N+1

Remember the N+1 problem from the previous lesson? It is the most common ORM performance bug. You load 100 authors with one query, then loop over them and ask each for its posts, producing 100 more queries. Every ORM supports eager loading (include in Prisma and Sequelize) precisely to avoid this. Under the hood it does one of two things: a JOIN, or a second query with WHERE author_id IN (...) whose rows are then grouped in memory.

The example below builds all three strategies by hand on SQLite and counts queries, so you can see exactly what include saves.

Node.js N+1 queries versus eager loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

With 5 authors the naive loop already needs 6 queries; with 500 it needs 501, each a network round trip in a real application. The fix is to ask the ORM for the relation up front (include) and to turn on query logging in development so you notice repeated statements. In Prisma, set the log: ['query'] option on the client; in Sequelize, pass a logging function.

Migrations: how a schema change becomes SQL

Every ORM migration tool does a variation of the same job: compare the schema you want with the schema that exists, and produce the SQL that moves from one to the other. Prisma derives it from schema.prisma; Sequelize and Knex use hand-written (or generated) up/down files.

This stand-in does the diff for a tiny schema format. It generates CREATE TABLE for new tables, ALTER TABLE ... ADD COLUMN for new columns, and refuses to apply anything destructive without a human decision, which is the same safety rule real tools follow when a column disappears from the schema.

Node.js A schema diff that writes migration SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The existing row received the default value for the new required column. Without the default, the database could not satisfy NOT NULL for that row, and the migration would fail in production, usually at the worst possible moment. Real tools make you handle the same situation with a default or a multi-step "add nullable column, backfill, then make it required" plan.

Raw SQL when you need it

No ORM covers everything: reports with window functions, bulk upserts, database-specific features. Both libraries let you drop to SQL, and both offer a safe way to pass values.

JavaScript
// read-only: Prisma: tagged template, values become parameters automatically
const rows = await prisma.$queryRaw`
  SELECT author_id, count(*) AS total FROM "Post" WHERE published = ${true} GROUP BY author_id
`;

// read-only: Sequelize: named replacements are escaped for you
const [results] = await sequelize.query(
  'SELECT author_id, count(*) AS total FROM "Posts" WHERE published = :published GROUP BY author_id',
  { replacements: { published: true } },
);

The danger is the unsafe variants: Prisma's $queryRawUnsafe and string concatenation into sequelize.query accept SQL text built from user input, which is how SQL injection happens (see Web Application Security). Use the template or replacement forms.

Trade-offs: should you use an ORM?

Question ORM Hand-written SQL or a query builder
Speed of building CRUD features Very fast More typing
Type safety (Prisma) Excellent You write the types yourself
Hidden queries and N+1 Easy to cause You see every query
Complex reports Often needs raw SQL anyway Natural
Schema changes Generated migrations Written by hand
Learning cost Library API on top of SQL SQL only
Switching databases Easier, but dialect details leak Rewrite dialect-specific queries

A sensible rule: use an ORM for the 90 percent of ordinary reads and writes, learn SQL well enough to read what the ORM sends, and use raw SQL for the 10 percent that is hard to express. Always enable query logging in development, always eager-load relations you will need, always paginate, and review migration SQL before you run it in production. If your data is mostly documents, see MongoDB and Redis from Node.js instead.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the include option (eager loading) in an ORM mainly help you avoid?

    include loads related rows together with the parent rows, by a JOIN or one extra IN query, instead of one extra query per parent.
  2. 2. In Prisma, which command applies already-created migrations to a production database?

    migrate deploy only applies existing migration files in order. migrate dev is for development: it creates new migrations and may reset data.
  3. 3. Why is sequelize.sync({ alter: true }) a poor choice for a production database?

    Production schema changes should be explicit, reviewed, versioned and recorded. sync can alter tables automatically and put data at risk.
  4. 4. Which raw-query style protects against SQL injection?

    Parameters are sent separately from the SQL text, so input can never change the structure of the query.
  5. 5. How many PrismaClient (or Sequelize) instances should a normal server create?

    Each instance owns a connection pool. Creating them per request multiplies connections and can exhaust the database.

Summary

  • An ORM maps tables to models and generates parameterized SQL; Prisma is schema-first with a generated typed client, Sequelize defines models in JavaScript.
  • Use migrations for real databases: Prisma migrate dev creates them, migrate deploy applies them; avoid sequelize.sync() outside tests.
  • Eager loading (include) turns N+1 queries into one JOIN or two queries. Turn on query logging in development to spot repeats.
  • Use raw SQL for hard cases, but only through parameterized forms.
  • Create one client per process, pass transactions explicitly, and review the SQL your migrations produce.

Next: Hashing and HMAC