SQLite CLI Dot-Commands, Schema Queries & PRAGMA Performance Reference
Comprehensive syntax reference for .mode csv, .dump, .import, Write-Ahead Logging (WAL mode), in-memory databases, and indexes.
Essential SQLite CLI Dot-Commands
Utility commands executed directly in the sqlite3 terminal prompt.
| Dot-Command | Description | Example Usage |
|---|---|---|
| `.open <db_file>` | Open or create a database file | `.open app.db` |
| `.mode <format>` | Set query output display format (table, csv, json, column) | `.mode markdown` |
| `.headers on` | Display column names at the top of query results | `.headers on` |
| `.schema <table>` | Print the CREATE TABLE DDL statement for a table | `.schema users` |
| `.dump <table>` | Export database or table schema and data as SQL text statements | `.dump > backup.sql` |
| `.import <file> <table>` | Bulk import data from CSV or text file directly into table | `.import customers.csv customers` |
Production PRAGMA Performance Optimization Suite
Execute these PRAGMA directives immediately after opening a database connection to achieve 10x-50x speedups.
| PRAGMA Directive | Optimal Setting | Performance Benchmark Impact |
|---|---|---|
| `PRAGMA journal_mode = WAL;` | `WAL` | Enables Write-Ahead Logging, allowing concurrent readers without blocking writers |
| `PRAGMA synchronous = NORMAL;` | `NORMAL` | Eliminates redundant disk syncs in WAL mode while preserving full durability against app crashes |
| `PRAGMA cache_size = -64000;` | `-64000` (64MB RAM) | Allocates dedicated RAM cache to keep active index B-Trees in memory |
| `PRAGMA foreign_keys = ON;` | `ON` | Mandatory! SQLite disables relational foreign key enforcement by default |
| `PRAGMA temp_store = MEMORY;` | `MEMORY` | Stores temporary tables and sorting buffers in RAM rather than writing to disk |
Interactive Client-Side Tools for this Guide
Test and run these patterns directly inside your browser with zero server uploads:
SQL INSERT to JSON Converter Online
Extract database rows from SQL INSERT statements into clean JSON arrays. Automatically casts data types with zero server uploads.
JSON to JSON Schema Draft-07 Generator
Automatically infer and generate standard JSON Schema definitions from raw API responses and JSON documents with zero server uploads.
Frequently Asked Questions
Why is WAL mode superior to standard rollback journals in SQLite?
In standard rollback journal mode, writing to the database locks the entire file, preventing all other connections from reading. In WAL mode, changes are appended to a separate `-wal` file, allowing readers to read simultaneously without locking or latency spikes.
How do I create a lightning-fast in-memory database in SQLite?
Pass `:memory:` as the database filename: `sqlite3 :memory:`. The database exists entirely in volatile RAM and operates at microsecond speeds, perfect for unit tests and cache engines.