PART 1 · SCHEMATIC CHANGE MANAGEMENT
The Evolution of the Schema: The Original `init.sql` vs. Migrations
You can delete a volume using `docker compose down -v` on a local test environment, but the production environment contains player data.
Startup script (init.sql / 01_schema.sql)
Versioned Migrations (Flyway, Goose, Liquibase, Alembic)
What it's suitable for:
• Initial deployment of a local test environment from scratch
• Automated tests in CI/CD, where a clean database is set up for each run
• Demonstrating a complete reference structure during a practical defense
Limitation:
• Describes only the final state. Does not answer the question: “How can I safely add a `season_id` column to a table containing 10 million matches without losing data?”
How they are organized in a real project:
• Each schema change is stored in a separate file with a version number: V001__init.sql, V002__add_clan_rating.sql
• The database itself contains a service table named `schema_migrations` with a list of versions that have already been applied and their checksums
• During deployment, the tool compares the files in Git with `schema_migrations` and commits only the new changes in a transaction
• In PostgreSQL, most DDL commands are transactional: if a migration fails midway, the entire schema will be rolled back!
An advantage of PostgreSQL: Unlike MySQL, in PostgreSQL even `CREATE TABLE` and `ALTER TABLE` are executed within a `BEGIN ... COMMIT` transaction.