Advantage of using liquibase refactoring tags over custom refactoring using SQL

I am using liquibase to maintain my change sets written in SQL. Besides automatic rollback support(and that too only in some tags), I cannot see any advantage of using liquibase refactoring tags. Why should I learn and use Liquibase refactoring tags when there is no significant advantage?

If you change your database vendor, then the Liquibase scripts would be more portable than your embedded DDL scripts. For example, currently you might be using PostgreSQL but if the same structure you want to maintain on Oracle from now on you can do that with little changes to the Liquibase scripts.

Yes you are right but I am pretty sure that I will not be changing the database.
But you know its always difficult to convince your team to learn and use all these new tags especially if they are not going to get any additional benefit. I want to keep the usage of this tool as simple as possible so that the development team can adapt to the new process of database scripts management.

So unless there is some convincing reason, I think its better to stick with SQL rather than learning all the new tags.

There are tags that perform higher-order refactorings such as addLookupTable and splitColumn which allow you to generate multiple SQL calls from a single (arguably simpler) tag, as well as the ability to create your own more complex calls and/or perform custom validation as well. 

If your development group prefers to work with raw SQL, you may want to look at the formatted SQL (http://blog.liquibase.org/2010/05/liquibase-formatted-sql.html) changelog format.

Nathan

Hi, I have the exact same problem described by mursil_sayed, plus 1 more:

 

We already have our database in SVN, tons of plain old sql files, changing their format to XML or formatted SQL would be a nightmare.

 

What should I do? Is it maybe that Liquibase is not for me?

 

Something else about this post:

 

“If your development group prefers to work with raw SQL, you may want to look at the formatted SQL (http://blog.liquibase.org/2010/05/liquibase-formatted-sql.html) changelog format.”

 

The link has changed to this: http://www.liquibase.org/manual/formatted_sql_changelogs, and raw SQL (plain old SQL) is not the same as formatted SQL, “formatted SQL” still needs “formatting”, or am I missing something here?

 

Thanks in advance

 


 

 

 

if you have a lot of sql scripts already in SVN, it would probably be best to leave them as they are. Liquibase has an tag that lets you perform the contents of an SQL script as a changeSet. 


If you prefer to keep your changes as raw sql, you can still use liquibase as a way to track which sql files have executed, but you can also use a mix of tags, formatted sql, and xml changes going forward as well.


If you have a large set of existing sql files, you may want to look at creating a script to generate a changelog.xml file that references them in the correct order.


Nathan

Thanks, I’m going to look for references about this sqlFile tag you said, if you have more info about that “correct order” you mention it would be awesome.

 

You said: “but you can also use a mix of tags, formatted sql, and xml changes going forward as well.”

 

What is the problem with no “PLAIN OLD SQL” formats (XML, formatted SQL):

  • Learning curve for developers, change managers, DBA’s, etc.
  • Developers may adopt it but they’ll hate you for bringing the new XML format to their world and they won’t eat at the same table you do for a few weeks.
  • DBA’s, that’s another story, their whole world is SQL based, their thoughts are SQL based, they’ll do anything to get rid of that new XML format, they won’t use it, they won’t want to learn it, and what they say is always appreciated by the boss
  • Another thing, in the end the XML format (or formatted SQL) is still a lock-in. What happens if tomorrow we want to change from Liquibase to some other tool, that new tool won’t read Liquibase format for sure, and if it reads plain old SQL, we would have to reverse all XML files to SQL files, and it won’t be funny to translate all those files back again

 

 

 

By “Correct Order” I am assuming that you have some existing way that you apply your sql files (alphabetically, by creation date, according to documentation, etc). If you are looking to have that process automated by liquibase, you would would need to make sure they are listed in your changelog file in that same correct order.


There is nothing wrong with straight SQL files, there are just pros and cons for using them vs xml or formatted sql. XML format is foreign to DBAs and is abstracted from the SQL that will be ran, but it does have advantages such as 

  • allowing you to use the same changelog file across multiple database types
  • allowing you to specify more complex changes easily (such as vs multiple SQL statements which take some thinking to realize that is the higher-level operation being attempted)

The advantage formatted sql gives you is the ability to break up sql statements into multiple changesets without having to create as many .sql files. Liquibase tracks what was executed at the changeSet level, so if you have a big sql file in a single tag, if it fails part way through you end up in a strange state where liquibase doesn’t think a change has been applied and will retry it from the beginning. Because of that, it is best to have a changeSet per transaction, which usually means a changeset per DDL statement since they autocommit in most databases. With sqlFile, that means you end up with lots and lots of files. By using formatted SQL, you can have fewer files and still get the changeSet granularity. 


Note: formatted SQL is designed to be usable outside of liquibase by having the liquibase metadata in SQL comment blocks, so if you do decide to stop using liquibase, you can run the formatted SQL scripts directly.


Nathan