I'm evaluating Liquibase and trying to figure out if it has any advantages for data migrations over just using SQL scripts. Suppose I'm doing the following:

My version 0 database schema looks like this:

CREATE TABLE  `Person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

The database is populated with some existing data represented by the following insert:

INSERT INTO `Person` (`id`, `firstName`, `lastName`) VALUES (1, 'foo', 'bar');

I then decide to add another column to the Person table that is not null but I don't want to lose any existing data. The migration script from version 0 to version 1 would look like this:

ALTER TABLE `Person` ADD COLUMN `dob` date DEFAULT NULL;

UPDATE `Person` set `dob` = '1970-01-01';

ALTER TABLE `Person` MODIFY COLUMN `dob` NOT NULL;

Could Liquibase make this use case easier?

• sql
• liquibase

I went through the same thing recently. See: Adding a non-nullable column to existing table fails. Is the "value" attribute being ignored?

There is no way (in a single SQL statement) to add a column with a value for existing rows without setting that as the column's default value. So if you want the existing rows to have a different value than new rows added (including if you do not want a default value on the column at all) you will need at least two SQL statements. So using liquibase you would still need to add this column in the three steps you describe.

So my answer to your question is: Yes there are advantages of using liquibase over just maintaining a series of SQL scripts. But this is not one of them. :)

The main thing liquibase gives you is the ability to track which changes have been applied to which databases.

In your example, the SQL you are listing would be exactly what liquibase would do, but it will be able to know that your dev database is at version Y and when you "update" your database it will only apply a couple new changes whereas production is on version X and when you "update" production it will apply more changes.

Note: liquibase tracks changes independently rather than using a single version to support multiple developers and/or code branches.

Beyond the work of tracking the applied changes, other advantages liquibase would give you include:

• Ability to have the same change description on multiple database types.
• Ability to have if/then logic in what is applied.
• Generated documentation of database history.
• Ability to specify more complex, multi-statement changes easily.

Mite gives you the best of both worlds in my opinion. You can continue to use your sql scripts and it manages whether or no a script has been executed on the database and gives you the same up and down capability of your typical rails based migration or Liquibase.

Let me know what you think.

https://github.com/soitgoes/mite