QueryBuilder (Raw SQL)

The QueryBuilder is a low-level, Knex-like API for building raw SQL queries. Access it via sql.query('table'). It's suitable for performance crucial database queries since there is no serialization and the driver output is directly returned from the query.

Key Features

• Works with any table (even without a model)
• Not type-safe (returns plain objects)
• Flexible for migrations, admin scripts, or advanced SQL
• Supports filtering, selecting, joining, pagination, CTEs, truncate, softDelete, and more

Best Practice: Use QueryBuilder for raw SQL, migrations, or when want the max performance in a query. For app logic, prefer the ModelQueryBuilder.

Basics

For type-safe application queries use the ModelQueryBuilder. This page focuses on raw SQL with sql.query(table).

Basic Usage

const users = await sql.query('users').where('status', 'active').many();

Pagination with Cursor

Paginate with cursor is a pagination method that allows you to paginate the results with a cursor that does not use the offset clause (more efficient for large datasets). Discriminator is used to identify the unique value to paginate by. By default it generates an order by clause to the query orderBy(discriminator, "asc") if not already present.

// Get the first page
const [users, cursor] = await User.query().paginateWithCursor(1, { discriminator: "age" });

// Get the second page
const [users2, cursor2] = await User.query().paginateWithCursor(1, { discriminator: "age" }, cursor);

Joins

const postsWithUsers = await Post.query()
  .join('users', 'posts.userId', 'users.id')
  .select('posts.*', 'users.name')
  .many();

// Alias are supported in `join` and `from`
const postsWithUsersWithAlias = await Post.query()
  .join('users as u', 'posts.userId', 'u.id')
  .select('posts.*', 'u.name')
  .many();

Advanced Features

annotate (Column Aliasing)

Add computed columns or aliases to your result set.

const userCount = await User.query()
  .annotate('count', 'id', 'total') // with sql method
  .first();

const user = await User.query()
  .annotate('id', 'userId') // only alias
  .first();

console.log(userCount.$annotations.total);
console.log(userCount.$annotations.userId);

removeAnnotations

Remove all annotations from the result.

const user = await User.query().
  annotate('count', 'id', 'total')
  .removeAnnotations()
  .first();

console.log(user.$annotations); // error in ts

pluck

Extract a single column as an array.

const names = await User.query().pluck('name'); // string[]

increment / decrement

Atomically increment or decrement a column.

await User.query().increment('age', 1);
await User.query().decrement('age', 1);

lockForUpdate / forShare

Apply row-level locking (Postgres/MySQL only).

const users = await User.query().lockForUpdate().many();
const users = await User.query().forShare().many();

with (Common Table Expressions, CTE)

Use CTEs for advanced queries.

const users = await User.query()
  .with('users_cte', (qb) => qb.select('name').where('isActive', true))
  .many();

Example Usage

// After connecting with SqlDataSource
const users = await sql.query("users").where("status", "active").many();

Filtering

const users = await sql.query("users").where("age", ">", 18).many();

Raw right-hand side with rawStatement

When you need to compare a column to another column or an expression without creating a binding, use sql.rawStatement. Identifiers inside the raw string are automatically quoted per database dialect.

// After connecting with SqlDataSource
await sql.query("users")
  .where("id", sql.rawStatement("user.id"))
  .many();

Selecting Columns

const names = await sql.query("users").select("name").many();

Pagination

const page = await sql.query("users").paginate(1, 10);
console.log(page.data, page.paginationMetadata);

Chunking Large Datasets

The chunk method processes large datasets in manageable pieces without loading everything into memory:

// Process users in chunks of 250
for await (const users of sql.query('users').chunk(250)) {
  await processUserBatch(users);
}

Pagination Methods

Method	Use Case	Return
paginate(page, perPage)	API responses with metadata	{ data, paginationMetadata }
limit(n).offset(m)	Manual pagination	Array of results
chunk(size)	Large dataset processing	Async iterable

From

const users = await sql
  .query("users")
  .from(
    (qb) => {
      qb.select("name").from("users as internal_users").where("internal_users.age", ">", 18);
    },
    "external_users"
  )
  .many();

Joins

const postsWithUsers = await sql
  .query("posts")
  .join("users", "posts.userId", "users.id")
  .select("posts.*", "users.name")
  .many();

CTEs (Common Table Expressions)

const users = await sql
  .query("users")
  .with("users_cte", (qb) => qb.select("name").where("isActive", true))
  .many();

Truncate

await sql.query("users").truncate();

Soft Delete

await sql.query("users").softDelete({ column: "deleted_at" });

Limitations

• No type safety: results are plain objects
• No decorator or relation support
• Use with caution in app logic

Comparison to ModelQueryBuilder

• Type Safety: QueryBuilder is not typed, ModelQueryBuilder is.
• Decorator/Relation Support: Only ModelQueryBuilder supports model decorators and relations.
• Use Case: Use QueryBuilder for raw SQL, migrations, or admin scripts.

Full API Reference

Filtering

• where, orWhere, andWhere, whereNot, andWhereNot, orWhereNot, whereIn, whereNotIn, whereNull, whereNotNull, whereBetween, whereLike, whereNotLike, andWhereLike, andWhereNotLike, orWhereLike, orWhereNotLike, whereExists, whereNotExists

await sql.query("users").where("email", "like", "%@example.com").many();
await sql.query("users").whereIn("status", ["active", "pending"]).many();
await sql.query("users").whereNull("deletedAt").many();
await sql.query("users").whereBetween("age", [18, 30]).many();
await sql.query("users").whereNot("name", "Alice").many();

Subqueries & Nested Conditions

• Overloads on where/andWhere/orWhere and whereIn/whereNotIn support callbacks and subqueries.

// Grouped conditions with a callback, this ignores everything that is not a where clause
await sql
  .query("users")
  .where((qb) => {
    qb.where("age", ">", 18).orWhere("isActive", true);
  })
  .many();

// Column compared to a subquery (defaults to IN)
await sql
  .query("users")
  .where("id", (sub) => sub.select("userId").from("posts").where("published", true))
  .many();

// Column compared to a subquery with explicit operator
await sql
  .query("users")
  .where("id", "not in", (sub) => sub.select("userId").from("posts"))
  .many();

// OR with subquery
await sql
  .query("users")
  .orWhere("id", (sub) => sub.select("ownerId").from("teams"))
  .many();

Joins

• join, leftJoin, rightJoin, innerJoin

await sql.query("posts").join("users", "posts.userId", "users.id").many();

Group By & Having

• groupBy, having

await sql.query("users").groupBy("status").having("COUNT(*)", ">", 1).many();

Unions

• union, unionAll

await sql
  .query("users")
  .select("name")
  .union("SELECT name FROM users")
  .many();

Aggregates

• getCount, getMax, getMin, getAvg, getSum

const count = await sql.query("users").getCount();
const maxAge = await sql.query("users").getMax("age");

Select & Raw Select

• select, selectRaw, clearSelect

await sql.query("users").select("name", "email").many();
await sql.query("users").selectRaw("count(*) as count").first();
await sql.query("users").clearSelect().many();

Annotations

• annotate, removeAnnotations, clearRemoveAnnotations

await sql.query("users").annotate("COUNT(*)", "count").first();
await sql.query("users").removeAnnotations().first();

Pluck

• pluck

const names = await sql.query("users").pluck("name");

Pagination

• paginate, limit, offset, chunk

const page = await sql.query("users").paginate(1, 10);
await sql.query("users").limit(5).offset(10).many();

// Process large datasets in chunks
for await (const users of sql.query("users").chunk(100)) {
  // Process each chunk of 100 users
  console.log(`Processing ${users.length} users...`);
}

Streaming (experimental)

Process query results as a stream without loading everything at once. No hooks or serialization are run.

// Async generator
for await (const user of await sql.query("users").stream()) {
  console.log(`Processing user: ${user.name}`);
}

// Node.js Readable stream
const stream = await sql.query("users").stream();
stream.on('data', (user) => console.log(user));
stream.on('end', () => console.log('Done'));

Locking

• lockForUpdate, forShare

await sql.query("users").lockForUpdate().many();
await sql.query("users").forShare().many();

CTEs (Common Table Expressions)

• with, withRecursive, withMaterialized

await sql
  .query("users")
  // Normal CTE
  .with("users_cte", (qb) => qb.select("name"))
  // Recursive CTE
  .withRecursive("users_cte", (qb) => qb.select("name"))
  // Materialized CTE (PostgreSQL/CockroachDB only)
  .withMaterialized("users_cte", (qb) => qb.select("name"))
  .many();

Copying & Query Output

• copy, toQuery, unWrap

const qb = sql.query("users").where("isActive", true);
const qbCopy = qb.clone();
const sqlString = qb.toQuery();

QueryBuilder Only

• All methods are available on QueryBuilder, but no model/relation helpers.

---

Next: Relations