SQLite.

SQLite is a single-file database. Orchid opens any .db, .sqlite, or .sqlite3 file on your machine — no server, no credentials, no network.

Requirements

• A SQLite file on your machine. SQLite 3.30+ recommended (Orchid uses the bundled driver).
• Read permission on the file. Write permission if you want to run mutations.

Connect

1. Open Integrations → + Add connection → SQLite.
2. Click Browse and pick the .db file.
3. Save. The schema appears immediately — no test step required.

No credentials, no host, no port. The file path is the entire configuration.

Optional settings

WAL mode

Write-Ahead Logging (WAL) is the recommended journaling mode for any SQLite file that gets read by more than one process — which includes Orchid + your app. WAL allows readers and writers to operate concurrently. Without WAL, Orchid can throw database is locked while your application is writing.

Switch a file to WAL mode once, from your shell or a one-off cell:

-- Enable WAL on this database (persistent)
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;    -- safe + fast for most workloads
      

WAL creates two sidecar files next to the database: your.db-wal and your.db-shm. Keep them in the same directory; never copy the .dbalone if WAL is active or you'll lose recent writes.

ATTACH DATABASE

SQLite can join multiple files in a single query. Useful when you keep dimension tables in one file and event data in another. Run this inside an Orchid SQL cell:

-- Attach a second SQLite file as 'aux'
ATTACH DATABASE '/Users/me/data/dims.db' AS aux;

-- Query across files
SELECT e.user_id, u.name, count(*) AS events
FROM main.events e
JOIN aux.users u ON u.id = e.user_id
GROUP BY 1, 2
ORDER BY events DESC;

-- Detach when done
DETACH DATABASE aux;
      

Attached databases live for the duration of the cell's connection. Orchid pools connections per integration, so the ATTACH may persist across cells in the same session — defensive DETACH at the end avoids surprises.

FTS5 full-text search

SQLite ships with FTS5, a powerful full-text index. Orchid's bundled SQLite has it compiled in. Create a virtual table once, then query like normal.

-- One-time: build an FTS5 index over an existing table
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, body,
  content='articles',
  content_rowid='id'
);

-- Populate
INSERT INTO articles_fts(rowid, title, body)
SELECT id, title, body FROM articles;

-- Query: ranked search results
SELECT a.id, a.title, snippet(articles_fts, 1, '<b>', '</b>', '…', 12) AS preview
FROM articles_fts
JOIN articles a ON a.id = articles_fts.rowid
WHERE articles_fts MATCH 'orchid AND notebook*'
ORDER BY rank
LIMIT 20;
      

Sharing a SQLite project

If you commit the .db file with your project, anyone who opens the project will have working SQL immediately — no setup. Perfect for sample datasets and tutorials (the bundled Chinook sample uses exactly this).

For larger files (over ~50 MB), keep the .db outside git and document the path. Orchid stores the absolute path in the connection profile.

Common gotchas

• "database is locked" — another process has the file open in write mode. Enable WAL (see above), close the other tool, or wait.
• "file is not a database" — the file isn't SQLite, or it's encrypted (e.g. SQLCipher). Orchid doesn't bundle SQLCipher.
• WAL sidecar files missing — Orchid plays nice with WAL-mode databases, but if your file is in WAL mode, make sure -wal and -shm sidecar files are alongside it. Copying just the .db loses uncommitted writes.
• Loadable extensions not allowed — built-in extensions (FTS5, JSON1, R*Tree, math functions) are available. load_extension() for third-party .dylib / .so / .dll is disabled by design — a notebook shouldn't be a code-loading surface.
• Schema changes don't appear — SQLite schema changes are picked up on the next query. Click the refresh icon in the schema panel if you ran CREATE TABLE outside Orchid.

Example queries

-- List tables
SELECT name FROM sqlite_master WHERE type = 'table';
      

-- Database file size and page stats
SELECT
  page_count * page_size AS bytes,
  page_count,
  page_size
FROM pragma_page_count(), pragma_page_size();
      

Where to go next

For more on writing SQL cells, see SQL cells.