Using two different databases with identical hibernate mapping files

Question

Basically the idea is to use the same hibernate mapping files for two different underlying databases. In production the underlying database is MySQL5 and for testing purposes I'd like to use Apache Derby - in order to avoid setting up and maintaining various MySQL databases for testing purposes.

I hoped that just switching the DataSource's Driver and changing a few params would do the job, but I've already run into some minor difficulties. So there are in fact two questions. The first concrete question is:

I. Is it possible to tell Derby which datatype to use if a datatype is available in MySQL and is not in Derby. The mapping is as follows:

  <property name="about">
    <column name="`about`" not-null="false" sql-type="text"></column>
  </property>

Derby doesn't know the sql-type "text" so it refuses to create the table. It is Derby 10.4.2.0 and Hibernate 3.2.6. by the way.

II. What's your experience with using two different databases for testing and in production? I know there are some drawbacks that e.g. you can't test stored procedures or database specific queries - but on the other hand it makes testing easier and faster (if you finally got it running). What do you think?

Answer 1

Question #1 - don't specify sql type; use Hibernate type instead:

<property name="about" type="string" length="4096"/>

You can then extend Derby (or MySQL) dialect provided by Hibernate to map that type to appropriate DB type based on (un)specified length. Take a look at MySQLDialect for an example; it maps string type to either varchar or one of text types based on length.

Question #2 - did you mean using different databases for development and production? Because using different databases for testing and production is like playing a Russian roulette with fully loaded barrel - you ain't gonna win :-)

You always need to test all applicable deployment configurations. Using different DBs for development and production is not a bad approach if you indeed need to support both as it helps locate the portability issues early.

Answer 2

The primary reason to use a different type of database in testing is, in my opinion, to have faster unit tests where it is impractical to fake or mock the database (although when you can you should do the latter). Note that there should be a staging session where the tests can run against a production type of database to make certain you are not coding to the test database. It also forces you to code to a generic database -- sometimes that is a good idea and sometimes that is not.

Obviously if you need a database specific function (stored procedure or even special type) you should use the same type.

In the case of MySQL, you can set up tables in a test to be stored in memory. If you are likely to need MySQL specific items that is an option. If the goal is to be database generic than using a different database in development/low-level testing can be a good thing.

Answer 3

There should be no problem to using different database vendors in different environments (pay attention to ChssPly76's answer that TEST and PROD should be the same). Although I haven't tried Derby (yet).

Personally I like to use HSQLDB for the DEV environment. It's small, flexible, and easily portable, requiring little-to-no setup. Using a tool like Unitils to glue together Hibernate, DbUnit, and JUnit has worked exceedingly well for me. Before the JUnit tests are executed the HSQLDB is loaded with static test data. This allows for the data access layer JUnit tests to have assertions based on real data (it's loaded from some xml files sitting near the tests).

(One word of caution with Unitils is that the default "loadStrategy" is to drop all existing data before loading, so be careful where you point that thing).

Answer 4

In my experience, the more database implementations you test with, the sooner you find any places where you have accidentally depended on something which is not portable across databases.

If Derby is making your testing faster and easier, then that is a great result, since faster and easier testing encourages more testing.

I think that Derby is an excellent choice for testing with, as Derby tries very hard to conform to the SQL standard and to only support the syntax and behaviors that are specified in the standard, so this should help ensure that you are avoiding use of non-standard database features.

But I agree that as you get close to deploying your application, you need to be testing it using a configuration which is as close as possible to your intended deployment configuration.