oc-database scripts

This folder contains Bun CLIs used by the oc-database skill to:

• run read-only SQLite queries against an opencode.db file (sqlite_readonly.ts)
• validate SQL strings against a strict “SELECT-only” policy (sql_validate.ts)

Run these scripts directly with Bun (there are no npm scripts wired up for them).

Prerequisites

• Bun installed and available as bun
• For sqlite_readonly.ts: a SQLite database file on disk (defaults to your local opencode.db path)

Installation (for running/tests)

From this folder:

bun install

Notes:

• This folder includes a package.json and TypeScript config for type-checking/tests.

File overview

• sqlite_readonly.ts — read-only query runner (Bun + bun:sqlite)
• sql_validate.ts — SQL policy validator (exported as a reusable module)
• utils.ts — shared helpers (arg parsing, exit codes, output directory safety)

Related tests (useful for behavior reference):

• sql_validate.test.ts
• sqlite_readonly.test.ts
• utils.test.ts

sqlite_readonly.ts

Executes a validated SQL query against a SQLite database using Bun’s native SQLite driver.

What it does

• validates the SQL with validateSql() from sql_validate.ts
• opens the database with readonly: true
• sets PRAGMA query_only = ON before running the query
• formats results and writes them:
  • to stdout
  • and to a file under /tmp/oc-database/

Usage

bun sqlite_readonly.ts [OPTIONS] "<SQL>"

Options:

• -h, --help — show help
• -q, --quiet — suppress informational lines (DB:, Out:, Results saved to:)
• -d, --db <path> — database file path
  • default: ($XDG_DATA_HOME || $HOME/.local/share)/opencode/opencode.db
• -o, --out <slug> — output filename slug written under /tmp/oc-database/<slug>
  • allowed characters: A–Z a–z 0–9 . _ -
  • path traversal (.., ., .. segments) is rejected

Output behavior

• Output directory is always /tmp/oc-database/.
• If --out is not provided, the script generates a timestamp-based filename like:
  • YYYYMMDDTHHmmssSSS_result.txt
• Output is always formatted as aligned columns with headers and separator lines
• When the query returns zero rows, an empty string is emitted

Safety constraints

In addition to SQLite’s readonly open mode:

• the DB path MUST exist and MUST be a regular file
• the DB path MUST NOT be a symlink
• SQL MUST pass the sql_validate.ts policy (see below)
• the output filename MUST be a safe slug (no directories)

Examples

Print help:

bun sqlite_readonly.ts --help

End-to-end example using a temporary database:

# 1) Create a small example database
bun -e "import { Database } from 'bun:sqlite'; const db = new Database('/tmp/oc-db-readme.db', { create: true }); db.run('CREATE TABLE project (id INTEGER PRIMARY KEY, name TEXT)'); db.run(\"INSERT INTO project (name) VALUES ('alpha'), ('beta')\"); db.close();"

# 2) Run a safe SELECT query (quiet mode = machine-friendly output)
bun sqlite_readonly.ts -q --db /tmp/oc-db-readme.db "SELECT id, name FROM project"

# 3) Write results to a chosen file under /tmp/oc-database/
bun sqlite_readonly.ts -q --db /tmp/oc-db-readme.db --out projects.txt "SELECT id, name FROM project"

sql_validate.ts

Exports validateSql(sql: string) which enforces a strict “read-only SELECT” policy.

import { validateSql } from "./sql_validate.js";

const result = validateSql("SELECT id, name FROM project");
if (!result.valid) throw new Error(result.error);

Validation rules (enforced)

The validator:

• rejects embedded LF/CR (\n or \r)
• rejects semicolons (;) to prevent multiple statements
• rejects SQLite dot-commands (queries starting with .)
• only allows queries starting with:
  • SELECT ..., or
  • WITH ... SELECT ... (CTE must be followed by SELECT)
• rejects DML/DDL keywords inside a WITH ... query string:
  • DELETE, UPDATE, INSERT, REPLACE, CREATE, DROP, ALTER, ATTACH, DETACH, VACUUM, ANALYZE, REINDEX, BEGIN, COMMIT, ROLLBACK
• rejects references to forbidden identifiers (case-insensitive after normalization):
  • ACCESS_TOKEN, REFRESH_TOKEN, SECRET
• rejects wildcard selection:
  • SELECT * and , *
  • table.*
  • (aggregate wildcards like COUNT(*) are allowed; arithmetic * is allowed)
• only allows SQL comment markers at the very start of the query:
  • leading /* ... */ blocks are stripped
  • a leading -- ... line comment is treated as an empty query
  • comment markers appearing after the query starts are rejected

Notes:

• validateSql("") returns { valid: true } (see sql_validate.test.ts).
• A “valid” result only means the string passes policy checks; it does not guarantee the SQL will execute successfully.

Shared helpers (utils.ts)

These helpers are imported by scripts in this folder.

• parseArgs(argv)
  • parses common flags: -h/--help, -q/--quiet, -r/--recursive, -d/--db, -o/--out, and -- separator
  • never calls process.exit() (unknown flags are collected in unknown[])
• info(message, quiet) — prints # <message> unless quiet
• die(message) — prints ERROR: <message> and exits with EXIT_USAGE (2)
• requirePositionalArg(args, usageLine, argName) — enforces “no unknown flags” and “at least one positional”
• passFailStatus(pass) — returns { status: "PASS"|"FAIL", exitCode: 0|1 }
• ensureOutputDir(dir) — creates directory recursively; throws if the target path is a symlink
• createTempFile(prefix) — creates a unique empty temp file under /tmp/ and schedules cleanup on process exit

Exit code constants:

• EXIT_OK = 0
• EXIT_FAIL = 1
• EXIT_USAGE = 2

Troubleshooting

• sqlite_readonly.ts prints ERROR: ... and exits 1 on validation, file, query, or write failures.
• If your SQL is rejected, run it through validateSql() (or see sql_validate.test.ts for accepted/rejected examples).