zanith

Raw SQL

When you need full SQL control — with automatic parameterization for safety. Use it as an escape hatch, not a default.

When to use raw SQL

The query builder covers the vast majority of queries. But sometimes you need a complex CTE, a specific PostgreSQL function, a recursive query, or a pattern the builder doesn't support yet. Raw SQL is your escape hatch for those cases.

Use the builder when

  • ·Standard CRUD operations
  • ·Relational queries with JOINs
  • ·Aggregates and GROUP BY
  • ·Type safety matters most

Use raw SQL when

  • ·Complex CTEs or recursive queries
  • ·PostgreSQL-specific functions
  • ·Performance-critical custom SQL
  • ·The builder doesn't support it yet

Tagged template literals

Raw SQL uses JavaScript tagged template literals. Values inserted with ${}are automatically parameterized as $1, $2, etc. — they are never interpolated into the SQL string. This prevents SQL injection by design.

TSparameterized query
const users = await db.raw<{ id: string; email: string }>`
  SELECT id, email FROM users
  WHERE created_at > ${startDate}
  AND role = ${role}
  LIMIT ${limit}
`;
 
// What Zanith sends to PostgreSQL:
// SQL:    SELECT id, email FROM users WHERE created_at > $1 AND role = $2 LIMIT $3
// Params: [startDate, role, limit]
//
// The values are NEVER in the SQL string — always parameterized.

Type the result

Add a generic type parameter to tell TypeScript what shape the rows will have. Without it, results are any[].

TS
// Typed — TypeScript knows the shape
const result = await db.raw<{ total: number; month: string }>`
  SELECT
    DATE_TRUNC('month', created_at)::text as month,
    COUNT(*)::int as total
  FROM posts
  WHERE published = true
  GROUP BY 1
  ORDER BY 1 DESC
`;
 
// result: Array<{ total: number, month: string }>
result[0].total;   // ✅ TypeScript knows this is number
result[0].month;   // ✅ TypeScript knows this is string

Complex queries

CTE (WITH clause)

TS
const report = await db.raw`
  WITH monthly AS (
    SELECT
      DATE_TRUNC('month', created_at) as month,
      COUNT(*) as total,
      SUM(view_count) as views
    FROM posts
    WHERE published = true
    GROUP BY 1
  )
  SELECT * FROM monthly
  WHERE total > 5
  ORDER BY month DESC
`;

Recursive query

TS
const tree = await db.raw`
  WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 as depth
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
  )
  SELECT * FROM category_tree ORDER BY depth, name
`;

Inside transactions

Raw SQL works inside transactions — it runs on the same connection:

TS
await db.transaction(async (tx) => {
  await tx.user.create({ data: { email: '[email protected]' } });
 
  // Raw SQL in the same transaction
  await tx.raw`
    UPDATE counters SET n = n + 1 WHERE name = 'users'
  `;
});

Important notes