AJM Softwares · Technical note
Keep schema changes reviewable and repeatable
Store each schema change as a numbered or otherwise ordered migration alongside the application code. A migration history lets the team see what changed, apply pending changes in a known order, and understand which version of the schema an environment has reached.
Do not rely on a developer manually changing production to make an application release work. Review SQL changes, test them against a representative database, and make deployment instructions clear about which code version expects which schema.
Plan for code and schema to overlap
Deployments are not always instantaneous. Some requests or application instances may still use the previous code while the new version is starting. A staged change can reduce risk: add a compatible column or table, release code that can work with both forms, migrate existing data, then remove obsolete fields in a later release after they are no longer used.
This approach takes more than one release, but it gives the team a way to inspect progress and respond if a deploy is interrupted. For a small application with a coordinated maintenance window, a simpler change may be appropriate; choose based on how the service is deployed and the impact of downtime.
Check the cost of data changes
Adding a constraint, changing a type, rebuilding an index, or updating many existing rows can behave differently across database engines and table sizes. Check the database's documentation for locking, transaction, and execution behavior. PostgreSQL documents that the lock level for ALTER TABLE depends on the specific subcommand, so do not assume every schema edit has the same production impact.
For a large backfill, consider bounded batches, progress tracking, and a way to resume safely. Verify the resulting data before making the new application behavior depend on it. Keep each step small enough to observe and diagnose if it takes longer or affects traffic more than expected.
Test the deployment path, not just the SQL syntax
Apply migrations to a clean local database and to a staging database that resembles production. Check both the migration result and the application flows that read and write the changed data. Confirm how a failed migration is detected and what the operator should do next.
A down migration is not always a safe undo, especially after new data has been written in a changed format. Decide whether the recovery path is a reversible migration, a forward fix, or a restore, and document the data impact before deployment. For managed platforms such as Cloudflare D1, follow that platform's migration history and command behavior as well as the SQL engine's rules.