How to create schema in Postgres DB, before liquibase start to work?

Question

I have standalone application. It’s on java, spring-boot, postgres and it has liquibase.

I need to deploy my app and liquibase should create all tables, etc. But it should do it into custom schema not in public. All service tables of liquibase (databasechangelog and databasechangeloglock) should be in custom schema too. How can I create my schema in DB before liquibase start to work? I must do it inside my app when it’s deploying, in config or some like. Without any manual intervention into the DB.

application.properties:

spring.datasource.jndi-name=java:/PostgresDS
spring.jpa.properties.hibernate.default_schema=my_schema
spring.jpa.show-sql = false
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.datasource.continue-on-error=true
spring.datasource.sql-script-encoding=UTF-8

liquibase.change-log = classpath:liquibase/changelog-master.yaml
liquibase.default-schema = my_schema

UPD:

When liquibase start, it's create two tables databasechangelogs and one more table. After that, liquibase start working. But I want liquibase in liquibase.default-schema = my_schema, but it's not exist when liquibase start to work and it an error: exception is liquibase.exception.LockException: liquibase.exception.DatabaseException: ERROR: schema "my_schema" does not exist

I want liquibase work in custom schema, not in public:

liquibase.default-schema = my_schema

but before liquibase can do it, the schema must be created. Liquibase can't do this because it not started yet and for start it needs schema. Vicious circle.

Luxe

@a_horse_with_no_name I want to create my tables(changesets in liquibase will do it) in my custom schema. But when liquibase start to work, my custom schema is not created yet. – Luxe

Confused with your two statements "liquibase should create all tables" and "How can I create my schema in DB before liquibase start to work?". two statements are contradictory to each other – Clicker

@Clicker liquibase should create tables. Tables in my custom schema, but liquibase can't create schema. I want to create it before liquibase start to work. – Luxe

ohh, I misread it. Thanks – Clicker

@a_horse_with_no_name I can't. When liquibase start, it's create two tables databasechangelogs and one more table. After that, liquibase start working. But I want liquibase in liquibase.default-schema = my_schema, but it's not exist when liquibase start to work and it an error: exception is liquibase.exception.LockException: liquibase.exception.DatabaseException: ERROR: schema "my_schema" does not exist – Luxe

Please edit your question and include that information; don't use comments to communicate information pertinent to your question. – Bamberger

So you do want the liquibase tables to be created in that schema. Your answer to the first time I asked that question was that you want to create your tables in that schema. You should make creating that schema part of your initial database setup - you are not creating the database in your Liquibase script either, so whatever you use to create the database, that part should also create the needed schema. – Obstacle

@a_horse_with_no_name I want ALL tables in my_schema. Sorry for misunderstanding. – Luxe

@a_horse_with_no_name I see, but DB is already created. My point is make all changes with DB when my app is deployed . Without manual intervention in the DB. – Luxe

Answer 1

I found a solution with my application.properties.

org.springframework.jdbc.datasource.init.ScriptUtils worked before liquibase.

Logs:

14:11:14,760 INFO  [org.springframework.jdbc.datasource.init.ScriptUtils] 
(ServerService Thread Pool -- 300) Executing SQL script from URL 
[vfs:/content/app.war/WEB-INF/classes/schema.sql]

14:11:14,761 INFO  [org.springframework.jdbc.datasource.init.ScriptUtils] 
(ServerService Thread Pool -- 300) Executed SQL script from URL 
[vfs:/content/app.war/WEB-INF/classes/schema.sql] in 1 ms.

14:11:14,912 ERROR [stderr] (ServerService Thread Pool -- 300) INFO 9/27/18 
2:11 PM: liquibase: Successfully acquired change log lock

14:11:15,292 ERROR [stderr] (ServerService Thread Pool -- 300) INFO 9/27/18 
2:11 PM: liquibase: Reading from my_schema.databasechangelog

14:11:15,320 ERROR [stderr] (ServerService Thread Pool -- 300) INFO 9/27/18 
2:11 PM: liquibase: Successfully released change log lock

I just put schema.sql with CREATE SCHEMA IF NOT EXISTS my_schema; into resources dir and all working properly.

Thanks all for help.

Update: It's work for Spring boot 1.X. If you are use Spring Boot 2, you should enable schema.sql in properties file, with spring.datasource.initialization-mode=always. More info in Spring Boot - Loading Initial Data

Update 2: In Spring Boot 2.5.2 (maybe in earlier versions too) this solution is not working now, as @peterh has wrote in comment. Sad but true. The last version I was try this solution and it's work was Spring Boot 2.0.9 In docs Spring Boot says that it was redesigned from Spring Boot 2.5.x

Update 3: Some information why they kill this feature -> https://github.com/spring-projects/spring-boot/issues/22741

Luxe

This is good stuff. Unfortunately the Spring Boot 2.x documentation says "If you are using a Higher-level Database Migration Tool, like Flyway or Liquibase, you should use them alone to create and initialize the schema. Using the basic schema.sql and data.sql scripts alongside Flyway or Liquibase is not recommended and support will be removed in a future release." ref. So yes, it works .... for now. – Tamartamara

@Tamartamara yes you are right, from the very beginning it was a workaround due to liquibase requires the schema before the initialization. I hope they(lb maintainers) will move the right way and add something to create initial schema before writing databasechangelog – Luxe

Answer 2

To solve this, we need to run a SQL statement that creates the schema during Spring Boot initialization at the point when DataSource bean had been already initialized so DB connections can be easily obtained but before Liquibase runs.

By default, Spring Boot runs Liquibase by creating an InitializingBean named SpringLiquibase. This happens in LiquibaseAutoConfiguration.

Knowing this, we can use AbstractDependsOnBeanFactoryPostProcessor to configure SpringLiquibase to depend on our custom schema creating bean (SchemaInitBean in the example below) which depends on DataSource. This arranges the correct execution order.

My application.properties:

db.schema=my_schema
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.default_schema=${db.schema}
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.liquibase.enabled=true
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml
spring.liquibase.defaultSchema=${db.schema}

Add the @Configuration class below to the project, for example put it in a package processed by component scan.

@Slf4j
@Configuration
@ConditionalOnClass({ SpringLiquibase.class, DatabaseChange.class })
@ConditionalOnProperty(prefix = "spring.liquibase", name = "enabled", matchIfMissing = true)
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, HibernateJpaAutoConfiguration.class })
@Import({SchemaInit.SpringLiquibaseDependsOnPostProcessor.class})
public class SchemaInit {

    @Component
    @ConditionalOnProperty(prefix = "spring.liquibase", name = "enabled", matchIfMissing = true)
    public static class SchemaInitBean implements InitializingBean {

        private final DataSource dataSource;
        private final String schemaName;

        @Autowired
        public SchemaInitBean(DataSource dataSource, @Value("${db.schema}") String schemaName) {
            this.dataSource = dataSource;
            this.schemaName = schemaName;
        }

        @Override
        public void afterPropertiesSet() {
            try (Connection conn = dataSource.getConnection();
                 Statement statement = conn.createStatement()) {
                log.info("Going to create DB schema '{}' if not exists.", schemaName);
                statement.execute("create schema if not exists " + schemaName);
            } catch (SQLException e) {
                throw new RuntimeException("Failed to create schema '" + schemaName + "'", e);
            }
        }
    }


    @ConditionalOnBean(SchemaInitBean.class)
    static class SpringLiquibaseDependsOnPostProcessor extends AbstractDependsOnBeanFactoryPostProcessor {

        SpringLiquibaseDependsOnPostProcessor() {
            // Configure the 3rd party SpringLiquibase bean to depend on our SchemaInitBean
            super(SpringLiquibase.class, SchemaInitBean.class);
        }
    }
}

This solution does not require external libraries like Spring Boot Pre-Liquibase and not affected by limitations on data.sql / schema.sql support. My main motivation for finding this solution was a requirement I had that schema name must be a configurable property. Putting everything in one class and using plain JDBC is for brevity.

Encyclical

This is perfect, no external libraries for something so specific and no 'schema.sql', something that didn't satisfy me. – Fianna

I've imported your code in my project but cannot figure out where do I get all the classes related to Liquibase. E.g. SpringLiquibase. I've only ` <dependency> <groupId>org.liquibase</groupId> <artifactId>liquibase-core</artifactId> </dependency>` I think it makes sense to elaborate on this. Add imports or tell what liquibase dependencies you are using. – Intort

I've figured them out: import liquibase.integration.spring.SpringLiquibase; import org.springframework.boot.autoconfigure.condition.ConditionalOnBean; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; – Intort

Answer 3

Simple solution based on Pavel D. answer. It also can be used without liquibase

@Slf4j
@Component
public class SchemaConfig implements BeanPostProcessor {

    @Value("${db.schema}")
    private String schemaName;

    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
        if (!StringUtils.isEmpty(schemaName) && bean instanceof DataSource) {
            DataSource dataSource = (DataSource) bean;
            try (Connection conn = dataSource.getConnection();
                 Statement statement = conn.createStatement()) {
                log.info("Going to create DB schema '{}' if not exists.", schemaName);
                statement.execute("create schema if not exists " + schemaName);
            } catch (SQLException e) {
                throw new RuntimeException("Failed to create schema '" + schemaName + "'", e);
            }
        }
        return bean;
    }
}

Columbus

Answer 4

You can use Spring Boot Pre-Liquibase module for this. It is exactly what it is meant for. It executes some SQL prior to executing Liquibase itself. Pre-Liquibase sets itself up in the Spring Boot AutoConfigure chain so that it is guaranteed to always execute before Liquibase.

Step 1

Add the following Starter to your project:

<dependency>
    <groupId>net.lbruun.springboot</groupId>
    <artifactId>preliquibase-spring-boot-starter</artifactId>
    <version>  ---latest-version---  </version>
</dependency>

Step 2

Add a SQL file to src/main/resources/preliquibase with a name of postgresql.sql and content like this:

CREATE SCHEMA IF NOT EXISTS ${spring.liquibase.default-schema};

The ${} syntax denotes a placeholder variable. Pre-Liquibase will resolve it from the properties in your Spring Environment.

Step 3

Set application properties like this:

spring.liquibase.default-schema=${my.db.schemaname}
spring.jpa.properties.hibernate.default_schema=${my.db.schemaname}

Now - in this example - the only thing left to decide is where the my.db.schemaname value comes from. That is your choice. The example project advocates that it should come from an OS environment variable, in particular if your are deploying to a cloud.

Final words

WARNING: Pre-Liquibase is possibly way too flexible in that it allows to execute any SQL code. Don't be tempted to put stuff in Pre-Liquibase files which rightfully belong in an Liquibase ChangeSet. Honestly, the only usage I can think of for Pre-Liquibase is to set up a database "home" (meaning a schema or a catalog) where Liquibase db objects can live so that instances of the same application can be separated by schema or catalog while residing on the same database server.

(Disclosure: I'm the author of Pre-Liquibase module)

Oppress

Hi, I can't test it right now, but look nice! – Luxe

Beware: using this library may mess with your transaction manager, causing entities to no longer be saved in the database when your application is running... – Ottie

@Davio. Sounds odd to me. The library is only active during startup. It doesn't mess with or change any other Spring beans or anything like that. – Oppress

I confirm. I have a problem with this lib which disrupts spring initialization. – Eirene

Answer 5

While the logic from Pavel is correct, the solution is too complex. It's easily solved by having 2 datasources (one for Spring and another for liquibase), and makes the dependent.

Below code is simpler, and it uses a .sql script file. This gives added benefit of creating other DB objects (e.g. functions etc) while creating your schema.

@Configuration
public class InitializeDB {
    @Value("${spring.datasource.url}")
    private String dsUrl;
    @Value("${spring.datasource.username}")
    private String dbUser;
    @Value("${spring.datasource.password}")
    private String dbPassword;

    @Bean
    public DataSourceInitializer dsInitializer(
        @Qualifier("primaryDataSource") final DataSource primaryDataSource) {
        ResourceDatabasePopulator dbPopulator = new ResourceDatabasePopulator();
        dbPopulator.addScript(
            new ClassPathResource("<your sql scripe path>"));
        DataSourceInitializer dsInitializer = new DataSourceInitializer();
        dsInitializer.setDataSource(primaryDataSource);
        dsInitializer.setDatabasePopulator(dbPopulator);
        return dsInitializer;
    }

    @Bean
    @Primary
    public DataSource primaryDataSource() {
        return getDataSource();
    }

    @Bean
    @LiquibaseDataSource
    @DependsOn("dsInitializer")
    public DataSource liquibaseDataSource() {
        return getDataSource();
    }

    private DataSource getDataSource() {
        DataSourceBuilder builder = DataSourceBuilder.create();
        builder.driverClassName("org.postgresql.Driver");
        builder.url(dsUrl);
        builder.username(dbUser);
        builder.password(dbPassword);
        return builder.build();
    }
}

Logic of this is pretty simple. When spring loads dsInitializer, it first loads primaryDataSource which is just Spring DataSource. Once dsInitializer bean is created, it initialized the DB with the script provided in it's config. liquibaseDataSource waits for dsInitializer to be constructed and complete. Since it's annotated with @LiquibaseDataSource, Liquibase use this specific datasource for it's own operation.

Conk

Answer 6

I'm developing for a Kubernetes microservice environment and want to learn as little as possible about Java/Mavin/XML and friends. The solution I came up with is mount a volume to liquibase/liquibase with an entrypoint script that uses the liquibase execute-sql command. Note, I'm not setting the current working directory to my project until after execute-sql so that that command will not require databasechangeloglock.

#!/bin/bash
set -x
export PATH=/opt/java/openjdk/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
liquibase execute-sql --sql="create schema if not exists myschema"
cd /changelog
liquibase release-locks
liquibase update

Duran