Prisma and TypeORM Generated Query Plans #

A Node service using Prisma renders an order list with customers and line items. The endpoint issues three queries and looks healthy in the ORM’s own logs — until one of them turns out to be SELECT … FROM order_lines WHERE order_id IN ($1, $2, …, $200) against a table with no index on order_id, and the plan is a sequential scan of 400 million rows repeated for every page.

Reading ORM SQL is covered in ORM-generated EXPLAIN output. This page covers the two most common Node ORMs, whose query shapes differ from the Python and Ruby equivalents in ways that matter for plans.

The Condition #

Prisma compiles its query API into SQL with two relation-loading strategies:

Its pagination uses LIMIT/OFFSET for skip/take, and cursor pagination (cursor, skip: 1) produces a keyset condition. Parameters are always bound, never inlined.

TypeORM exposes both a repository API and a query builder:

Both ORMs log SQL when configured to, and both can execute raw statements, which is how plans are captured: prisma.$queryRawUnsafe('EXPLAIN (ANALYZE, BUFFERS) …') and dataSource.query('EXPLAIN …').

What each ORM sends A table comparing the two ORMs. Prisma issues separate queries per relation by default, or lateral joins with the join strategy. TypeORM issues LEFT JOINs by default, and switches to a two-query id-then-join mode when pagination is combined with joins. relations pagination with relations Prisma (default) separate IN queries LIMIT/OFFSET or cursor Prisma (join strategy) lateral joins LIMIT on the parent TypeORM find LEFT JOINs two-query id mode TypeORM query builder explicit joins manual control the same API call can produce very different plans between versions

Annotated Evidence #

Prisma’s default strategy, logged:

prisma:query SELECT "public"."orders"."id", … FROM "public"."orders" WHERE "public"."orders"."account_id" = $1 ORDER BY "public"."orders"."placed_at" DESC LIMIT $2 OFFSET $3
prisma:query SELECT "public"."customers"."id", … FROM "public"."customers" WHERE "public"."customers"."id" IN ($1,$2,…,$38)
prisma:query SELECT "public"."order_lines"."id", … FROM "public"."order_lines" WHERE "public"."order_lines"."order_id" IN ($1,…,$200)

Capturing the plan of the third statement:

const plan = await prisma.$queryRawUnsafe<string[]>(
  `EXPLAIN (ANALYZE, BUFFERS) SELECT id, order_id, quantity FROM order_lines WHERE order_id = ANY($1::bigint[])`,
  orderIds,
);
Seq Scan on order_lines  (actual time=0.9..3104.6 rows=8102 loops=1)
  Filter: (order_id = ANY ('{…}'::bigint[]))
  Rows Removed by Filter: 412004110
  Buffers: shared hit=41204 read=1802210
-- no index on order_lines.order_id: the batched query scans the whole table

TypeORM’s two-query pagination mode:

query: SELECT DISTINCT "distinctAlias"."Order_id" AS "ids_Order_id"
       FROM (SELECT … FROM "orders" "Order" LEFT JOIN "order_lines" … ) "distinctAlias"
       ORDER BY "ids_Order_id" DESC LIMIT 20
query: SELECT … FROM "orders" "Order" LEFT JOIN "order_lines" "lines" ON …
       WHERE "Order"."id" IN (…20 ids…)
-- the first query exists to make LIMIT count orders, not joined rows
Three things to check in Node ORM SQL Three items are annotated. A batched IN query against an unindexed foreign key producing a sequential scan. TypeORM's distinct id query, which exists so pagination counts parents. An OFFSET in the parent query, which grows more expensive with page depth. WHERE order_id IN (…) → Seq Scan missing index on the FK SELECT DISTINCT … LIMIT 20 (id query) TypeORM pagination mode LIMIT $2 OFFSET $3 deep pages get slower both ORMs log their SQL; read it before tuning anything

Step-by-Step Resolution #

  1. Turn on SQL logging. Prisma: new PrismaClient({ log: ['query'] }). TypeORM: logging: ['query'] in the data source options. Count the statements per request.

  2. Index every foreign key the ORM batches on. The IN queries are only as good as the index behind them — see covering indexes for foreign key joins.

  3. Capture plans through the ORM’s raw interface, so the parameters, role and connection match production.

  4. Choose the relation strategy deliberately. For to-one relations a join is usually better; for to-many relations separate batched queries avoid row multiplication. Prisma’s relationLoadStrategy and TypeORM’s query builder both allow the choice per query.

  5. Replace skip/take deep pagination with cursor pagination. Prisma’s cursor option emits a keyset condition; TypeORM needs a manual where clause. The plan difference is in offset vs keyset pagination plans.

  6. Watch batch sizes. Both ORMs send one placeholder per id; very large pages produce very long statements, with the variant problem described in variable IN lists and statement cache bloat.

  7. Re-check after ORM upgrades. Both projects have changed their default strategies between versions; a plan captured a year ago may no longer describe what is sent.

One list endpoint, three fixes The original endpoint takes 3,210 milliseconds, dominated by the unindexed batched query. Adding an index on order_lines.order_id brings it to 104 milliseconds. Switching page 500 from offset to cursor pagination brings that page from 1,840 milliseconds to 96. original 3,210 ms + FK index 104 ms page 500, offset 1,840 ms page 500, cursor 96 ms the ORM was never the problem; the schema and pagination were

Before and After #

-- BEFORE: batched relation query without an index
Seq Scan on order_lines  Rows Removed by Filter: 412004110     Execution Time: 3104.6 ms

-- AFTER: CREATE INDEX CONCURRENTLY order_lines_order_id_idx
Index Scan using order_lines_order_id_idx  Index Searches: 200  Execution Time: 4.1 ms

Reading generated SQL is the whole skill #

Neither ORM hides its SQL, and both make it easy to log. The recurring mistake is to reason about performance in terms of the ORM’s API — “include is slow”, “find is fast” — rather than in terms of the statements it produces. The same API call produces different SQL depending on the relation’s cardinality, whether pagination is present, which version is installed, and which loading strategy is configured.

Two habits make that manageable. First, log SQL in development permanently and read it while building features, so a pathological shape is noticed when it is written rather than when it is deployed. Second, keep a small script that captures plans for the application’s top statements by running EXPLAIN through the ORM’s raw interface against a production-sized database; running it after dependency upgrades catches strategy changes that would otherwise appear as an unexplained regression.

The database-side habits are unchanged from any other stack: rank by total time in pg_stat_statements, check call counts against page sizes, and read the plan rather than guessing from the API.

Common Pitfalls #

Unindexed foreign keys. Batched relation queries scan. Diagnostic signal: sequential scans under IN conditions. Fix: index the referencing columns.

Joins for to-many relations. Parent rows multiply. Diagnostic signal: result row counts far above the page size. Fix: separate batched queries.

Deep skip/take pagination. Cost grows with page depth. Diagnostic signal: late pages slower than early ones. Fix: cursor pagination.

Assuming defaults are stable. Relation strategies change between versions. Diagnostic signal: plan changes after a dependency upgrade. Fix: re-capture plans after upgrades.

Frequently Asked Questions #

How do I see the SQL Prisma generates? #

Instantiate the client with query logging enabled — new PrismaClient({ log: ['query'] }) — and every statement with its parameters is logged. For plans, run EXPLAIN through $queryRawUnsafe so the capture uses the same connection and role.

Why does TypeORM issue two queries when I paginate with relations? #

Because a LEFT JOIN to a to-many relation multiplies parent rows, so a plain LIMIT would cut joined rows rather than parents. TypeORM first selects the distinct parent ids with the limit, then fetches those parents with their joins.

Does Prisma use joins or separate queries for relations? #

By default it issues separate batched queries per relation and assembles the result in the client. Newer versions support a join-based strategy that uses lateral joins in a single query.

Up: ORM-Generated EXPLAIN Output