mysql 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; `

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; `

Change column types safely in SQLAlchemy

When changing a column type that requires a table rewrite, follow these steps: 1. Create a new column with the desired type 2. Write to both columns during the transition period 3. Backfill data from the old column to the new column 4. Move reads from the old column to the new column 5. Stop writing to the old column 6. Drop the old column Bad: `python def upgrade(): # Directly changing a column type can cause table locks op.alter_column('users', 'some_column', type_=sa.String(50), existing_type=sa.Integer()) def downgrade(): op.alter_column('users', 'some_column', type_=sa.Integer(), existing_type=sa.String(50)) ` Good: `python Migration 1: Add new column def upgrade(): # Adding a new column first op.add_column('users', sa.Column('some_column_new', sa.String(50))) def downgrade(): op.drop_column('users', 'some_column_new') ` `python Migration 2: Complete the transition (after backfilling data) def upgrade(): # After ensuring all data is migrated op.drop_column('users', 'some_column') op.alter_column('users', 'some_column_new', new_column_name='some_column') def downgrade(): op.alter_column('users', 'some_column', new_column_name='some_column_new') op.add_column('users', sa.Column('some_column', sa.Integer())) `