Hibernate, multi-tenancy and auto-update database schema

Want to enjoy the advantages (and disadvantages) of auto-updating the database schema when using Hibernate, but do you have a multi-tenant architecture? Welcome under cat.

What is the problem?

If your application uses Hibernate and you need to provide automatic updating of the database schema when using a multi-tenant architecture with a schema-per-tenant strategy, you cannot simply enable the option to auto-update the scheme:

<property name="hbm2ddl.auto">update</property> 

Let us see why. It is assumed that the reader has an idea of ​​how Hibernate multi-tenancy works.

Configuration

DB: Postgres 9.2
ORM: Hibernate 4.3.7. Final + Envers
Multi-tenancy: schema-per-tenant with shared JDBC data source.
')
Hibernate basic settings:

 <!--<property name="hbm2ddl.auto">update</property>--> <property name="connection.datasource">java:/portalDS</property> <property name="hibernate.dialect">by.tychina.PostgreSQL9MultiTenantDialect</property> <property name="hibernate.multiTenancy">SCHEMA</property> <property name="hibernate.multi_tenant_connection_provider">by.tychina.tenant.HibernateMultiTenantConnectionProvider</property> <property name="hibernate.tenant_identifier_resolver">by.tychina.tenant.HibernateTenantIdentifierResolver</property> <property name="org.hibernate.envers.audit_strategy">org.hibernate.envers.strategy.ValidityAuditStrategy</property> 

Helper classes

The tenant class is used to store the tenant identifier:

 package by.tychina.tenant; /** * @author Sergey Tychina */ public class TenantId { /** * Represents database schema name */ private String tenantId; public TenantId(String tenantId) { this.tenantId = tenantId; } public String getTenantId() { return tenantId; } } 

where the tenantId field is the name of the schema in the database.

The Persistence class, responsible for initializing the Hibernate configuration and initializing the SessionFactory:

 package by.tychina; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; /** * @author Sergey Tychina */ public class Persistence { private final Configuration configuration; private final SessionFactory sessionFactory; private static Persistence instance = null; private Persistence() { configuration = new Configuration(); configuration.configure(); sessionFactory = configuration.buildSessionFactory(); } public static synchronized Persistence getInstance() { if (instance == null) { instance = new Persistence(); } return instance; } public Configuration getConfiguration() { return configuration; } public SessionFactory getSessionFactory() { return sessionFactory; } } 

The ConnectionProvider class needed to open a connection to the database using the data source:

 package by.tychina; import by.tychina.tenant.TenantId; import javax.naming.InitialContext; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; /** * @author Sergey Tychina */ public class ConnectionProvider { private static final String DATASOURCE_JNDI_NAME = "java:/portalDS"; private static DataSource dataSource = null; private static synchronized DataSource getDataSource() { if (dataSource == null) { try { dataSource = (DataSource) new InitialContext().lookup(DATASOURCE_JNDI_NAME); } catch (Exception e) { throw new RuntimeException(e); } } return dataSource; } public static Connection getConnection() { try { return getDataSource().getConnection(); } catch (SQLException e) { throw new RuntimeException("Unable to get connection", e); } } public static Connection getConnection(TenantId tenantId) { Connection connection = getConnection(); try { connection.createStatement().execute("SET SCHEMA '" + tenantId.getTenantId() + "'"); } catch (SQLException e) { try { connection.close(); } catch (SQLException e1) { e1.printStackTrace(); } throw new RuntimeException("Could not alter connection to '" + tenantId.getTenantId() + "' schema", e); } return connection; } } 

Hibernate requires a class that will open a connection to the database with the specified tenant and without it (option hibernate.multi_tenant_connection_provider):

 package by.tychina.tenant; import by.tychina.ConnectionProvider; import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider; import java.sql.Connection; import java.sql.SQLException; /** * @author Sergey Tychina */ public class HibernateMultiTenantConnectionProvider implements MultiTenantConnectionProvider { @Override public Connection getAnyConnection() throws SQLException { return ConnectionProvider.getConnection(); } @Override public void releaseAnyConnection(Connection connection) throws SQLException { connection.close(); } @Override public Connection getConnection(String tenantIdentifier) throws SQLException { return ConnectionProvider.getConnection(new TenantId(tenantIdentifier)); } @Override public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException { releaseAnyConnection(connection); } @Override public boolean supportsAggressiveRelease() { return false; } @Override public boolean isUnwrappableAs(Class unwrapType) { return false; } @Override public <T> T unwrap(Class<T> unwrapType) { return null; } } 

Hibernate needs the class needed to determine the current tenant (the hibernate.tenant_identifier_resolver option):

 package by.tychina.tenant; import org.hibernate.context.spi.CurrentTenantIdentifierResolver; /** * @author Sergey Tychina */ public class HibernateTenantIdentifierResolver implements CurrentTenantIdentifierResolver { @Override public String resolveCurrentTenantIdentifier() { // Put some logic here to get current transaction tenant id // Use ThreadLocal for example to store TenantId when starting a transaction TenantId transactionTenantId = null; return transactionTenantId.getTenantId(); } @Override public boolean validateExistingCurrentSessions() { return true; } } 

Postres dialect to support multi-tenancy

In case class mapping uses sequence to generate id, the standard PostgreSQL9Dialect supplied by Hibernate will not allow to correctly generate update scripts for creating a sequence. The problem lies in the following method of the org.hibernate.dialect.PostgreSQL81Dialect class:

 public String getQuerySequencesString() { return "select relname from pg_class where relkind='S'"; } 

This method is called by Hibernate to get the existing sequence to determine which update scripts to generate. The problem is that this SQL query returns a sequence for all the schemas, and not only for the one for which we generate update scripts. The dialect created by us deals with the solution of this problem:

 package by.tychina; import org.hibernate.dialect.PostgreSQL9Dialect; /** * @author Sergey Tychina */ public class PostgreSQL9MultiTenantDialect extends PostgreSQL9Dialect { /** * Query to get sequences for current schema ONLY. Original method returns query to get all sequences (in all schemas) * that breaks multi-tenancy. */ @Override public String getQuerySequencesString() { return "SELECT c.relname FROM pg_catalog.pg_class c " + "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " + "WHERE c.relkind IN ('S','') " + "AND n.nspname <> 'pg_catalog' " + "AND n.nspname <> 'information_schema' " + "AND n.nspname !~ '^pg_toast' " + "AND pg_catalog.pg_table_is_visible(c.oid)"; } } 

This SQL query was received when running psql with the -E parameter and executing the \ ds command:

 demo_portal=> \ds ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('S','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** 

In the Hibernate configuration, specify the dialect:

 <property name="hibernate.dialect">by.tychina.PostgreSQL9MultiTenantDialect</property> 

Auto-update schema

In the Hibernate configuration, the hbm2ddl.auto option is commented out, since Hibernate cannot update the database schema using the selected multi-tenancy strategy. To automatically update the schema, we will write our implementation (almost completely copied from Hibernate source codes):

 package by.tychina.schema; import by.tychina.ConnectionProvider; import by.tychina.Persistence; import by.tychina.tenant.TenantId; import org.hibernate.cfg.Configuration; import org.hibernate.cfg.Environment; import org.hibernate.dialect.Dialect; import org.hibernate.engine.jdbc.internal.FormatStyle; import org.hibernate.engine.jdbc.internal.Formatter; import org.hibernate.tool.hbm2ddl.DatabaseMetadata; import org.hibernate.tool.hbm2ddl.SchemaUpdateScript; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.util.List; /** * @author Sergey Tychina */ public class MultiTenantHibernateSchemaUpdate { public static void execute(TenantId tenantId) { Connection connection = ConnectionProvider.getConnection(tenantId); Configuration configuration = Persistence.getInstance().getConfiguration(); String originalSchema = configuration.getProperty(Environment.DEFAULT_SCHEMA); Statement stmt = null; try { Dialect dialect = Dialect.getDialect(configuration.getProperties()); Formatter formatter = FormatStyle.DDL.getFormatter(); configuration.setProperty(Environment.DEFAULT_SCHEMA, tenantId.getTenantId()); DatabaseMetadata meta = new DatabaseMetadata(connection, dialect, configuration); stmt = connection.createStatement(); List<SchemaUpdateScript> scripts = configuration.generateSchemaUpdateScriptList(dialect, meta); for (SchemaUpdateScript script : scripts) { String formatted = formatter.format(script.getScript()); stmt.executeUpdate(formatted); } } catch (SQLException e) { throw new RuntimeException(e); } finally { if (originalSchema != null) { configuration.setProperty(Environment.DEFAULT_SCHEMA, originalSchema); } else { configuration.getProperties().remove(Environment.DEFAULT_SCHEMA); } try { if (stmt != null) { stmt.close(); } connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } 

Operating procedure:

• Set the current schema in a hibernate configuration using
  

   configuration.setProperty(Environment.DEFAULT_SCHEMA, tenantId.getTenantId()); 

• Generate scripts to update the schema using
  

   List<SchemaUpdateScript> scripts = configuration.generateSchemaUpdateScriptList(dialect, meta); 

• Apply scripts
  

   for (SchemaUpdateScript script : scripts) { String formatted = formatter.format(script.getScript()); stmt.executeUpdate(formatted); } 

• Restore the original name of the circuit in the configuration
  

   if (originalSchema != null) { configuration.setProperty(Environment.DEFAULT_SCHEMA, originalSchema); } else { configuration.getProperties().remove(Environment.DEFAULT_SCHEMA); } 

When the application starts, after initializing Hibernate, it is enough to call

 MultiTenantHibernateSchemaUpdate.execute(tenantId); 

and the schema will be updated.

Thank.