DEV Community 👩‍💻👨‍💻

Richard Umoffia
Richard Umoffia

Posted on

How to properly manage migration files

So I recently started working with mySQL, coming from MongoDB background and I've never really had to manage migrations.

So what is the best way to manage migration files? Do you commit them? etc etc. Do you sync your local migration files with that of the server?

Top comments (5)

Collapse
 
claudiodavi profile image
Claudio Davi

Back at my previous workplace we had a folder inside the project (on git) which contained the scripts that should be executed in order to keep our databases in sync.
Sort of like this:

version/dbprovider/query_alias.sql
0.1Beta/mysql/update_usertable_timestamp.sql

Then we had a tool that would get the version that you wanted to go, and execute all the scripts that you did not have until you got to your desired version. This tool used the database for two purposes: Save all the files that were already executed and most importantly, execute missing queries.

Collapse
 
msamgan profile image
Mohammed Samgan Khan

the whole concept of the migration is the provide a common platform for Database to the entire team. so, yes we definitely commit them.

The DB in use local or test or production all have a table called migration(or something similar) which keeps the track which migrations have been run in that particular database. And the current state of DB is manipulated according to that only.

I am from PHP background so if you are using laravel or CakePHP they come with there Migration tools. In case you are using core PHP you can use the phinx for the migration. It's a great library.

In case you are using any other language, there must be some library available. have a look around.

thanks.

Collapse
 
iamafro profile image
Richard Umoffia

Thanks for your response. This really helped

Collapse
 
msamgan profile image
Mohammed Samgan Khan

any time brother.

Collapse
 
adarshkkumar profile image
Adarsh.K.Kumar

Also take a look at Flyway. Have used it in a Java and Dotnet project.
Also Spring Boot have seamless integration with it.

It have quite some features like versioned migration, repeatable migration, undo migration (roll back) etc which helped us to move away from custom .SQL and powershell based migration which was a pain.

The Complete Guide to Full Stack Web3 Development

>> Check out this classic DEV post <<