Examples

The examples read and write tables that you create as admin, while functions run SQL as the authenticated role — and scheduled ones as service (see Execution Role). To run them as written, create their tables and grant both roles access first:

SQL

CREATE TABLE webhook_events (id SERIAL PRIMARY KEY, event_type TEXT NOT NULL, payload JSONB,
                             source_ip TEXT, received_at TIMESTAMPTZ);
CREATE TABLE sessions (id SERIAL PRIMARY KEY, expires_at TIMESTAMPTZ NOT NULL);
CREATE TABLE audit_logs (id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE events (id SERIAL PRIMARY KEY, user_id TEXT, type TEXT,
                     created_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE documents (id SERIAL PRIMARY KEY, content TEXT, summary TEXT, summarized_at TIMESTAMPTZ);

GRANT ALL ON ALL TABLES IN SCHEMA public TO authenticated, service;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO authenticated, service;

ctx.fs9.write() does not create directories, so the examples that write files also create their output directory once with fs9_mkdir.

Data Transformation: XLSX to CSV

Convert an uploaded spreadsheet to CSV using the xlsx package. This example requires bundling with esbuild.

src/index.js

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 results = [];
    for (const sheetName of workbook.SheetNames) {
      const csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
      const outPath = `/output/${sheetName}.csv`;
      await ctx.fs9.write(outPath, csv);
      results.push({ sheet: sheetName, path: outPath, rows: csv.split("\n").length });
    }

    return { sheets: results };
  }
};

Terminal

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

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"}'

See c4pt0r/db9-function-xls-csv-tbl for a complete working example.

Webhook Handler

Accept external webhook payloads and store them in a table.

webhook.js

module.exports = {
  handler: async (input, ctx) => {
    if (!input?.event || !input?.data) {
      throw new Error("Missing event or data field");
    }

    await ctx.db.query(
      `INSERT INTO webhook_events (event_type, payload, source_ip, received_at)
       VALUES ($1, $2, $3, NOW())`,
      [input.event, JSON.stringify(input.data), input.source || "unknown"]
    );

    const count = await ctx.db.query(
      "SELECT count(*) as total FROM webhook_events WHERE event_type = $1",
      [input.event]
    );

    return {
      accepted: true,
      eventType: input.event,
      totalEvents: count.rows[0][0]
    };
  }
};

Terminal

db9 functions create webhook-handler --db myapp -f webhook.js

Invoke it from your own backend through the REST API. The request is authenticated with your DB9 API key and the payload goes inside "input", so a third-party service cannot post its webhook to this URL directly — receive it in your backend and forward it (source_ip is whatever you pass as input.source):

Terminal

curl -X POST \
  -H "Authorization: Bearer $DB9_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"input": {"event": "user.created", "data": {"userId": 42, "email": "alice@example.com"}}}' \
  https://api.db9.ai/customer/databases/{db_id}/functions/{fn_id}/invoke

Scheduled Data Cleanup

Clean up stale data on a schedule with a cron-scheduled function.

cleanup.js

module.exports = {
  handler: async (input, ctx) => {
    const sessions = await ctx.db.query(
      "DELETE FROM sessions WHERE expires_at < NOW() RETURNING id"
    );

    const logs = await ctx.db.query(
      "DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '90 days' RETURNING id"
    );

    // Archive old events to fs9
    const oldEvents = await ctx.db.query(
      `SELECT * FROM events WHERE created_at < NOW() - INTERVAL '30 days'`
    );

    if (oldEvents.row_count > 0) {
      const csv = oldEvents.rows.map(row => row.join(",")).join("\n");
      // write() replaces an existing file, so name the archive per run, not per day
      const archivePath = `/archives/events-${new Date().toISOString().slice(0, 10)}-${ctx.self.runId}.csv`;
      await ctx.fs9.write(archivePath, csv);

      await ctx.db.query(
        "DELETE FROM events WHERE created_at < NOW() - INTERVAL '30 days'"
      );
    }

    return {
      sessionsDeleted: sessions.row_count,
      logsDeleted: logs.row_count,
      eventsArchived: oldEvents.row_count
    };
  }
};

Terminal

db9 db sql myapp -q "SELECT fs9_mkdir('/archives', true)"
db9 functions create cleanup --db myapp -f cleanup.js \
  --fs9-scope /archives:rw --timeout 120000

Run it once with db9 functions invoke cleanup --db myapp to check it — after the next step it can no longer be invoked directly. The CLI has no schedule flag; schedule it through the REST API with run_as: "service" and a cron_schedule (here daily at 03:00 UTC), resending the source as bundle_ref:

Terminal

jq -n --rawfile src cleanup.js \
  '{bundle_ref: ("inline:" + $src), run_as: "service", cron_schedule: "0 3 * * *"}' |
curl -X PUT \
  -H "Authorization: Bearer $DB9_API_KEY" \
  -H "Content-Type: application/json" \
  -d @- \
  https://api.db9.ai/customer/databases/{db_id}/functions/{fn_id}

A schedule cannot be removed once set; see Cron Scheduling.

Analytics API Endpoint

Build an analytics endpoint that aggregates data and returns formatted results.

analytics.js

module.exports = {
  handler: async (input, ctx) => {
    const days = input?.days || 7;

    const [activity, topUsers, summary] = await Promise.all([
      ctx.db.query(`
        SELECT date_trunc('day', created_at) as day, count(*) as events
        FROM events
        WHERE created_at >= NOW() - $1 * INTERVAL '1 day'
        GROUP BY 1 ORDER BY 1
      `, [days]),

      ctx.db.query(`
        SELECT user_id, count(*) as actions
        FROM events
        WHERE created_at >= NOW() - $1 * INTERVAL '1 day'
        GROUP BY 1 ORDER BY 2 DESC LIMIT 10
      `, [days]),

      ctx.db.query(`
        SELECT
          count(*) as total_events,
          count(DISTINCT user_id) as unique_users,
          min(created_at) as earliest,
          max(created_at) as latest
        FROM events
        WHERE created_at >= NOW() - $1 * INTERVAL '1 day'
      `, [days])
    ]);

    return {
      period: `${days} days`,
      summary: {
        totalEvents: summary.rows[0]?.[0],
        uniqueUsers: summary.rows[0]?.[1],
      },
      dailyActivity: activity.rows.map(([day, count]) => ({ day, count })),
      topUsers: topUsers.rows.map(([userId, actions]) => ({ userId, actions })),
    };
  }
};

Terminal

db9 functions create analytics --db myapp -f analytics.js
db9 functions invoke analytics --db myapp --payload '{"days":30}'

External API Integration with Secrets

Call an external API using secrets for authentication.

summarize.js

module.exports = {
  handler: async (input, ctx) => {
    const apiKey = ctx.secrets.get("OPENAI_KEY");

    const doc = await ctx.db.query(
      "SELECT content FROM documents WHERE id = $1", [input.documentId]
    );

    if (doc.row_count === 0) throw new Error("Document not found");

    const response = await fetch("https://api.openai.com/v1/chat/completions", {
      method: "POST",
      headers: {
        "Authorization": `Bearer ${apiKey}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        model: "gpt-4o-mini",
        messages: [
          { role: "system", content: "Summarize the following document in 3 sentences." },
          { role: "user", content: doc.rows[0][0] }
        ]
      }),
    });

    if (!response.ok) {
      throw new Error(`OpenAI ${response.status}: ${await response.text()}`);
    }
    const result = await response.json();
    const summary = result.choices[0].message.content;

    await ctx.db.query(
      "UPDATE documents SET summary = $1, summarized_at = NOW() WHERE id = $2",
      [summary, input.documentId]
    );

    return { documentId: input.documentId, summary };
  }
};

Terminal

db9 functions secrets set OPENAI_KEY --db myapp     # prompts for the value
db9 functions create summarize --db myapp -f summarize.js \
  --secret OPENAI_KEY=OPENAI_KEY

secrets set prompts for the value only in a terminal; in a script it fails with No secret value provided. Pipe the value in instead: printf '%s' "$OPENAI_KEY" | db9 functions secrets set OPENAI_KEY --db myapp --value-stdin.

Note

This example requires https://api.openai.com in the function’s network_allowlist; until then fetch() fails with url not in allowlist. The CLI cannot set it — send "network_allowlist": ["https://api.openai.com"] with PUT /customer/databases/{db_id}/functions/{fn_id}, which also needs the source as bundle_ref (see REST API — Functions).

Next Steps

• Overview — Quick start and deployment basics
• Runtime & ctx — ctx.db, ctx.fs9, ctx.self API
• Configuration — Secrets, fs9 scope, network, cron, limits
• Troubleshooting — Errors, logs, debugging