CODEWITHSUNDEEP
sqlliquibase
user3494404
user3494404

Reputation: 21

Determine "version" of DB from Liquibase tables

We use Liquibase for configuration management across multiple MS SQL Server DB instances. If all DBs have executed all available Liquibase scripts, is the following a reliable query to confirm that all DBs are in sync? I am looking for a way to do this from a DB script and not Maven or any other command line utility.

select top 1 ID from DATABASECHANGELOG
order by DATEEXECUTED desc

Upvotes: 2

Views: 6874

Answers (3)

Nathan Voxland
Nathan Voxland

Reputation: 15773

Besides the conditional changeSets that @SteveDonnie mentioned, the order that changeSets execute in can vary depending on how and when you are updating your changelog and database. For example, if you have changeSets A,B and then developer X adds C and developer Y updates D then X's order is A,B,C and Y's order is A,B,D. When they both merge their changes together the final order may be A,B,C,D and when X runs the final version his order will be A,B,C,D but Y's final order will be A,B,D,C.

Y's database will be fully up to date, but a SELECT TOP 1 will return "C" for him even though you were expecting "D". It may or may not be a scenario you run into, but is another reason why a simple single row select will not tell you if databases are up to date or not.

Liquibase does have a "status" command you can run against a database and a changelog and it will return whether the database is up to date or not.

Upvotes: 1

Mark O'Connor
Mark O'Connor

Reputation: 77971

I would suggest tagging your database. Two supported mechanisms:

  1. tagDatabase refactor command, contained in a changeset
  2. command line tag option

I personally would favor the first option so that the versioning is built into the changeset files. The second option is useful when performed as part of your application's upgrade process (Create a rollback marker).

Finally, once your database is tagged the latest version can be retrieved using SQL:

SELECT TOP 1 cl.tag
  FROM DATABASECHANGELOG cl
 WHERE cl.tag is not null 
 ORDER BY 1 DESC

Obviously this approach assumes that your tag strings have a numeric component, so that they sort as expected.

Upvotes: 1

SteveDonie
SteveDonie

Reputation: 9016

That might work for really simple scenarios, but if your changelogs have any kind of 'conditional' parts you are going to need some more logic than that.

What you really want to know is whether the set of changes applied to each database is the same. Since changesets are identified by id+author, you should get both those columns from DATABASECHANGELOG and then do set comparison on those to see that the sets were exactly equal.

Upvotes: 1

Related Questions