Generate DB script with IF condition

General Discussion
1

Currently, Liquibase will generate script like the following

CREATE TABLE ABC (…)
GO
INSERT INTO DATABASECHANGELOG] ([MD5SUM], [DATEEXECUTED], [DESCRIPTION], [LIQUIBASE], [COMMENTS], [ID], [AUTHOR], [FILENAME]) VALUES (…)
GO

Is there an option to generate

IF exists (select 1 from DATABASECHANGELOG where …)
begin
create table ABC (…)
insert into DATABASECHANGELOG
end

so that the same script can be run (or re-run) for multiple targets?

2

@tom2011 ,

May I confirm you are trying to run the following changeset:

And wish to run it in multiple targets (like say dev, test, prod?). That’s what liquibase is designed to do, you don’t need to mess with this portion:

Am I missing something? Or are all your targets sharing one Database changelog table?

Thanks,

Ronak

3

We do not have direct access to production database hence using the existing way will always be a challenge. It will be much easier if Liquibase can still generate the whole script but enclose it with a check

4

Sorry, I am still missing something. That’s fine if YOU do not have direct access but Liquibase has access to production right?

So, when liquibase tries to run a changeset it will look in the changelog table to see if it already ran past changesets before it runs. So, I would just put a changeset for table ABC, and then if you find it is in production (how does it get there but you don’t see it in lower environments?) I would just mark that changeset as ran thru a changelog sync.

If you are saying tables are created directly in prod, we have a best practice for that as well:

  • create table in prod (possibly b/c of a hotfix or whatever)
  • create a changeset in the changelog afterwards
  • perform a changelog sync on prod to indicate to liquibase it has ran (essentially it updates the dbchangelog table)
  • run liquibase update on the lower environments to accommodate for the hotfix.

You will need to get some communication on schema changes happening directly in prod. Liquibase was meant to be the only updater to that schema. Sometimes that is not always feasible but you do have to go back and tell liquibase.

I would discourage use of logic in your prod deployments. That’s an anti-pattern and you can’t rehearse for a release and know 100% for sure what will happen.

5

Liquibase does not access to production. We just prepare a script for their DBA to apply.

6

Okay, so to bring it back to your original question:

if you are preparing scripts for productions, the environments should be refreshed from production and the pattern is to run liquibase update. No option for logic within a changeset, b/c it would produce different results and the whole paradigm of liquibase is to rehearse the exact deploy you will be using in production.