# SQLite Explorer for Obsidian A desktop-only Obsidian plugin for browsing SQLite databases stored in your vault. Open any `.sqlite`, `.sqlite3`, or `.db` file directly in Obsidian and query it with a read-only SQL runner, or embed live query results in your notes using fenced code blocks. Buy Me a Coffee ## Features - Opens SQLite database files from the vault file tree in a dedicated browser view - Table selector, schema inspector, read-only SQL runner, Markdown copy, and CSV export - `sqlite-query` fenced code blocks rendered in reading view with `table`, `list`, and `value` display modes - Static snapshot embedding — convert a live query block into a Markdown table frozen at a point in time - Configurable row limits for previews and query results - Per-database memory of the last selected table ## Installation ### Community Plugins 1. Open `Settings -> Community Plugins`. 2. Search for `SQL Explorer`. 3. Install and enable. ### From vault plugin folder 1. Run `npm install && npm run build` 2. Copy `manifest.json`, `main.js`, and `styles.css` into your vault's `.obsidian/plugins/sqlite-explorer/` folder 3. Enable the plugin in **Settings → Community plugins** ### Development ```bash npm install npm run dev # watch mode — rebuilds on file changes npm run lint # ESLint check npm test # run unit tests ``` ## Database Browser View Open any `.sqlite`, `.sqlite3`, or `.db` file from the vault file tree. Obsidian routes it to the SQLite browser view automatically. **Toolbar controls:** | Control | Description | |---------|-------------| | Table selector | Switch between user tables in the database | | Refresh | Reload the database file from disk | | Show/Hide Schema | Toggle column schema for the selected table (name, type, primary key, nullability, default) | | Show/Hide Query | Toggle the read-only SQL runner | | Copy Markdown | Copy the current result grid as a Markdown table | | Export CSV | Save the current result as a CSV file next to the database in the vault | You can also open a database file via the command palette: **Open current SQLite file in database browser**. ## sqlite-query Blocks Embed a live database query in any note using a fenced `sqlite-query` block. Blocks render in **reading view** only. ### Basic syntax ````markdown ```sqlite-query source: Assets/data.sqlite sql: | SELECT name, population FROM cities ORDER BY population DESC ``` ```` ### All fields | Field | Required | Default | Description | |-------|----------|---------|-------------| | `source` | Yes | — | Path to the database file (see [Path resolution](#path-resolution)) | | `sql` | Yes | — | The SQL query to execute (must be a `SELECT` or `WITH … SELECT`) | | `view` | No | `table` | Display mode: `table`, `list`, or `value` | | `refresh` | No | `manual` | Refresh behaviour — currently only `manual` is supported | ### Path resolution - **Vault-root path**: prefix with `/` — the leading slash is stripped and the path is resolved from the vault root. ``` source: /Assets/databases/inventory.sqlite ``` - **Note-relative path**: no leading slash — resolved relative to the folder containing the note. ``` source: ../data/inventory.sqlite ``` ### View modes **`table`** (default) — renders a standard grid, one row per database row. ````markdown ```sqlite-query source: /data/shop.sqlite view: table sql: | SELECT product, price, stock FROM inventory LIMIT 10 ``` ```` **`list`** — renders each row as a vertical key–value list. Useful when rows have many columns or when column names are meaningful labels. ````markdown ```sqlite-query source: /data/shop.sqlite view: list sql: | SELECT * FROM products WHERE id = 42 ``` ```` **`value`** — renders only the first cell of the first row as plain text. Useful for embedding a single computed value inline. ````markdown ```sqlite-query source: /data/shop.sqlite view: value sql: | SELECT COUNT(*) FROM orders WHERE status = 'pending' ``` ```` ### Refresh and Embed buttons Each rendered block has two action buttons in its header: - **Refresh** — re-runs the query against the current state of the database file on disk. - **Embed** — replaces the live `sqlite-query` block with a static Markdown snapshot. The snapshot includes a timestamp and a frozen copy of the result as a Markdown table, making it safe to share or archive without requiring the database file. ### SQL rules The plugin enforces read-only SQL. A query is accepted only if it: - starts with `SELECT` or `WITH … SELECT` - is a single statement (no semicolons) - contains none of the following keywords outside of quoted identifiers or string literals: `INSERT`, `UPDATE`, `DELETE`, `REPLACE`, `CREATE`, `ALTER`, `DROP`, `ATTACH`, `DETACH`, `PRAGMA`, `VACUUM`, `REINDEX`, `ANALYZE`, `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `RELEASE` Keywords that appear inside quoted identifiers (e.g. `"delete_log"`) or string literals (e.g. `WHERE action = 'drop'`) are permitted. ### Multiple blocks pointing to the same database When a note contains several `sqlite-query` blocks referencing the same database file, all blocks share a single in-memory database session opened on first render. The session is closed automatically once all blocks on the page have finished rendering, so the file is only read from disk once per render cycle. ## Settings Open **Settings → SQLite Plugin** to configure: | Setting | Default | Description | |---------|---------|-------------| | Preview row limit | 100 | Maximum rows shown when browsing a table in the database view | | Max query result rows | 500 | Maximum rows returned by custom queries and `sqlite-query` blocks | The plugin remembers the last selected table for each database file across sessions. ## Technical notes **Why sql.js?** The plugin uses [sql.js](https://github.com/sql-js/sql.js) (SQLite compiled to WebAssembly) rather than native bindings like `better-sqlite3`. This avoids native Node/Electron rebuild requirements inside Obsidian's desktop runtime. Since `0.6.0`, the sql.js WASM binary is embedded into `main.js` at build time. Release packages only need the standard Obsidian Community Plugin assets: `manifest.json`, `main.js`, and `styles.css`. **Read-only by design** The database file is read into memory as a `Uint8Array` and opened as an in-memory SQLite database. No changes are ever written back to disk. Row caps (configurable in settings) limit memory use from large result sets. **Architecture** See [ARCHITECTURE.md](./ARCHITECTURE.md) for module boundaries, data flow, and testing scope.