title: Multi-tenancy using single database multiple schema with Hibernate and Spring Boot saving data to wrong schema
body: <p>I am trying to seed my multiple tenant (single database, multiple schemas) system with data but running into an issue that wasn't present when I was using the same code with a single database. I fully expect during my research that I have missed something obvious.</p>
  <p>Each schema will contain the exact same table structure.</p>
  <p>Here is my Tenant Context</p>
  <pre><code>public class TenantContext {
  
      public static final String DEFAULT_TENANT_IDENTIFIER = &quot;public&quot;;
  
      private static final ThreadLocal&lt;String&gt; TENANT_IDENTIFIER = new ThreadLocal&lt;&gt;();
  
      public static void setTenant(String tenantIdentifier) {
          TENANT_IDENTIFIER.set(tenantIdentifier);
      }
  
      public static void reset(String tenantIdentifier) {
          TENANT_IDENTIFIER.remove();
      }
  
      @Component
      public static class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {
          @Override
          public String resolveCurrentTenantIdentifier() {
              String currentTenantId = TENANT_IDENTIFIER.get();
              return currentTenantId != null ?
                      currentTenantId :
                      DEFAULT_TENANT_IDENTIFIER;
          }
  
          @Override
          public boolean validateExistingCurrentSessions() {
              return false;
          }
      }
  }
  </code></pre>
  <p>And my HibernateConfig</p>
  <pre><code>@Configuration
  public class HibernateConfig {
  
      @Autowired
      private JpaProperties jpaProperties;
  
      @Bean
      public JpaVendorAdapter jpaVendorAdapter() {
          return new HibernateJpaVendorAdapter();
      }
  
      @Bean
      public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
               MultiTenantConnectionProvider multiTenantConnectionProvider, CurrentTenantIdentifierResolver currentTenantIdentifierResolver) {
  
          Map&lt;String, Object&gt; jpaPropertiesMap = new HashMap&lt;&gt;();
          jpaPropertiesMap.putAll(jpaProperties.getProperties());
          jpaPropertiesMap.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
          jpaPropertiesMap.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
          jpaPropertiesMap.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, TenantContext.TenantIdentifierResolver.class);
  
          LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
          entityManagerFactoryBean.setDataSource(dataSource);
          entityManagerFactoryBean.setPackagesToScan(UppStudentAppBeApplication.class.getPackage().getName());
          entityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter());
          entityManagerFactoryBean.setJpaPropertyMap(jpaPropertiesMap);
  
          return entityManagerFactoryBean;
      }
  }
  </code></pre>
  <p>And my TenantConenctionProvider</p>
  <pre><code>@Component
  public class TenantConnectionProvider implements MultiTenantConnectionProvider {
  
      private static Logger logger = LoggerFactory.getLogger(TenantConnectionProvider.class);
  
      @Autowired
      private DataSource dataSource;
  
      public TenantConnectionProvider(DataSource dataSource) {
          this.dataSource = dataSource;
      }
  
      @Override
      public Connection getAnyConnection() throws SQLException {
          return dataSource.getConnection();
      }
  
      @Override
      public void releaseAnyConnection(Connection connection) throws SQLException {
          connection.close();
      }
  
      @Override
      public Connection getConnection(String tenantIdentifier) throws SQLException {
          logger.info(&quot;Get connection for tenant  &quot; + String.join(&quot;:&quot;, tenantIdentifier ));
          final Connection connection = getAnyConnection();
          try {
              //connection.createStatement().execute( String.format(&quot;SET SCHEMA \&quot;%s\&quot;;&quot;, tenantIdentifier));
              connection.setSchema(tenantIdentifier);
          } catch ( SQLException e ) {
              throw new HibernateException(
                      &quot;Could not alter JDBC connection to specified schema [&quot; +
                              tenantIdentifier + &quot;]&quot;,
                      e
              );
          }
          return connection;
      }
  
      @Override
      public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
          try {
              //connection.createStatement().execute( String.format(&quot;SET SCHEMA \&quot;%s\&quot;;&quot;, TenantContext.DEFAULT_TENANT_IDENTIFIER) );
              connection.setSchema(TenantContext.DEFAULT_TENANT_IDENTIFIER);
          } catch ( SQLException e ) {
              throw new HibernateException(
                      &quot;Could not alter JDBC connection to specified schema [&quot; +
                              tenantIdentifier + &quot;]&quot;,
                      e
              );
          }
          releaseAnyConnection(connection);
      }
  
      @Override
      public boolean supportsAggressiveRelease() {
          return false;
      }
  
      @Override
      public boolean isUnwrappableAs(Class unwrapType) {
          return false;
      }
  
      @Override
      public &lt;T&gt; T unwrap(Class&lt;T&gt; unwrapType) {
          return null;
      }
  }
  </code></pre>
  <p>I call my seeding class that builds out my tenants and schemas using flyway migration.</p>
  <p>I then try to loop through the saved tenants switching the TenantContext. Which when debugging appears to work. However when I try and do anything with the repo I get the following error.</p>
  <blockquote>
  <p>o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column campus0_.createdat does not exist<br />
  Hint: Perhaps you meant to reference the column &quot;campus0_.created_at&quot;.<br />
  Position: 32</p>
  </blockquote>
  <p>As I said earlier this worked fine previously when it was a single database and schema. I am not 100% sure on where I have gone wrong. Am I supposed to register the schemas some how? If so how can I onboard new tenants without redeploying? Should I use a custom query at this stage that uses the schema in the repo?</p>
  <p>Thank you in advance for any help or advice.</p>
  <p><strong>EDIT 1</strong>
  So I have now got past my initial hurdle by checking the hibernate properties so by changing the hibernate config as follows</p>
  <pre><code>@Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
               MultiTenantConnectionProvider multiTenantConnectionProvider,
               HibernateProperties hibernateProperties) {
  
          Map&lt;String, Object&gt; jpaPropertiesMap = hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
          //jpaPropertiesMap.putAll(jpaProperties.getProperties());
          jpaPropertiesMap.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
          jpaPropertiesMap.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
          jpaPropertiesMap.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, TenantContext.TenantIdentifierResolver.class);
  
          LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
          entityManagerFactoryBean.setDataSource(dataSource);
          entityManagerFactoryBean.setPackagesToScan(UppStudentAppBeApplication.class.getPackage().getName());
          entityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter());
          entityManagerFactoryBean.setJpaPropertyMap(jpaPropertiesMap);
  
          return entityManagerFactoryBean;
  }
  </code></pre>
  <p>This has now removed the above naming error. However now it is saving to my default schema rather than the schema set in the TenantIdentifierResolver.</p>