49

I'm currently using SVN for my PHP projects. I was thinking I should get my database under version control too, but what's the best way to do that? Do I just make a db-folder in my project in SVN, paste SQL changes into a file called from_1.0_to_2.0.sql and commit?

Improve this question
3
  • 3
    Are you talking about backing up only the structure or the data, too?
    – acme
    Commented Feb 3, 2011 at 8:44
  • 1
    Is there something similar to the version control mentioned here that also keeps version control of the actual data?
    – Andrew
    Commented Apr 1, 2015 at 21:14
  • duplicate How can I put a database under git (version control)?
    – milahu
    Commented Mar 13, 2022 at 17:49

6 Answers 6

Reset to default
31

Note that in December 2012, you had another option: DBV (DataBase Version)

It is based on this Github project (stalled in 2018), and is a database version control web application featuring schema management, revision scripts, and more.

database schema

It has been discontinued since early 2021.

Improve this answer
5
  • Thanks, this is just awesome! Does it have bidirectional running revisions? I mean if i have to revert to older db schema revision!
    – Keerthivasan
    Commented Aug 31, 2016 at 11:27
  • @Keerthivasan 4 years later, I don't really know. You might have to test it out.
    – VonC
    Commented Aug 31, 2016 at 11:40
  • 1
    Unfortunately It doesn't have bidirectional running revisions.
    – Miguel Angel Mendoza
    Commented Mar 31, 2017 at 2:40
  • In January 2022, the DBV website is no longer available and the Github project is no longer maintained, last update dates from March 2018.
    – Lucian P.
    Commented Jan 20, 2022 at 21:11
  • @LucianP. Thank you. I have updated the answer to clearly mention the current state of this project.
    – VonC
    Commented Jan 20, 2022 at 21:46
23

Whenever you make changes to your database, you should save those changes in a migration, that you can then later on run on other servers at the same time you update your code. But you basically got the right idea. You can write some tools to make it more automated; for example version each file, and then create a table like migration_version in your database, which will contain the current version of the database. You can then create a migrate script that will run all the migrations required to get the database up-to-date.

Note that if you want bi-directional db versioning (so that you can revert back to previous db version too), you need to write the required queries for that too for each version.

There are also some tools that can aid you in writing the migrations, such as MySQLdiff

Improve this answer
2
13

Check out

Liquibase

http://www.liquibase.org/quickstart

The idea is this:

All database changes are stored in a human readable yet trackable form and checked into source control. - Liquibase.org front page

This is a fantastic piece of software that allows you to version your database, in roughly the same process as the top rated answer, except this wheel has already been written and is ready to roll. I use it at work, it's a fantastic solution. Implement it yourself if you want to learn how it works, but Liquibase works great if you want a tool to get things done.

Improve this answer
1
  • 3
    I spent quite some hours working with Liquibase to see if it can satisfy my requirements. I eventually found out that to use it on any practical db, you have to pay for it on a subscription basis. So to save anyone else the time: if it's for your business, it could work. If it's for personal projects, it's prohibitively expensive.
    – Q''
    Commented Dec 17, 2021 at 10:58
1

You can use the MySQL Workbench tool.

The file generated with the modeling tool could esaly be saved under SVN. The tool allows you to synchronise your database with the model in bidirectionnal way.

Improve this answer
3
  • 3
    Not easily. The file saved by MySQL Workbench is binary, which of course you cannot merge. We use it too but it is quite a pain to mantain.
    – paul.ago
    Commented Dec 24, 2013 at 9:26
  • 1
    I agree. It is good for version control when you don't need to merge but if you do, it is useless. I wish worbench would solve that problem, it would be perfect tool for versioning DB.
    – Srneczek
    Commented Jan 7, 2015 at 14:03
  • 3
    I just reliazed that workbench can compare even SQL scripts (not just models) so why not to automate sql script generation from workbench (somehow - or even spend few more seconds to do it manually) and version control this files. Then you still can compare live DB and generated scrips and you can migrate since scripts are readable.
    – Srneczek
    Commented Jan 7, 2015 at 14:15
0

kdbv may its helpful its upgrade your mysql database to current latest version from older without tracking change in mysql tables

Improve this answer
0

Migrations in Laravel - PHP framework seems really useful but there is one big BUT. There is only online tool for designing schema in Laravel ORM language called "Eloquent" (here). So it is still unusable if you need to design and maintain database of some serious project.

Improve this answer