3.3.1.1. 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

If you are using Studio, add the currentSchema connection parameter to the Connection params field in the Data Store Properties window. Studio will automatically update project files according to your data source configuration method. Otherwise, specify the connection parameter manually as described below.

If you configure the data source in the application, add the full URL property, for example:

cuba.dataSource.jdbcUrl = jdbc:postgresql://localhost/demo?currentSchema=my_schema

If you obtain the data source from JNDI, add the currentSchema parameter to the connection URL in the data source definition (for Tomcat it is in context.xml) and to the connectionParams property of the createDb and updateDb Gradle tasks, for example:

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

Now 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'

If you are using Studio, add the currentSchema connection parameter to the Connection params field in the Data Store Properties window. Studio will automatically update project files according to your data source configuration method. Otherwise, specify the connection parameter manually as described below.

If you configure the data source in the application, add the full URL property, for example:

cuba.dataSource.jdbcUrl = jdbc:sqlserver://localhost;databaseName=demo;currentSchema=my_schema

If you obtain the data source from JNDI, add the currentSchema parameter to the connection URL in the data source definition (for Tomcat it is in context.xml) and to the connectionParams property of the createDb and updateDb Gradle tasks.

task updateDb(dependsOn: assembleDbScripts, type: CubaDbUpdate) {
    dbms = 'mssql'
    dbmsVersion = '2012'
    host = 'localhost'
    dbName = 'demo'
    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.