Couple of questions from a liquibase noob

Question

Hi, Im evaluating liquibase for a project starting today.

Has anybody used it to create procedures, functions, basically all of the plsql stuff?

If not, is it possible to write embedded sql code in the xml files?

Thanks in advance.

Answer 1

While I haven't used liquibase for stored procedures, I have some experience of Liquibase for more generic operations.

It is possible to write custom sql, either embedded in the xml file or referenced from an external file.

Answer 2

There is a built-in createProcedure tag in liquibase for managing procedures. The best approach is usually to combine the or tags with runOnChange so liquibase will update your procedure when and only when you update the definition. That way you can do diffs between your changelog xml files over time and see how the procedure has changed.

Using the sqlFile tag to reference file per stored-proc is also popular, or, like you said, you can use the sql tag to inline custom sql.

Answer 3

I've encountered issues with trying the use the sql tag for stored procedures, triggers, and functions, but in my case these were provably issues with the MySQL JDBC driver, and not Liquibase itself. The practice I've settled into is to use the sqlFile refactoring as Nathan suggests, then to control the SP/trigger/function code in the same project as the changelog, versioned in the source code system along with it. This lets you manage the SP/whatever code just like it was real source code.

Setting runOnChange="true" in the changeSet containing the sqlFile refactoring is essential. It is this switch (thank you, Nathan) that enables real source control of procedural database code.