sqlite

name	sqlite
description	Embedded SQLite — sqlite3 CLI, pragmas, JSON1, WAL, migrations; pairs with cursor-mirror; server DBs use postgres.
license	MIT
tier	1
allowed-tools	["read","grep","glob","run_terminal_cmd"]
related	["postgres","schema","cursor-mirror","datasette","robust-first"]
tags	["moollm","sqlite","embedded","json1","wal","migrations","datasette"]

SQLite

Part of MOOLLM · 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

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:

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 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/

• MOOLLM: repo · Skill index and docs: skills/README