Spring Boot liquibase : How to exclude SQL statement execution in a particular environment/spring profile

Introduction:

When you are using liquibase with Spring boot for database migration, you might often end up in a situation where you want to execute SQL statements only in particular environment for various reasons like local/dev data setup, Integration tests etc. You would not want these test data setup statements executed in the actual environment like QA/Production. Lets see how we can make a particular SQL statement/changeset in liquibase terms execute in a particular environment.

Scenario :

Consider that we have a Employee table in our db.tables.changelog.sql file as shown below.

--liquibase formatted sql

-- changeset demo:20230605-01

-- preconditions onFail:MARK_RAN onError:MARK_RAN

-- precondition-sql-check expectedResult:0 select count(*) from information_schema.tables where lower(table_name) = 'employee';

CREATE TABLE Employee

(

    id integer,

    firstName varchar,

    lastName varchar,

    email varchar,

    addressLine varchar,

    city varchar

);

-- rollback drop table employee;

And, we have the below data setup file demo.local.datainserts.changelog.sql 

--liquibase formatted sql

-- changeset demo:20230605-01 context:local

-- preconditions onFail:MARK_RAN onError:MARK_RAN

-- precondition-sql-check expectedResult:0 select count(*) from employee;

insert into employee (id, firstName, lastName, email, addressLine, city) values (1, 'John', 'Wick', 'johnwick@testmail.com', 'addressLine', 'NewYork');

Notice that we have specified the changeset to be executed only when the context is "local".

Configuration: To inform Spring about the context to use for a specific profile, navigate to your application-local.yaml (assuming the profile is "local") and add the following configuration:

spring:

  liquibase:

    contexts: local

Alternatively, you can modify the application.yaml file to pass the spring.profiles.active value to the Liquibase context:

spring:

  liquibase:

    contexts: ${spring.profiles.active}

That's it! The SQL statement or changeset will be executed only when the context is "local" and will be skipped in any other context or environment.

Conclusion:

By leveraging the power of Liquibase contexts and Spring Boot configuration, you can control the execution of SQL statements or changesets in specific environments. This flexibility allows you to manage test data setup or environment-specific scripts effectively while ensuring they are not executed in production environments. Liquibase simplifies the database migration process, and with the context feature, you can further enhance its functionality.

Happy coding!

More information about liquibase contexts can be found in the official documentation here. 

Share: