How to create a database with flyway?

Question

Question: Is it possible to create a new DB in a migration script and then connect to it? How?

My Scenario: I'm trying to use flyway in my Java project (RESTful application using Jersey2.4 + tomcat 7 + PostgreSQL 9.3.1 + EclipseLink) for managing the changes between different developers which are using git. I wrote my init script and ran it with:

PGPASSWORD='123456' psql -U postgres -f migration/V1__initDB.sql

and it worked fine. The problem is that I can't create new DB with my scripts. when I include the following line in my script:

CREATE DATABASE my_database OWNER postgres ENCODING 'UTF8';

I get this error:

org.postgresql.util.PSQLException: ERROR: CREATE DATABASE cannot run inside a transaction block
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:395)
    at com.googlecode.flyway.core.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:230)
    at com.googlecode.flyway.core.dbsupport.SqlScript.execute(SqlScript.java:89)
    at com.googlecode.flyway.core.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:72)
    at com.googlecode.flyway.core.command.DbMigrate$2.doInTransaction(DbMigrate.java:252)
    at com.googlecode.flyway.core.command.DbMigrate$2.doInTransaction(DbMigrate.java:250)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:56)
    at com.googlecode.flyway.core.command.DbMigrate.applyMigration(DbMigrate.java:250)
    at com.googlecode.flyway.core.command.DbMigrate.access$700(DbMigrate.java:47)
    at com.googlecode.flyway.core.command.DbMigrate$1.doInTransaction(DbMigrate.java:189)
    at com.googlecode.flyway.core.command.DbMigrate$1.doInTransaction(DbMigrate.java:138)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:56)
    at com.googlecode.flyway.core.command.DbMigrate.migrate(DbMigrate.java:137)
    at com.googlecode.flyway.core.Flyway$1.execute(Flyway.java:872)
    at com.googlecode.flyway.core.Flyway$1.execute(Flyway.java:819)
    at com.googlecode.flyway.core.Flyway.execute(Flyway.java:1200)
    at com.googlecode.flyway.core.Flyway.migrate(Flyway.java:819)
    at ir.chom.MyApp.<init>(MyApp.java:28)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at org.glassfish.hk2.utilities.reflection.ReflectionHelper.makeMe(ReflectionHelper.java:1117)
    at org.jvnet.hk2.internal.Utilities.justCreate(Utilities.java:867)
    at org.jvnet.hk2.internal.ServiceLocatorImpl.create(ServiceLocatorImpl.java:814)
    at org.jvnet.hk2.internal.ServiceLocatorImpl.createAndInitialize(ServiceLocatorImpl.java:906)
    at org.jvnet.hk2.internal.ServiceLocatorImpl.createAndInitialize(ServiceLocatorImpl.java:898)
    at org.glassfish.jersey.server.ApplicationHandler.createApplication(ApplicationHandler.java:300)
    at org.glassfish.jersey.server.ApplicationHandler.<init>(ApplicationHandler.java:279)
    at org.glassfish.jersey.servlet.WebComponent.<init>(WebComponent.java:302)
    at org.glassfish.jersey.servlet.ServletContainer.init(ServletContainer.java:167)
    at org.glassfish.jersey.servlet.ServletContainer.init(ServletContainer.java:349)
    at javax.servlet.GenericServlet.init(GenericServlet.java:160)
    at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1280)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1091)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5176)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5460)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3954)
    at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:426)
    at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1345)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1530)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1540)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1540)
    at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1519)
    at java.lang.Thread.run(Thread.java:724)

It seems that this is a problem with JDBC that uses autocommit option. This option can be disabled with something like this:

Connection connection = dataSource.getConnection();
Connection.setAutoCommit(false);        // Disables auto-commit.

but I don't know how to pass this option to flyway connection. Also if I solve this I think I will have problem with passing password to \c command.

Stilt

Answer 1

Flyway always operates within the database used in the jdbc connection string.

Once connected, all scripts run within a transaction. As CREATE DATABASE is not supported within transactions, you will not be able to accomplish what you want.

What you can do however, is create a schema instead. Flyway will even do this for you, if you point it at a non-existing one.

Festoonery

I end up using default postgres database and postgres user and use CREATE SCHEMA to create a new schema inside postgres db for now. Later for production I will create other DB and User. Thanks for your support and greater thanks for the flyway. – Stilt

It does seem a little weird to not support database creation. I'm not an expert in database migrations, but I'm looking to use a tool for database deployments. With Flyway I'm stuck needing separate script/solution/tool to handle the database part. – Seismograph

In MySQL, database == schema (they're the same) so this is basically supported. This helped me: dzone.com/articles/flyway-21-released-automatic – Faina

As @Axel mentioned, I've removed DB name from connection URL and used schemas option to provide the database name (mine is MySQL) and Flyway has created the missing database. Excellent. – Heartsease

If I am not wrong, this implies/requires your user (under which the rest of the 'regular' migrations are executed) to also have 'CREATE' privileges; shouldn't this practice be limited? I.e. shouldn't we prefer to create schemas under a privileged user and continue migrations as a "regular" one? In that case, @Gili's solution would be the proper way to deal with this, right? – Arms

FYI to others looking to do this in MySQL - you specify schemas not in the connection string, but as a separate parameter to the flyway task - see flywaydb.org/documentation/commandline/migrate – Syndic

Answer 2

I dont know if this is even possible to do in flyway.

Flyway is intended to connect to an already existing database (whether it is empty or not). It also would be a good practice to keep your database creation separate from your database migrations.

Ku

Answer 3

Here is a workaround that worked for me (assuming the use of the Maven plugin):

Configure the plugin with two executions. The first execution creates the database. The second execution migrates an existing database.

    <plugin>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-maven-plugin</artifactId>
        <version>${flyway.version}</version>
        <executions>
            <execution>
                <id>create-db</id>
                <goals>
                    <goal>migrate</goal>
                </goals>
                <configuration>
                    <driver>org.postgresql.Driver</driver>
                    <url>jdbc:postgresql://database-server/</url>
                    <user>postgres</user>
                    <password>password</password>
                    <placeholders>
                        <DATABASE.NAME>MyDatabase</DATABASE.NAME>
                    </placeholders>
                    <locations>
                        <location>com/foo/bar/database/create</location>
                    </locations>
                </configuration>
            </execution>
            <execution>
                <id>migrate-db</id>
                <goals>
                    <goal>migrate</goal>
                </goals>
                <configuration>
                    <driver>org.postgresql.Driver</driver>
                    <url>jdbc:postgresql://database-server/MyDatabase</url>
                    <user>postgres</user>
                    <password>password</password>
                    <locations>
                        <location>com/foo/bar/database/migrate</location>
                    </locations>
                </configuration>
            </execution>
        </executions>
        <dependencies>
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <version>${postgresql.version}</version>
            </dependency>
        </dependencies>
    </plugin>

Then add V1__Created_database.sql to the com/foo/bar/database/create directory. This file contains:

CREATE DATABASE ${DATABASE.NAME}

Bucksaw

not sure about having a requirement on requireThat;. how about using - assert (databaseName != null); ? – Delftware

@Delftware That'll work fine. requireThat will give you nicer error messages but obviously that's not required. – Bucksaw

@Bucksaw Maybe you could help me? For some reason flyway-maven-plugin can't connect to database using this code. It can't find <configuration> in block of executions and thorws "Can't connecto to database, check url, user, password". If I use only one <configuration> without executions it works fine. – Kindle

@EDWIN I suggest posting a separate question with a link to your full pom.xml file. You're probably pasting the right code but the enclosing section is wrong. – Bucksaw

Can you share your code? Everty time I run flyway:migrate I got org.flywaydb.core.api.FlywayException: Unable to connect to the database – Synchrocyclotron

@Synchrocyclotron Your command-line is wrong. Running mvn flyway:migrate does not trigger create-db and migrate-db executions. Please post a separate Maven question asking how to do this. – Bucksaw

Answer 4

Flyway can't create database for you. It can create schema if you didn't create one by

flyway.schemas: schema1,schema2

Fluoride

Answer 5

You can try what is suggested in this issue: https://github.com/flyway/flyway/issues/2556, use the createDatabaseIfNotExist parameter in the mysql url configured in flyway, as below:

  <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>6.4.1</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/<databaseName>?createDatabaseIfNotExist=true</url>
        <user>root</user>
        <password>root</password>
    </configuration>

Phonography

Answer 6

I have created a workaround for this scenario. I had a use case to create to database for each tenant in a multitenancy app setup.

Added the create db script as a "Repeatable Migrations" .

I have performed 2 flyway migrations to achieve :

1. First flyway.migrate() to create db from a default/master database : added a sql file R__create_db.sql which has content :

CREATE DATABASE "${db_name}";

2. Second flyway.migrate() using the datasource to the above new database to create tables and other operations.

Flannery

Would another option be to use the beforeConnect callback (documentation.red-gate.com/flyway/flyway-cli-and-api/concepts/…) to create the database? This way you wouldn't have to run migrate twice. – Swetlana

Thanks @DavidAtkinson for your response. Option 1: I used the datasource url configured with the new database, in that case I am getting database does not exist error. Option 2: Used datasource url without the database name, and getting an error "ERROR: cross-database references are not implemented" . – Flannery

@DavidAtkinson , is there any way to do in single flyway migrate other than option 1 or 2 as these are not working for this scenario ? – Flannery

Answer 7

If u have schema database creation command in V1 of your scripts, flyway can generate it but not database:

flyway -baselineOnMigrate=true -url=jdbc:mysql://localhost/ -schemas=test_db -user=root -password=root_pass -locations=filesystem:/path/to/scrips/ migrate

and similar to this in the script file:

DROP SCHEMA IF EXISTS `test_db` ;
CREATE SCHEMA `test_db` COLLATE utf8_general_ci ;

Halfway

Creation of schema is not the same as creation of database, though the two often seem synonymous – Astrolabe

FYI, about database versus schema… Some databases such as MySQL are limited to a single database, and within that one database you may create multiple schemas. More powerful databases such as Postgres support multiple databases, and each of those databases can have multiple schema inside. Formally, in the SQL standard, each database is called a “catalog”. For more info, see the Question, What's the difference between a catalog and a schema in a relational database? – Jack