sqlite

# SQLite Part of MOOLLM · [skills/sqlite/](https://github.com/SimHacker/moollm/tree/main/skills/sqlite) **SQLite** is an **embedded** SQL engine: library + file (or `:memory:`). No separate server process. **MOOLLM** uses the **`sqlite`** mechanism in schemapedia for the **engine**; the **`sql`** mechanism names the **SQL language** family shared with Postgres and others. This skill is the **wrapper** around the **SQLite ecosystem**: the **`sqlite3`** CLI, **C API** and language bindings, **JSON1**, **WAL**, migration runners, and **file format** documentation—not a fork of SQLite. For **read-only web exploration and JSON API** over `.db` files, use the **`datasette`** skill and schemapedia **`datasette`** mechanism (including the **cursor-mirror** export path). ## When to use - **Local persistence** for tools, agents, tests, and single-user apps. - **Cursor** session stores (see **cursor-mirror**) — SQLite under the hood. - **Prototyping** a schema before committing to **PostgreSQL** for multi-user production. Use **`postgres`** when you need **server-side** roles, **connection pooling**, **Timescale**, **pgvector**, or **HA** patterns. ## CLI: sqlite3 ```bash sqlite3 ./app.db ``` Useful interactive commands: - `.tables` — list tables - `.schema [table]` — show DDL - `.headers on` / `.mode column` — readable output - `.dump` — SQL text export **Exit: `CTRL+D` or `.quit`** ## Catalog: sqlite_master Metadata lives in **`sqlite_master`** (and internal schema tables). Inspect: ```sql SELECT type, name, tbl_name, sql FROM sqlite_master ORDER BY type, name; ``` ## Pragmas that matter - **`journal_mode`** — **`WAL`** is often right for **read-heavy** concurrent access; understand **locking** (writers still serialize). - **`foreign_keys`** — `PRAGMA foreign_keys = ON;` for **referential** checks (must be enabled per connection in many drivers). - **`synchronous`** — durability vs speed tradeoff; **never** weaken without understanding **data loss** risk. - **`user_version`** — integer **you** own for **migration** versioning. Official pragma reference: https://www.sqlite.org/pragma.html ## JSON1 **JSON1** adds SQL functions for JSON in columns — `json_extract`, `json_each`, etc. https://www.sqlite.org/json1.html ## Types: affinity and STRICT SQLite uses **dynamic typing** with **type affinity**. **STRICT** tables (optional) reject invalid types on insert. Read: https://www.sqlite.org/stricttables.html ## Migrations - **Ordered SQL files** applied by a runner, or **ORM** migrations (Drizzle, Prisma, Alembic, …). - Track **`user_version`** or use a **schema_migrations** table — pick one convention per project. - **sqldiff** (when installed) can compare databases — https://www.sqlite.org/sqldiff.html ## Full-text search (FTS5) **FTS5** is SQLite’s **full-text** engine for **search** indexes — separate virtual tables from ordinary rows. https://www.sqlite.org/fts5.html ## Interop with PostgreSQL | Concern | SQLite | PostgreSQL | |---------|--------|------------| | Concurrency | Single writer; WAL helps readers | MVCC; server tuning | | Extensions | JSON1, FTS5 built-in | Timescale, pgvector, … | | Deploy | Single file | Host + roles + backups | **Porting** often requires **type** and **locking** semantics review — not a mechanical rename. ## Datasette (HTTP layer on the same `.db` files) **[Datasette](../datasette/SKILL.md)** publishes SQLite files as a **read-only** web UI and **JSON API** using the **`datasette`** CLI, optional **metadata YAML**, and **`--crossdb`** for multiple databases. It does not replace **`sqlite3`** for DDL or bulk writes. MOOLLM wires **cursor-mirror** to Datasette via **`export_datasette`** and **`reference/universal/datasette-metadata.yml`** — see the **datasette** skill for the full integration protocol. ## cursor-mirror **Cursor** stores **SQLite** databases locally. The **cursor-mirror** skill documents **table shapes** and **YAML** models — see **`schemas/mechanisms/cursor-mirror`** in the registry. ## Related skills - **`postgres`** — server **Postgres** operations and extensions. - **`schema`** — schemapedia **`sqlite`** mechanism profile. - **`cursor-mirror`** — introspection over **Cursor** SQLite data. ## Part of MOOLLM **This skill's directory (browse and fetch everything):** [skills/sqlite/](https://github.com/SimHacker/moollm/tree/main/skills/sqlite) - **MOOLLM:** [repo](https://github.com/SimHacker/moollm) · **Skill index and docs:** [skills/README](https://github.com/SimHacker/moollm/blob/main/skills/README.md)