Query Builder API

The Query Builder provides a fluent, type-safe API for constructing SQL queries programmatically.

Installation

The Query Builder is included in the core package:

npm install @northprint/duckdb-wasm-adapter-core

Factory Functions

select()

Creates a SELECT query builder.

function select(...columns: string[]): SelectQueryBuilder

import { select } from '@northprint/duckdb-wasm-adapter-core';

const query = select('id', 'name', 'email')
  .from('users')
  .where('active', '=', true)
  .build();
// SELECT id, name, email FROM users WHERE active = true

insertInto()

Creates an INSERT query builder.

function insertInto(table: string): InsertQueryBuilder

import { insertInto } from '@northprint/duckdb-wasm-adapter-core';

const query = insertInto('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .build();
// INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

update()

Creates an UPDATE query builder.

function update(table: string): UpdateQueryBuilder

import { update } from '@northprint/duckdb-wasm-adapter-core';

const query = update('users')
  .set({ active: false })
  .where('last_login', '<', '2023-01-01')
  .build();
// UPDATE users SET active = false WHERE last_login < '2023-01-01'

deleteFrom()

Creates a DELETE query builder.

function deleteFrom(table: string): DeleteQueryBuilder

import { deleteFrom } from '@northprint/duckdb-wasm-adapter-core';

const query = deleteFrom('users')
  .where('status', '=', 'deleted')
  .build();
// DELETE FROM users WHERE status = 'deleted'

SelectQueryBuilder

Column Selection

select()

Add columns to SELECT clause.

select(...columns: string[]): this

select('id', 'name', 'email')
select('u.name', 'p.title', 'COUNT(*) as post_count')
select('*')

FROM Clause

from()

Specify the main table.

from(table: string, alias?: string): this

.from('users')
.from('users', 'u')
.from('(SELECT * FROM active_users)', 'au')

WHERE Conditions

where()

Add WHERE conditions.

where(column: string, operator?: ComparisonOperator, value?: any): this
where(callback: (builder: WhereBuilder) => void): this

.where('age', '>', 18)
.where('status', '=', 'active')
.where('name', 'LIKE', '%john%')
.where((builder) => {
  builder.where('role', '=', 'admin')
         .orWhere('role', '=', 'moderator');
})

orWhere()

Add OR WHERE conditions.

orWhere(column: string, operator?: ComparisonOperator, value?: any): this

.where('role', '=', 'admin')
.orWhere('role', '=', 'moderator')

whereIn()

Add WHERE IN conditions.

whereIn(column: string, values: any[]): this

.whereIn('status', ['active', 'pending'])
.whereIn('department_id', [1, 2, 3])

whereNotIn()

Add WHERE NOT IN conditions.

whereNotIn(column: string, values: any[]): this

.whereNotIn('status', ['deleted', 'banned'])

whereNull()

Add WHERE IS NULL conditions.

whereNull(column: string): this

.whereNull('deleted_at')

whereNotNull()

Add WHERE IS NOT NULL conditions.

whereNotNull(column: string): this

.whereNotNull('email')

whereBetween()

Add WHERE BETWEEN conditions.

whereBetween(column: string, min: any, max: any): this

.whereBetween('age', 18, 65)
.whereBetween('created_at', '2024-01-01', '2024-12-31')

whereRaw()

Add raw WHERE conditions.

whereRaw(sql: string, bindings?: any[]): this

.whereRaw('EXTRACT(year FROM created_at) = ?', [2024])
.whereRaw('salary > (SELECT AVG(salary) FROM employees)')

JOIN Operations

join()

Add INNER JOIN.

join(table: string, alias: string, leftColumn: string, operator: string, rightColumn: string): this
join(table: string, alias: string, callback: (builder: JoinBuilder) => void): this

.join('posts', 'p', 'users.id', '=', 'p.user_id')
.join('comments', 'c', (builder) => {
  builder.on('posts.id', '=', 'c.post_id')
         .where('c.approved', '=', true);
})

leftJoin()

Add LEFT JOIN.

leftJoin(table: string, alias: string, leftColumn: string, operator: string, rightColumn: string): this

.leftJoin('profiles', 'pr', 'users.id', '=', 'pr.user_id')

rightJoin()

Add RIGHT JOIN.

rightJoin(table: string, alias: string, leftColumn: string, operator: string, rightColumn: string): this

innerJoin()

Alias for join().

outerJoin()

Add FULL OUTER JOIN.

outerJoin(table: string, alias: string, leftColumn: string, operator: string, rightColumn: string): this

GROUP BY

groupBy()

Add GROUP BY clause.

groupBy(...columns: string[]): this

.groupBy('department')
.groupBy('department', 'role')

HAVING

having()

Add HAVING conditions.

having(column: string, operator?: ComparisonOperator, value?: any): this

.groupBy('department')
.having('COUNT(*)', '>', 5)
.having('AVG(salary)', '>=', 50000)

orHaving()

Add OR HAVING conditions.

orHaving(column: string, operator?: ComparisonOperator, value?: any): this

ORDER BY

orderBy()

Add ORDER BY clause.

orderBy(column: string, direction?: 'ASC' | 'DESC'): this

.orderBy('name')
.orderBy('created_at', 'DESC')
.orderBy('salary', 'ASC')

orderByRaw()

Add raw ORDER BY clause.

orderByRaw(sql: string): this

.orderByRaw('RANDOM()')
.orderByRaw('LENGTH(name) DESC')

LIMIT and OFFSET

limit()

Add LIMIT clause.

limit(count: number): this

.limit(10)
.limit(100)

offset()

Add OFFSET clause.

offset(count: number): this

.offset(20)
.limit(10)
.offset(50) // LIMIT 10 OFFSET 50

Common Table Expressions (CTEs)

with()

Add WITH clause.

with(name: string, query: QueryBuilder | string): this

.with('active_users', 
  select('*').from('users').where('status', '=', 'active')
)
.from('active_users')

withRecursive()

Add recursive WITH clause.

withRecursive(name: string, query: QueryBuilder | string): this

UNION Operations

union()

Add UNION.

union(query: QueryBuilder | string): this

select('name', 'email').from('customers')
.union(
  select('name', 'email').from('suppliers')
)

unionAll()

Add UNION ALL.

unionAll(query: QueryBuilder | string): this

Subqueries

whereExists()

Add WHERE EXISTS.

whereExists(query: QueryBuilder | string): this

.whereExists(
  select('1').from('orders').where('orders.user_id', '=', 'users.id')
)

whereNotExists()

Add WHERE NOT EXISTS.

whereNotExists(query: QueryBuilder | string): this

InsertQueryBuilder

values()

Specify values to insert.

values(data: Record<string, any> | Record<string, any>[]): this

// Single row
insertInto('users')
  .values({
    name: 'Alice',
    email: 'alice@example.com',
    age: 25
  })

// Multiple rows
insertInto('users')
  .values([
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' }
  ])

columns()

Specify columns for insert.

columns(...columns: string[]): this

insertInto('users')
  .columns('name', 'email')
  .values(['Alice', 'alice@example.com'])

fromSelect()

Insert from SELECT query.

fromSelect(query: QueryBuilder | string): this

insertInto('archived_users')
  .columns('name', 'email')
  .fromSelect(
    select('name', 'email')
      .from('users')
      .where('status', '=', 'inactive')
  )

onConflict()

Add ON CONFLICT clause (PostgreSQL-style).

onConflict(columns?: string[]): ConflictBuilder

insertInto('users')
  .values({ email: 'alice@example.com', name: 'Alice Updated' })
  .onConflict(['email'])
  .doUpdate({ name: 'Alice Updated' })

UpdateQueryBuilder

set()

Specify columns to update.

set(data: Record<string, any>): this
set(column: string, value: any): this

update('users')
  .set({
    status: 'active',
    updated_at: 'NOW()'
  })
  .where('id', '=', 1)

update('users')
  .set('last_login', 'NOW()')
  .where('id', '=', 1)

increment()

Increment a numeric column.

increment(column: string, amount?: number): this

update('users')
  .increment('login_count')
  .increment('points', 10)
  .where('id', '=', 1)

decrement()

Decrement a numeric column.

decrement(column: string, amount?: number): this

update('products')
  .decrement('stock', 1)
  .where('id', '=', productId)

DeleteQueryBuilder

join()

Add JOIN to DELETE query.

join(table: string, alias: string, leftColumn: string, operator: string, rightColumn: string): this

deleteFrom('users')
  .join('user_sessions', 'us', 'users.id', '=', 'us.user_id')
  .where('us.expired', '=', true)

Type Safety

Typed Query Builder

Use TypeScript interfaces for type safety:

interface User {
  id: number;
  name: string;
  email: string;
  age: number;
  department: string;
}

const query = select<User>('id', 'name', 'email')
  .from('users')
  .where('age', '>', 18)
  .build();

// Result will be typed as Pick<User, 'id' | 'name' | 'email'>[]

Generic Constraints

interface QueryBuilder<T = any> {
  build(): string;
  buildWithBindings(): { sql: string; bindings: any[] };
}

interface SelectQueryBuilder<T = any> extends QueryBuilder<T> {
  select<K extends keyof T>(...columns: K[]): SelectQueryBuilder<Pick<T, K>>;
  from(table: string, alias?: string): this;
  where<K extends keyof T>(column: K, operator?: ComparisonOperator, value?: T[K]): this;
}

Comparison Operators

type ComparisonOperator = 
  | '=' | '!=' | '<>' | '<' | '<=' | '>' | '>='
  | 'LIKE' | 'NOT LIKE' | 'ILIKE' | 'NOT ILIKE'
  | 'IN' | 'NOT IN'
  | 'IS' | 'IS NOT'
  | 'EXISTS' | 'NOT EXISTS'
  | 'BETWEEN' | 'NOT BETWEEN'
  | 'SIMILAR TO' | 'NOT SIMILAR TO'
  | '~' | '!~' | '~*' | '!~*';

Raw SQL

raw()

Include raw SQL in queries.

function raw(sql: string, bindings?: any[]): RawExpression

import { raw } from '@northprint/duckdb-wasm-adapter-core';

select('name', raw('COUNT(*) as total'))
  .from('users')
  .where('created_at', '>', raw('NOW() - INTERVAL 1 DAY'))
  .groupBy('name')

Utility Methods

clone()

Clone a query builder.

clone(): this

const baseQuery = select('*').from('users');
const activeUsers = baseQuery.clone().where('active', '=', true);
const inactiveUsers = baseQuery.clone().where('active', '=', false);

toSQL()

Get SQL without executing.

toSQL(): string

const sql = select('*')
  .from('users')
  .where('active', '=', true)
  .toSQL();

console.log(sql); // SELECT * FROM users WHERE active = true

bindings()

Get parameter bindings.

bindings(): any[]

debug()

Enable debug mode for this query.

debug(enabled?: boolean): this

select('*')
  .from('users')
  .debug(true)
  .where('id', '=', 1)

Best Practices

1. Use parameterized queries to prevent SQL injection
2. Chain methods for better readability
3. Use aliases for complex queries with joins
4. Leverage TypeScript for type safety
5. Use CTEs for complex logic instead of nested subqueries
6. Test generated SQL during development
7. Use raw expressions sparingly and carefully
8. Cache query builders when reusing the same patterns