Database Best Practices

Enforce database migration discipline, secure query patterns, and robust schema design conventions.

Instructions

When working with databases, follow these rules strictly:

Migrations

1. **One change per migration** - Each migration file should contain exactly one logical schema change (add table, add column, add index, etc.)

2. **Always add down migration** - Every migration must include a rollback/down function that reverses the change

3. **Test rollback before push** - Run the down migration locally to verify it works before committing

Queries

1. **Use parameterized queries** - NEVER use string concatenation to build SQL queries. Always use parameterized/prepared statements to prevent SQL injection

2. **Index frequently filtered columns** - Identify columns used in WHERE, JOIN, and ORDER BY clauses and ensure they have appropriate indexes

3. **Limit results with pagination** - Always paginate large result sets using LIMIT/OFFSET or cursor-based pagination

Schema Design

1. **Soft delete with deleted_at** - Implement soft deletes using a `deleted_at` timestamp column instead of hard deletes

2. **Add created_at, updated_at** - Every table should have `created_at` and `updated_at` timestamp columns for audit trails

3. **Use UUIDs for public IDs** - Use UUID/GUID for publicly exposed identifiers instead of auto-incrementing integers to prevent enumeration attacks

Examples

Good Migration

```sql

-- up

ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;

-- down

ALTER TABLE users DROP COLUMN email_verified;

```

Bad Query (String Concatenation)

```sql

-- DON'T DO THIS

query = "SELECT * FROM users WHERE email = '" + userInput + "'"

```

Good Query (Parameterized)

```sql

-- DO THIS

query = "SELECT * FROM users WHERE email = ?"

execute(query, [userInput])

```

Good Schema

```sql

CREATE TABLE posts (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

title VARCHAR(255) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

deleted_at TIMESTAMP NULL

);

CREATE INDEX idx_posts_deleted_at ON posts(deleted_at);

CREATE INDEX idx_posts_created_at ON posts(created_at);

```

Constraints