Use a Database in Code Tools

Every code tool has access to a per-user SQLite database with 10 GB of storage. No setup, no provisioning, no migrations to manage. Just import the helper and start querying.

Overview

When a code tool runs, Kyew injects a db.mjs helper into the sandbox. This helper wraps authenticated fetch calls to your per-user SQLite database. The database persists across tool runs, so you can build tools that track data over time.

The Basics

Import the Helper

import { query, exec, batch } from "./db.mjs";

Three functions cover all database operations:

Creating Tables

Use exec for DDL statements. Always use CREATE TABLE IF NOT EXISTS so your tool works on first run and every run after:

await exec(`CREATE TABLE IF NOT EXISTS prices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  symbol TEXT NOT NULL,
  price REAL NOT NULL,
  date TEXT NOT NULL,
  fetched_at TEXT DEFAULT (datetime('now'))
)`);

No migration system needed. If you want to add a column later, use ALTER TABLE ... ADD COLUMN.

Inserting Data

Use query with spread parameters:

await query(
  "INSERT INTO prices (symbol, price, date) VALUES (?, ?, ?)",
  "AAPL", 185.50, "2024-03-14"
);

Querying Data

const result = await query(
  "SELECT * FROM prices WHERE symbol = ? ORDER BY date DESC LIMIT 10",
  "AAPL"
);

// Access rows
const rows = result.rows;
const latest = result.rows[0];
console.log(latest.price); // 185.50

Batch Operations

For multiple statements in a single round-trip, use batch. Note that inside batch, params IS an array:

await batch([
  {
    sql: "INSERT INTO prices (symbol, price, date) VALUES (?, ?, ?)",
    params: ["AAPL", 185.50, "2024-03-14"]
  },
  {
    sql: "INSERT INTO prices (symbol, price, date) VALUES (?, ?, ?)",
    params: ["GOOGL", 142.30, "2024-03-14"]
  },
  {
    sql: "INSERT INTO prices (symbol, price, date) VALUES (?, ?, ?)",
    params: ["MSFT", 425.20, "2024-03-14"]
  }
]);

Named Databases

By default, all code tools share a single database. If you want data isolation between tools, set db_name in the tool's code_config:

tool(action="create",
  name="price_tracker",
  tool_type="code",
  code_config={
    runtime: "javascript",
    db_name: "finance",
    code: "..."
  })

Tools with the same db_name share data. Tools with different db_name values have completely separate databases.

Inspecting Your Databases

List All Databases

tool(action="db", db_action="list")

Show Tables

tool(action="db", db_action="tables")
tool(action="db", db_action="tables", db_name="finance")

Show Table Schema

tool(action="db", db_action="schema")
tool(action="db", db_action="schema", db_name="finance")

Common Pitfalls

Wrong: Array params with query

// WRONG - params must be spread
await query("INSERT INTO t VALUES (?, ?)", [1, 2]);

// RIGHT
await query("INSERT INTO t VALUES (?, ?)", 1, 2);

Wrong: Accessing result directly

// WRONG - query returns { rows, meta }, not an array
const row = (await query("SELECT * FROM t"))[0];

// RIGHT
const row = (await query("SELECT * FROM t")).rows[0];

Wrong: Forgetting IF NOT EXISTS

// WRONG - fails on second run
await exec("CREATE TABLE prices (symbol TEXT, price REAL)");

// RIGHT
await exec("CREATE TABLE IF NOT EXISTS prices (symbol TEXT, price REAL)");

Complete Example: Price Tracker

A tool that fetches stock prices, stores them in SQLite, and returns a summary with trends.

import { query, exec } from "./db.mjs";

export default {
  async fetch(request) {
    const { symbol } = await request.json();

    // Ensure table exists
    await exec(`CREATE TABLE IF NOT EXISTS prices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      symbol TEXT NOT NULL,
      price REAL NOT NULL,
      change_pct REAL,
      date TEXT NOT NULL,
      fetched_at TEXT DEFAULT (datetime('now'))
    )`);

    // Fetch current price (using a connection's auth)
    const authHeader = process.env.KYEW_CONN_MARKET_DATA_AUTH_HEADER;
    const res = await fetch(
      `https://api.marketdata.example.com/v1/quote/${symbol}`,
      { headers: { Authorization: authHeader } }
    );
    const quote = await res.json();

    const today = new Date().toISOString().split("T")[0];

    // Store the price
    await query(
      "INSERT INTO prices (symbol, price, change_pct, date) VALUES (?, ?, ?, ?)",
      symbol, quote.price, quote.change_percent, today
    );

    // Get historical data for trends
    const history = await query(
      "SELECT date, price FROM prices WHERE symbol = ? ORDER BY date DESC LIMIT 30",
      symbol
    );

    // Calculate 7-day and 30-day averages
    const rows = history.rows;
    const avg7 = rows.slice(0, 7).reduce((sum, r) => sum + r.price, 0) / Math.min(rows.length, 7);
    const avg30 = rows.reduce((sum, r) => sum + r.price, 0) / rows.length;

    return Response.json({
      symbol,
      current_price: quote.price,
      change_today: `${quote.change_percent > 0 ? "+" : ""}${quote.change_percent.toFixed(2)}%`,
      avg_7day: avg7.toFixed(2),
      avg_30day: avg30.toFixed(2),
      data_points: rows.length,
      trend: quote.price > avg7 ? "above 7-day average" : "below 7-day average"
    });
  }
};

Next Steps

• Code Tools — Full code tool documentation including security model and sandbox details
• tool Reference — Complete parameter reference including db actions
• Build a Scheduled Report — Combine database storage with scheduled runs