drizzle Rules

Do not rename columns

When renaming columns in PostgreSQL, follow a safe migration pattern to avoid breaking changes to applications: 1. Create a new column 2. Update application code to write to both the old and new columns 3. Backfill data from the old column to the new column 4. Update application code to read from the new column instead of the old one 5. Once all deployments are complete, stop writing to the old column 6. Drop the old column in a later migration Bad: `sql ALTER TABLE users RENAME COLUMN some_column TO new_name; `

Do not rename tables

When renaming tables, use a multi-step approach instead of direct renaming to prevent downtime. 1. Create a new table 2. Write to both tables 3. Backfill data from the old table to the new table 4. Move reads from the old table to the new table 5. Stop writing to the old table 6. Drop the old table Bad: `sql ALTER TABLE users RENAME TO customers; `

Limit non unique indexes in PostgreSQL

Limit non-unique indexes to a maximum of three columns in PostgreSQL databases: Bad: `sql CREATE INDEX index_users_on_multiple_columns ON users (column_a, column_b, column_c, column_d); ` Good: `sql CREATE INDEX CONCURRENTLY index_users_on_selective_columns ON users (column_d, column_b); `

Only concurrent indexes in PostgreSQL

When creating indexes in PostgreSQL, always use the CONCURRENTLY option to prevent blocking writes during index creation. Bad: `sql CREATE INDEX idx_users_email ON users(email); ` Good: `sql CREATE INDEX CONCURRENTLY idx_users_email ON users(email); `

Always use JSONB in PostgreSQL

Always use jsonb instead of json data type when creating columns in PostgreSQL databases. Bad: `sql ALTER TABLE users ADD COLUMN properties json; ` Good: `sql ALTER TABLE users ADD COLUMN properties jsonb; `

Use check constraints for setting NOT NULL columns in PostgreSQL

When adding a NOT NULL constraint to an existing column in PostgreSQL, use a check constraint first to avoid blocking reads and writes while every row is checked. Bad: `sql -- This can cause performance issues with large tables ALTER TABLE users ALTER COLUMN some_column SET NOT NULL; ` Good: `sql -- Step 1: Add a check constraint without validation ALTER TABLE users ADD CONSTRAINT users_some_column_null CHECK (some_column IS NOT NULL) NOT VALID; -- Step 2: In a separate transaction, validate the constraint ALTER TABLE users VALIDATE CONSTRAINT users_some_column_null; -- Step 3: Add the NOT NULL constraint and remove the check constraint ALTER TABLE users ALTER COLUMN some_column SET NOT NULL; ALTER TABLE users DROP CONSTRAINT users_some_column_null; `

Split foreign keys in PostgreSQL

When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables: 1. First create the foreign key constraint without validation 2. Then validate existing data in a separate migration Bad: `sql -- In a single migration ALTER TABLE users ADD CONSTRAINT fk_users_orders FOREIGN KEY (order_id) REFERENCES orders (id); ` Good: `sql -- In first migration: add without validating ALTER TABLE users ADD CONSTRAINT fk_users_orders FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID; -- In second migration: validate existing data ALTER TABLE users VALIDATE CONSTRAINT fk_users_orders; `

Split unique constraints in PostgreSQL

When adding unique constraints in PostgreSQL, create the unique index concurrently first before adding the constraint to avoid blocking reads and writes. Bad: `sql -- Creates a unique constraint directly, which blocks reads and writes ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email); ` Good: `sql -- First create a unique index concurrently (non-blocking) CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (email); -- Then add the constraint using the existing index ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx; `

Split check constraints

When adding check constraints in migrations, split the operation into two steps to avoid blocking writes during the table scan: 1. First create the check constraint without validation 2. Then validate existing data in a separate migration Bad: `sql -- In a single migration ALTER TABLE users ADD CONSTRAINT ck_users_age_positive CHECK (age >= 0); ` Good: `sql -- In first migration: add without validating ALTER TABLE users ADD CONSTRAINT ck_users_age_positive CHECK (age >= 0) NOT VALID; -- In second migration: validate existing data ALTER TABLE users VALIDATE CONSTRAINT ck_users_age_positive; `