SQLite CLI

Website: https://sqlite.org CLI Tool: sqlite3 Documentation: https://sqlite.org/cli.html

Description

SQLite is a self-contained, serverless, zero-configuration SQL database engine. The sqlite3 CLI is the command-line interface for interacting with SQLite databases. It allows you to create, query, and manage SQLite database files directly from the terminal.

Install

# macOS (pre-installed, or via Homebrew)
brew install sqlite

# Ubuntu/Debian
sudo apt install sqlite3

# Fedora
sudo dnf install sqlite

# Windows (via Chocolatey)
choco install sqlite

Usage

Open/Create Database

# Open existing or create new database
sqlite3 mydb.db

# Open in read-only mode
sqlite3 -readonly mydb.db

# Open with specific options
sqlite3 -header -column mydb.db

Execute SQL Directly

# Single command
sqlite3 mydb.db "SELECT * FROM users;"

# From file
sqlite3 mydb.db < script.sql

# Multiple commands
sqlite3 mydb.db "CREATE TABLE t(x); INSERT INTO t VALUES(1); SELECT * FROM t;"

Output Formats

# CSV output
sqlite3 -csv mydb.db "SELECT * FROM users;"

# JSON output
sqlite3 -json mydb.db "SELECT * FROM users;"

# Column format with headers
sqlite3 -header -column mydb.db "SELECT * FROM users;"

# Tab-separated
sqlite3 -separator $'\t' mydb.db "SELECT * FROM users;"

Export Data

# Export to CSV
sqlite3 -header -csv mydb.db "SELECT * FROM users;" > users.csv

# Dump entire database to SQL
sqlite3 mydb.db .dump > backup.sql

# Dump specific table
sqlite3 mydb.db ".dump users" > users.sql

Import Data

# Import CSV
sqlite3 mydb.db ".mode csv" ".import data.csv tablename"

# Restore from SQL dump
sqlite3 newdb.db < backup.sql

Interactive Commands (Dot Commands)

Command	Description
`.help`	Show all commands
`.tables`	List all tables
`.schema`	Show CREATE statements
`.schema TABLE`	Show schema for specific table
`.headers on/off`	Toggle column headers
`.mode MODE`	Set output mode (csv, json, column, etc.)
`.output FILE`	Redirect output to file
`.read FILE`	Execute SQL from file
`.dump`	Export database as SQL
`.import FILE TABLE`	Import data from file
`.indexes`	List all indexes
`.quit` or `.exit`	Exit sqlite3

Examples

Quick Database Inspection

# List all tables
sqlite3 mydb.db ".tables"

# Show table schema
sqlite3 mydb.db ".schema users"

# Count rows
sqlite3 mydb.db "SELECT COUNT(*) FROM users;"

# Show first 10 rows
sqlite3 -header -column mydb.db "SELECT * FROM users LIMIT 10;"

Data Export/Backup

# Full backup
sqlite3 mydb.db .dump > backup.sql

# Export table to CSV
sqlite3 -header -csv mydb.db "SELECT * FROM orders WHERE date > '2024-01-01';" > recent_orders.csv

# Export to JSON
sqlite3 -json mydb.db "SELECT * FROM products;" > products.json

Database Maintenance

# Check integrity
sqlite3 mydb.db "PRAGMA integrity_check;"

# Vacuum (reclaim space)
sqlite3 mydb.db "VACUUM;"

# Show database info
sqlite3 mydb.db "PRAGMA database_list;"

# Show table info
sqlite3 mydb.db "PRAGMA table_info(users);"

Create and Populate

# Create table and insert
sqlite3 mydb.db << 'EOF'
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
EOF

# Verify
sqlite3 -header -column mydb.db "SELECT * FROM users;"

One-liner Scripts

# Get all table names as JSON array
sqlite3 -json mydb.db "SELECT name FROM sqlite_master WHERE type='table';"

# Row count for all tables
sqlite3 mydb.db "SELECT name, (SELECT COUNT(*) FROM pragma_table_info(name)) as cols FROM sqlite_master WHERE type='table';"

# Find tables with specific column
sqlite3 mydb.db "SELECT m.name FROM sqlite_master m, pragma_table_info(m.name) p WHERE p.name='user_id';"

Notes

File-based: Each database is a single .db file
Zero config: No server, no setup, just works
ACID compliant: Full transaction support
Cross-platform: Same file works on any OS
Output modes: csv, json, column, html, insert, line, list, markdown, table, tabs
In-memory: Use :memory: for temporary database
Dot commands: Start with ., not SQL (no semicolon needed)
SQL commands: End with ;
NULL display: Use .nullvalue NULL to show NULL explicitly
Foreign keys: Enable with PRAGMA foreign_keys = ON;
WAL mode: Better concurrency with PRAGMA journal_mode=WAL;

WORLDBOOK

sqlite

SQLite CLI

Description

Install

Usage

Open/Create Database

Execute SQL Directly

Output Formats

Export Data

Import Data

Interactive Commands (Dot Commands)

Examples

Quick Database Inspection

Data Export/Backup

Database Maintenance

Create and Populate

One-liner Scripts

Notes

Get this worldbook via CLI

Comments (0)