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

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 2

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.