Switching between embedded and external databases without drama sounds like a wish. Tonight it was a need. I was helping a team push a small Java app to a staging box while we still had late night coffee and a buzz from new toys like Tomcat 7 and Maven 3. Locally the app flew with an embedded H2. On staging it connected to a well aged MySQL. Same code. Same ORM. Different mood. The app booted then tripped over a TIMESTAMP default and a sneaky boolean. The error was tiny but the ripple was not. That is when it clicked for me. If we want small teams to move fast, we need to treat database switching as a first class habit, not a last minute trick.
This post is a field note on making that shift feel easy with HSQLDB and H2 on one side and your favorite server database on the other. It is full of things we keep learning the hard way, plus a way to set up your project so you can go from embedded to external with one property change and no surprises. The tech is simple. The craft is in the details.
Why bother with embedded at all
Speed. When an app starts with an in memory store, tests run faster and you do not wait on a remote box or a shared server. New hires can clone the repo and get a running system in minutes. You avoid the classic three day Dev Setup purgatory. Embedded also shines in repeatable builds on CI. Fresh database. Fresh data. No mystery state. That said, production truth lives in a server database like MySQL, PostgreSQL, SQL Server or Oracle. Your app needs to feel at home in both worlds.
The simple switch that is not so simple
On paper it looks easy. Change the JDBC URL. Swap a driver. Done. In practice small differences pile up. Here is what usually bites teams and how to avoid it when moving between HSQLDB, H2 and an external database.
Pick your embedded database with intent
H2 is fast and friendly. It has modes that mimic MySQL and Oracle. HSQLDB 2 brought a big step toward standard SQL and better date time handling compared to 1.8. Both are great for tests and local work. Decide based on your production target and your needs:
- SQL behavior: H2 can run in MySQL mode, PostgreSQL mode and others. Helpful but do not rely on it to paper over all differences.
- MVCC and concurrency: H2 and HSQLDB have different locking stories. They are fine for tests. For heavy parallel writes, trust your server database for final checks.
- File versus memory: For local dev, in memory is perfect. For a shared local database, H2 has a tiny TCP server mode. For HSQLDB, remember to call SHUTDOWN to flush on exit when using files.
Own your dialects and naming
If you use Hibernate or JPA, set the right Dialect. It defines limit and offset, identity columns, and function names. For H2 use org.hibernate.dialect.H2Dialect. For HSQLDB use the 2.x dialect. For MySQL or PostgreSQL pick their specific entries. Avoid relying on hbm2ddl to create prod schemas. It is nice for tests, not for real deployments.
One config switch for the whole stack
Put your database choice behind a single property. With Spring you can define two DataSource beans and activate one with a profile or a flag. With Maven you can keep two profiles that filter a properties file during build. The app code should not know which database it is talking to. The switch stays outside.
Schema changes that travel well
Track schema with a migration tool. Liquibase works well and has been stable. Put changesets in version control and run them on both embedded and external targets in CI. You catch drift early and you ditch that giant SQL file someone keeps editing by hand.
Portable SQL rules that save weekends
- Auto increment and identity: MySQL uses AUTO_INCREMENT. PostgreSQL likes sequences and SERIAL. H2 supports identity and sequences. HSQLDB supports identity since 2.0. Pick one pattern and map it via your ORM. Avoid hand written DDL with vendor only syntax.
- Booleans: MySQL sometimes stores booleans as TINYINT. PostgreSQL has a real boolean. H2 and HSQLDB both have boolean. Make sure your ORM mapping is boolean and let the dialect translate.
- Date and time: Precision differs. Fractional seconds can vanish. H2 defaults to good precision. HSQLDB 2 is better than 1.8. Be careful with CURRENT_TIMESTAMP defaults. Test reads and writes across both engines.
- Empty string and null: Oracle treats empty string as null. Others do not. If you plan to switch to Oracle some day, write tests for this now.
- Quoted identifiers and case: Quoted names are case sensitive. Unquoted names may fold to upper or lower case depending on the engine. Stick to lowercase unquoted in DDL and mapping files.
- Upsert: Each vendor is unique. MySQL has INSERT ON DUPLICATE KEY UPDATE. PostgreSQL has not had a native upsert for years. H2 supports MERGE. Use ORM save and update or write two step logic.
- Limit and offset: MySQL uses LIMIT. PostgreSQL uses LIMIT and OFFSET. H2 supports both. Let the ORM handle paging when possible.
- Foreign keys and deferrable: MySQL does not support deferrable. Do not depend on it if MySQL is a target.
- Text and blobs: Treat long text as CLOB and binary as BLOB in mappings. Let the dialect pick the right column type.
Pools and connections
For embedded you might not need a pool at all. For external databases, start small with DBCP or BoneCP. Keep validation queries short and set a sane timeout. Treat connection limits on shared staging servers with care. Leaks that never show up locally will show up under load.
Repeat the switch in CI
Create two build jobs. One runs the test suite on H2 or HSQLDB. Another runs the exact same suite against the external database. Use the same migrations. If you cannot afford it on every push, run the external job nightly. The first time it fails you will feel very smart for setting it up.
What this means for your team
For leads and product folks, the point is simple. Fast local feedback plus faithful production behavior is not a luxury. It is how you keep release risk low while shipping weekly or even daily. If the team can swap databases with a flag and keep tests green in both places, you cut down surprises and you keep work moving.
There is also a culture win. When every developer can boot the app with an embedded database and no extra setup, you remove a barrier to entry. When the same developer can run a quick pass on a real server database before merging, you keep quality high without adding meetings or extra steps.
Budget wise this is cheap. The tools are free. The hard part is a bit of discipline. Pick one embedded database. Define a clean switch. Add a second CI job. Agree on a short list of SQL rules. Write a page in the repo with the setup. That is it. You will pay for this work only once. You will save time on every release.
A quick checklist you can use this week
- Choose H2 or HSQLDB and standardize on it for local work.
- Create a single property switch for your DataSource. No code changes needed when switching.
- Set the correct Hibernate dialects for both embedded and external targets.
- Adopt Liquibase for schema changes and run it on both databases.
- Turn on a second CI job that runs tests on the external database once a day.
- Write down your portable SQL rules and clean up queries that break them.
- Smoke test paging and date time fields in both databases.
- Decide on a connection pool for external only. Keep embedded simple.
Your turn
By Friday, try this experiment. Point your app to H2 or HSQLDB for local work. Then flip to your server database and run the same tests. Note every bump. Fix the first three. Commit the switch and the docs. Share the results with your team. My bet is you will cut your next release night by hours.
If you have a story where a tiny database detail took down a demo at the worst moment, send it my way. The more we share these gotchas, the more we turn them into boring footnotes. And boring is good when it comes to data.
Here is to apps that feel at home in your laptop and on a server, and to teams that can switch between embedded and external databases with a smile instead of a sigh.