Runtime & ctx

Every function receives two arguments: input (the JSON payload) and ctx (the runtime context). The ctx object is the primary way functions interact with your database.

module.exports = {
  handler: async (input, ctx) => {
    // ctx.db    — SQL query interface
    // ctx.fs9   — filesystem read/write interface
    // ctx.self  — metadata about the current function run
  }
};

ctx.self

Metadata about the current execution:

Property	Type	Description
`functionId`	`string`	UUID of the function
`versionId`	`string`	UUID of the active version being executed
`runId`	`string`	UUID of the current run
`triggerType`	`string`	How the function was invoked: `invoke`, `cron`, or `api`

module.exports = {
  handler: async (input, ctx) => {
    console.log(`Run ${ctx.self.runId} triggered via ${ctx.self.triggerType}`);
    return { runId: ctx.self.runId };
  }
};

ctx.db

SQL query interface. Has a single method:

`ctx.db.query(sql, params?)`

Execute a SQL statement with optional parameterized values.

const result = await ctx.db.query(
  "SELECT id, name, email FROM users WHERE active = $1 LIMIT $2",
  [true, 10]
);

Parameters:

sql — SQL string with $1, $2, etc. for parameter placeholders
params — optional array of parameter values

Returns a QueryResult (the default sql-api format; for node-postgres results see below):

Field	Type	Description
`columns`	`Array<{name, type}>`	Column metadata
`rows`	`unknown[][]`	Row tuples, ordered to match `columns`
`row_count`	`number`	Number of rows affected or returned
`command`	`string`	SQL command tag: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, etc.

Rows are returned as arrays of values (not objects). Map them yourself if you need named fields:

module.exports = {
  handler: async (input, ctx) => {
    const result = await ctx.db.query("SELECT id, name, email FROM users");
    const users = result.rows.map(([id, name, email]) => ({ id, name, email }));
    return { users, total: result.row_count };
  }
};

In this format a NUMERIC value is a JavaScript number with its scale dropped (1.50 becomes 1.5), a BIGINT above 2^53 can be rounded (9007199254740993 becomes 9007199254740992), and json/jsonb, arrays, and timestamps arrive as text ("{\"a\": 1}", "{x,y}", "2026-10-26 15:00:00+00"). Cast to text in the query (amount::text) when you need exact values, or use the node-postgres format, which returns int8 and numeric as exact strings.

node-postgres results

A function created or updated with --db-api pg (API field "db_api": "pg"; CLI 2.2.5) gets node-postgres’s query interface instead. ctx.db.query(text, values) and ctx.db.query({ text, values, rowMode }) return a node-postgres result, with rows as objects keyed by column name (arrays with rowMode: "array"):

const r = await ctx.db.query("SELECT name, qty FROM items WHERE qty > $1", [4]);
// r.command  → "SELECT"
// r.rowCount → 1
// r.rows     → [{ name: "pear", qty: 5 }]
// r.fields   → [{ name: "name", tableID: 0, columnID: 0, dataTypeID: 25, ... }, ...]

Values are parsed the way node-postgres parses them: int8 and numeric are exact strings ("9007199254740993", "1.50"), timestamptz and date are Date objects, json/jsonb are parsed, and arrays are JavaScript arrays. In fields, tableID and columnID are always 0 and dataTypeModifier is -1, even for numeric(10,2), so do not rely on them. Text with several statements and no values returns an array with one result per statement. A failed query throws node-postgres’s error object: err.code is the SQLSTATE ("42P01"), err.message is the server message without the ERROR: prefix, and err.detail is set when the server sends one.

Functions keep the default sql-api format unless you choose pg, and an update without --db-api keeps the function’s current format.

SQL Access Patterns

Read data

module.exports = {
  handler: async (input, ctx) => {
    const result = await ctx.db.query(
      "SELECT id, title, status FROM tasks WHERE assignee = $1 ORDER BY created_at DESC",
      [input.userId]
    );
    return {
      tasks: result.rows.map(([id, title, status]) => ({ id, title, status })),
      count: result.row_count
    };
  }
};

Write data

module.exports = {
  handler: async (input, ctx) => {
    await ctx.db.query(
      "INSERT INTO events (type, payload, created_at) VALUES ($1, $2, NOW())",
      [input.eventType, JSON.stringify(input.data)]
    );
    return { recorded: true };
  }
};

Transactions

Separate ctx.db.query() calls do not share a transaction: a BEGIN in one call and an UPDATE in the next run independently, and a later ROLLBACK call undoes nothing. To make several statements atomic, send them in one call, wrapped in BEGIN and COMMIT and without parameters. An error in any statement then rolls the whole block back, and a final ROLLBACK undoes it. Without BEGIN, the default sql-api format commits each statement of a multi-statement call as it runs, so an error leaves the earlier statements applied. In the default format the call returns only the last statement’s result; with --db-api pg it returns one result per statement. Text with several statements cannot take a values array: that fails with cannot insert multiple commands into a prepared statement (42601).

module.exports = {
  handler: async (input, ctx) => {
    const amount = Number(input.amount); // interpolate numbers only: the block takes no $1 parameters
    await ctx.db.query(
      `BEGIN;
       UPDATE accounts SET balance = balance + ${amount} WHERE id = 2;
       UPDATE accounts SET balance = balance - ${amount} WHERE id = 1;
       COMMIT`
    );
    return { transferred: amount };
  }
};

Aggregations

module.exports = {
  handler: async (input, ctx) => {
    const result = await ctx.db.query(`
      SELECT
        date_trunc('day', created_at) as day,
        count(*) as total,
        count(DISTINCT user_id) as unique_users
      FROM events
      WHERE created_at >= NOW() - INTERVAL '7 days'
      GROUP BY 1 ORDER BY 1 DESC
    `);
    return {
      metrics: result.rows.map(([day, total, unique_users]) => ({
        day, total, unique_users
      }))
    };
  }
};

Permission Setup

Functions run as the authenticated role. Tables created by the admin role need explicit grants:

GRANT ALL ON ALL TABLES IN SCHEMA public TO authenticated;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO authenticated;

Sequence privileges are enforced, so the sequence grant is required for any function that inserts into a SERIAL or identity column, or calls nextval(), currval(), or setval() directly. See Execution Role for the details.

ctx.fs9

Filesystem interface for reading and writing files in your database’s storage. Every path a function touches must be inside an --fs9-scope declared at deploy time: without any scope, every ctx.fs9 call fails with FS9 resource was not found. write() does not create missing parent directories either. See Filesystem Access for both.

The example below needs --fs9-scope /reports:rw and an existing /reports directory (db9 db sql myapp -q "SELECT fs9_mkdir('/reports', true)").

Method	Description
`read(path)`	Read file contents as UTF-8 string
`readBase64(path)`	Read file contents as base64-encoded string
`write(path, content)`	Write or overwrite a file
`list(path?)`	List directory entries with metadata
`stat(path)`	Get file metadata (size, type, mtime, generation, mode, sealed)
`delete(path)`	Delete a file or directory

module.exports = {
  handler: async (input, ctx) => {
    // Write a file
    await ctx.fs9.write("/reports/summary.txt", "Report content here");

    // Read it back
    const content = await ctx.fs9.read("/reports/summary.txt");

    // List directory contents
    const entries = await ctx.fs9.list("/reports/");

    // Get file metadata
    const stat = await ctx.fs9.stat("/reports/summary.txt");
    // stat: { path, type: "file", size: 19, mtime: "2026-04-01T...",
    //         generation: ..., mode: 420, sealed: false }

    return { content, fileCount: entries.length, stat };
  }
};

The list() and stat() responses include full metadata for each entry:

Field	Type	Description
`path`	`string`	Full path of the entry
`type`	`string`	`file` or `dir`
`size`	`number`	Size in bytes
`mtime`	`string`	Last modified time (ISO 8601)
`generation`	`number`	Version generation counter
`mode`	`number`	Unix file mode
`sealed`	`boolean`	Whether the file is sealed (immutable)

Reading binary files

For binary files (images, xlsx, zip), use readBase64:

const base64 = await ctx.fs9.readBase64("/data/image.png");
const buffer = Buffer.from(base64, "base64");

Bundling and Dependencies

The default deployment mode is a single file. If your function needs npm packages or multiple source files, bundle them with esbuild before deploying.

Why bundle?

The runtime does not have access to node_modules
require() of external packages fails at runtime
Multi-file import ./utils is not supported

Bundling with esbuild

1. Initialize a project:

mkdir my-function && cd my-function
npm init -y
npm install --save-dev esbuild

2. Install dependencies you need:

npm install xlsx csv-stringify

3. Write your function:

const XLSX = require("xlsx");

module.exports = {
  handler: async (input, ctx) => {
    const base64 = await ctx.fs9.readBase64(input.path);
    const workbook = XLSX.read(Buffer.from(base64, "base64"), { type: "buffer" });
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const csv = XLSX.utils.sheet_to_csv(sheet);
    const outPath = "/output/" + input.path.split("/").pop().replace(/\.xlsx?$/, ".csv");
    await ctx.fs9.write(outPath, csv);
    return { output: outPath, rows: csv.split("\n").length };
  }
};

4. Add a build script to package.json:

{
  "scripts": {
    "build": "esbuild src/index.js --bundle --platform=node --target=es2020 --outfile=dist/index.js"
  }
}

5. Build and deploy:

npm run build
cat dist/index.js | db9 functions create xlsx-to-csv --db myapp -f - \
  --fs9-scope /data:ro --fs9-scope /output:rw

The function reads under /data (read-only) and writes the CSV to /output, which must exist. Upload an input file and create the output directory before the first invoke:

db9 fs cp ./report.xlsx myapp:/data/report.xlsx
db9 db sql myapp -q "SELECT fs9_mkdir('/output', true)"
db9 functions invoke xlsx-to-csv --db myapp --payload '{"path":"/data/report.xlsx"}'

Bundle size limits

Keep bundles under 5 MB for reliable deployments. Use esbuild’s tree-shaking and --external flag to minimize size.

Next Steps

Overview — Quick start and deployment basics
Configuration — Secrets, fs9 scope, network, cron, limits
Examples — Real-world patterns