SQLite Query Cookbook

Self-contained, serverless, zero-configuration in-process database engine widely deployed in application files. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Create In-Memory SQLite Database

Initialize zero-latency temporary in-memory storage.

-- Connect string: :memory:
CREATE TABLE temp_cache (
  key TEXT PRIMARY KEY,
  value TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

#memory#sqlite#init

2. Enable High-Speed WAL Mode

Switch SQLite journal mode to WAL for concurrent read/write performance.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

#wal#pragma#performance

3. Inspect Table Schema Metadata

Retrieve column definitions and data types using PRAGMA.

PRAGMA table_info('users');

#pragma#schema#metadata

4. Create Full-Text Search Table (FTS5)

Build high-speed text search index over documents.

CREATE VIRTUAL TABLE docs_fts USING fts5(title, content);

-- Insert & query
INSERT INTO docs_fts(title, content) VALUES ('SQL Guide', 'Learn relational database queries.');
SELECT * FROM docs_fts WHERE docs_fts MATCH 'relational OR database';

#fts5#search#virtual

5. SQLite Upsert with ON CONFLICT

Handle primary key collisions with update clauses.

INSERT INTO settings (key, val) VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET val = excluded.val;

#upsert#conflict#sqlite

6. Parse JSON with json_extract()

Extract properties from JSON text strings.

SELECT id, json_extract(data, '$.user.name') AS name
FROM events
WHERE json_extract(data, '$.active') = 1;

#json#extract#sqlite

7. Reclaim Disk Space with VACUUM

Defragment single-file database storage.

VACUUM;

#vacuum#maintenance#size

8. Attach External Database File

Query tables across separate SQLite database files.

ATTACH DATABASE 'archive.db' AS archive;
SELECT * FROM main.orders JOIN archive.old_orders USING(id);

#attach#multi-db#file

9. Format Date & Time Modifiers

Manipulate timestamps using built-in datetime helpers.

SELECT datetime('now', 'start of month', '+1 month', '-1 day');

#datetime#dates#helpers

10. Enable Foreign Key Enforcement

Turn on foreign key constraint checking.

PRAGMA foreign_keys = ON;

#foreign_keys#pragma#integrity

Test your SQLite architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study SQLite Flashcards →