Any advantages to using Liquibase for database migrations?

Go To StackoverFlow.com

7

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?

2012-04-05 17:32
by Patrick Trainor


2

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. :)

2012-04-06 03:23
by David
Thanks for linking to your question/answer, that helps - Patrick Trainor 2012-04-06 13:35
Of course you can add a not null column with a default in "SQL". Which DBMS are you referring to that does not support this - a_horse_with_no_name 2012-04-06 18:10
@ahorsewithnoname - Sorry, to be clear I mean: There is no single SQL command which can create a not-null column that specifies a value for existing rows but which isn't setting the default column value. I'm happy to be proven wrong but I think all the combinations of commands to achieve this are at least 3 lines long - David 2012-04-06 22:28
@David: you mean "specfiy a value for existing rows" - a_horse_with_no_name 2012-04-06 22:32
@ahorsewithnoname - Yeah, I did. Fixe - David 2012-04-06 22:33
@David: understood. You might rephrase your answer. You can supply a default value when adding a not-null column. But you cannot define a value different than that for existing rows. Your "no way to supply a default value" got me confused - a_horse_with_no_name 2012-04-06 22:35
If you don't need to work as database-agnostic then Flyway can be better solution which is simpler. Another disadvantage of it no down-grade option but automatically down-grade didn't look nice to me - İsmail Yavuz 2017-11-16 09:10


5

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.
2012-04-06 17:38
by Nathan Voxland
Do you mean "Ability to have the same change description on multiple database types"? What does that mean? Why would you want to have the same description - Steven Shaw 2012-08-16 01:57
For example, if your application supports both MySQL and Postgres, you can write single changelog that can be executed against both database type - Nathan Voxland 2012-08-17 06:46
Ah yes, I think the word "description" threw me. I was thinking of an English description of the change rather than a generic one - Steven Shaw 2012-08-18 12:48


0

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

2012-07-28 22:35
by Martin Murphy
Ads