Java 如何使用flyway创建数据库？

How to create a database with flyway?

提问by sajjadG

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:

我的场景：我试图在我的 Java 项目（使用 Jersey2.4 + tomcat 7 + PostgreSQL 9.3.1 + EclipseLink 的 RESTful 应用程序）中使用 flyway 来管理使用 git 的不同开发人员之间的更改。我写了我的 init 脚本并运行它：

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.doInTransaction(DbMigrate.java:252)
    at com.googlecode.flyway.core.command.DbMigrate.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.access0(DbMigrate.java:47)
    at com.googlecode.flyway.core.command.DbMigrate.doInTransaction(DbMigrate.java:189)
    at com.googlecode.flyway.core.command.DbMigrate.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.execute(Flyway.java:872)
    at com.googlecode.flyway.core.Flyway.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 autocommitoption. This option can be disabled with something like this:

似乎这是使用autocommit选项的JDBC 的问题。可以通过以下方式禁用此选项：

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 \ccommand.

但我不知道如何将此选项传递给飞路连接。此外，如果我解决了这个问题，我认为将密码传递给\c命令时会出现问题。

采纳答案by Axel Fontaine

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

Flyway 始终在 jdbc 连接字符串中使用的数据库内运行。

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.

连接后，所有脚本都在事务中运行。由于事务中不支持 CREATE DATABASE，您将无法完成您想要的操作。

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.

但是，您可以做的是创建一个模式。Flyway 甚至会为你做这件事，如果你把它指向一个不存在的。

回答by stikku

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.

Flyway 旨在连接到已经存在的数据库（无论它是否为空）。将数据库创建与数据库迁移分开也是一种很好的做法。

回答by Kiarash Zamanifar

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

如果您在脚本的 V1 中有模式数据库创建命令，flyway 可以生成它但不能生成数据库：

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 ;

回答by Gili

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

这是一个对我有用的解决方法（假设使用 Maven 插件）：

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.sqlto the com/foo/bar/database/createdirectory. This file contains:

然后添加V1__Created_database.sql到com/foo/bar/database/create目录中。该文件包含：

CREATE DATABASE ${DATABASE.NAME}

CREATE DATABASE ${DATABASE.NAME}