When an embedded database is enough for shipping
You want to ship. You do not want to babysit a server. For a lot of apps right now the right move is to bundle an embedded database and call it a day. That is where HSQLDB and its cousin H2 shine.
When is embedded enough?
Think desktop tools and small web apps that live on a single node. Think admin consoles, prototypes that found a market, plugins and internal dashboards. If your data fits on disk and your traffic looks like a steady stream not a fire hose, embedded will save you time.
What are you trading for that speed to ship?
You trade the ability to split database and app server across machines. You accept process local access and a file on disk that your app owns. You get fewer moving parts, which means fewer late night calls. You also accept that big joins and wild concurrency will ask for more than a jar can give.
So why HSQLDB specifically?
HSQLDB is tiny, mature and friendly to Java apps. It supports in memory and file based modes, good SQL coverage, triggers and sequences. You drop a jar, point a JDBC URL at a folder, and you are running. For a lot of teams that is the win.
How does HSQLDB compare with H2?
H2 is fast and has great tooling like a built in console. HSQLDB leans on standards and consistency and plays nicely with strict schemas. Both are strong choices. If you value a bundled console and quick prototyping you might lean H2. If your app cares about predictable SQL behavior, HSQLDB is a safe bet.
What are the setup basics that keep you out of trouble?
Pick a clear database path outside temp folders. Use a JDBC URL with file mode for local installs or memory mode for tests. Wire a migration tool like Flyway or Liquibase so schema changes travel with your build. Turn on SQL logging in dev so you catch odd queries long before your users do.
Any quirks I should remember on day one?
Watch autocommit. Wrap writes in transactions. Set a sane isolation level for your workload. Be explicit with timestamps and time zones. Use sequences or identity for primary keys and test inserts under concurrency. Keep varchar sizes realistic and use indexes where your queries spend time.
How do I back up and upgrade without drama?
For small installs a simple file copy with the app stopped is fine. For live instances run SCRIPT to dump SQL and keep it with your build artifacts. When you bump the database engine version, run your migrations on a staging copy first and rehearse the restore path. Boring backups are good backups.
What about security in this setup?
Keep the database embedded and local. Do not expose the server mode unless you must. Store credentials outside your repo and rotate them with your release flow. If you need to protect files at rest, look at file system encryption on the host and keep OS permissions tight.
Can I tune it a bit without going down a rabbit hole?
Yes. Set cache sizes based on your working set. For a better user feel, let the engine batch writes by tuning write delay, then measure. Index the columns used in where and order by. Keep an eye on the log and vacuum or checkpoint during maintenance windows to keep startup quick.
How does this play with tests and continuous delivery?
Run in memory for unit tests and file mode for integration tests. Load schema on startup and add seed data for smoke tests. Your CI can spin the app and run through the whole flow with no external services. That makes your build green for the right reasons.
When should I move out of embedded land?
When your data set grows beyond a single disk, when you need multiple app nodes writing at the same time, or when reporting jobs start to steal oxygen from live traffic. At that point PostgreSQL or MySQL is the next stop. If you used JDBC and migrations, the exit is not a cliff.
What is the mental model that keeps this sane?
Think of HSQLDB or H2 as a reliable library that stores your state. It sits inside your app, keeps your users happy, and lets you ship without a full time database server. Plan the escape hatch from day one and you can change gears without blowing up the release train.
Bottom line if your app is just getting out the door, an embedded database is not a shortcut, it is the correct move. Ship with HSQLDB or H2, bake in migrations and backups, and keep an exit path ready. That buys you focus on features, not fire drills.