Database Tools Skill

Database operations for SQLite and PostgreSQL.

When to Use

• Query databases
• Run migrations
• Backup and restore
• Check database health
• Analyze queries

SQLite Operations

Connect & Query

# Connect to database
sqlite3 my.db

# List tables
sqlite3 my.db ".tables"

# Schema of table
sqlite3 my.db ".schema users"

# List indexes
sqlite3 my.db ".indexes"

Query Examples

# Select all from table
sqlite3 my.db "SELECT * FROM users;"

# Select with where
sqlite3 my.db "SELECT * FROM users WHERE id = 1;"

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

# Insert row
sqlite3 my.db "INSERT INTO users (name, email) VALUES ('John', 'john@example.com');"

# Update row
sqlite3 my.db "UPDATE users SET name = 'Jane' WHERE id = 1;"

# Delete row
sqlite3 my.db "DELETE FROM users WHERE id = 1;"

Export/Import

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

# Import from CSV
sqlite3 my.db ".import users.csv users"

# Export schema
sqlite3 my.db ".schema" > schema.sql

# Full database dump
sqlite3 my.db ".dump" > backup.sql

Vacuum & Optimize

# Check database size
ls -lh my.db

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

# Analyze (update statistics)
sqlite3 my.db "ANALYZE;"

PostgreSQL Operations

Connect & Query

# Connect
psql -U user -d database

# Connect to remote
psql -h hostname -U user -d database

# With password
PGPASSWORD=secret psql -U user -d database

Common Commands

# List tables
\dt

# Describe table
\d users

# List indexes
\di

# List sequences
\ds

# List views
\dv

# Quit
\q

Query Examples

# Select all
SELECT * FROM users;

# Select with limit
SELECT * FROM users LIMIT 10;

# Join example
SELECT u.name, o.total FROM users u 
  JOIN orders o ON u.id = o.user_id;

# Aggregate
SELECT status, COUNT(*) FROM orders GROUP BY status;

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

User Management

# Create user
CREATE USER newuser WITH PASSWORD 'secret';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO newuser;

# Grant table access
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newuser;

# Revoke access
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM newuser;

Backup & Restore

# Dump single database
pg_dump -U user database > backup.sql

# Dump with compression
pg_dump -U user database | gzip > backup.sql.gz

# Restore
psql -U user database < backup.sql

# Restore from gzipped
gunzip -c backup.sql.gz | psql -U user database

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

Migration Examples

Create Table Migration

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

Add Column Migration

-- migrations/002_add_status.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active';

-- Rollback
ALTER TABLE users DROP COLUMN status;

Rename/Migrate

-- Rename table
ALTER TABLE users RENAME TO accounts;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

Performance Analysis

Explain Queries

-- Explain query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Explain without running
EXPLAIN SELECT * FROM users;

Find Slow Queries

# PostgreSQL: Enable slow query log
# In postgresql.conf:
# log_min_duration_statement = 1000

# View logs
tail -f /var/log/postgresql/postgresql.log

# Using pg_stat_statements
SELECT query, calls, mean_time, total_time 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Index Recommendations

-- Find missing indexes from queries
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;

Database Health Check

SQLite Health

#!/bin/bash
# SQLite health check
DB="$1"

echo "=== SQLite Health Check ==="
echo "Database: $DB"
echo ""

echo "Size:"
ls -lh "$DB"
echo ""

echo "Table count:"
sqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table';"
echo ""

echo "Index count:"
sqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='index';"
echo ""

echo "Integrity check:"
sqlite3 "$DB" "PRAGMA integrity_check;"

PostgreSQL Health

#!/bin/bash
# PostgreSQL health check

echo "=== PostgreSQL Health ==="
echo ""

echo "Database size:"
psql -U user -d db -c "SELECT pg_database_size(current_database());"

echo "Table sizes:"
psql -U user -d db -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 5;"

echo "Active connections:"
psql -U user -d db -c "SELECT count(*) FROM pg_stat_activity;"

echo "Long running queries:"
psql -U user -d db -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY duration DESC LIMIT 5;"

Notes

• Always backup before migrations
• Use transactions for multi-step changes
• Test migrations on staging first
• Use parameterized queries to prevent SQL injection
• Thepopebot stores data in SQLite at data/thepopebot.sqlite