| name | database |
| description | Database design and optimization. Use for schema design, queries, migrations, indexing, and performance tuning. |
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
CREATE TABLE users (...);
CREATE TABLE api_keys (...);
CREATE TABLE model_providers (...);
CREATE TABLE request_logs (...);
CREATE TABLE User (...);
CREATE TABLE apiKey (...);
CREATE TABLE mdl_prvdr (...);
Column Naming
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
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
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,
SELECT * FROM users WHERE deleted_at IS NULL;
UUIDs vs Auto-increment
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
id SERIAL PRIMARY KEY
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 *
SELECT * FROM users WHERE id = $1;
SELECT id, name, email FROM users WHERE id = $1;
Use Proper JOINs
SELECT * FROM users, orders WHERE users.id = orders.user_id;
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
Batch Operations
INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
INSERT INTO logs (message) VALUES ('log3');
INSERT INTO logs (message) VALUES
('log1'),
('log2'),
('log3');
Use EXISTS instead of IN for large sets
SELECT * FROM users
WHERE id IN (SELECT user_id FROM active_sessions);
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
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_logs_user_date ON request_logs(user_id, created_at DESC);
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
CREATE INDEX idx_users_tags ON users USING GIN(tags);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
Index Guidelines
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX idx_users_is_active ON users(is_active);
🔄 Migrations
Migration Best Practices
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);
DROP TABLE IF EXISTS users;
Safe Schema Changes
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
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
query := "SELECT * FROM users WHERE email = '" + email + "'"
query := "SELECT * FROM users WHERE email = $1"
row := db.QueryRow(query, email)
Least Privilege
CREATE USER report_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON users, api_keys TO app_user;
📊 JSONB Patterns (PostgreSQL)
CREATE TABLE model_configs (
id UUID PRIMARY KEY,
model_name VARCHAR(100),
parameters JSONB DEFAULT '{}'::jsonb
);
SELECT * FROM model_configs
WHERE parameters->>'temperature' = '0.7';
CREATE INDEX idx_configs_params ON model_configs USING GIN(parameters);
UPDATE model_configs
SET parameters = parameters || '{"max_tokens": 1000}'::jsonb
WHERE id = $1;
📚 References