oc/plugins/sqlite/README.md

# sqlite

An OpenCode V2 plugin that adds a small set of `sqlite_*` tools so a model
can read and write SQLite database files from a session.

It is intentionally **not** a replacement for the existing app. It registers
extra, opt-in tools under the `sqlite` namespace, leaving every other part of
OpenCode unchanged.

- OpenCode V2 plugin docs: <https://opencode.ai/v2/docs/build/plugins/>
- OpenCode plugin config: <https://opencode.ai/v2/docs/plugins>
- better-sqlite3 (sync API, used under the hood): <https://github.com/WiseLibs/better-sqlite3>

## What you get

Eight tools, all under the `sqlite` namespace (effective names shown):

| Tool | Purpose |
| ---- | ------- |
| `sqlite_open` | Open or create a database, bind it to a short alias. |
| `sqlite_close` | Close a previously opened connection. |
| `sqlite_list` | List all currently open connections and their modes. |
| `sqlite_query` | Run a SQL query (SELECT) and return rows. |
| `sqlite_exec` | Run a non-SELECT statement (INSERT/UPDATE/DELETE/DDL). |
| `sqlite_script` | Run a multi-statement SQL script in one transaction. |
| `sqlite_tables` | List user-visible tables (and optionally views). |
| `sqlite_schema` | Show columns and the original CREATE statement. |

Every tool except `list` is addressed by the `alias` returned from
`sqlite_open`, so a single session can keep several databases live at once.

## Install

The plugin source is a single TypeScript file at
`~/.config/opencode/plugins/sqlite/index.ts`. It has **two npm dependencies**
that must be installed into the OpenCode config's own `node_modules` (not
the project you are running OpenCode in). The OpenCode service loads them
from `~/.config/opencode/node_modules` regardless of the project directory.

The host redirects module resolution to that location, so if you copy this
plugin to another machine you must run the install steps there too.

### 1. Match `@opencode/plugin` to your OpenCode version

The package name and the API shape change between releases. The docs'
import specifiers are correct, but only at the matching version.

```sh
opencode --version                              # e.g. v2.0.24
cd ~/.config/opencode
npm i @opencode/plugin@$(opencode --version | sed 's/^v//')
```

A stale `@opencode-ai/plugin` (V1-era name) sitting in `node_modules` is a
leftover user-side dependency and does **not** reflect what the host
provides. Ignore its export list.

### 2. Install the runtime dependency (`better-sqlite3`)

`better-sqlite3` ships native bindings; the install step runs the right
prebuilt binary for your Node/electron ABI on most platforms.

```sh
cd ~/.config/opencode
npm i better-sqlite3
```

If the prebuilt binary does not match your platform, the install will print
`node-gyp` output. On Linux you may need `build-essential`, `python3`, and
`make`:

```sh
sudo apt-get install -y build-essential python3 make
```

If you need to pin a specific version, the same version OpenCode's bundled
Node loads is usually the safest choice. The plugin was developed against
`better-sqlite3@13.0.3`; anything `>= 11.0.0` exposes the same surface.

### 3. Restart the OpenCode service

The background service caches module resolution, so a freshly installed
package is invisible until the service restarts.

```sh
opencode service restart
opencode plugin list               # expect a row for "sqlite" with source = local
```

If `opencode plugin list` does not show the plugin, check
`~/.local/share/opencode/log/opencode.log` and filter on the plugin
directory name. The two messages you are most likely to hit are:

- `Cannot find package '@opencode/plugin' …` — step 1 was skipped.
- `Cannot find package 'better-sqlite3' …` — step 2 was skipped.

### 4. (Optional) Configure the plugin

In `~/.config/opencode/opencode.jsonc` (or a project-local
`.opencode/opencode.jsonc`), add the plugin. If you are using a relative
path, **remember the path resolves against the config file, not the working
directory**.

```jsonc
{
  "$schema": "https://opencode.ai/config.json",
  "plugins": [
    "./plugins/sqlite"
  ]
}
```

You can also pass options here:

```jsonc
{
  "plugins": [
    {
      "package": "./plugins/sqlite",
      "options": {
        "maxRows": 5000,
        "maxBytes": 10485760,
        "defaultMode": "create",
        "allowedRoots": ["/home/me/data", "/tmp"]
      }
    }
  ]
}
```

| Option | Default | Meaning |
| ------ | ------- | ------- |
| `maxRows` | `1000` | Hard row cap on `sqlite_query`. Caller can ask for less but not more. |
| `maxBytes` | `5242880` (5 MiB) | Hard byte cap on the JSON-serialised result of `sqlite_query`. |
| `defaultMode` | `"create"` | Open mode when `sqlite_open` is called without `mode`. |
| `allowedRoots` | `[]` (no restriction) | Absolute or cwd-relative roots; paths outside are rejected. |

If you copied this plugin to a different location, replace the path in the
example above with wherever you put it. The plugin files are pure
TypeScript — they are loaded by OpenCode's own transformer, so no separate
build step is needed.

## Usage

A typical session goes: open → inspect → query → modify → close.

```text
> open ./data/app.db as alias "app"
> tables on "app"
> schema of users on "app"
> query "SELECT id, email FROM users WHERE created_at > ?"
   on "app" with params ["2026-01-01"]
> exec "UPDATE users SET email = ? WHERE id = ?"
   on "app" with params {"email": "new@example.com", "id": 42}
> script "BEGIN; DELETE FROM sessions WHERE expired < strftime('%s','now');
          INSERT INTO audit(message) VALUES ('cleanup'); COMMIT;"
   on "app"
> close "app"
```

### Modes for `sqlite_open`

| Mode | Behaviour |
| ---- | --------- |
| `create` (default) | Open if exists, otherwise create. Read-write. |
| `rw` | Open read-write; fail if the file does not exist (`fileMustExist: true`). |
| `ro` | Open read-only. `sqlite_exec` and `sqlite_script` reject writes; `sqlite_query` works. |
| `memory` | In-memory database regardless of `path`. |

Relative `path` values resolve against OpenCode's current working directory
for the session (`ctx.location.directory`). Absolute paths are taken as-is.
Use `:memory:` to skip filesystem entirely.

### Parameters

`sqlite_query` and `sqlite_exec` accept a `params` field that is either:

- An **array** of values for `?` placeholders (in order), or
- An **object** of `{ name: value, … }` for `:name` / `@name` / `$name`
  placeholders. The name is given **without** the leading sigil; better-
  sqlite3 maps any of the three prefix styles in the SQL to the same name.

Examples:

```text
// positional
params: [42, "alice"]

// named (matches ":id", "@id", or "$id" in the SQL)
params: {"id": 42, "name": "alice"}
```

### Reading large results

`sqlite_query` returns rows lazily. When the result is truncated, the
output ends with `(truncated by cap of N rows / M bytes)`. To get the
rest, either:

- Add a tighter `WHERE` clause and re-run, or
- Raise `maxRows` / `maxBytes` in the plugin config, or
- Run several `sqlite_query` calls with `LIMIT` / `OFFSET` in the SQL
  itself.

### Reading BLOBs

BLOB columns are returned as `{"__b64__": "...", "__bytes__": N}`. The
base64 string is the raw bytes; the byte count is provided so the model
can decide whether to read them or skip.

### Errors

Every tool returns a string. Errors are reported on a single line starting
with `Error: ` and include the SQLite error code when available, for
example:

```text
Error: SQLite error SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed: users.email
```

The model should be told (or already knows) that this prefix means "the
operation failed; do not pretend it succeeded."

### Output on the TUI vs the web client

The OpenCode web and desktop clients pass tool output through the
markdown renderer, but the TUI renders tool output as a literal
`<text>` node (see `packages/tui/.../session/index.tsx` — the
`GenericTool` component). A GFM table in the TUI therefore shows up as a
wall of `|` characters and is hard to scan in the TUI's collapsed
3-line preview.

To make the data legible in both clients, `sqlite_query` with
`format: "table"` (the default) ships the result twice:

1. **First, a plain ASCII table** with space-padded columns. The TUI's
   `<text>` shows this as a real aligned table; web/desktop show the
   same lines too (it is plain text, not markdown).
2. **Then, a fenced GFM table** in markdown form. The web/desktop
   clients render this with a proper grid; the TUI ignores the markdown
   syntax and shows the pipes as-is. Discard visually in the TUI; it is
   still parseable markdown for any downstream tool that reads the
   transcript.

So a `SELECT * FROM users` in the TUI looks like:

```text
SQL: SELECT * FROM users ORDER BY id  params: []
Returned 3 row(s). Bytes used: 96.

id  name     age
--  -------  ---
1   Alice    30
2   Bob      25
3   Charlie  35

| id | name | age |
| --- | --- | --- |
| 1 | Alice | 30 |
| ...
```

The same call in the web client draws the second block as a bordered
grid and the first block as preformatted text.

**TUI visibility tip:** the TUI hides generic tool output by default
(`generic_tool_output_visibility` is `false`) and ships with no
keybinding for the toggle. The fastest way to turn it on:

1. Press `Ctrl+P` to open the command palette, type `generic`, and pick
   *Show generic tool output*. (Toggle the same command to hide it
   again.)
2. Or bind a key in `~/.config/opencode/tui.json` (global) or
   `.opencode/tui.json` (project-local). The TUI's keybind id is
   `session_toggle_generic_tool_output`:

   ```jsonc
   {
     "keybinds": {
       "session_toggle_generic_tool_output": "ctrl+t"
     }
   }
   ```

Without either, the tool calls show as collapsed cards with only the
input and a status icon — no output, no table, nothing to scroll. The
state is per-session; toggling once turns it on for the rest of the
session, and KV persists it across restarts.

## File layout

This plugin is a single directory:

```text
~/.config/opencode/plugins/sqlite/
├── index.ts      # the plugin
├── README.md     # this file
└── SETUP.md      # the install steps in copy-paste form
```

To move it to another machine, copy the whole directory, then run the
steps in `SETUP.md` (or the "Install" section above) on the new machine.
Nothing in the plugin hard-codes the absolute path of the OpenCode config.

## Troubleshooting

| Symptom | Likely cause | Fix |
| ------- | ------------ | --- |
| `opencode plugin list` does not show `sqlite` | Plugin directory is not in `plugins`; or `.git` was created inside `plugins/sqlite` (that turns the directory into a *different* plugin shape and OpenCode fails to load it). | Add `./plugins/sqlite` to `plugins` in `opencode.jsonc`; remove any `.git` inside the plugin dir. |
| `failed to load plugin … Cannot find package '@opencode/plugin'` | Step 1 of the install was skipped. | `cd ~/.config/opencode && npm i @opencode/plugin@<version>`. |
| `failed to load plugin … Cannot find package 'better-sqlite3'` | Step 2 was skipped, or `npm i` was run in the wrong directory. | `cd ~/.config/opencode && npm i better-sqlite3`. |
| Plugin loads but `sqlite_open` reports `Cannot find module 'better-sqlite3'` at runtime | Same as above, but only surfaces when the tool actually runs. | Re-run `npm i better-sqlite3` inside `~/.config/opencode`. |
| `node-gyp` build fails during install | Missing C toolchain. | `sudo apt-get install -y build-essential python3 make` (Debian/Ubuntu), or the equivalent for your distro, then `npm i better-sqlite3` again. |
| `SQLITE_READONLY_DBMOVED` or `attempt to write a readonly database` | Connected in `ro` mode. | Re-open with `mode: "create"` (the default). |
| `sqlite_open` reports `Path … is outside the configured allowedRoots` | The plugin is configured with `allowedRoots` and the path is outside them. | Add the parent directory to `allowedRoots`, or remove the option. |
| `sqlite_exec` reports `SQLite returned SQLITE_SCHEMA …` | A cached prepared statement went stale because the schema changed under it. | Re-run the same call; the plugin retries with a fresh prepare. If it persists, the statement is genuinely incompatible with the current schema. |

For server-side plugin logs, use
`opencode --standalone --print-logs` (the `--print-logs` flag alone prints
client logs; plugin loading shows up in the service log at
`~/.local/share/opencode/log/opencode.log`).

## Notes

- The plugin does not enable or disable any of OpenCode's existing
  providers, models, hooks, or tools. It only adds eight new tools.
- better-sqlite3 is **synchronous**. Open runs in milliseconds; a single
  query on a normal-sized database runs in well under a second. Heavy
  long-running writes are not a great fit — they will block the plugin's
  `execute` until they complete, which is the only model the better-
  sqlite3 API offers.
- All write tools refuse to run on a connection opened in `ro` mode,
  including the implicit-write operations inside `sqlite_script`.
- The plugin keeps connections in memory. They are closed on OpenCode
  shutdown. The cleanup hook also runs on plugin reload, so editing the
  file in place does not leak file descriptors.

## Lifecycle and concurrency

**What happens if I never call `sqlite_close`?**

Data is safe. `better-sqlite3` auto-commits every transaction and the
default `synchronous=FULL` setting fsyncs on commit, so a successful
`sqlite_exec` return means the bytes are on disk. The only cost is a
leaked file descriptor: each open connection holds one fd on the
database file (plus, in WAL mode, one each on the `-wal` and `-shm`
sidecar files). Connections are released when:

- the model calls `sqlite_close`,
- the plugin is reloaded (edit `index.ts`, save),
- the OpenCode service is restarted.

For a one-off task it is fine to skip `sqlite_close` and let the next
service restart clean up. For a long-lived service that opens many
aliases, call `sqlite_close` to keep the fd count predictable.

**`":memory:"` databases** are gone the moment the last handle closes,
even within the same process. The two `sqlite_open(":memory:")` calls
above are *not* the same database.

**Can I call the tools concurrently?**

There are three levels to think about:

1. *Within one OpenCode service process* — Node is single-threaded and
   `better-sqlite3` is a synchronous API, so even if the model fires
   several tool calls in parallel, the service processes them one at a
   time. The database itself never sees a real concurrent call from
   here, so you do not need locks.

2. *Multiple OpenCode CLI / TUI sessions against the same service* —
   the service is a single process, so the same single-threaded rule
   applies.

3. *Multiple independent OpenCode services (or any other process)
   opening the same database file* — this is real concurrency.
   `better-sqlite3` opens the file with `locking_mode=normal` and the
   default `busy_timeout=5000ms`, so the second writer politely waits
   up to 5 seconds for the first writer's transaction to commit. If
   the wait expires, the call returns `SQLITE_BUSY` and the model
   should retry. There is no built-in knob on this plugin to raise
   the timeout; if you need a longer wait, run
   `PRAGMA busy_timeout = 30000;` once after `sqlite_open` (use
   `sqlite_exec`).

4. *Network filesystems (NFS, SMB, sshfs)* — SQLite's locking is
   unreliable on them and `SQLITE_BUSY` is the best case. Copy the
   database to local disk first, or run a real database server.

**Read-only mode (`mode: "ro"`)** opens the file normally and rejects
writes inside the plugin. The journal mode is left at the default
(`delete` for files, `memory` for `:memory:`), so concurrent reads
work as expected.

## What happens if the process dies mid-task

`better-sqlite3` opens with `synchronous=FULL` by default, which means
**every commit is fsynced to disk before the `sqlite_exec` call
returns**. Anything the model has already written is on the disk and
will be there on the next open, no matter how the process dies.

| Event | Already-committed data | Connections |
| ----- | ---------------------- | ----------- |
| Close the TUI / CLI window | safe | unchanged — the service is still running, the handle is still open |
| `opencode service stop` (SIGTERM) | safe | unclosed. Node exits without a `db.close()` call unless the host installs a handler. The OS reclaims the fds. |
| `kill -9` the service | safe | unclosed. The kernel reclaims the fds immediately. |
| Power loss / kernel panic | safe (last fsynced commit) | irrelevant — the process is gone. |
| `sqlite_open(":memory:")` and *anything* that ends the process | **lost** — `:memory:` lives only in process RAM | n/a |

Verified empirically: `INSERT … ; setInterval; kill -9` left the
three rows intact on a fresh re-open. Same result for `kill -TERM`
with and without a SIGTERM handler that calls `db.close()`.

**The only ways to lose data are:**

1. Using `":memory:"`. There is no on-disk copy.
2. Turning `synchronous` off with `PRAGMA synchronous = OFF;` — the
   commit returns before the bytes hit the disk, so a crash after the
   return but before the kernel writes can lose that statement. This
   plugin does not do this for you, but `sqlite_exec` will run it if
   asked.
3. Running on a network filesystem (NFS, SMB, sshfs). Both SQLite's
   locking and POSIX fsync semantics are unreliable there.

**About the "leak fd" worry from the previous section:** that only
matters while the service is alive. As soon as the process exits, the
OS reclaims every fd it owned, including the database file and any
journal sidecar. A crash or hard kill is *less* leaky than a graceful
shutdown that the plugin forgets to clean up.