15

We have couple of data schemas and we investigate the migration to Liquibase. (One of data schemas is already migrated to Liquibase).

Important question for us is if Liquibase supports dry run:

  • We need to run database changes on all schemas without commit to ensure we do not have problems.
  • In case of success all database changes run once again with commit.

(The question similar to this SQL Server query dry run but related to Liquibase)

Added after the answer

I read documentation related to updateSQL and it is not answers the requirements of “dry run”. It just generates the SQL (in command line, in Ant task and in Maven plugin). I will clarify my question:

Does Liquibase support control on transactions?

I want to open transaction before executing of Liquibase changelog, and to rollback the transaction after the changelog execution. Of course, I need to verify the result of the execution.

Is it possible?

Added

Without control on transactions (or dry run) we can not migrate to Liquibase all our schemas.

Please help.

Community
  • 1
  • 1
Michael
  • 10,063
  • 18
  • 65
  • 104
  • This is all depends on your DBMS. Not all DBMS support transactional DDL. In Oracle this would simple not be possible (because you cannot rollback a `drop table`, or `alter table`) . *If* your DBMS supports transactional DDL (e.g. Postgres), then everything will work without a special "dry run" mode because if an error occurs, Liquibase will rollback the unsuccessful changeset. –  Feb 23 '14 at 08:30
  • We use Postgres and hsqldb. I do not need a special "dry run" mode, but rollback of an unsuccessful changeset is not enough. We have 3 data schemas and we need first to run execution on all 3 schemas with rollback (not database changes are performed). If the execution will success on all 3 data schemas - we will run a new execution on all 3 schemas with commit. – Michael Feb 24 '14 at 07:46
  • If you put everything in a single changeset, you get exactly what you need with Postgres –  Feb 24 '14 at 07:50
  • Yes, it will work. But best practices recommends to have one Change per ChangeSet http://www.liquibase.org/bestpractices.html Probably we can not do it with Liquibase – Michael Feb 24 '14 at 08:19
  • 1
    Best practices always depend on requirements. If you requirement is to do all changes in a single transaction, then I don't see a reason why you shouldn't put everything into a single changeset. And besides: those best practices are mainly there for DBMS that do **not** support transactional DDL because cleaning up a half executed changeSet is really ugly then. –  Feb 24 '14 at 08:29

4 Answers4

13

You can try "updateSQL" mode, it will connect db (check you access rights), acquire db lock, generate / print SQL sentences to be applied (based on db state and you current liquibase change sets) also it will print chageset id's missing in current state of db and release db lock.

Okars Gvairševs
  • 181
  • 1
  • 3
  • Can you clarify it? I already use updatedatabase and it commits everything. How should I run it to prevent the commit for the dry run? (I use Ant task http://www.liquibase.org/documentation/ant/updatedatabase_ant_task.html) – Michael Feb 18 '14 at 09:55
  • not sure about ANT but from liquibase CLI -> java -jar liquibase-core-2.0.5.jar --url=$DB_URL --username=$DB_USER --password=$DB_PASSWD updateSQL – Okars Gvairševs Feb 18 '14 at 14:54
  • @Michael Checkout the "outputFile" attribute of the "updateDatabase" ANT task. Description reads: "Save SQL to given file rather than executing " – Mark O'Connor Feb 18 '14 at 22:15
  • @Mark O'Connor, Thanks for the comment, but how should I understand if an execution was successful? Should I parse the file? To read the return value from Ant? – Michael Feb 20 '14 at 14:27
  • @Michael Perhaps you should consider writing a custom ANT task that calls the Liquibase APIs directly. This would give you the fine control you are looking for. – Mark O'Connor Feb 20 '14 at 18:48
10

Unfortunately, no.

By default, Liquibase commits the transaction executing all statements of a changeset. I assume that the migration paths you have in mind usually involve more than a single changeset.

The only way you can modify the transaction behavior is the runInTransaction attribute for the <changeset> tag, as documented here. By setting it to false, you effectively disable the transaction management, i.e. it enables auto-commit mode as you can see in ChangeSet.java.

I think that this feature could be a worthwhile addition to Liquibase, so I opened a feature request: CORE-1790.

blubb
  • 9,510
  • 3
  • 40
  • 82
  • Thanks (+1)! Clarification: If I will set `runInTransaction` to false for all changesets in a changelog and some changeset will fail what will happens to updateDatabase (http://www.liquibase.org/documentation/ant/updatedatabase_ant_task.html)? Will it fail also? Thanks for the opening of the RFE (feature request)!!! – Michael Feb 27 '14 at 08:24
  • You're welcome :) It will fail, but you will be left with an inconsistent migration state on your DB, which is probably the worst possible scenario. – blubb Feb 27 '14 at 17:14
  • It means that without solving of the feature request CORE-1790 we can not use Liquibase for more than one data schema. Hope it will be solved soon. – Michael Mar 02 '14 at 07:45
  • Any progress with it ? – Victor Zanella Nov 08 '17 at 10:18
4

I think your answer is "it does not support dry runs" but the problem is primarily with the database and not with liquibase.

Liquibase does run each changeSet in a transaction and commits it after inserting into the DATABASECHANGELOG table so in theory you could override liquibase logic to roll back that transaction instead of committing it, but you will run into the problem where most SQL ran by liquibase is auto-committing.

For example, if you had a changeSet of:

<changeSet>
  <createTable name="test">
   ...
   </createTable>
</changeSet>

What is ran is:

START TRANSACTION
CREATE TABLE NAME ...
INSERT INTO DATABASECHANGELOG...
COMMIT

but even if you changed the last command to ROLLBACK the create table call will auto-commit when it runs and the only thing that will actually roll back is the INSERT.

NOTE: there are some databases that will rollback DDL SQL such as postgresql, but the majority do not.

INSERT/UPDATE commands would run in a transaction and could be auto-rolled back at the end, but liquibase does not have a postCondition command to do the in-transaction check of the state that would be required. That would be a useful feature (https://liquibase.jira.com/browse/CORE-1793) but even it would not be usable if there are any auto-committing change tags in the changeset. If you added a postcondition to create table example above, the postcondition would fail and the update would fail, but the table would still be there.

Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64
  • Thanks (+1)! Of course, “dry run” functionality depends on the database, but also on Liquibase. For example, we use postgresql, and it supports transactional DDL, but we can not use it since Liquibase does not support it. What we need is following: 1) Open a transaction 2) execute a changelog 3) rollback the transaction During rollback of the transaction all operations should be reverted: DDL and DML of Liquibase, DDL and DML of the changelog. I hope that the feature request CORE-1790 will be implemented in this way. – Michael Mar 02 '14 at 07:56
  • Since there are normally multiple changeSets that have not yet been run, would you expect all the changeSets to run in a transaction that rolls back at the end? Or would you expect to be stepping through the changeSets one at a time? – Nathan Voxland Mar 03 '14 at 06:07
  • I think that changeSets should run in a single transaction that should be rolled back at the end. – Michael Mar 03 '14 at 08:53
0

If your Liquibase migration is sufficiently database agnostic, you can just run it on an in-memory H2 database (or some other "throwaway database") that you can spin up easily using a few lines of code.

var info = new Properties();
info.put("user", "sa");
info.put("password", "");

try (var con = new org.h2.Driver().connect("jdbc:h2:mem:db", info)) {
    var accessor = new FileSystemResourceAccessor();
    var jdbc = new JdbcConnection(con);
    var database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(jdbc);

    Liquibase liquibase = new Liquibase("/path/to/liquibase.xml", accessor, database);
    liquibase.update("");
}

I've blogged about this approach more in detail here.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509