Direct SQL (db)

Query your app’s database with “db”

ctx.db.query lets a task run SQL directly against your Compose app’s database. In the cloud this is the hosted Postgres database that backs your app; in local dev it is the local SQLite database, so the same task code works in both environments. Use it for state that does not fit collections — for example managing dynamic lookup tables (like a wallet tracker’s address list) or maintaining your own custom schema.

Signature

db: {
  query<T = Record<string, unknown>>(
    schema: string,
    sql: string,
    params?: (string | number | boolean | null)[],
    retryConfig?: ContextFunctionRetryConfig
  ): Promise<DbQueryResult<T>>;
}

The result is always an object with a rows array:

export type DbQueryResult<T = Record<string, unknown>> = {
  rows: T[];
};

The schema argument

The first argument is the Postgres schema your query runs against, and it is required. Compose sets the schema as the search_path before running your query, so your SQL can reference tables in that schema without qualifying every name. The schemas reserved for Compose internals — public, pg_catalog, and information_schema — are blocked, and the name must be a valid SQL identifier (letters, digits, and underscores, starting with a letter or underscore). Pick a custom schema for your tables, for example app or tracker.

Parameters

Use Postgres-native positional placeholders ($1, $2, …) and pass values in the params array. When running locally against SQLite, the placeholders are converted to ? for you, so you can write one query that works in both environments.

Examples

Create and read a lookup table

import { TaskContext } from "compose";

export async function main({ db }: TaskContext) {
  await db.query(
    "tracker",
    `CREATE SCHEMA IF NOT EXISTS tracker`,
  );

  await db.query(
    "tracker",
    `CREATE TABLE IF NOT EXISTS watched_wallets (
      address text PRIMARY KEY,
      added_at timestamptz DEFAULT now()
    )`,
  );

  await db.query(
    "tracker",
    `INSERT INTO watched_wallets (address) VALUES ($1) ON CONFLICT DO NOTHING`,
    ["0x1234567890abcdef1234567890abcdef12345678"],
  );

  const { rows } = await db.query<{ address: string }>(
    "tracker",
    `SELECT address FROM watched_wallets`,
  );

  return rows.map((r) => r.address);
}

Like all context functions, ctx.db.query calls are logged for auditing and are deterministically cached within a task run — if a run is interrupted and resumed, completed queries return their cached results instead of re-executing. See Context Functions for details.

Compose

Guides

Reference

Query your app’s database with “db”

Signature

The schema argument

Parameters

Examples

Create and read a lookup table

​Query your app’s database with “db”

​Signature

​The schema argument

​Parameters

​Examples

​Create and read a lookup table

Query your app’s database with “db”

Signature

The schema argument

Parameters

Examples

Create and read a lookup table