This is a bit OT for this list but I wanted to see what others people were doing.
I'm using migrations to manage my database schema.
I'm also using mybatis with mysql db (ISAM engine).
When I write my unit tests for my mappers I either need to go against an actual database or a mock database or an in memory db.
All 3 options have their pros and cons but I opted for the last since it gives me the most pros for the least cons IMO.
I get to test the actual sql statements for correctness as well as potentially test transaction handling etc.
I'm having a problem now with migrations since I have no idea how to *cleanly* invoke it to create the in memory db for me from another module (I'm using maven). In addition to that I am having problems because the db syntax is different from mysql to hsqldb. In the past using hibernate to autogenerate my db worked for this because it knew the syntax based on db drivers. I could replicate this using a separate migrations for hsqldb (sql mappers theoretically won't have to change since I am using standard ansi sql syntax).
The duplication of code there feels hackish (although I could autogenerate).
I was wondering if anyone has a testing path that they would like to share.