Continuous Deployment - Database

Whats the question exactly? The migrations purpose is to version the database schema

How would you write your application code to be flexible no matter what the state of the database is?

You might have to migrate in stages. Imagine you have old_column and want the values in there to start use new_column. Let's just pretend you're renaming the column (it could be a change between date and datetime - but let's keep this simple!) You would need several deploys/migrations.

• Deploy 1 - Migration to add new_column
• Deploy 2 - Change App code to insert/update both old AND new columns. This is great, but you don't know if ALL the rows have been updated, so some new_column values may be null.
• Deploy 3 - Update all values of new_column to match old_column (note that for long tables a single large update may be hazardous, you may have to have a process do this in batches - Postgres will rewrite the entire table if you do an UPDATE on all rows)
• Deploy 4 - old_column and new_column are now synced - new values go into both, updates go into both, any untouched rows have been matched up.
• Deploy 5 - Make app reference only new_column
• Deploy 6 - Migrate and drop old_column now that no code references it.

This is dependent on user numbers, uptime requirements etc. Certain database operations can lock tables for long periods - be very sure you know the effects of each action and make sure you won't get any timeouts by testing on a copy of your database.