write-sql-queries

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Write Queries Skill

编写查询技能

When to Use

使用场景

Use this skill when:

Writing database queries for Cloudflare Workers
Implementing CRUD operations (Create, Read, Update, Delete)
Building complex queries with JOINs, subqueries, or aggregations
Working with D1, Durable Objects SQLite, or PostgreSQL

在以下场景使用本技能：

为Cloudflare Workers编写数据库查询
实现CRUD操作（创建、读取、更新、删除）
构建包含JOIN、子查询或聚合操作的复杂查询
操作D1、Durable Objects SQLite或PostgreSQL数据库

Database Selection

数据库选择

Database	Class	Sync/Async	Import
Cloudflare D1	`D1QB`	async	`import { D1QB } from 'workers-qb'`
Durable Objects	`DOQB`	sync	`import { DOQB } from 'workers-qb'`
PostgreSQL	`PGQB`	async	`import { PGQB } from 'workers-qb'`

数据库	类名	同步/异步	导入方式
Cloudflare D1	`D1QB`	异步	`import { D1QB } from 'workers-qb'`
Durable Objects	`DOQB`	同步	`import { DOQB } from 'workers-qb'`
PostgreSQL	`PGQB`	异步	`import { PGQB } from 'workers-qb'`

Critical: Sync vs Async

重要注意：同步与异步的区别

typescript

// D1QB/PGQB - ALWAYS use await
const result = await qb.fetchAll({ tableName: 'users' }).execute();

// DOQB - NEVER use await (synchronous)
const result = qb.fetchAll({ tableName: 'users' }).execute();

DOQB is synchronous. This is the most common mistake. Inside Durable Objects:

typescript

// CORRECT
const users = qb.fetchAll({ tableName: 'users' }).execute();

// WRONG - don't await DOQB
const users = await qb.fetchAll({ tableName: 'users' }).execute();

typescript

// D1QB/PGQB - 必须使用await
const result = await qb.fetchAll({ tableName: 'users' }).execute();

// DOQB - 绝对不能使用await（同步执行）
const result = qb.fetchAll({ tableName: 'users' }).execute();

DOQB是同步的。这是最常见的错误。在Durable Objects中：

typescript

// 正确写法
const users = qb.fetchAll({ tableName: 'users' }).execute();

// 错误写法 - 不要对DOQB使用await
const users = await qb.fetchAll({ tableName: 'users' }).execute();

Schema Definition

Schema定义

Define a schema type for autocomplete and type safety:

typescript

type Schema = {
  users: {
    id: number;
    name: string;
    email: string;
    created_at: string;
  };
  posts: {
    id: number;
    user_id: number;
    title: string;
    content: string;
  };
};

const qb = new D1QB<Schema>(env.DB);
// Now tableName, fields, returning all have autocomplete

定义Schema类型以获得自动补全和类型安全：

typescript

type Schema = {
  users: {
    id: number;
    name: string;
    email: string;
    created_at: string;
  };
  posts: {
    id: number;
    user_id: number;
    title: string;
    content: string;
  };
};

const qb = new D1QB<Schema>(env.DB);
// 现在tableName、fields、returning都支持自动补全

SELECT Patterns

SELECT查询模式

Object Syntax

对象语法

typescript

// Fetch all rows
const users = await qb.fetchAll({
  tableName: 'users',
}).execute();

// Fetch one row
const user = await qb.fetchOne({
  tableName: 'users',
  where: {
    conditions: 'id = ?',
    params: [1],
  },
}).execute();

// Select specific fields
const emails = await qb.fetchAll({
  tableName: 'users',
  fields: ['id', 'email'],
}).execute();

typescript

// 获取所有行
const users = await qb.fetchAll({
  tableName: 'users',
}).execute();

// 获取单行
const user = await qb.fetchOne({
  tableName: 'users',
  where: {
    conditions: 'id = ?',
    params: [1],
  },
}).execute();

// 选择特定字段
const emails = await qb.fetchAll({
  tableName: 'users',
  fields: ['id', 'email'],
}).execute();

Fluent API (SelectBuilder)

链式API（SelectBuilder）

typescript

// Basic chain
const users = await qb.select('users')
  .where('is_active = ?', true)
  .orderBy({ name: 'ASC' })
  .limit(10)
  .all();

// Single row
const user = await qb.select('users')
  .where('id = ?', userId)
  .one();

// Count query
const count = await qb.select('users')
  .where('is_active = ?', true)
  .count();

console.log(count.results?.total);

typescript

// 基础链式调用
const users = await qb.select('users')
  .where('is_active = ?', true)
  .orderBy({ name: 'ASC' })
  .limit(10)
  .all();

// 获取单行
const user = await qb.select('users')
  .where('id = ?', userId)
  .one();

// 计数查询
const count = await qb.select('users')
  .where('is_active = ?', true)
  .count();

console.log(count.results?.total);

WHERE Clauses

WHERE子句

typescript

// Single condition
where: {
  conditions: 'email = ?',
  params: ['john@example.com'],
}

// Multiple conditions (AND)
where: {
  conditions: ['status = ?', 'role_id = ?'],
  params: ['active', 2],
}

// Numbered parameters (for reuse)
where: {
  conditions: 'owner_id = ?1 OR assignee_id = ?1',
  params: ['user123'],
}

// Simple string (no params)
where: 'is_active = true'

// String array (AND, no params)
where: ['is_active = true', 'deleted_at IS NULL']

typescript

// 单个条件
where: {
  conditions: 'email = ?',
  params: ['john@example.com'],
}

// 多个条件（AND连接）
where: {
  conditions: ['status = ?', 'role_id = ?'],
  params: ['active', 2],
}

// 编号参数（可复用）
where: {
  conditions: 'owner_id = ?1 OR assignee_id = ?1',
  params: ['user123'],
}

// 简单字符串（无参数）
where: 'is_active = true'

// 字符串数组（AND连接，无参数）
where: ['is_active = true', 'deleted_at IS NULL']

whereIn for Bulk Lookups

whereIn批量查询

typescript

// Single column
const users = await qb.select('users')
  .whereIn('id', [1, 2, 3, 4, 5])
  .all();

// Multiple columns (composite key)
const records = await qb.select('assignments')
  .whereIn(['user_id', 'project_id'], [[1, 101], [2, 102], [3, 103]])
  .all();

typescript

// 单列查询
const users = await qb.select('users')
  .whereIn('id', [1, 2, 3, 4, 5])
  .all();

// 多列查询（复合键）
const records = await qb.select('assignments')
  .whereIn(['user_id', 'project_id'], [[1, 101], [2, 102], [3, 103]])
  .all();

DISTINCT

DISTINCT去重

typescript

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

// DISTINCT ON (PostgreSQL only)
const latestPerDepartment = await qb.select('employees')
  .distinct(['department'])
  .fields(['department', 'name', 'created_at'])
  .orderBy({ department: 'ASC', created_at: 'DESC' })
  .all();
// SELECT DISTINCT ON (department) department, name, created_at FROM employees

typescript

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

// DISTINCT ON（仅PostgreSQL支持）
const latestPerDepartment = await qb.select('employees')
  .distinct(['department'])
  .fields(['department', 'name', 'created_at'])
  .orderBy({ department: 'ASC', created_at: 'DESC' })
  .all();
// SELECT DISTINCT ON (department) department, name, created_at FROM employees

JOINs

JOIN连接

typescript

// INNER JOIN
const usersWithRoles = await qb.fetchAll({
  tableName: 'users',
  fields: ['users.name', 'roles.name AS role_name'],
  join: {
    type: 'INNER',
    table: 'roles',
    on: 'users.role_id = roles.id',
  },
}).execute();

// LEFT JOIN
join: {
  type: 'LEFT',
  table: 'profiles',
  on: 'users.id = profiles.user_id',
}

// Multiple JOINs
join: [
  { type: 'INNER', table: 'roles', on: 'users.role_id = roles.id' },
  { type: 'LEFT', table: 'profiles', on: 'users.id = profiles.user_id' },
]

typescript

// INNER JOIN内连接
const usersWithRoles = await qb.fetchAll({
  tableName: 'users',
  fields: ['users.name', 'roles.name AS role_name'],
  join: {
    type: 'INNER',
    table: 'roles',
    on: 'users.role_id = roles.id',
  },
}).execute();

// LEFT JOIN左连接
join: {
  type: 'LEFT',
  table: 'profiles',
  on: 'users.id = profiles.user_id',
}

// 多JOIN连接
join: [
  { type: 'INNER', table: 'roles', on: 'users.role_id = roles.id' },
  { type: 'LEFT', table: 'profiles', on: 'users.id = profiles.user_id' },
]

JOIN Convenience Methods

JOIN便捷方法

typescript

// Using convenience methods (fluent API)
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' })
  .all();

// CROSS JOIN
const combinations = await qb.select('colors')
  .crossJoin({ table: 'sizes' })
  .all();

// NATURAL JOIN (auto-matches columns with same name)
const combined = await qb.select('orders')
  .naturalJoin('customers')
  .all();

typescript

// 使用便捷方法（链式API）
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' })
  .all();

// CROSS JOIN交叉连接
const combinations = await qb.select('colors')
  .crossJoin({ table: 'sizes' })
  .all();

// NATURAL JOIN自然连接（自动匹配同名列）
const combined = await qb.select('orders')
  .naturalJoin('customers')
  .all();

Subqueries

子查询

typescript

// IN with subquery
const activeProjectsQuery = qb
  .select('projects')
  .fields('id')
  .where('status = ?', 'active');

const tasks = await qb.select('tasks')
  .where('project_id IN ?', activeProjectsQuery)
  .all();

// EXISTS with subquery
const permissionQuery = qb
  .select('permissions')
  .where('user_id = ?', userId)
  .where('action = ?', 'edit');

const docs = await qb.select('documents')
  .where('EXISTS ?', permissionQuery)
  .all();

typescript

// IN子查询
const activeProjectsQuery = qb
  .select('projects')
  .fields('id')
  .where('status = ?', 'active');

const tasks = await qb.select('tasks')
  .where('project_id IN ?', activeProjectsQuery)
  .all();

// EXISTS子查询
const permissionQuery = qb
  .select('permissions')
  .where('user_id = ?', userId)
  .where('action = ?', 'edit');

const docs = await qb.select('documents')
  .where('EXISTS ?', permissionQuery)
  .all();

Pagination (Manual)

分页（手动实现）

typescript

const pageSize = 20;
const page = 2;

const users = await qb.fetchAll({
  tableName: 'users',
  orderBy: 'created_at DESC',
  limit: pageSize,
  offset: (page - 1) * pageSize,
}).execute();

typescript

const pageSize = 20;
const page = 2;

const users = await qb.fetchAll({
  tableName: 'users',
  orderBy: 'created_at DESC',
  limit: pageSize,
  offset: (page - 1) * pageSize,
}).execute();

Pagination Helper

分页助手

typescript

// Use .paginate() for automatic pagination metadata
const result = await qb.select('users')
  .where('active = ?', true)
  .orderBy({ created_at: 'DESC' })
  .paginate({ page: 2, perPage: 20 });

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

typescript

// 使用.paginate()自动获取分页元数据
const result = await qb.select('users')
  .where('active = ?', true)
  .orderBy({ created_at: 'DESC' })
  .paginate({ page: 2, perPage: 20 });

// 返回结果：
// {
//   results: [...],
//   pagination: {
//     page: 2,
//     perPage: 20,
//     total: 150,
//     totalPages: 8,
//     hasNext: true,
//     hasPrev: true
//   }
// }

UNION / INTERSECT / EXCEPT

UNION / INTERSECT / EXCEPT集合操作

typescript

// UNION - combine results, remove duplicates
const allUsers = await qb.select('active_users')
  .fields(['id', 'name'])
  .union(qb.select('archived_users').fields(['id', 'name']))
  .all();

// UNION ALL - keep duplicates
const allRecords = await qb.select('table1')
  .fields(['id'])
  .unionAll(qb.select('table2').fields(['id']))
  .all();

// INTERSECT - only common rows
const commonUsers = await qb.select('users')
  .fields(['id'])
  .intersect(qb.select('admins').fields(['user_id']))
  .all();

// EXCEPT - rows in first but not second
const regularUsers = await qb.select('all_users')
  .fields(['id'])
  .except(qb.select('blocked_users').fields(['user_id']))
  .all();

// Chain multiple set operations
const combined = await qb.select('table1')
  .fields(['id'])
  .union(qb.select('table2').fields(['id']))
  .union(qb.select('table3').fields(['id']))
  .orderBy({ id: 'ASC' })  // ORDER BY applies to combined result
  .all();

typescript

// UNION - 合并结果并去重
const allUsers = await qb.select('active_users')
  .fields(['id', 'name'])
  .union(qb.select('archived_users').fields(['id', 'name']))
  .all();

// UNION ALL - 合并结果保留重复
const allRecords = await qb.select('table1')
  .fields(['id'])
  .unionAll(qb.select('table2').fields(['id']))
  .all();

// INTERSECT - 仅保留共同行
const commonUsers = await qb.select('users')
  .fields(['id'])
  .intersect(qb.select('admins').fields(['user_id']))
  .all();

// EXCEPT - 保留第一个查询有但第二个没有的行
const regularUsers = await qb.select('all_users')
  .fields(['id'])
  .except(qb.select('blocked_users').fields(['user_id']))
  .all();

// 链式多个集合操作
const combined = await qb.select('table1')
  .fields(['id'])
  .union(qb.select('table2').fields(['id']))
  .union(qb.select('table3').fields(['id']))
  .orderBy({ id: 'ASC' })  // ORDER BY作用于合并后的结果
  .all();

CTEs (Common Table Expressions)

CTE（公共表表达式）

typescript

// Simple CTE - WITH clause
const ordersWithActiveUsers = 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();
// WITH active_users AS (SELECT * FROM users WHERE status = ?)
// SELECT * FROM orders INNER JOIN active_users ON orders.user_id = active_users.id

// Multiple CTEs
const result = await qb.select('summary')
  .with('recent_orders', qb.select('orders').where('created_at > ?', lastWeek))
  .with('top_customers', qb.select('customers').where('total_spent > ?', 1000))
  .all();

// CTE with explicit column names
const stats = await qb.select('user_counts')
  .with(
    'user_stats',
    qb.select('users').fields(['department', 'COUNT(*) as cnt']).groupBy('department'),
    ['dept', 'count']  // Column aliases for the CTE
  )
  .all();
// WITH user_stats(dept, count) AS (SELECT department, COUNT(*) as cnt FROM users GROUP BY department)

typescript

// 简单CTE - WITH子句
const ordersWithActiveUsers = 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();
// WITH active_users AS (SELECT * FROM users WHERE status = ?)
// SELECT * FROM orders INNER JOIN active_users ON orders.user_id = active_users.id

// 多个CTE
const result = await qb.select('summary')
  .with('recent_orders', qb.select('orders').where('created_at > ?', lastWeek))
  .with('top_customers', qb.select('customers').where('total_spent > ?', 1000))
  .all();

// 指定列名的CTE
const stats = await qb.select('user_counts')
  .with(
    'user_stats',
    qb.select('users').fields(['department', 'COUNT(*) as cnt']).groupBy('department'),
    ['dept', 'count']  // CTE的列别名
  )
  .all();
// WITH user_stats(dept, count) AS (SELECT department, COUNT(*) as cnt FROM users GROUP BY department)

Order By

排序（Order By）

typescript

// Simple
orderBy: 'name'

// With direction
orderBy: { name: 'DESC' }

// Multiple columns
orderBy: [
  { created_at: 'DESC' },
  'name ASC',
]

typescript

// 简单排序
orderBy: 'name'

// 指定排序方向
orderBy: { name: 'DESC' }

// 多列排序
orderBy: [
  { created_at: 'DESC' },
  'name ASC',
]

Group By and Having

分组与Having

typescript

const stats = await qb.fetchAll({
  tableName: 'orders',
  fields: ['customer_id', 'COUNT(*) as order_count', 'SUM(total) as total_spent'],
  groupBy: 'customer_id',
  having: 'SUM(total) > 1000',
}).execute();

typescript

const stats = await qb.fetchAll({
  tableName: 'orders',
  fields: ['customer_id', 'COUNT(*) as order_count', 'SUM(total) as total_spent'],
  groupBy: 'customer_id',
  having: 'SUM(total) > 1000',
}).execute();

Lazy Execution (Large Datasets)

延迟执行（处理大数据集）

typescript

// D1/PostgreSQL - AsyncIterable
const lazyResult = await qb.fetchAll({
  tableName: 'large_table',
  lazy: true,
}).execute();

for await (const row of lazyResult.results!) {
  processRow(row);
}

// DOQB - Iterable (sync)
const lazyResult = qb.fetchAll({
  tableName: 'large_table',
  lazy: true,
}).execute();

for (const row of lazyResult.results!) {
  processRow(row);
}

typescript

// D1/PostgreSQL - AsyncIterable异步迭代
const lazyResult = await qb.fetchAll({
  tableName: 'large_table',
  lazy: true,
}).execute();

for await (const row of lazyResult.results!) {
  processRow(row);
}

// DOQB - Iterable同步迭代
const lazyResult = qb.fetchAll({
  tableName: 'large_table',
  lazy: true,
}).execute();

for (const row of lazyResult.results!) {
  processRow(row);
}

INSERT Patterns

INSERT插入模式

Single Row

单行插入

typescript

const newUser = await qb.insert({
  tableName: 'users',
  data: {
    name: 'John Doe',
    email: 'john@example.com',
  },
  returning: '*',
}).execute();

typescript

const newUser = await qb.insert({
  tableName: 'users',
  data: {
    name: 'John Doe',
    email: 'john@example.com',
  },
  returning: '*',
}).execute();

Multiple Rows

多行插入

typescript

const newUsers = await qb.insert({
  tableName: 'users',
  data: [
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' },
  ],
  returning: ['id', 'name'],
}).execute();

typescript

const newUsers = await qb.insert({
  tableName: 'users',
  data: [
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' },
  ],
  returning: ['id', 'name'],
}).execute();

ON CONFLICT - IGNORE

ON CONFLICT - 忽略冲突

Skip insertion if conflict occurs:

typescript

await qb.insert({
  tableName: 'users',
  data: { email: 'existing@example.com', name: 'Ignored' },
  onConflict: 'IGNORE',
}).execute();

发生冲突时跳过插入：

typescript

await qb.insert({
  tableName: 'users',
  data: { email: 'existing@example.com', name: 'Ignored' },
  onConflict: 'IGNORE',
}).execute();

ON CONFLICT - REPLACE

ON CONFLICT - 替换冲突行

Replace existing row on conflict:

typescript

await qb.insert({
  tableName: 'users',
  data: { email: 'existing@example.com', name: 'Replaced' },
  onConflict: 'REPLACE',
}).execute();

发生冲突时替换现有行：

typescript

await qb.insert({
  tableName: 'users',
  data: { email: 'existing@example.com', name: 'Replaced' },
  onConflict: 'REPLACE',
}).execute();

UPSERT (ON CONFLICT DO UPDATE)

UPSERT（ON CONFLICT DO UPDATE）

typescript

import { Raw } from 'workers-qb';

await qb.insert({
  tableName: 'users',
  data: {
    email: 'john@example.com',
    name: 'John',
    login_count: 1,
  },
  onConflict: {
    column: 'email',  // or ['email', 'tenant_id'] for composite
    data: {
      login_count: new Raw('login_count + 1'),
      updated_at: new Raw('CURRENT_TIMESTAMP'),
    },
    // Optional: conditional update
    where: 'excluded.updated_at > users.updated_at',
  },
}).execute();

typescript

import { Raw } from 'workers-qb';

await qb.insert({
  tableName: 'users',
  data: {
    email: 'john@example.com',
    name: 'John',
    login_count: 1,
  },
  onConflict: {
    column: 'email',  // 复合键可使用['email', 'tenant_id']
    data: {
      login_count: new Raw('login_count + 1'),
      updated_at: new Raw('CURRENT_TIMESTAMP'),
    },
    // 可选：条件更新
    where: 'excluded.updated_at > users.updated_at',
  },
}).execute();

Using Raw for SQL Expressions

使用Raw处理SQL表达式

typescript

import { Raw } from 'workers-qb';

await qb.insert({
  tableName: 'posts',
  data: {
    title: 'My Post',
    created_at: new Raw('CURRENT_TIMESTAMP'),
    slug: new Raw("LOWER(REPLACE('My Post', ' ', '-'))"),
  },
}).execute();

typescript

import { Raw } from 'workers-qb';

await qb.insert({
  tableName: 'posts',
  data: {
    title: 'My Post',
    created_at: new Raw('CURRENT_TIMESTAMP'),
    slug: new Raw("LOWER(REPLACE('My Post', ' ', '-'))"),
  },
}).execute();

UPDATE Patterns

UPDATE更新模式

Basic Update

基础更新

typescript

await qb.update({
  tableName: 'users',
  data: {
    name: 'Updated Name',
  },
  where: {
    conditions: 'id = ?',
    params: [userId],
  },
}).execute();

typescript

await qb.update({
  tableName: 'users',
  data: {
    name: 'Updated Name',
  },
  where: {
    conditions: 'id = ?',
    params: [userId],
  },
}).execute();

Update with Raw Expressions

使用Raw表达式更新

typescript

import { Raw } from 'workers-qb';

await qb.update({
  tableName: 'posts',
  data: {
    view_count: new Raw('view_count + 1'),
    updated_at: new Raw('CURRENT_TIMESTAMP'),
  },
  where: {
    conditions: 'id = ?',
    params: [postId],
  },
}).execute();

typescript

import { Raw } from 'workers-qb';

await qb.update({
  tableName: 'posts',
  data: {
    view_count: new Raw('view_count + 1'),
    updated_at: new Raw('CURRENT_TIMESTAMP'),
  },
  where: {
    conditions: 'id = ?',
    params: [postId],
  },
}).execute();

Update with Returning

更新并返回结果

typescript

const updated = await qb.update({
  tableName: 'users',
  data: { status: 'verified' },
  where: {
    conditions: 'email = ?',
    params: ['john@example.com'],
  },
  returning: ['id', 'status', 'updated_at'],
}).execute();

console.log(updated.results);

typescript

const updated = await qb.update({
  tableName: 'users',
  data: { status: 'verified' },
  where: {
    conditions: 'email = ?',
    params: ['john@example.com'],
  },
  returning: ['id', 'status', 'updated_at'],
}).execute();

console.log(updated.results);

Multiple WHERE Conditions

多条件WHERE

typescript

await qb.update({
  tableName: 'tasks',
  data: { status: 'completed' },
  where: {
    conditions: ['project_id = ?', 'assignee_id = ?'],
    params: [projectId, userId],
  },
}).execute();

typescript

await qb.update({
  tableName: 'tasks',
  data: { status: 'completed' },
  where: {
    conditions: ['project_id = ?', 'assignee_id = ?'],
    params: [projectId, userId],
  },
}).execute();

DELETE Patterns

DELETE删除模式

Basic Delete

基础删除

typescript

await qb.delete({
  tableName: 'sessions',
  where: {
    conditions: 'user_id = ?',
    params: [userId],
  },
}).execute();

typescript

await qb.delete({
  tableName: 'sessions',
  where: {
    conditions: 'user_id = ?',
    params: [userId],
  },
}).execute();

Delete with Returning

删除并返回结果

typescript

const deleted = await qb.delete({
  tableName: 'users',
  where: {
    conditions: 'id = ?',
    params: [userId],
  },
  returning: ['id', 'email'],
}).execute();

console.log('Deleted:', deleted.results);

typescript

const deleted = await qb.delete({
  tableName: 'users',
  where: {
    conditions: 'id = ?',
    params: [userId],
  },
  returning: ['id', 'email'],
}).execute();

console.log('Deleted:', deleted.results);

Ordered Delete with Limit

带排序和限制的删除

typescript

// Delete oldest 100 expired sessions
await qb.delete({
  tableName: 'sessions',
  where: 'expires_at < CURRENT_TIMESTAMP',
  orderBy: 'expires_at ASC',
  limit: 100,
}).execute();

typescript

// 删除最早的100条过期会话
await qb.delete({
  tableName: 'sessions',
  where: 'expires_at < CURRENT_TIMESTAMP',
  orderBy: 'expires_at ASC',
  limit: 100,
}).execute();

Raw Queries

原生查询（Raw Queries）

For complex SQL not covered by the builder:

typescript

import { FetchTypes } from 'workers-qb';

// Fetch multiple rows
const results = await qb.raw({
  query: 'SELECT * FROM users WHERE email LIKE ?',
  args: ['%@example.com'],
  fetchType: FetchTypes.ALL,  // or 'ALL'
}).execute();

// Fetch single row
const user = await qb.raw({
  query: 'SELECT * FROM users WHERE id = ? LIMIT 1',
  args: [userId],
  fetchType: FetchTypes.ONE,  // or 'ONE'
}).execute();

// Execute without fetching (INSERT/UPDATE/DELETE)
await qb.raw({
  query: 'UPDATE users SET last_seen = CURRENT_TIMESTAMP WHERE id = ?',
  args: [userId],
}).execute();

对于构建器不支持的复杂SQL：

typescript

import { FetchTypes } from 'workers-qb';

// 获取多行结果
const results = await qb.raw({
  query: 'SELECT * FROM users WHERE email LIKE ?',
  args: ['%@example.com'],
  fetchType: FetchTypes.ALL,  // 或直接写'ALL'
}).execute();

// 获取单行结果
const user = await qb.raw({
  query: 'SELECT * FROM users WHERE id = ? LIMIT 1',
  args: [userId],
  fetchType: FetchTypes.ONE,  // 或直接写'ONE'
}).execute();

// 执行不返回结果的查询（INSERT/UPDATE/DELETE）
await qb.raw({
  query: 'UPDATE users SET last_seen = CURRENT_TIMESTAMP WHERE id = ?',
  args: [userId],
}).execute();

Query Debugging

查询调试

toSQL() - Get Query Without Executing

toSQL() - 不执行获取查询语句

typescript

// Get the SQL and parameters without executing
const { sql, params } = qb.select('users')
  .where('id = ?', 1)
  .where('status = ?', 'active')
  .toSQL();

console.log(sql);    // SELECT * FROM users WHERE (id = ?) AND (status = ?)
console.log(params); // [1, 'active']

typescript

// 获取SQL语句和参数但不执行
const { sql, params } = qb.select('users')
  .where('id = ?', 1)
  .where('status = ?', 'active')
  .toSQL();

console.log(sql);    // SELECT * FROM users WHERE (id = ?) AND (status = ?)
console.log(params); // [1, 'active']

toDebugSQL() - Interpolated SQL (for logging only)

toDebugSQL() - 插值后的SQL（仅用于日志）

typescript

// Get SQL with parameters interpolated - NEVER use for execution
const debugSql = qb.select('users')
  .where('id = ?', 1)
  .where("name = ?", "O'Brien")
  .toDebugSQL();

console.log(debugSql); // SELECT * FROM users WHERE (id = 1) AND (name = 'O''Brien')

typescript

// 获取参数已插值的SQL - 绝对不能用于执行
const debugSql = qb.select('users')
  .where('id = ?', 1)
  .where("name = ?", "O'Brien")
  .toDebugSQL();

console.log(debugSql); // SELECT * FROM users WHERE (id = 1) AND (name = 'O''Brien')

EXPLAIN - Query Plan Analysis

EXPLAIN - 查询计划分析

typescript

// Get the query execution plan
const plan = await qb.select('users')
  .where('id = ?', 1)
  .explain();

// Returns array of plan rows showing how the database will execute the query
console.log(plan.results);
// [{ id: 0, parent: 0, notused: 0, detail: 'SCAN users' }]

typescript

// 获取查询执行计划
const plan = await qb.select('users')
  .where('id = ?', 1)
  .explain();

// 返回计划行数组，展示数据库执行查询的方式
console.log(plan.results);
// [{ id: 0, parent: 0, notused: 0, detail: 'SCAN users' }]

Query Hooks

查询钩子

typescript

// beforeQuery - modify queries before execution
qb.beforeQuery((query, type) => {
  // Add tenant filter to all queries
  if (type !== 'INSERT' && type !== 'RAW') {
    query.query = query.query.replace('WHERE', `WHERE tenant_id = ${tenantId} AND`)
  }
  return query
})

// afterQuery - log, modify results, record metrics
qb.afterQuery((result, query, duration) => {
  console.log(`Query took ${duration}ms:`, query.query)
  metrics.record(query.query, duration)
  return result
})

注册中间件风格的钩子处理所有查询：

typescript

// beforeQuery - 执行前修改查询
qb.beforeQuery((query, type) => {
  // 为所有查询添加租户过滤
  if (type !== 'INSERT' && type !== 'RAW') {
    query.query = query.query.replace('WHERE', `WHERE tenant_id = ${tenantId} AND`)
  }
  return query
})

// afterQuery - 日志记录、结果修改、指标统计
qb.afterQuery((result, query, duration) => {
  console.log(`查询耗时${duration}ms:`, query.query)
  metrics.record(query.query, duration)
  return result
})

Transactions

事务

D1QB Transactions (async, batch-based)

D1QB事务（异步，基于批量）

typescript

// D1 uses batching - all queries succeed or all fail together
const results = await qb.transaction(async (tx) => {
  return [
    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] }
    }),
  ]
})

typescript

// D1使用批量处理 - 所有查询要么全部成功要么全部失败
const results = await qb.transaction(async (tx) => {
  return [
    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 Transactions (sync, SQLite BEGIN/COMMIT)

DOQB事务（同步，SQLite BEGIN/COMMIT）

typescript

// DOQB uses SQLite's native transaction support
// Should be called within blockConcurrencyWhile for proper isolation
this.ctx.blockConcurrencyWhile(() => {
  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()
    // Automatically commits on success, rolls back on error
  })
})

typescript

// DOQB使用SQLite原生事务支持
// 应在blockConcurrencyWhile中调用以保证隔离性
this.ctx.blockConcurrencyWhile(() => {
  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()
    // 成功自动提交，出错自动回滚
  })
})

Checklist

检查清单

Before executing queries, verify:

Called
```
.execute()
```
on the query (or
```
.all()
```
,
```
.one()
```
,
```
.paginate()
```
)
Using
```
await
```
for D1QB/PGQB, no
```
await
```
for DOQB
Using parameterized queries (
```
?
```
placeholders), not string interpolation
WHERE clause is provided for UPDATE/DELETE (to avoid affecting all rows)
Schema type is defined for autocomplete and type safety
Using
```
Raw
```
for SQL expressions (not strings) in data objects
Use
```
.toSQL()
```
or
```
.toDebugSQL()
```
for debugging, not for execution

执行查询前请验证：

已调用查询的.execute()（或.all()、.one()、.paginate()）
D1QB/PGQB使用了await，DOQB绝对不使用await
使用参数化查询（?占位符），而非字符串拼接
UPDATE/DELETE查询已提供WHERE子句（避免影响所有行）
已定义Schema类型以获得自动补全和类型安全
数据对象中的SQL表达式使用了Raw而非字符串
调试时使用.toSQL()或.toDebugSQL()，而非直接执行调试语句

Common Mistakes

常见错误

typescript

// WRONG: Forgot .execute()
const users = await qb.fetchAll({ tableName: 'users' });

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

// WRONG: String interpolation (SQL injection risk)
where: `email = '${userEmail}'`

// CORRECT: Parameterized
where: { conditions: 'email = ?', params: [userEmail] }

// WRONG: Using await with DOQB
const result = await doqb.fetchAll({ tableName: 'users' }).execute();

// CORRECT: No await with DOQB
const result = doqb.fetchAll({ tableName: 'users' }).execute();

typescript

// 错误：忘记调用.execute()
const users = await qb.fetchAll({ tableName: 'users' });

// 正确
const users = await qb.fetchAll({ tableName: 'users' }).execute();

// 错误：字符串拼接（存在SQL注入风险）
where: `email = '${userEmail}'`

// 正确：参数化查询
where: { conditions: 'email = ?', params: [userEmail] }

// 错误：对DOQB使用await
const result = await doqb.fetchAll({ tableName: 'users' }).execute();

// 正确：DOQB不使用await
const result = doqb.fetchAll({ tableName: 'users' }).execute();