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?

Comments

[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.

[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.

[Richard Umoffia]

Thanks for your response. This really helped

[Mohammed Samgan Khan]

any time brother.

[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.