bug-ops/zeph/database

# 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;"

.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

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

-- Insert
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');
 
-- 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 = 'alice@example.com';
 
-- 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', 'alice@new.com');
INSERT INTO users (name, email) VALUES ('Alice', 'alice@new.com')
  ON CONFLICT(email) DO UPDATE SET name = excluded.name;

# 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 plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';
 
-- Full explain
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
 
-- 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;

# 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

\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)

-- 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';

# 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

-- Execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
 
-- Execution plan with actual runtime stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT * FROM users WHERE email = 'alice@example.com';
 
-- 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;

# 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

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

# 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

-- Execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
 
-- Extended explain (JSON format, MySQL 5.7+)
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'alice@example.com';
 
-- 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;

-- 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';
 
-- Window functions
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
 
-- Conditional aggregation
SELECT
    COUNT(*) as total,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive
FROM users;