Answer.

History of the issue
Working on large projects requires the database schema to evolve in parallel with the application code. Without software version control for schema changes (adding, removing, and modifying tables, indexes, and keys), the team quickly loses synchronization, the risk of losing changes increases, migration failures occur, and rolling back or reproducing bugs becomes complicated.

The problem
The traditional approach — manually changing the database through SQL scripts — leads to an implicit order of executing changes, difficulties in rollback, and version mismatches between environments (dev, test, prod). Without a common tool for storing migrations, it's hard to understand who changed the schema, when, and why.

The solution
Schema migration systems and database versioning practices are used for this task. The application of tools (e.g., Liquibase, Flyway, Alembic for different DBMS) allows storing SQL scripts for schema changes directly in the version control system (git), forming a strict sequence of migrations, and automating schema updates across all environments.

Example code (migration using Flyway):

-- V002__add_column_email.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

Integrating Flyway (for example, for Java):

Flyway.configure().dataSource(url, user, pass).load().migrate();

Key features:

• Allows all developers to "see" and apply the same sequence of changes.
• Easy to "roll back" or restore any version of the schema.
• The entire history of schema changes is transparent and can be reviewed along with the business logic.

Tricky questions.

Is it possible to store the "initial state" of the schema (snapshot) instead of migrations? At first glance, a "dump" of the entire schema seems simpler than migrations. But then problems will arise with rollback, restoring intermediate states, and merging changes from different branches. Migrations allow for applying only new changes sequentially and in the correct order.

Do migrations need to be synchronized manually between different environments? No, modern systems respect versioning and only apply those migrations that have not yet been in the database. The key is not manual synchronization, but automated application of migrations during deployment.

Is it enough to have only SQL migration scripts or should something else be stored? A good practice is to store, in addition to SQL scripts, their descriptions (purpose, author, date) as well as validator tests on new structural changes, to automate the quality control of migrations.

Typical mistakes and anti-patterns

• Applying "manual" changes outside of migrations: leads to desynchronization of environments.
• Editing existing migrations "retroactively" — jeopardizes the history and non-idempotent actions.
• Ignoring reversible (down) migrations.

Example from real life

Negative case

The project used regular schema dumps and "manual" application of changes by developers. After launching the update, the client noticed that some new columns did not appear in the production database, and some indexes were duplicated after each attempt to update the schema.

Pros:
Fast for very small projects.

Cons:
Support difficulties; inability to understand what exactly was changed and by whom; inability to roll back; different environments diverge.

Positive case

The team integrated Flyway, all structural changes are made via migrations with code reviews, rolling back any versions took just minutes, and it is easy to run tests and controls in all environments.

Pros:
Automation, history, low likelihood of bugs during deployment.

Cons:
The need to document each structural change slightly longer.