CODEWITHSUNDEEP
javapostgresqlliquibasedropwizard
Christian
Christian

Reputation: 7429

Liquibase create Schema for postgres

I'm using Dropwizard (1.0.0) and Liquibase to create a database if it's not existing.

Problem here is that I'm using a different Postgres schema (not public). It seems like Liquibase is not able to create this schema before is it? I was expecting Liquibase to generate this schema, but it always throws a "Schema with name xx not found" if I try to build the database.

Upvotes: 18

Views: 73017

Answers (6)

Johan Amanou
Johan Amanou

Reputation: 43

Works great with Pre-Liquibase. To create schema, using SpringBoot 2.7 (at the moment):

1 - Add to pom:

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

2 - Create file "src/main/resources/preliquibase/default.sql" with content:

CREATE SCHEMA IF NOT EXISTS foobar;

Done. Executes before Liquibase (and you can have "?currentSchema=foobar" in your "jdbcUrl:").

Upvotes: 1

Jed
Jed

Reputation: 41

simple approach is an initial call to liquibase using default schema public with a "create schema if not exists foo" and then a second call with the actual migration changelog.

Upvotes: 0

lbruun
lbruun

Reputation: 431

Apart from the fact that you are using Dropwizard then the Spring Boot Pre-Liquibase module does what you are asking.

There's a chicken-and-egg problem with Liquibase in the sense that it cannot be used to configure its own prerequisite, for example a schema. This is the problem which Pre-Liquibase solves. It executes some SQL prior to Liquibase itself.

Sometimes you'll want to host multiple instances of an application on the same database host. Then you'll want to separate them by schema. This is but one possible use-case for Pre-Liquibase.

Pre-Liquibase is meant for Spring so it will not work out-of-the-box for your Dropwizard scenario. But feel free to steal ideas.

(full disclosure: I'm the author of Pre-Libuibase)

Upvotes: 7

Maulik Kakadiya
Maulik Kakadiya

Reputation: 1647

Liquibase does not have CREATE SCHEMA, you need to manage create schema function by running SQL query.

<changeSet author="liquibase_user" id="1">
    <sql>
        CREATE SCHEMA IF NOT EXISTS liquibase_demo;
    </sql>
</changeSet>

Upvotes: 6

Abe
Abe

Reputation: 9031

Not directly in answer to the question, but posting it for anyone who ran into the error I did, with creating tables in multiple schemas. I was getting an error executing this from maven with the defaultSchemaName configuration.

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.2:update (default-cli) on project demo: Error setting up or running Liquibase: ERROR: relation "databasechangelog" already exists [Failed SQL: CREATE TABLE databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))] -> [Help 1]

I tried to fix it by adding the following configurations to pom.xml, but that was only a partial solution:

<defaultSchemaName>foo</defaultSchemaName>
<changelogSchemaName>foo</changelogSchemaName>

Finally, I got this fixed by adding foo to the end of my connection string also, like this jdbc:postgresql://localhost:5432/postgres?currentSchema=foo

Upvotes: 10

fspinnenhirn
fspinnenhirn

Reputation: 1889

Even though Liquibase does not have CREATE SCHEMA in its bundled changes/refactorings (and therefore doesn't generate one during a dropwizard db dump), you could still include this as a changeset in your migrations changelog using the sql tag, as follows:

<changeSet author="christian" id="1">
    <sql dbms="postgresql" endDelimiter=";">
        CREATE SCHEMA foo
    </sql>
</changeSet>

Note that Liquibase will create it's own tables in the PUBLIC schema, regardless - before applying any changesets:
If you run db migrate --dry-run in dropwizard, you'll see that Liquibase would first execute

CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK ...
CREATE TABLE PUBLIC.DATABASECHANGELOG ...

before running

CREATE SCHEMA foo;

Upvotes: 19

Related Questions