Connecting to a Non-Default Database Schema - CUBA Platform. Developer’s Manual

7.5. Connecting to a Non-Default Database Schema

PostgreSQL and Microsoft SQL Server support connection to a specific database schema. By default, the schema is public on PostgreSQL and dbo on SQL Server.

PostgreSQL

In order to use non-default schema on PostgreSQL, specify the currentSchema parameter in the connectionParams property of the createDb and updateDb Gradle tasks, for example:

task createDb(dependsOn: assembleDbScripts, type: CubaDbCreation) {
    dbms = 'postgres'
    host = 'localhost'
    dbName = 'my_db'
    connectionParams = '?currentSchema=my_schema'
    dbUser = 'cuba'
    dbPassword = 'cuba'
}

If you are using Studio, add this connection parameter to the Connection params field on the Project properties page. Studio will update build.gradle automatically. After that, you can update or re-create the database, and all tables will be created in the specified schema.

Microsoft SQL Server

On Microsoft SQL Server, providing a connection property is not enough, you have to link the schema with the database user. Below is an example of creating a new database and using a non-default schema in it.

Create a login:

create login JohnDoe with password='saPass1'

Create a new database:
```
create database my_db
```

Connect to the new database as sa, create a schema, then create a user and give him owner rights:

create schema my_schema

create user JohnDoe for login JohnDoe with default_schema = my_schema

exec sp_addrolemember 'db_owner', 'JohnDoe'

Now you should specify the currentSchema parameter in the connectionParams property of the updateDb Gradle task (or in Studio project properties). In fact, this property is not handled by SQL Server JDBC driver, but it tells Studio and CUBA Gradle plugin what schema to use.

task updateDb(dependsOn: assembleDbScripts, type: CubaDbUpdate) {
    dbms = 'mssql'
    dbmsVersion = '2012'
    host = 'localhost'
    dbName = 'my_db'
    connectionParams = ';currentSchema=my_schema'
    dbUser = 'JohnDoe'
    dbPassword = 'saPass1'
}

Keep in mind, that you cannot re-create the SQL Server database from Studio or by executing createDb in the command line, because non-default schema requires association with a user. But if you run Update database in Studio or updateDb in the command line, all required tables will be created in the existing database and specified schema.