v1.14.0

Minor Changes

• #164 df6fb06 Thanks @G4brym! - Add orWhereNull, orWhereNotNull, orWhereBetween, orWhereNotBetween, orWhereLike, and orWhereNotLike convenience methods to SelectBuilder

• #162 91d4cc1 Thanks @G4brym! - Add orWhere() method to SelectBuilder for building OR conditions in fluent query chains

• #158 8d1e2ec Thanks @G4brym! - Add WHERE clause convenience methods to SelectBuilder: .when(), .whereNull(), .whereNotNull(), .whereBetween(), .whereNotBetween(), .whereLike(), .whereNotLike(), and .whereNotIn()

Patch Changes

• #160 1ae866e Thanks @G4brym! - Fix falsy WHERE/HAVING params (false, 0, empty string) being silently dropped in update, delete, and select queries

• #159 306e973 Thanks @G4brym! - Fix CROSS JOIN to not include spurious ON clause in generated SQL

• #165 ed919f2 Thanks @fc221! - Fix PostgreSQL migrations to create the internal migrations table with PostgreSQL-compatible SQL and avoid unnecessary placeholder rewriting for queries without parameters.

• #161 92675a9 Thanks @G4brym! - Fix PGQB incorrectly converting bare ? placeholders to $ instead of $1, $2, ... in PostgreSQL queries

• #163 84b5d0f Thanks @G4brym! - Add optional otherwise callback to .when() for inline if/else query building

v1.13.0

What's Changed

• Add DISTINCT support
• Add additional JOIN types (RIGHT, FULL, CROSS, NATURAL)
• Add UNION/INTERSECT/EXCEPT set operations
• Add CTEs (WITH clause) support
• Add toSQL() and toDebugSQL() for query inspection
• Add pagination helper
• Add EXPLAIN support
• Add transaction support
• Add query hooks (beforeQuery/afterQuery)
• Improve error messages with context and hints

DISTINCT Support

Select unique rows with .distinct(), with PostgreSQL DISTINCT ON support:

// Simple DISTINCT
const uniqueEmails = await qb.select('users').distinct().fields(['email']).all();

Additional JOIN Types

New convenience methods for all JOIN types:

const result = await qb.select('users')
  .innerJoin({table: 'orders', on: 'users.id = orders.user_id'})
  .leftJoin({table: 'profiles', on: 'users.id = profiles.user_id'})
  .rightJoin({table: 'teams', on: 'users.team_id = teams.id'})
  .fullJoin({table: 'projects', on: 'users.id = projects.owner_id'})
  .naturalJoin('departments')
  .all();

Set Operations (UNION/INTERSECT/EXCEPT)

Combine query results with set operations:

const allUsers = await qb.select('active_users').fields(['id', 'name'])
  .union(qb.select('archived_users').fields(['id', 'name']))
  .orderBy({name: 'ASC'})
  .all();

const commonIds = await qb.select('users').fields(['id'])
  .intersect(qb.select('admins').fields(['user_id']))
  .all();

CTEs (WITH Clause)

Define reusable subqueries with Common Table Expressions:

const result = await qb.select('orders')
  .with('active_users', qb.select('users').where('status = ?', 'active'))
  .innerJoin({table: 'active_users', on: 'orders.user_id = active_users.id'})
  .all();

Query Inspection

Debug queries without executing them:

const {sql, params} = qb.select('users').where('id = ?', 1).toSQL();
// sql: "SELECT * FROM users WHERE id = ?"
// params: [1]

const debugSql = qb.select('users').where('id = ?', 1).toDebugSQL();
// "SELECT * FROM users WHERE id = 1"

Pagination Helper

Get results with pagination metadata in one call:

const result = await qb.select('users')
  .where('active = ?', true)
  .paginate({page: 2, perPage: 20});

// result.results: [...users...]
// result.pagination: { page: 2, perPage: 20, total: 150, totalPages: 8, hasNext: true, hasPrev: true }

Transaction Support

Execute multiple queries atomically:

// D1 (async, batch-based)
const results = await qb.transaction(async (tx) => [
  tx.insert({tableName: 'orders', data: {user_id: 1, total: 100}}),
  tx.update({
    tableName: 'users',
    data: {balance: new Raw('balance - 100')},
    where: {conditions: 'id = ?', params: [1]}
  }),
]);

// DOQB (sync, SQLite transactions)
qb.transaction((tx) => {
  tx.insert({tableName: 'orders', data: {user_id: 1, total: 100}}).execute();
  tx.update({
    tableName: 'users',
    data: {balance: new Raw('balance - 100')},
    where: {conditions: 'id = ?', params: [1]}
  }).execute();
});

Query Hooks

Add middleware for logging, metrics, or tenant filtering:

qb.beforeQuery(async (query, type) => {
  console.log(`Executing ${type}: ${query.query}`);
  return query;
});

qb.afterQuery(async (result, query, duration) => {
  metrics.record(query.query, duration);
  return result;
});

Full Changelog: v1.12.0...v1.13.0

v1.12.0

What's Changed

• Fix bug when reusing parameters by @G4brym in #142
• Add table schema for better ts type inference by @G4brym in #141
• Add support for ArrayBuffer by @G4brym in #143

You can now get type inference without having to always specify the table schema, just specify once when initialing the
query builder, and then get type autocomplete on tablename, fields, order by and return types, example:

import {D1QB} from 'workers-qb';

type Schema = {
users: {
  id: number;
  name: string;
  email: string;
};
};

const qb = new D1QB<Schema>(env.DB);

const allUsers = await qb.fetchAll({
tableName: 'users',
}).execute();                                                                                                                                                                                                                                                                                                           

Full Changelog: v1.11.2...v1.12.0

v1.11.2

What's Changed

• Fix DOQB constructor parameter type

Full Changelog: v1.11.1...v1.11.2

v1.11.1

What's Changed

• feat(docs): add documentation for background writes and logger by @google-labs-jules[bot] in #122
• Fix D1QB and DOQB constructor parameter type by @G4brym in #128
• feat: export rowsRead and rowsWritten from execute by @Caio-Nogueira in #126

Full Changelog: v1.11.0...v1.11.1

v1.11.0

What's Changed

• feat: Add subquery support in .where() .having() and .join() clauses by @G4brym in #118
  Subquery docs here

Full Changelog: v1.10.2...v1.11.0

v1.10.2

What's Changed

• Fix lazy?: boolean typescript types in modular selects

Full Changelog: v1.10.0...v1.10.2

v1.10.0

What's Changed

• Add support for unordered parameters in the update function by @G4brym in #104

Example usage of the .update() function with unordered parameters

await qb.update({
  tableName: 'testTable',
  data: {
    my_field: 'test_update',
    another: 123,
    third_field: 'third value',
  },
  where: {
    conditions: ['field = ? AND another_field = ?', 'id = ?'],
    params: ['test', 'another_test', 345],
  },
}).execute()

Full Changelog: v1.9.0...v1.10.0

v1.9.0

What's Changed

• feat: make selects optionally lazy by @LuisDuarte1 in #101

In a effort to make workers-qb more efficient for databases that can support cursors/iterables it now supports lazy selects so that, a query like this doesn't potentially OoM the worker:

	SELECT * FROM table

The API for this is backwards-compatible and you can enable lazy selects by specifying the lazy parameter:

// this will now return a iterable instead of a list
this.db
	.select('table')
	.execute({lazy: true})

It also works for .fetchAll too

// it will also return a iterable
this.db
	.fetchAll({tableName: "table", lazy: true})
	.execute()

Full Changelog: v1.8.0...v1.9.0

v1.8.0

What's Changed

• feat: add whereIn for SelectBuilder by @LuisDuarte1 in #97

Example usage:
db.select('employee').whereIn('role', ["eng", "hr", "sales"])
db.select('employee').whereIn(['role', 'team'], [["eng", "workers"], ["eng", "workflows"]])

Full Changelog: v1.7.0...v1.8.0