Search code examples
postgresqljooqmicronaut

Error CREATE DATABASE cannot run inside a transaction block


I am trying to create a database from a Java application running in Micronaut framework using jOOQ.

The code

contextSupplier.get().createDatabaseIfNotExists(this.databaseName).execute();

with a PostgreSQL datasource fails with:

org.jooq.exception.DataAccessException: SQL [do $$ begin create database "mydb"; exception when sqlstate '42P07' then null; end $$]; ERROR: CREATE DATABASE cannot run inside a transaction block

Is there a simple way to turn transactions off within jOOQ just for this statement?


Solution

  • As of jOOQ 3.14, the DSLContext.createDatabaseIfNotExists() method is supported by these dialects (see its @Support annotation):

    @Support({AURORA_POSTGRES,COCKROACHDB,MARIADB,MEMSQL,MYSQL,SQLDATAWAREHOUSE,SQLSERVER})
    

    So, this statement is not yet supported in jOOQ for PostgreSQL - if I remember correctly, for this precise reason. Perhaps there's a different way to emulate the statement, but it hasn't been implemented yet.