1

I'm working on an intranet with a database. It's php and mysql. I'm using Git for version control.

My problem is that very often I need to change the database structure. At the moment, we have a file where we write what we have done to the database, and whenever we do a pull we're supposed to apply the most recent commands in that file. It looks like this:

#2017-08-14 TAN: Remove swedish char from DB column name.
ALTER TABLE artiklar CHANGE COLUMN `alarmvalue` min_stored_amount int(11) DEFAULT '0';

#2017-08-29 SAG: Added changelog in database
CREATE TABLE `changelog` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `reg_date` timestamp NOT NULL,
  `comment` varchar(10000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 2017-08-25 TAN: Add new role "monitorStock" to person.
ALTER TABLE personal ADD COLUMN monitorStock int(1) DEFAULT '0' after qc;

This both seems unnecessary messy and also error prone. It is easy to forget but in that case we notice it quickly, but it can cause worse problems. As long as I only add tables or columns to existing tables, everything is fine, but if I rename or remove anything there's no way to reverse it. It must be a better way to solve this.

In short, I want to version control the database structure, but not its content.

klutt
  • 30,332
  • 17
  • 55
  • 95
  • By using [Liquibase](http://www.liquibase.org/) or similar – 1615903 Dec 19 '17 at 09:33
  • You can also refer the following posts: https://stackoverflow.com/questions/846659/how-can-i-put-a-database-under-git-version-control and https://stackoverflow.com/questions/13608706/using-version-control-git-on-a-mysql-database. – Marina Liu Dec 20 '17 at 01:51
  • Another one I answered: https://stackoverflow.com/questions/47170885/how-to-upgrade-mysql-database-schema/47171045#47171045 – Bill Karwin Dec 20 '17 at 06:04

0 Answers0