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.

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.

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.

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}

Answer 4

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 ;