Managing multiple DBs in a Spring project

During our daily Rails development we use 2 local databases. The first a development DB that can be used to view the features we’re implementing. The other a test DB that is usually empty or possibly seeded with some fixtures. The test DB is used to run our test suite against. Schema changes are made in the form of migration scripts to the development DB then the schema is copied over wholesale to the test DB.
In a previous blogpost I talked out using flyway to manage DB schema migrations in a single DB schema. I’m going to suggest a way to manage the multiple schemas we’re used to from the rails world.

I’m going to use maven profiles to manage the different DB specifics. Then show how the DBs can be migrated using the command line and IntelliJ integration.

There are a few steps to this. Here they are outlined:

• Define DB connection details in Spring context
• Interpolate connection details using application.properties
• Interpolate connection differences using maven profiles

First of all the persistence.xml and Spring context

Persistence.xml has no connection details at all it is all in the Spring context:

<persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL">/>

The connection details live in the Spring context. Notice the DB connection details referenced as a variable ${db.url}.

<bean id="dataSourceRef" name="dataSource"
  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName" value="org.postgresql.Driver"/>
  <property name="url" value="${db.url}"/>n<property name="username" value="pivotal"/>
  <property name="password" value=""/>
  <property name="maxActive" value="-1"/>
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="dataSourceRef"/>
  <property name="jpaVendorAdapter">
    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
      <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQLDialect"/>
      <property name="database" value="POSTGRESQL"/>
      <property name="showSql" value="true" />
      <property name="generateDdl" value="true"/>
    </bean>
  </property>
  </bean>
</beans>

The connection details are then defined in src/main/resources/application.properties

db.url=${database.url}

That again is another variable, that is then defined in the appropriate profile in maven’s pom.xml

<profiles>
  <profile>
    <activation><activeByDefault>true</activeByDefault></activation>
    <id>spring-dev</id>
    <properties>
      <database.url>jdbc:postgresql://localhost:5432/spring_dev</database.url>
    </properties>
  </profile>
  <profile>
    <id>spring-test</id>
    <properties>
      <database.url>jdbc:postgresql://localhost:5432/spring_test</database.url>
    </properties>
  </profile>
</profiles>

The maven resources also needs to point to the location of the application.properties file

<build>
  <resources>
    <resource>
      <directory>src/main/resources</directory>
      <filtering>true</filtering>
    </resource>
  </resources>
</build>

With all of those pieces in place we can finally use the profiles both from the command line and from IntelliJ.
Migrate the development database then the test database using flyway

mvn compile flyway:migrate -P spring-dev
mvn compile flyway:migrate -P spring-test

Within IntelliJ the profile can be selected in the Maven control.

I’ve only shown how to migrate the different DBs individually but obviously these profiles can be used to define any differences between the different environments used to build, test and deploy an application.

The git source is available here