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.

Axel Fontaine · Accepted Answer · 2013-11-11T10:43:47.560

54

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.

edited Nov 11 '13 at 10:43

answered Nov 07 '13 at 07:56

Axel Fontaine

34,542
16
106
137

3

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. – sajjadG Nov 07 '13 at 09:10
7

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. – Jason Capriotti Jun 04 '15 at 17:13
2

In MySQL, database == schema (they're the same) so this is basically supported. This helped me: https://dzone.com/articles/flyway-21-released-automatic – Ryan Shillington Feb 02 '17 at 18:13
1

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. – manikanta Dec 07 '17 at 12:17
1

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? – Octavian Theodor Mar 02 '18 at 01:12
1

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 https://flywaydb.org/documentation/commandline/migrate – Bobby Jul 26 '18 at 14:43

score 12 · Answer 2 · answered Nov 06 '13 at 21:42

12

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.

answered Nov 06 '13 at 21:42

stikku

536
7
27

Gili · Answer 3 · 2019-01-20T17:51:10.920

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}

not sure about having a requirement on requireThat;. how about using - assert (databaseName != null); ? — denov, Jul 01 '17 at 21:14
@denov That'll work fine. [requireThat](https://bitbucket.org/cowwoc/requirements/) will give you nicer error messages but obviously that's not required. — Gili, Jul 02 '17 at 04:26
@Gili Maybe you could help me? For some reason flyway-maven-plugin can't connect to database using this code. It can't find `` in block of executions and thorws "Can't connecto to database, check url, user, password". If I use only one `` without executions it works fine. — Nikita Kalugin, May 29 '19 at 04:30
@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. — Gili, May 30 '19 at 19:00
Can you share your code? Everty time I run flyway:migrate I got `org.flywaydb.core.api.FlywayException: Unable to connect to the database` — tristobal, Jul 13 '20 at 16:50
@tristobal 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. — Gili, Jul 14 '20 at 20:47

score 1 · Answer 4 · answered Oct 01 '20 at 08:09

1

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

flyway.schemas: schema1,schema2

answered Oct 01 '20 at 08:09

Omar Ghazi

161
3
7

score 1 · Answer 5 · edited Aug 18 '21 at 03:35

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>

Kiarash Zamanifar · Answer 6 · 2017-05-13T11:27:59.483

-2

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 ;

edited May 13 '17 at 11:27

answered Oct 21 '15 at 23:48

Kiarash Zamanifar

647
1
9
19

4

Creation of schema is not the same as creation of database, though the two often seem synonymous – Mitch Kent Jun 27 '16 at 12:53
3

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](https://www.postgresql.org) 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?](http://stackoverflow.com/q/7022755/642706) – Basil Bourque May 12 '17 at 06:07

How to create a database with flyway?

6 Answers6

Linked

Related