# pi-db

A unified database extension for the [pi](https://github.com/earendil-works/pi-coding-agent) coding agent. One `db_*` toolset covers every configured database — pick a target with the `connection` parameter. Ships with two backends:

- **SQL Server 2008** — zero-dependency `sqlcmd` CLI backend (Windows integrated auth, GBK-safe decoding)
- **MySQL 8** — `mysql2` connection pool backend

More dialects (PostgreSQL, Oracle, ...) plug into the same tool surface without touching the tools.

## Features

- Unified `db_*` tools: query, execute, list databases/tables, describe tables, connection status
- One config file, one registry; adding a database is a config entry, adding a dialect is one interface
- Lazy pools (MySQL) and stateless per-call spawns (SQL Server)
- Safety gate: DML runs directly, DDL pops a confirmation dialog, and DDL is always blocked in headless modes (print/json/rpc)
- Secrets support `${ENV_VAR}` references — no plaintext passwords required in the config
- `/db` command for a one-shot health check of every connection

## Install

```bash
pi install npm:@fanchaozz/pi-db
```

Then create the config file at `~/.pi/agent/pi-db-config.json` (see below) and run `/reload` in pi. The extension auto-discovers from `~/.pi/agent/extensions/` — see [pi extensions](https://github.com/earendil-works/pi-coding-agent/blob/main/docs/extensions.md) for global vs project-local placement.

> **SQL Server note:** the sqlserver dialect shells out to `sqlcmd`, so the SQL Server Client Tools must be on `PATH`. The mysql dialect has no native requirements.

## Configure

Create `~/.pi/agent/pi-db-config.json`:

```jsonc
{
  "connections": {
    "sqlserver08": {
      "dialect": "sqlserver",
      "server": "localhost",          // default: localhost
      "database": "master",           // default database
      "auth": "integrated"            // Windows integrated auth (-E)
      // or SQL auth: "auth": { "username": "sa", "password": "${SQLSERVER_PASSWORD}" }
    },
    "mysql8": {
      "dialect": "mysql",
      "host": "127.0.0.1",
      "port": 3306,
      "user": "root",
      "password": "${MYSQL8_PASSWORD}",  // plain passwords also work, env refs are safer
      "database": null                   // default schema; null = pass `database` per call
    }
  }
}
```

Edit the file and `/reload` to apply. Connection names are arbitrary — they become the values of the `connection` tool parameter.

## Tools

| Tool | Purpose |
|------|---------|
| `db_list_connections` | List configured connections (no secrets shown) |
| `db_query` | SELECT queries, JSON structured results |
| `db_execute` | DML (direct) or DDL (confirmed) |
| `db_list_databases` | Online databases on a connection |
| `db_list_tables` | Tables/views with row estimates and comments |
| `db_describe_table` | Column layout: type, nullability, defaults, keys, comments |
| `db_status` | Health check + server version |

Common parameters: `connection` (name), `database` (optional, cross-schema work), `sql` / `table`.

Command: `/db` — health-check every connection in one notification.

> The MySQL backend returns up to 500 rows per `db_query` and warns when truncated — add `WHERE`/`LIMIT` for large scans.

## Security model

- `db_execute` DML (`INSERT`/`UPDATE`/`DELETE`) executes immediately.
- DDL (`CREATE`/`ALTER`/`DROP`/`TRUNCATE`/`RENAME`/`GRANT`/`REVOKE`/`DENY`, judged on the first statement) opens a confirmation dialog.
- Headless modes (print/json/rpc) block DDL outright.
- Recommend `${ENV_VAR}` for passwords; the config file lives outside the extension directory.

## Extending with a new dialect

1. Implement the `DbDialect` interface in `src/<dialect>.ts` (`src/base.ts` is the contract).
2. Register a factory in `src/config.ts`.
3. Add a config entry, `/reload`. Zero tool changes.

Known SQL Server backend boundary: `sqlcmd` text output is CSV-parsed, so field values containing commas/newlines can misalign columns — normalize such fields with `CONVERT(varchar, ...)` in the query when it matters.

## License

MIT