write-sql-queries

Guide for writing SELECT, INSERT, UPDATE, DELETE queries with workers-qb

Safety Notice

This listing is imported from skills.sh public index metadata. Review upstream SKILL.md and repository scripts before running.

Copy this and send it to your AI assistant to learn

Install skill "write-sql-queries" with this command: npx skills add g4brym/workers-qb/g4brym-workers-qb-write-sql-queries

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

Database Selection

DatabaseClassSync/AsyncImport
Cloudflare D1D1QBasyncimport { D1QB } from 'workers-qb'
Durable ObjectsDOQBsyncimport { DOQB } from 'workers-qb'
PostgreSQLPGQBasyncimport { PGQB } from 'workers-qb'

Critical: Sync vs Async

// 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:

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

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

Schema Definition

Define a schema type for autocomplete and type safety:

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

SELECT Patterns

Object Syntax

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

Fluent API (SelectBuilder)

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

WHERE Clauses

// 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']

whereIn for Bulk Lookups

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

DISTINCT

// 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

JOINs

// 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' },
]

JOIN Convenience Methods

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

Subqueries

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

Pagination (Manual)

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

// 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
//   }
// }

UNION / INTERSECT / EXCEPT

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

CTEs (Common Table Expressions)

// 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)

Order By

// Simple
orderBy: 'name'

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

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

Group By and Having

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)

// 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);
}

INSERT Patterns

Single Row

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

Multiple Rows

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

Skip insertion if conflict occurs:

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

ON CONFLICT - REPLACE

Replace existing row on conflict:

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

UPSERT (ON CONFLICT DO UPDATE)

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

Using Raw for SQL Expressions

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

Basic Update

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

Update with Raw Expressions

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

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

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

DELETE Patterns

Basic Delete

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

Delete with Returning

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

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

Raw Queries

For complex SQL not covered by the builder:

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

Query Debugging

toSQL() - Get Query Without Executing

// 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']

toDebugSQL() - Interpolated SQL (for logging only)

// 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')

EXPLAIN - Query Plan Analysis

// 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' }]

Query Hooks

Register middleware-style hooks for all queries:

// 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
})

Transactions

D1QB Transactions (async, batch-based)

// 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] }
    }),
  ]
})

DOQB Transactions (sync, SQLite BEGIN/COMMIT)

// 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
  })
})

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

Common Mistakes

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

Source Transparency

This detail page is rendered from real SKILL.md content. Trust labels are metadata-based hints, not a safety guarantee.

Open in GitHubOpen in ClawHub

Related Skills

Related by shared tags or category signals.

General

image-gen

Generate AI images from text prompts. Triggers on: "生成图片", "画一张", "AI图", "generate image", "配图", "create picture", "draw", "visualize", "generate an image".

Archived SourceRecently Updated
--0xfango
General

explainer

Create explainer videos with narration and AI-generated visuals. Triggers on: "解说视频", "explainer video", "explain this as a video", "tutorial video", "introduce X (video)", "解释一下XX（视频形式）".

Archived SourceRecently Updated
--0xfango
General

asr

Transcribe audio files to text using local speech recognition. Triggers on: "转录", "transcribe", "语音转文字", "ASR", "识别音频", "把这段音频转成文字".

Archived SourceRecently Updated
--0xfango
General

axure-prototype-generator

Axure 原型代码生成器 - 输出 JavaScript 格式 HTML 代码，支持内联框架直接加载可交互原型

Archived SourceRecently Updated
--15910701838