How to do database version control without migration of specific framework? [closed]

Question

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.

Blackbird

I recommend you choose a database source control that will be embedded with deployment (meaning, generating the deployment script) and will also be able to do so based on task from your tasks management system – Sniperscope

Answer 1

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.

Riella

How do you handle adding column or delete column? It would be painful to dropping table and build again everytime you need to add column or delete column. – Blackbird

@user1273587, I don't necessarily see how it would be painful unless you're going to try and replicate all the data that was stored inside the database (in that case, you could add an insert_data.sql file). The purpose of this is to be able to create the database from scratch on a new machine or revert back to a previous schema. I would modify the Create_tables.sql for the former and issue an ALTER statement for existing databases. – Riella

Answer 2

dbv.php is a database version control application that you could use. Here is an article by Sitepoint on how to use it.

Pusillanimous

If you are able to give some easy start guide, I would rather choose this answer for the bounty. From the function description in the link, it seems that this is what I want, but I don't have the time to try yet, so some more description will help me to judge if it worth a bounty. Also, it is hard to convince people that an answer with only links deserves a bounty. – Blackbird

@user1273587 I would love to write a guide, but I am not experienced with the solution and since the guide I linked is so well-written, my reply would just be a re-write of that article. I don't think there's much more I can do. Hopefully someone else will come along and expand the answer. – Pusillanimous

Answer 3

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

Tabbitha

Links to 3rd party tools to answer questions are generally frowned upon by the Stack Overflow community. – Gissing

Answer 4

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.

Physostomous

I would say this is nearest to what I want, but at the same time this answer suggest I can only write my own if I want the function? – Blackbird

Yes, but generating an sql delta script from 2 schemas is not that difficult, if you have a hook that can execute a shell script at the right time. There are probably tools or scripts out there to generate a delta or get you most of the way. mysqldump will give you the schema in a predictable format, if you turn off the data part, and the mysql specific options, what's left is relatively easy to parse. – Physostomous

If you have to write the script for yourself, it's worth bearing in mind, if you create 2 databases from the same dump, on the same mysql server, then when you generate new schema dumps, they'll be ordered in the same way. As long as you only add to the schemas (in the same order) and don't drop/reload one and not the other, they'll stay ordered the same way, hence the schemas can just be compared like a string file in sections. The fastest would be to generate a diff, then parse that to get the appropriate bits from the schema, but it would also probably be the most complex script. – Physostomous

The simplest way is probably to script generating a list of CREATE TABLE functions, and line numbers, and hence script extracting the table sections out of each schema and comparing them separately, to see if you need to generate an ALTER TABLE section, for each new column you create, and you'd have to be quite strict in not removing or altering existing columns, so the delta always added to the schema, as changing schema might be dangerous, so should always be done manually. – Physostomous

Answer 5

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

Dorolice

Phinx requires zend framework, and it seems like just another migration plugin as the one given by laravel. dbv.php seems what I want, but it would be great if I can have some more help in start using it. – Blackbird

Answer 6

Using a tool like Phinx you can have much more options and feature. Link to github repository https://github.com/robmorgan/phinx

Smirk

Answer 7

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.

Mayes