Skip to main content

Query inspection

The toSQL() method lets you see exactly what SQL will be executed before running it. It walks the full plan tree including nested includes and returns an ExplainedQuery for each level.

Basic usage​

const { engine } = biref.adapters.get('postgres');

const explained = biref
  .query<BirefSchema>(model)
  .public.users
  .select('id', 'email')
  .where('status', 'eq', 'active')
  .orderBy('email', 'asc')
  .limit(10)
  .toSQL(engine);

console.log(explained.sql);
// SELECT "id", "email" FROM "public"."users"
//   WHERE "status" = $1 ORDER BY "email" ASC LIMIT $2

console.log(explained.params);
// ['active', 10]

ExplainedQuery​

interface ExplainedQuery {
  entity: string;                       // 'public.users'
  sql: string;                          // parameterized SQL
  params: readonly unknown[];           // bound values
  includes: readonly ExplainedQuery[];  // one per nested include
}

Inspecting includes​

Each nested .include() produces a child ExplainedQuery:

const { engine } = biref.adapters.get('postgres');

const explained = biref
  .query<BirefSchema>(model)
  .public.users
  .select('id', 'email')
  .include('orders', (order) =>
    order
      .select('id', 'total')
      .include('order_items', (item) => item.select('variant_id', 'quantity')),
  )
  .toSQL(engine);

// Root query
console.log(explained.entity);  // 'public.users'
console.log(explained.sql);     // SELECT "id", "email" FROM "public"."users"

// First include
const [orders] = explained.includes;
console.log(orders.entity);     // 'commerce.orders'
console.log(orders.sql);        // SELECT "id", "total" FROM "commerce"."orders" WHERE "user_id" IN (?)

// Nested include
const [items] = orders.includes;
console.log(items.entity);      // 'commerce.order_items'

Per-adapter SQL​

Pass different engines to see the SQL for each adapter:

const { engine: pgEngine } = biref.adapters.get('postgres');
const { engine: myEngine } = biref.adapters.get('mysql');

const chain = biref.query(model).public.users.select('id', 'email');

// Postgres: double-quoted identifiers, $N params
console.log(chain.toSQL(pgEngine).sql);
// SELECT "id", "email" FROM "public"."users"

// MySQL: backtick identifiers, ? params
console.log(chain.toSQL(myEngine).sql);
// SELECT `id`, `email` FROM `public`.`users`

Use cases​

  • Debugging -- see what SQL your chain produces without hitting the database
  • Logging -- log every query before execution
  • Review -- audit the generated SQL during code review
  • Testing -- assert on the SQL shape in unit tests without a live database