How It Works

Turbine's performance advantage comes from a single architectural decision: use Postgres's built-in JSON functions to resolve nested relations server-side instead of sending multiple queries.

The N+1 Problem

When you ask an ORM for "users with their posts", most ORMs send multiple queries:

Query 1: SELECT * FROM users WHERE org_id = 1
Query 2: SELECT * FROM posts WHERE user_id IN (1, 2, 3, ...)

Add another level (posts with comments), and you get another query:

Query 3: SELECT * FROM comments WHERE post_id IN (10, 11, 12, ...)

Each query pays a network round-trip. The ORM then stitches results together in JavaScript by matching foreign keys. This is the N+1 pattern.

How Prisma Does It

Prisma sends separate queries for each relation level and stitches results in its Rust query engine:

TypeScript

// Your code
const users = await prisma.user.findMany({
  where: { orgId: 1 },
  include: {
    posts: {
      include: { comments: true },
    },
  },
});

SQL

-- Prisma sends 3 queries:
SELECT * FROM users WHERE org_id = 1;
SELECT * FROM posts WHERE user_id IN ($1, $2, $3, ...);
SELECT * FROM comments WHERE post_id IN ($1, $2, $3, ...);

Then the Rust engine iterates through all results, matches IDs, and builds the nested JavaScript objects. This works, but each query adds ~1ms of network latency plus the CPU cost of stitching.

How Drizzle Does It

Drizzle uses LATERAL JOIN for nested queries. Better than N+1, but the result is a flat table that must be de-duplicated:

SQL

-- Drizzle generates something like:
SELECT u.*, p.*, c.*
FROM users u
LEFT JOIN LATERAL (
  SELECT * FROM posts WHERE user_id = u.id
) p ON true
LEFT JOIN LATERAL (
  SELECT * FROM comments WHERE post_id = p.id
) c ON true
WHERE u.org_id = 1;

The problem: a user with 5 posts each having 3 comments produces 15 result rows for that user. Drizzle de-duplicates and restructures this in JavaScript.

How Turbine Does It

Turbine uses Postgres json_agg and json_build_object to build the entire nested JSON tree server-side:

TypeScript

// Your code
const users = await db.users.findMany({
  where: { orgId: 1 },
  with: {
    posts: {
      with: { comments: true },
    },
  },
});

SQL

-- Turbine generates ONE query:
SELECT u.*,
  (SELECT COALESCE(json_agg(sub), '[]'::json) FROM (
    SELECT p.*,
      (SELECT COALESCE(json_agg(sub2), '[]'::json) FROM (
        SELECT c.* FROM comments c WHERE c.post_id = p.id
      ) sub2) AS comments
    FROM posts p WHERE p.user_id = u.id
  ) sub) AS posts
FROM users u WHERE u.org_id = 1

One query. One round-trip. One parse.

Postgres executes the subqueries, aggregates the results into JSON arrays, and returns a result set where each row contains the parent data plus nested JSON columns. The client calls JSON.parse() once per relation column -- no iteration, no matching, no deduplication.

Visual Comparison

Prisma (3 round-trips):
  App ──── Query 1 ────> DB ──── Response 1 ────> App
  App ──── Query 2 ────> DB ──── Response 2 ────> App
  App ──── Query 3 ────> DB ──── Response 3 ────> App
  App: stitch results in Rust engine
  Total: 3 round-trips + stitching

Drizzle (1 round-trip, large result):
  App ──── LATERAL JOIN ────> DB ──── Flat rows ────> App
  App: de-duplicate and restructure
  Total: 1 round-trip + dedup (many rows)

Turbine (1 round-trip, compact result):
  App ──── json_agg query ────> DB ──── Nested JSON ────> App
  App: JSON.parse()
  Total: 1 round-trip + parse (minimal rows)

The SQL in Detail

For a 4-level nested query:

TypeScript

const org = await db.organizations.findUnique({
  where: { id: 1 },
  with: {
    users: {
      with: {
        posts: {
          where: { published: true },
          orderBy: { createdAt: 'desc' },
          limit: 10,
          with: {
            comments: {
              orderBy: { createdAt: 'desc' },
              limit: 5,
            },
          },
        },
      },
    },
  },
});

Turbine generates:

SQL

SELECT json_build_object(
  'id', o.id,
  'name', o.name,
  'slug', o.slug,
  'plan', o.plan,
  'users', COALESCE((
    SELECT json_agg(json_build_object(
      'id', u.id,
      'email', u.email,
      'name', u.name,
      'posts', COALESCE((
        SELECT json_agg(json_build_object(
          'id', p.id,
          'title', p.title,
          'comments', COALESCE((
            SELECT json_agg(
              json_build_object(
                'id', c.id,
                'body', c.body,
                'createdAt', c.created_at
              ) ORDER BY c.created_at DESC
            )
            FROM comments c
            WHERE c.post_id = p.id
            LIMIT 5
          ), '[]'::json)
        ) ORDER BY p.created_at DESC)
        FROM posts p
        WHERE p.user_id = u.id AND p.published = true
        LIMIT 10
      ), '[]'::json)
    ))
    FROM users u WHERE u.org_id = o.id
  ), '[]'::json)
) AS data
FROM organizations o WHERE o.id = $1

Four levels of nesting. One query. One round-trip. The database does all the joining and aggregation in a single query plan.

Why json_agg Is Fast

Postgres's json_agg is highly optimized:

Server-side aggregation -- the JSON tree is built inside Postgres's executor, not shipped as raw rows over the wire
Compact wire format -- nested JSON is smaller than the equivalent flat table with duplicated parent rows
Index-friendly -- the subqueries use standard WHERE clauses that benefit from foreign key indexes
Single query plan -- Postgres optimizes the entire query together, including subquery planning

The main trade-off: json_agg can be expensive for very large result sets (thousands of nested rows) because Postgres must serialize everything to JSON. For typical web application queries (10-100 parent rows, 5-50 children each), it is significantly faster than multiple round-trips.

When to Use Raw SQL Instead

Turbine's json_agg approach is optimal for:

Loading entities with their relations (the common case)
Dashboard pages with nested data
API endpoints returning nested JSON

For these use cases, raw SQL may be better:

Complex aggregations across many tables
Window functions and CTEs
Full-text search with ranking
Recursive queries (tree structures)

Use db.raw for these:

TypeScript

const result = await db.raw<{ day: Date; count: number }>`
  SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*)::int AS count
  FROM posts WHERE org_id = ${orgId}
  GROUP BY day ORDER BY day
`;