Lesson 15 of 15 · 25 min
Safe schema changes
By the end of this lesson you can plan and write a schema migration as expand → backfill → tighten steps, run it inside a transaction, keep old queries working with a compatibility view, and order the steps so the migration cannot fail halfway.
Up to now each change was a single statement on a small table. In production, schema changes are written as migrations: numbered scripts kept in version control (0042_add_customer_full_name.sql), reviewed like code, applied in order to every environment (development, staging, production) and recorded in a table so each runs exactly once. Tools such as Flyway, Liquibase, Alembic, Rails or Django migrations automate the bookkeeping; the thinking is the same everywhere.
Three questions shape every migration:
- Who reads or writes this table right now? Applications, scheduled jobs, dashboards. They keep running while you migrate.
- Can the change fail halfway? If it can, what state is the database left in?
- How do I undo it? Every up step should have a down step, or a clear reason why it cannot.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons