CODEWITHSUNDEEP
postgresqlgrailsgrails-ormmulti-tenant
jondow
jondow

Reputation: 282

Grails 3.3.x does not seem to support schema-per-tenant with PostgreSQL

Has anybody managed to use Grails 3.3.x with multi-tenancy and PostgreSQL? The problem appears to be with the SQL syntax used in DefaultSchemaHandler.groovy class. For PostgreSQL the correct syntax is: SET SCHEMA 'schemaName' but DefaultSchemaHandler omits the single ticks: SET SCHEMA schemaName which causes the startup to fail with:

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "information_schema"

Further exceptions list other schema's in the PG DB until startup fails completely. There doesn't seem to be any way to override DefaultSchemaHandler, nor anybody else battling with this issue, which surprises me.

I should add that this all works fine in Grails 3.2.11 which I was busy porting from.

Upvotes: 1

Views: 280

Answers (1)

jondow
jondow

Reputation: 282

I solved my own problem by implementing my own SchemaHandler as follows:

@CompileStatic
@Slf4j
class PostgreSQLSchemaHandler extends DefaultSchemaHandler {

    PostgreSQLSchemaHandler() {
        super("SET SCHEMA '%s'", "CREATE SCHEMA '%s'", "public")
    }
}

And then in application.yml I added schemaHandler as follows:

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: org.postgresql.Driver
    dialect: org.hibernate.dialect.PostgreSQLDialect
    schemaHandler: com.mypackage.PostgreSQLSchemaHandler

Upvotes: 4

Related Questions