EXPLAINER

ORMs and Prisma

An ORM maps database rows to objects in your language. Prisma is a popular TypeScript ORM that generates a client from a schema file. ORMs speed ordinary queries and can hide the SQL that explains a slow page.

Short answer: An ORM turns rows into objects. Prisma is a TypeScript ORM with a schema and a generated client. Use it for ordinary CRUD. Read the SQL it emits when a page is slow. The database is still Postgres, MySQL or SQLite.

What it is

An object-relational mapper (ORM) is a library that lets you work with database rows as objects or functions in your programming language. You describe models. The library generates SQL and maps results back. Prisma is a specific ORM popular in TypeScript: you write a schema.prisma file, it generates a typed client, and you call prisma.user.findMany(...) instead of writing SQL strings. Migrations can be generated from schema changes.

Other ORMs (SQLAlchemy, Entity Framework, Hibernate, Drizzle, Django's ORM) do the same job with different tastes. This page uses Prisma as the concrete example and the lessons apply to the category. The database underneath is still PostgreSQL, MySQL or SQLite.

Why and when it is used

Use an ORM when most of your queries are ordinary: find a row by id, list a user's items, insert a record, update a column. The generated types catch mistakes and new teammates move faster. Use Prisma in particular when the stack is TypeScript and you want one schema file as the source of truth.

Drop to SQL, or to the ORM's escape hatch, when the query is a report, a bulk update, or anything you have to explain. Do not use an ORM because you do not want to learn SQL. You will still debug SQL. Do not let the ORM own a query you cannot read.

How it works

Prisma's schema declares models and relations. prisma migrate produces SQL migrations you should commit and review. The generated client exposes methods per model. A relation can be loaded with the parent (include), which is convenient and is how people accidentally load enormous graphs. The N+1 problem appears when you list parents and then query children in a loop. The fix is to include or to query in batch, then confirm with logs that you issued a few SQL statements, not a few hundred.

Transactions exist ($transaction) and matter when two writes must succeed together. Types end at the database: a constraint failure arrives as a runtime error you should map to a user-facing message, not a stack trace.

Technologies and dependencies

Node, TypeScript, the Prisma packages, and a database URL in the environment. Migrations need a shadow database or equivalent rights in CI. Generated client code should be produced in CI or committed according to one team policy, not "sometimes". SQL knowledge remains a dependency even though it is not an npm package.

How to get started

  1. Create a Postgres or SQLite database. Point DATABASE_URL at it.
  2. Define one model with an id and one required field. Migrate. Look at the SQL file Prisma wrote.
  3. Insert and read a row from a script using the generated client.
  4. Add a second model with a relation. Write a list query and log the SQL. Make sure it is not N+1.
  5. Add a unique constraint and handle the error when you insert a duplicate.
  6. Put the migration in pull-request review the same way you review code.

If you cannot read the migration, do not merge it. A generated DROP TABLE is still a drop.

Cautions and trade-offs

ORMs encourage chatty queries and hidden sorts. Turn SQL logging on in development. Prisma's abstraction does not cover every database feature; a partial index or a database function may need raw SQL. That is fine. Mixing raw SQL and the client in a transaction requires care so you are on the same connection.

Schema drift happens when someone edits the database by hand. Migrations are the source of truth, or they are not; pick one. And an ORM is a poor place to hide multi-tenant rules. A where clause you forget in one method leaks rows. Centralise the tenant filter. Finally, do not upgrade Prisma, the database and the schema in one change. You will not know which one broke the query.