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 rows are directly returned from the query.

Key Features

Works with any table (even without a model)
Type-safe select methods - column types are inferred from select calls
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 sql
  .query("users")
  .paginateWithCursor(1, { discriminator: "age" });

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

Joins

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

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

// Add additional conditions with a callback
const postsWithActiveUsers = await sql
  .query("posts")
  .join("users", "posts.userId", "users.id", (q) =>
    q.where("users.isActive", true)
  )
  .select("posts.*", "users.name")
  .many();

Advanced Features

`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 sql.query("users").increment("age", 1);
await sql.query("users").decrement("age", 1);

`lockForUpdate` / `forShare`

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

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

`with` (Common Table Expressions, CTE)

Use CTEs for advanced queries.

const users = await sql
  .query("users")
  .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();

Insert & Update with Raw Statements

You can use sql.rawStatement() in insert and update operations to reference column values, expressions, or SQL functions without creating parameter bindings.

Raw Statements in Insert

// Insert with raw SQL expression
await sql.query("users").insert({
  name: sql.rawStatement("'John Doe'"),
  email: "john@example.com",
});

// Insert with column reference
await sql.query("audit_logs").insert({
  user_id: sql.rawStatement(
    "(SELECT id FROM users WHERE email = 'admin@example.com')"
  ),
  action: "login",
});

Raw Statements in Update

// Update a column to reference another column
await sql.query("users").update({
  display_name: sql.rawStatement("name"),
});

// Update with SQL expression
await sql.query("users").update({
  full_name: sql.rawStatement("CONCAT(first_name, ' ', last_name)"),
});

Common Use Cases

Copy column values:

await sql
  .query("users")
  .where("old_email", null)
  .update({
    old_email: sql.rawStatement("email"),
  });

Set timestamps:

await sql.query("users").update({
  last_login: sql.rawStatement("CURRENT_TIMESTAMP"),
});

Conditional updates:

await sql.query("products").update({
  status: sql.rawStatement(
    "CASE WHEN stock > 0 THEN 'available' ELSE 'out_of_stock' END"
  ),
});

Note: Raw statements bypass parameter binding, so ensure the SQL is safe from injection. Never use raw statements with user input directly.

Automatic JSON Serialization

When using insert() or update() with the QueryBuilder, plain objects and arrays are automatically serialized to JSON strings. This means you don't need to manually call JSON.stringify().

// Objects are automatically stringified
await sql.query("users").insert({
  name: "John",
  metadata: { preferences: { theme: "dark" }, tags: ["admin", "active"] },
});

// Arrays are automatically stringified
await sql.query("users").update({
  roles: ["admin", "editor"],
});

What Gets Serialized

Value Type	Auto-Stringify
Plain objects `{ key: "value" }`	✅ Yes
Arrays `[1, 2, 3]`	✅ Yes
`null` / `undefined`	❌ No
Primitives (string, number, boolean)	❌ No
`Date` instances	❌ No (handled by database driver)
`RawNode` (from `sql.rawStatement()`)	❌ No

This behavior applies to both the raw QueryBuilder and ModelQueryBuilder. For models with @column.json() decorators, the column's prepare function takes precedence.

Selecting Columns

Basic Select

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

Select with Alias (Tuple Syntax)

Use [column, alias] tuples to alias columns:

// Select with alias
const users = await sql
  .query("users")
  .select(["name", "userName"], ["email", "userEmail"])
  .many();

// users[0].userName - aliased from name
// users[0].userEmail - aliased from email

Mixed Selection

// Mix regular columns and aliased columns
const users = await sql
  .query("users")
  .select("id", ["name", "userName"], "status")
  .many();

Avoid Wildcards for Better Type Safety

While select("*") works, it's recommended to select specific columns for better type inference:

// ✅ Preferred: Explicit columns
const users = await sql.query("users").select("id", "name", "email").many();

// ⚠️ Less precise: Wildcard returns Record<string, any>
const users = await sql.query("users").select("*").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

Basic Joins

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

Joins with Additional Conditions

You can add additional conditions to the join ON clause by passing a callback:

const postsWithActiveUsers = await sql
  .query("posts")
  .join("users", "posts.userId", "users.id", (q) =>
    q.where("users.isActive", true)
  )
  .select("posts.*", "users.name")
  .many();

// This generates SQL similar to:
// SELECT posts.*, users.name FROM posts
// INNER JOIN users ON posts.userId = users.id AND users.isActive = true

The callback receives a query builder that supports all where methods:

// Multiple conditions
await sql
  .query("posts")
  .join("users", "posts.userId", "users.id", (q) =>
    q.where("users.isActive", true).andWhere("users.verified", true)
  )
  .many();

// Using different operators
await sql
  .query("posts")
  .innerJoin("users", "posts.userId", "users.id", (q) =>
    q
      .whereIn("users.status", ["active", "pending"])
      .andWhere("users.age", ">=", 18)
  )
  .many();

// Works with all join types: join, innerJoin, leftJoin, rightJoin, fullJoin
await sql
  .query("posts")
  .leftJoin("comments", "comments.postId", "posts.id", (q) =>
    q.where("comments.approved", true)
  )
  .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

Column values are typed as any (no model column type inference)
No decorator or relation support
Use with caution in app logic

Comparison to ModelQueryBuilder

Type Safety: QueryBuilder infers column names from selects; ModelQueryBuilder infers both names and types from the model.
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

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

// Join with additional conditions via callback
await sql
  .query("posts")
  .join("users", "posts.userId", "users.id", (q) =>
    q.where("users.isActive", true)
  )
  .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, selectFunc, clearSelect

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

Type-Safe Selects

The QueryBuilder provides type inference for select methods. Column names and aliases are tracked, giving you autocomplete and type checking on the result.

// Inferred type: { name: any, userEmail: any } | null
const user = await sql
  .query("users")
  .select("name", ["email", "userEmail"])
  .one();

console.log(user?.name);      // ✓ Valid
console.log(user?.userEmail); // ✓ Valid (aliased)

Explicit Types with `selectRaw`

Use the generic parameter to specify exact types:

// Inferred type: { total: number } | null
const result = await sql
  .query("users")
  .selectRaw<{ total: number }>("count(*) as total")
  .one();

console.log(result?.total); // number

Type-Safe SQL Functions with `selectFunc`

The selectFunc method auto-infers return types based on the function name:

// Return types are auto-inferred!
const stats = await sql
  .query("users")
  .selectFunc("count", "*", "userCount")   // userCount: number
  .selectFunc("avg", "age", "avgAge")      // avgAge: number
  .selectFunc("upper", "name", "upperName") // upperName: string
  .one();

console.log(stats?.userCount); // number
console.log(stats?.avgAge);    // number
console.log(stats?.upperName); // string

Type-Safe JSON Selects

JSON selection methods also support type inference:

// Inferred type: { userName: any } | null
const user = await sql
  .query("users")
  .selectJson("data", "$.user.name", "userName")
  .one();

// With explicit type
const typed = await sql
  .query("users")
  .selectJson<string, "userName">("data", "$.user.name", "userName")
  .one();
// typed?.userName is string

Subquery Type Safety

Subqueries also track their alias:

// Inferred type: { latestPost: any } | null
const user = await sql
  .query("users")
  .select((sub) => {
    sub.select("title").from("posts").where("userId", 1).limit(1);
  }, "latestPost")
  .one();

// With explicit type
const typed = await sql
  .query("users")
  .select<string, "latestPost">((sub) => {
    sub.select("title").from("posts").limit(1);
  }, "latestPost")
  .one();
// typed?.latestPost is string

Chaining Selects

Types accumulate when chaining multiple select calls:

// Inferred type: { name: any, total: number, avgAge: number }
const result = await sql
  .query("users")
  .select("name")
  .selectFunc("count", "*", "total")
  .selectFunc("avg", "age", "avgAge")
  .one();

Method	Inferred Type
`select("col")`	`{ col: any }`
`select(["col", "alias"])`	`{ alias: any }`
`selectRaw<T>(...)`	`T`
`selectFunc("count", col, alias)`	`{ [alias]: number }`
`selectFunc("sum", col, alias)`	`{ [alias]: number }`
`selectFunc("avg", col, alias)`	`{ [alias]: number }`
`selectFunc("upper", col, alias)`	`{ [alias]: string }`
`selectFunc("lower", col, alias)`	`{ [alias]: string }`
`selectJson(col, path, alias)`	`{ [alias]: any }`
`selectJsonText(col, path, alias)`	`{ [alias]: string }`
`select(cb, alias)`	`{ [alias]: any }`

selectRaw with CAST

CAST expressions are fully supported. The type after AS inside CAST() is recognized as a SQL type, not an alias:

// CAST type is not treated as alias
await sql.query("users").selectRaw("CAST(age AS VARCHAR) as ageString").one();

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();

Raw Queries

You can use the rawQuery method to execute raw SQL queries.

const users = await sql.rawQuery("SELECT * FROM users");

Raw Query with Parameters

You can use the rawQuery method to execute raw SQL queries with parameters. You can use ? as a placeholder for the parameters regardless of the database dialect. You can still use the database specific placeholder syntax.

// Generic placeholder syntax
const users = await sql.rawQuery("SELECT * FROM users WHERE age > ?", [18]);

// Database specific placeholder syntax
const users = await sql.rawQuery("SELECT * FROM users WHERE age > $1", [18]);

QueryBuilder Only

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

Next: Relations

Key Features​

Basics​

Basic Usage​

Pagination with Cursor​

Joins​

Advanced Features​

pluck​

increment / decrement​

lockForUpdate / forShare​

with (Common Table Expressions, CTE)​

Example Usage​

Filtering​

Raw right-hand side with rawStatement​

Insert & Update with Raw Statements​

Raw Statements in Insert​

Raw Statements in Update​

Common Use Cases​

Automatic JSON Serialization​

What Gets Serialized​

Selecting Columns​

Basic Select​

Select with Alias (Tuple Syntax)​

Mixed Selection​

Pagination​

Chunking Large Datasets​

Pagination Methods​

From​

Joins​

Basic Joins​

Joins with Additional Conditions​

CTEs (Common Table Expressions)​

Truncate​

Soft Delete​

Limitations​

Comparison to ModelQueryBuilder​

Full API Reference​

Filtering​

Subqueries & Nested Conditions​

Joins​

Group By & Having​

Unions​

Aggregates​

Select & Raw Select​

Type-Safe Selects​

Explicit Types with selectRaw​

Type-Safe SQL Functions with selectFunc​

Type-Safe JSON Selects​

Subquery Type Safety​

Chaining Selects​

selectRaw with CAST​

Pluck​

Pagination​

Streaming (experimental)​

Locking​

CTEs (Common Table Expressions)​

Copying & Query Output​

Raw Queries​

Raw Query with Parameters​

QueryBuilder Only​

Key Features

Basics

Basic Usage

Pagination with Cursor

Joins

Advanced Features

`pluck`

`increment` / `decrement`

`lockForUpdate` / `forShare`

`with` (Common Table Expressions, CTE)

Example Usage

Filtering

Raw right-hand side with rawStatement

Insert & Update with Raw Statements

Raw Statements in Insert

Raw Statements in Update

Common Use Cases

Automatic JSON Serialization

What Gets Serialized

Selecting Columns

Basic Select

Select with Alias (Tuple Syntax)

Mixed Selection

Pagination

Chunking Large Datasets

Pagination Methods

From

Joins

Basic Joins

Joins with Additional Conditions

CTEs (Common Table Expressions)

Truncate

Soft Delete

Limitations

Comparison to ModelQueryBuilder

Full API Reference

Filtering

Subqueries & Nested Conditions

Joins

Group By & Having

Unions

Aggregates

Select & Raw Select

Type-Safe Selects

Explicit Types with `selectRaw`

Type-Safe SQL Functions with `selectFunc`

Type-Safe JSON Selects

Subquery Type Safety

Chaining Selects

selectRaw with CAST

Pluck

Pagination

Streaming (experimental)

Locking

CTEs (Common Table Expressions)

Copying & Query Output

Raw Queries

Raw Query with Parameters

QueryBuilder Only