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

Solutions

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