database

name	database
description	> Use when this capability is needed.

Database CLI Operations

Quick Reference

Action	SQLite	PostgreSQL	MySQL
Connect	sqlite3 db.sqlite	psql -U user -d dbname	mysql -u user -p dbname
List databases	.databases	\l	SHOW DATABASES;
List tables	.tables	\dt	SHOW TABLES;
Describe table	.schema tablename	\d tablename	DESCRIBE tablename;
Quit	.quit	\q	\q or exit
Run file	.read file.sql	\i file.sql	source file.sql

SQLite (sqlite3)

Connection and Configuration

# Open or create a database
sqlite3 mydb.sqlite

# Open read-only
sqlite3 -readonly mydb.sqlite

# Execute SQL directly (non-interactive)
sqlite3 mydb.sqlite "SELECT * FROM users;"

# Execute SQL from file
sqlite3 mydb.sqlite < queries.sql
sqlite3 mydb.sqlite ".read queries.sql"

# Output modes
sqlite3 mydb.sqlite -header -column "SELECT * FROM users;"
sqlite3 mydb.sqlite -json "SELECT * FROM users;"
sqlite3 mydb.sqlite -csv "SELECT * FROM users;"
sqlite3 mydb.sqlite -markdown "SELECT * FROM users;"

Dot Commands

.help                    # List all dot commands
.tables                  # List all tables
.tables %user%           # List tables matching pattern
.schema                  # Show CREATE statements for all tables
.schema users            # Show CREATE statement for specific table
.indexes                 # List all indexes
.indexes users           # List indexes for specific table
.headers on              # Show column headers
.mode column             # Columnar output (also: csv, json, markdown, table, line)
.width 20 10 30          # Set column widths
.timer on                # Show query execution time
.dbinfo                  # Show database metadata
.dump                    # Dump entire database as SQL
.dump users              # Dump specific table
.import file.csv users   # Import CSV into table
.output result.txt       # Redirect output to file
.output stdout           # Reset output to terminal
.changes on              # Show number of rows changed
.eqp on                  # Show query plan automatically

Schema Operations

-- Create table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT (datetime('now'))
);

-- Add column
ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';

-- Rename table
ALTER TABLE users RENAME TO app_users;

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_name ON users(name);

-- Drop index
DROP INDEX idx_users_email;

-- Analyze (update query planner statistics)
ANALYZE;

CRUD Operations

-- Insert
INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');
INSERT INTO users (name, email) VALUES ('Bob', '[e-mail]'), ('Carol', '[e-mail]');

-- Select
SELECT * FROM users WHERE role = 'admin' ORDER BY name LIMIT 10;
SELECT name, COUNT(*) as cnt FROM orders GROUP BY name HAVING cnt > 5;

-- Update
UPDATE users SET role = 'admin' WHERE email = '[e-mail]';

-- Delete
DELETE FROM users WHERE created_at < datetime('now', '-1 year');

-- Upsert (insert or replace)
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]');
 users (name, email)  (, )
   CONFLICT(email) DO   name  excluded.name;

Import and Export

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

# Export to JSON
sqlite3 -json mydb.sqlite "SELECT * FROM users;" > users.json

# Import CSV
sqlite3 mydb.sqlite <<'EOF'
.mode csv
.import users.csv users
EOF

# Backup (SQL dump)
sqlite3 mydb.sqlite .dump > backup.sql

# Restore from dump
sqlite3 newdb.sqlite < backup.sql

# Binary backup (online, safe while database is in use)
sqlite3 mydb.sqlite ".backup backup.sqlite"

Query Analysis

-- Query plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[e-mail]';

-- Full explain
EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

-- Integrity check
PRAGMA integrity_check;

-- Database size info
PRAGMA page_count;
PRAGMA page_size;

-- Table info
PRAGMA table_info(users);

-- Foreign key check
PRAGMA foreign_key_check;

-- WAL mode (recommended for concurrent access)
PRAGMA journal_mode=WAL;

-- Optimize after bulk operations
VACUUM;

PostgreSQL (psql)

Connection

# Connect to local database
psql -U postgres -d mydb

# Connect with host and port
psql -h localhost -p 5432 -U myuser -d mydb

# Connection string (URI)
psql "postgresql://user:password@host:5432/dbname?sslmode=require"

# Execute SQL directly
psql -U postgres -d mydb -c "SELECT * FROM users;"

# Execute SQL file
psql -U postgres -d mydb -f queries.sql

# Output formatting
psql -U postgres -d mydb --csv -c "SELECT * FROM users;"
psql -U postgres -d mydb -t -A -c "SELECT count(*) FROM users;"  # tuples only, unaligned

Meta-Commands

\l                       -- List all databases
\c dbname                -- Connect to database
\dt                      -- List tables in current schema
\dt public.*             -- List tables in public schema
\dt+ users               -- Table details with size
\d users                 -- Describe table (columns, types, constraints)
\d+ users                -- Extended description (storage, stats)
\di                      -- List indexes
\di+ idx_users_email     -- Index details
\dn                      -- List schemas
\df                      -- List functions
\dv                      -- List views
\du                      -- List roles/users
\dp users                -- Show table privileges
\x                       -- Toggle expanded display (vertical rows)
\timing                  -- Toggle query timing display
\i file.sql              -- Execute SQL file
\o output.txt            -- Send output to file
\o                       -- Reset output to terminal
\! command               -- Execute shell command
\e                       -- Edit query in $EDITOR
\g                       -- Execute last query again
\s                       -- Show command history
\pset format csv         -- Set output format (csv, html, latex, wrapped)

Schema Inspection

-- List all tables with sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- List columns for a table
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- List indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- List foreign keys
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'users'::regclass;

-- Active connections
SELECT pid, usename, datname, state, query
FROM pg_stat_activity
WHERE state = 'active';

Backup and Restore

# Dump single database (SQL)
pg_dump -U postgres mydb > backup.sql

# Dump with compression
pg_dump -U postgres -Fc mydb > backup.dump

# Dump schema only
pg_dump -U postgres --schema-only mydb > schema.sql

# Dump data only
pg_dump -U postgres --data-only mydb > data.sql

# Dump single table
pg_dump -U postgres -t users mydb > users.sql

# Dump all databases
pg_dumpall -U postgres > all_databases.sql

# Restore from SQL dump
psql -U postgres mydb < backup.sql

# Restore from custom format
pg_restore -U postgres -d mydb backup.dump

# Restore single table
pg_restore -U postgres -d mydb -t users backup.dump

Query Analysis

-- Execution plan
EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

-- Execution plan with actual runtime stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT * FROM users WHERE email = '[e-mail]';

-- Table statistics
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables;

-- Index usage statistics
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

-- Update statistics
ANALYZE users;
ANALYZE;  -- all tables

-- Slow queries (requires pg_stat_statements extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

MySQL (mysql)

Connection

# Connect
mysql -u root -p
mysql -u myuser -p mydb
mysql -h hostname -P 3306 -u myuser -p mydb

# Execute SQL directly
mysql -u root -p -e "SELECT * FROM users;" mydb

# Execute SQL file
mysql -u root -p mydb < queries.sql

# Output formatting
mysql -u root -p -N -B -e "SELECT count(*) FROM users;" mydb  # raw value

Meta-Commands

SHOW DATABASES;
USE mydb;
SHOW TABLES;
SHOW TABLE STATUS;
DESCRIBE users;              -- column details
SHOW CREATE TABLE users;     -- full CREATE statement
SHOW INDEX FROM users;
SHOW PROCESSLIST;            -- active connections
SHOW VARIABLES LIKE '%max%'; -- server variables
SHOW STATUS LIKE 'Threads%'; -- server status

Backup and Restore

# Dump single database
mysqldump -u root -p mydb > backup.sql

# Dump with compression
mysqldump -u root -p mydb | gzip > backup.sql.gz

# Dump schema only
mysqldump -u root -p --no-data mydb > schema.sql

# Dump specific tables
mysqldump -u root -p mydb users orders > tables.sql

# Dump all databases
mysqldump -u root -p --all-databases > all.sql

# Restore
mysql -u root -p mydb < backup.sql

# Restore compressed
gunzip < backup.sql.gz | mysql -u root -p mydb

Query Analysis

-- Execution plan
EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

-- Extended explain (JSON format, MySQL 5.7+)
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[e-mail]';

-- Analyze (update index statistics)
ANALYZE TABLE users;

-- Check table integrity
CHECK TABLE users;

-- Optimize table (reclaim space)
OPTIMIZE TABLE users;

-- Show index cardinality
SHOW INDEX FROM users;

Common SQL Patterns

-- Pagination
SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 40;

-- Count with grouping
SELECT status, COUNT(*) as cnt FROM orders GROUP BY status ORDER BY cnt DESC;

-- Join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01';

-- Subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- CTE (Common Table Expression)
WITH active_users AS (
    SELECT * FROM users WHERE last_login > '2024-01-01'
)
SELECT * FROM active_users WHERE role = 'admin';


 name, department, salary,
       ()  (  department   salary )  rank
 employees;



    ()  total,
    (  status       )  active,
    (  status       )  inactive
 users;

Important Notes

• Always back up before destructive operations (DROP, DELETE, TRUNCATE, ALTER)
• Use transactions for multi-statement changes: BEGIN; ... COMMIT; (or ROLLBACK;)
• Prefer EXPLAIN ANALYZE over EXPLAIN to see actual vs estimated row counts
• For SQLite, use WAL mode (PRAGMA journal_mode=WAL) for concurrent read/write
• For PostgreSQL, use \x for wide tables to get vertical output
• For MySQL, add \G at the end of a query for vertical output
• Use -t (tuples only) and -A (unaligned) in psql for scriptable output
• Never store passwords in command-line arguments; use .pgpass (psql) or .my.cnf (mysql)
• Use parameterized queries in scripts to prevent SQL injection

---

Source: bug-ops/zeph — distributed by TomeVault.