database-patterns

# Database Patterns Skill ## ORM Selection | Scenario | ORM | |----------|-----| | Node.js, type-safe | Prisma | | Node.js, SQL-first | Drizzle | | Python, async | SQLAlchemy 2.0 | | Python, simple | SQLModel | | PHP | Doctrine, Eloquent | --- ## Schema Design ### Naming Conventions ```sql -- Tables: plural, snake_case CREATE TABLE user_profiles (...); -- Columns: snake_case user_id, created_at, is_active -- Indexes: idx_{table}_{columns} CREATE INDEX idx_users_email ON users(email); -- Foreign keys: fk_{table}_{ref_table} CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id) ``` ### Common Patterns #### Soft Delete ```sql ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL; -- Query active records SELECT * FROM users WHERE deleted_at IS NULL; ``` #### Audit Columns ```sql created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id) ``` #### UUID vs Serial | Use Case | Type | |----------|------| | Internal only | SERIAL/BIGSERIAL | | External/distributed | UUID | | Human readable | SERIAL with prefix | --- ## Index Strategies ### When to Index - Foreign keys (always) - Columns in WHERE clauses - Columns in ORDER BY - Columns in JOIN conditions ### Index Types | Type | Use Case | |------|----------| | B-tree | Equality, range (default) | | Hash | Equality only | | GIN | Arrays, JSONB, full-text | | GiST |...