Database API

Complete reference for the ctx.db database interface.

Methods

db.query

query<T>(sql: string, params?: any[]): T[]

Execute a SELECT query and return all matching rows.

Parameters

Name	Type	Description
`sql`	`string`	SQL query string with ? placeholders
`params` optional	`any[]`	Parameter values

Returns: T[] - Array of matching rows

Example

// Select all users
const users = db.query<User>('SELECT * FROM users');

// Select with parameters
const user = db.query<User>(
  'SELECT * FROM users WHERE id = ?',
  [userId]
)[0];

// Select with named parameters
const posts = db.query<Post>(
  'SELECT * FROM posts WHERE author_id = $authorId',
  { $authorId: authorId }
);

db.run

run(sql: string, params?: any[]): RunResult

Execute an INSERT, UPDATE, or DELETE statement.

Parameters

Name	Type	Description
`sql`	`string`	SQL statement with ? placeholders
`params` optional	`any[]`	Parameter values

Returns: RunResult - Object with changes and lastInsertRowid

Example

// Insert
const result = db.run(
  'INSERT INTO users (email, name) VALUES (?, ?)',
  [email, name]
);
console.log(result.lastInsertRowid); // New user ID

// Update
db.run('UPDATE users SET name = ? WHERE id = ?', [name, id]);

// Delete
db.run('DELETE FROM users WHERE id = ?', [id]);

db.transaction

transaction<T>(fn: () => T): T

Execute multiple statements in a transaction. Rolls back on error.

Parameters

Name	Type	Description
`fn`	`() => T`	Function containing database operations

Returns: T - Return value of the function

Example

db.transaction(() => {
  db.run('INSERT INTO orders (user_id) VALUES (?)', [userId]);
  const orderId = db.query('SELECT last_insert_rowid() as id')[0].id;
  
  for (const item of items) {
    db.run(
      'INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)',
      [orderId, item.productId, item.quantity]
    );
  }
});

RunResult Interface

interface RunResult

interface RunResult {
  changes: number;        // Number of rows affected
  lastInsertRowid: number; // ID of last inserted row
}

Change Tracking

All mutations via db.run() are automatically tracked. Subscribed SSE clients receive updates when watched tables change.