Database

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

-- Update
UPDATE users SET role = 'admin' WHERE email = 'alice@example.com';

-- Delete
DELETE FROM users WHERE created_at  users.csv

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

# Import CSV
sqlite3 mydb.sqlite  backup.sql

# Restore from dump
sqlite3 newdb.sqlite  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

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

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 & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: bug-ops
• Source: bug-ops/zeph
• License: MIT
• Homepage: https://bug-ops.github.io/zeph/

Install and usage instructions live in the source repository linked above.