I know migration is one way to do version control on database schema. However, as I have a database that is shared by multiple project and every project may change the schema a bit. So, I would need something like branch and merge function like git for codes, where migration cannot give such function. So, is there a version control tools specifically for database?
We are using php with laravel and mysql for database, and we are willing to switch to postgresql if needed.
On
You can set up all of your setup .sql files within the git repository for the project. Because you mentioned many projects use this database, it might be wise to use a single repository exclusively for the database:
Consider that the following directory is managed by git:
setup.php
setup/
----- Create_database.sql
----- Create_users_table.sql
----- Create_posts_table.sql
----- Create_some_view.sql
setup.php can now be used to execute all of the .sql files within the setup directory to make the database easier to deploy/clone. When you need to make a change to your database, simply use the features already built into git to branch and merge changes into this repository.
On
As mentioned above there is a lot of tools enterprise. Personally I went for creating my own one. I use an api dbdeploy .
It works: write your incremental scripts 1_c_table_t.sql 2_c_uk_index_t.sql ... It requires a table in the database where stores all the file numbers. After successfully inserting the sql,it does an insert in a version table.
Multiple teams working on that i create a repo in github and all developers repos depends on that. api uses the fist digit to identify the script.
thanks
On
I worked for a company, which had hooks on our source control server, which on a commit, would run a script, which at it's core created a mysql schema dump from our dev and staging databases, and from the diff between those schemas, it would create an sql script which if run, would allow a third database (a copy of staging) to be altered to produce a database with the same schema as dev, but containing all the data on staging. It would then subsequently run all tests and if they succeeded, it would present the sql delta script, as safe to run on production.
As long as you have an event, at which point you could run a script, you could similarly generate an sql script, that moves the schema forward to the new delta.
The alternative would be simply to keep track of the sql schema, in version control at each change, but this would then mean it'd need a human to work out (from the unified diff), what sql would be needed to implement the change.
On
You could try Phinx I currently use dbv.php as mentioned in another answer, but Phinx seems to have more advanced options which may help you achieve your goals. There documentation can be found here http://docs.phinx.org/en/latest/ and there is a example project walk through by the author here
On
The answer is simple. Just use an .sql file that specifies the db schema. Branch and modify the .sql file for you different as needed. You can specify anything you need like creation of the database and tables.
If you need something to start with, you can do a mysqldump on your db and start with that. After you have the file in source control branch and merge are straightforward after that.
Another option would be to create your base db dump then have various alter scripts that could customize the db to meet your needs. Have the different projects load the db dump, then apply the scripts that are appropriate.
On
Using a tool like Phinx you can have much more options and feature. Link to github repository https://github.com/robmorgan/phinx
dbv.php is a database version control application that you could use. Here is an article by Sitepoint on how to use it.