Raw Queries

Execute raw SQL queries when you need full control over the query structure.

Raw Select

Execute a raw SELECT query:

import { createQueryBuilder } from 'bun-query-builder'

const db = createQueryBuilder<typeof schema>({ schema, meta })

// Raw query with parameters (SQLite and MySQL use `?` placeholders)
const users = await db.unsafe(
  'SELECT * FROM users WHERE active = ? AND age > ?',
  [true, 18]
)

// Postgres uses numbered placeholders
const posts = await db.unsafe(
  'SELECT * FROM posts WHERE user_id = $1 AND published = $2',
  [1, true]
)

db.unsafe(sql, params) runs the statement and resolves to its rows. db.raw(sql, bindings) does not run anything: it builds a fragment for set() and where() (see Bound Fragments), and awaiting it rejects.

Raw Expressions in Queries

Use raw expressions within the query builder:

import { raw } from 'bun-query-builder'

// Raw in select
const results = await db
  .selectFrom('users')
  .selectRaw(raw`COUNT(*) AS total, AVG(age) AS avg_age`)
  .get()

// Raw in where (the interpolated Date is inlined as an escaped literal)
const since = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
const recentUsers = await db
  .selectFrom('users')
  .whereRaw(raw`created_at > ${since}`)
  .get()

// Raw in order by
const sorted = await db
  .selectFrom('products')
  .orderByRaw(raw`price * quantity DESC`)
  .get()

// Raw in group by
const grouped = await db
  .selectFrom('orders')
  .select(['SUM(amount) AS total'])
  .groupByRaw(raw`strftime('%Y-%m', created_at)`)
  .get()

// Raw in having
const filtered = await db
  .selectFrom('orders')
  .select(['user_id', 'SUM(amount) AS total'])
  .groupBy('user_id')
  .havingRaw(raw`SUM(amount) > 1000`)
  .get()

Unsafe Queries

For queries that cannot use parameterized values:

// Use with caution - values are not escaped
const result = await db.unsafe(`
  SELECT * FROM users
  WHERE email LIKE '%@example.com'
  ORDER BY created_at DESC
  LIMIT 10
`)

Placeholder style is the connection's, not the builder's

When you pass bindings to unsafe, you are writing for the driver directly, so the placeholders have to be the ones that connection understands. Postgres numbers its parameters; SQLite and MySQL take ?:

// Postgres
await db.unsafe('SELECT * FROM users WHERE email = $1 LIMIT 1', [email])

// SQLite and MySQL
await db.unsafe('SELECT * FROM users WHERE email = ? LIMIT 1', [email])

Postgres has no ? parameter — it reads the token as an operator and rejects the word that follows, so the error names whatever comes next (LIMIT, above) rather than the placeholder. Because the same call works on SQLite and MySQL, this shows up as a one-dialect failure. unsafe therefore refuses bindings on Postgres when the query has no $n, and names the real problem at the call site.

A query with no bindings is never inspected, so raw DDL and a literal ? inside a string are unaffected.

Execute Raw SQL

Execute non-query SQL statements:

// Create an index
await db.unsafe('CREATE INDEX idx_users_email ON users(email)')

// Update statistics
await db.unsafe('ANALYZE users')

// Truncate table
await db.unsafe('TRUNCATE TABLE logs')

Raw Fragments in the Builder (raw)

For raw fragments inside builder methods — selectRaw, whereRaw, orderByRaw, groupByRaw, havingRaw, and select() — use the exported raw helper:

import { raw } from 'bun-query-builder'

await db.selectFrom('users').selectRaw(raw`count(*) as c`).get()
await db.selectFrom('users').whereRaw(raw('age > 18')).get()
await db.selectFrom('users').orderByRaw(raw`created_at desc`).get()

// Interpolated values are SQL-escaped:
await db.selectFrom('orders').whereRaw(raw`status = ${userStatus}`).get()

The *Raw methods take exactly one fragment and no bindings. Passing extra arguments, or a fragment that carries bound values (db.raw('x = ?', [v])), throws instead of silently dropping the values. Bare strings still work at runtime but log a deprecation warning and are a TypeScript error.

Do not pass a Bun sql`...` query to builder methods. A Bun query object cannot be converted back to SQL text, so on Postgres and MySQL the builder throws a clear error if you pass one to a *Raw method, where(), or set(). raw returns a { raw } fragment that renders correctly and satisfies the SqlFragment type. Values interpolated into raw`...` are inlined as escaped literals, not bound; for user input that must be parameterised, use where(...) or a bound fragment (below).

Bound Fragments in set() and where()

To bind values inside a SQL expression, build a fragment with db.raw(sql, bindings). Each ? binds the matching array element, on every dialect (the builder renumbers them to $n on Postgres). A plain { sql, parameters } object works the same way.

// Expression in set()
await db
  .updateTable('products')
  .set({ stock: db.raw('stock - ?', [quantity]) })
  .where({ id: productId })
  .execute()

// Condition in where() / orWhere(), on select, update and delete builders
const matches = await db
  .selectFrom('users')
  .where(db.raw('LOWER(email) = ?', [email.toLowerCase()]))
  .get()

await db
  .deleteFrom('sessions')
  .where({ sql: 'expires_at < ?', parameters: [new Date().toISOString()] })
  .execute()

db.raw(sql, bindings) is not a query: await db.raw('SELECT ...', [..]) rejects with a TypeError. Use db.unsafe(sql, params) to run SQL.

Raw with Bun Tagged Templates

For a fully raw statement (not composed with the builder), use the connection's tagged template directly:

const userId = 1
const status = 'active'

// Tagged template syntax — executes as its own statement
const users = await db.sql`
  SELECT * FROM users
  WHERE id = ${userId}
  AND status = ${status}
`

File-Based Queries

Execute SQL from files:

// Execute a SQL file
await db.file('./migrations/setup.sql')

// Execute with parameters
await db.file('./queries/get-user.sql', { userId: 1 })

Raw with Type Inference

Get typed results from raw queries:

interface UserStats {
  country: string
  count: number
  avg_age: number
}

const stats = await db.unsafe<UserStats>(`
  SELECT
    country,
    COUNT(*) AS count,
    AVG(age) AS avg_age
  FROM users
  GROUP BY country
  ORDER BY count DESC
`)

// stats is typed as UserStats[]
stats.forEach((s) => {
  console.log(`${s.country}: ${s.count} users, avg age ${s.avg_age}`)
})

Repeated Queries

unsafe is not a prepared-statement handle. It returns a query that runs when you await it and resolves to an array of rows, so there is no .get() to call per execution and nothing to finalize:

// Run it once per set of values, awaiting each. Postgres placeholders shown —
// see "Placeholder style is the connection's, not the builder's" above.
const user1 = await db.unsafe('SELECT * FROM users WHERE id = $1', [1])
const user2 = await db.unsafe('SELECT * FROM users WHERE id = $1', [2])

// Each resolves to an array of rows — index in, or use the builder's first().
console.log(user1[0]?.name)

Keep the SQL text identical across those calls and vary only the bindings. That is what lets the driver reuse its plan, and it is also what keeps the values parameterized rather than concatenated in.

Transaction with Raw Queries

Execute raw queries within transactions:

await db.transaction(async (trx) => {
  // Raw insert
  await trx.unsafe(
    'INSERT INTO audit_log (action, user_id) VALUES (?, ?)',
    ['login', userId]
  )

  // Regular query builder
  await trx.updateTable('users').set({ last_login: new Date() }).where({ id: userId })

  // Raw update
  await trx.unsafe(
    'UPDATE statistics SET login_count = login_count + 1 WHERE user_id = ?',
    [userId]
  )
})

Explain Queries

Analyze query execution plans:

// Get query execution plan — `explain()` terminates a builder chain
const explain = await db.selectFrom('users').where({ active: true }).explain()
console.log(explain)

// Using CLI
// query-builder explain "SELECT * FROM users WHERE active = true"

Complete Example

import { createQueryBuilder, buildDatabaseSchema, buildSchemaMeta, raw } from 'bun-query-builder'

const models = {
  User: {
    name: 'User',
    table: 'users',
    primaryKey: 'id',
    attributes: {
      id: { validation: { rule: {} } },
      name: { validation: { rule: {} } },
      email: { validation: { rule: {} } },
      age: { validation: { rule: {} } },
      country: { validation: { rule: {} } },
      created_at: { validation: { rule: {} } },
    },
  },
}

const schema = buildDatabaseSchema(models)
const meta = buildSchemaMeta(models)
const db = createQueryBuilder<typeof schema>({ schema, meta })

// Complex analytics with raw queries
async function getComplexAnalytics() {
  // Complex aggregation not easily expressible with query builder
  interface MonthlyStats {
    month: string
    new_users: number
    returning_users: number
    total_active: number
  }

  const stats = await db.unsafe<MonthlyStats>(`
    WITH monthly_users AS (
      SELECT
        strftime('%Y-%m', created_at) AS month,
        id,
        COUNT(*) OVER (PARTITION BY id) AS visit_count
      FROM users
      WHERE created_at >= date('now', '-12 months')
    )
    SELECT
      month,
      SUM(CASE WHEN visit_count = 1 THEN 1 ELSE 0 END) AS new_users,
      SUM(CASE WHEN visit_count > 1 THEN 1 ELSE 0 END) AS returning_users,
      COUNT(*) AS total_active
    FROM monthly_users
    GROUP BY month
    ORDER BY month
  `)

  // Combine with query builder
  const basicStats = await db
    .selectFrom('users')
    .selectRaw(raw`
      COUNT(*) AS total,
      COUNT(CASE WHEN active = 1 THEN 1 END) AS active,
      AVG(age) AS avg_age
    `)
    .first()

  // Parameterized complex query (`?` placeholders; use $1, $2, ... on Postgres)
  const countryStats = await db.unsafe(
    `
    SELECT
      country,
      COUNT(*) AS user_count,
      AVG(age) AS avg_age
    FROM users
    WHERE active = ?
    GROUP BY country
    HAVING COUNT(*) > ?
    ORDER BY user_count DESC
    LIMIT ?
  `,
    [true, 10, 5]
  )

  return { stats, basicStats, countryStats }
}

getComplexAnalytics().then(console.log)

CLI Commands

Execute queries from the command line:

# Execute a raw query

query-builder unsafe "SELECT * FROM users LIMIT 5"

# Execute with parameters

query-builder unsafe "SELECT * FROM users WHERE id = $1" --params "[1]"

# Execute a SQL file

query-builder file ./migrations/seed.sql

# Explain a query

query-builder explain "SELECT * FROM users WHERE active = true"

Deferred SQLite INSERTs

For independent append-only records, SQLite builders expose deferInsert and flushDeferredInserts. These optional methods are absent on other drivers. Like unsafe(), they are raw operations and bypass query-builder hooks, timestamps, validation, and model behavior.

const db = createQueryBuilder()
if (db.deferInsert) {
  await Promise.all([
    db.deferInsert('events', { message: 'first' }),
    db.deferInsert('events', { message: 'second' }),
  ])
}

deferInsert captures the table, ordered columns, and bound values immediately. Table and column names are quoted as literal identifiers; values must be SQLite bindings, not SQL expressions. Missing columns retain their database defaults; explicit undefined binds NULL. Mutable binary bindings are copied.

Outside a transaction, contiguous records with identical column shapes can share an INSERT and native transaction on the next event-loop turn. The queue retains at most 32 rows or approximately 256 KiB of payload and metadata. Reaching either limit drains existing work synchronously; an oversized record executes immediately. Each promise settles after persistence or failure. A failed batch is rolled back before individual retries, isolating rejected records without duplicating a successful prefix. Trigger side effects in a failed retry are rolled back too. Triggers with non-database side effects are not suitable for retryable inserts. If native rollback fails, the connection is invalidated and closed; remaining and future work rejects instead of entering an uncertain transaction.

Inside a raw or managed application transaction, inserts execute immediately and their promises do not wait for commit. Ordinary transaction and INSERT semantics apply, including rollback by the caller. Transaction controls, schema changes, PRAGMAs, connection replacement, and close flush previously queued records first. Configuration replacement preserves captured old builders; pending work stays on its original connection. Reset and close reject subsequent deferred writes.

Ordinary SELECT/INSERT/UPDATE/DELETE statements do not implicitly flush the queue. Await the insert promise, or call db.flushDeferredInserts?.() synchronously, before reading or modifying data that depends on it. Deferred insertion is not a replacement for an ordered sequence of ordinary writes. SQLite durability and checkpoint settings are unchanged.