tomevault-io Database Skill
Database design, query optimization, and best practices for PostgreSQL/MySQL.
When to Use This Skill
- Designing database schemas
- Writing efficient queries
- Creating migrations
- Performance optimization
- Index design
📐 Schema Design
Table Naming
-- ✅ Good: snake_case, plural, descriptive
CREATE TABLE users (...);
CREATE TABLE api_keys (...);
CREATE TABLE model_providers (...);
CREATE TABLE request_logs (...);
-- ❌ Bad: CamelCase, singular, abbreviations
CREATE TABLE User (...);
CREATE TABLE apiKey (...);
CREATE TABLE mdl_prvdr (...);
Column Naming
-- ✅ Good: snake_case, clear names
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
display_name VARCHAR(100),
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
Common Patterns
Timestamps
-- Always include these columns
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
-- Auto-update updated_at with trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
Soft Deletes
deleted_at TIMESTAMP WITH TIME ZONE,
-- Query active records
SELECT * FROM users WHERE deleted_at IS NULL;
UUIDs vs Auto-increment
-- UUID (recommended for distributed systems)
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- Auto-increment (simpler, better for single DB)
id SERIAL PRIMARY KEY
-- or for larger tables
id BIGSERIAL PRIMARY KEY
🔗 Relationships
One-to-Many
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL
);
CREATE TABLE api_keys (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
key_hash VARCHAR(255) NOT NULL,
name VARCHAR(100)
);
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
Many-to-Many
CREATE TABLE users (
id UUID PRIMARY KEY
);
CREATE TABLE roles (
id UUID PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE user_roles (
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
PRIMARY KEY (user_id, role_id)
);
🔍 Query Optimization
Use EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT u.*, COUNT(ak.id) as key_count
FROM users u
LEFT JOIN api_keys ak ON ak.user_id = u.id
WHERE u.is_active = true
GROUP BY u.id;
Avoid SELECT *
-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = $1;
-- ✅ Good: Only needed columns
SELECT id, name, email FROM users WHERE id = $1;
Use Proper JOINs
-- ❌ Bad: Implicit join
SELECT * FROM users, orders WHERE users.id = orders.user_id;
-- ✅ Good: Explicit JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
Batch Operations
-- ❌ Bad: Multiple INSERT statements
INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
INSERT INTO logs (message) VALUES ('log3');
-- ✅ Good: Single batch INSERT
INSERT INTO logs (message) VALUES
('log1'),
('log2'),
('log3');
Use EXISTS instead of IN for large sets
-- ❌ Slower for large subqueries
SELECT * FROM users
WHERE id IN (SELECT user_id FROM active_sessions);
-- ✅ Better performance
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM active_sessions s WHERE s.user_id = u.id
);
📇 Indexing
When to Create Indexes
- Columns in WHERE clauses
- Columns in JOIN conditions
- Columns in ORDER BY
- Columns with high selectivity (many unique values)
Index Types
-- B-tree (default, most common)
CREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_logs_user_date ON request_logs(user_id, created_at DESC);
-- Partial index (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
-- GIN index (for arrays, JSONB)
CREATE INDEX idx_users_tags ON users USING GIN(tags);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
Index Guidelines
-- ✅ Good: Frequently queried columns
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);
-- ✅ Good: Foreign keys
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
-- ❌ Bad: Low cardinality columns
CREATE INDEX idx_users_is_active ON users(is_active); -- Only true/false
-- ❌ Bad: Too many indexes (slow writes)
🔄 Migrations
Migration Best Practices
-- migrations/001_create_users.sql
-- Up
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
-- Down
DROP TABLE IF EXISTS users;
Safe Schema Changes
-- ✅ Safe: Add column with default (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- ✅ Safe: Create index concurrently (no lock)
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
-- ⚠️ Careful: Add NOT NULL column
-- First add nullable, backfill, then add constraint
ALTER TABLE users ADD COLUMN role VARCHAR(50);
UPDATE users SET role = 'user' WHERE role IS NULL;
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
🔒 Security
Parameterized Queries
// ❌ NEVER: String concatenation (SQL injection!)
query := "SELECT * FROM users WHERE email = '" + email + "'"
// ✅ ALWAYS: Parameterized query
query := "SELECT * FROM users WHERE email = $1"
row := db.QueryRow(query, email)
Least Privilege
-- Create read-only user for reporting
CREATE USER report_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;
-- App user with limited permissions
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON users, api_keys TO app_user;
📊 JSONB Patterns (PostgreSQL)
-- Store flexible data
CREATE TABLE model_configs (
id UUID PRIMARY KEY,
model_name VARCHAR(100),
parameters JSONB DEFAULT '{}'::jsonb
);
-- Query JSONB
SELECT * FROM model_configs
WHERE parameters->>'temperature' = '0.7';
-- Index JSONB
CREATE INDEX idx_configs_params ON model_configs USING GIN(parameters);
-- Update JSONB
UPDATE model_configs
SET parameters = parameters || '{"max_tokens": 1000}'::jsonb
WHERE id = $1;
📚 References
Converted and distributed by TomeVault — claim your Tome and manage your conversions.