A database schema refers to the structure of its tables, their relationships, views, indexes, triggers as well as other objects, And as developers, we often want to perform modifications on that structure to keep it in sync with the new features of the software using it, these modification are called database migrations because they migrate the database from one state into another.
There are various approaches to execute those migrations, and in this post we will cover both the old-school and modern approaches for running them.
Real-World Database Migration Context
In a real-world project, each developer will typically have a local database on his local machine for testing the features he will implement, and there are other databases deployed on environments like development, staging and production.
During the development phase, each developer will need to run migrations on his local database, and when preparing the release of a new version of a software, the approved migrations since the last release must be gathered and executed against the concerned database.
Old-School Approach of Running Database Migrations
Back in the day, developers wrote SQL scripts and manually ran them directly against their local databases, and these scripts were shared in the remote repository so that other members can use them, and during the release, the person in charge would manually execute those scripts on the target database.
This approach was challenging, Keeping track of what migrations script has been already executed against which database was really hard because there was no way to know the actual state of the target database and as a result it was common to run a script more than once against the same database or forget to execute a specific script, Also there was no framework for specifying the order in which the migration scripts must be executed, those situations promoted a fear among developers and the persons responsible for deployments when they have to deal with migrations.
Managed Migration to the rescue
Tools for managing database migrations have gained popularity across different programming ecosystems. These tools were introduced to manage the migration process and reduce the effort required from developers.
The concept behind these tools is to provide a clean and robust way to structure the migrations against multiple databases by enforcing the specification of ordering and database versioning, developers won't need to directly run the migrations against databases anymore, instead they will configure these tools to do the work for them in an intelligent manner ensuring that :
- A migration script is executed only once against a database
- The migrations scripts are executed in the configured order
Under the hood these tools create a dedicated table on the target database, and each time a migration is executed, a record is added to this table with details about that migration, so that when the migration process is initiated again there will be a check on that table to determine which migrations have already been applied and which are pending.
For the Java ecosystem the most commonly used ones are Flyway and Liquibase, for the python ecosystem there is Alembic and for the JavaScript world there is Knex.js and TypeORM.
Integration of Managed Migration Tools at the Development and the Deployment phase
Managed database migration tools can be configured to launch the migration process either at application startup or independently from the software they are related to.
During the development phase, developers frequently need to edit the schema of their local databases. Therefore, it is recommended to configure these managed tools to start the migration as part of the application startup process, so developers don’t have to launch it manually repeatedly.
During the deployment phase, migrations need to be launched only once. Thus, running the migration manually using a single command is recommended.
In real-world projects, both approaches are used together. You can configure how you want the migration to be launched (at application startup or independently) based on the profile with which the application is executed and the environment on which the software is running.
Conclusion
Database migrations are essential for keeping your database schema in sync with evolving application features. Transitioning from manual to automated migrations simplifies this process and reduces errors.
By using these automated migration tools, you can integrate migrations into your development and deployment workflows, making database management more efficient. This allows you to focus on developing new features without worrying about migration issues.
Top comments (0)