Should I be autocreating my DB tables in code, or pre-init during install?

Question

I have a Java application that uses an Apache Derby database with the embedded driver. Right now, in the installation, I run the SQL create scripts out of process.

So when the application starts up, the tables are already existing. Recently I've started to think it would be better to have my SQL script in the classpath or something, load it, and execute all the statements so that the tables are created upon startup. The database itself can be autocreated if missing, so this seems like it might be a less error-prone way to bootstrap the tables.

My question is: Am I likely to run into any issues with detecting existence/creating tables in process on application startup vs. expecting them to be set up properly by my install?

Answer 1

I would depend on them occurring during the install. If you create them automatically during startup, you have to worry about multiple concurrent startups, a situation which is much less likely to occur during installation (and which your installer framework probably handles already).

Answer 2

I think that it is a bad idea to decide for the user what's best for him. Imagine that the application is started up accidentally on an incorrect database. Do you really want the application to create the tables on this incorrect database?

Instead, warn the user that the database tables were not found, and offer him an option to create them, e.g. by telling him that he should start the application with a specific command line option (e.g. /CREATETABLES) of by showing him a dialog in which he can choose to create the tables.

Things might become more complex if the user must not create the tables, but another responsible (an administrator, a key user) should create them. In that case, you could just give an error code and point the user to this responsible.

Answer 3

I think your first step should be to build them during the install and fail gracefully at runtime. Then flesh out the fail gracefully part to be self-repairing.

Detecting and handling error conditions in process is, IMO, always better than assuming (expecting) and crashing.

I would advocate not "expecting" tables to be set up properly by the install. I'm not saying you shouldn't set the database up during the install. What I'm advocating for is code that verifies database integrity. If you have code that verifies the existence and design of your schema and can repair/rebuild, then you don't need to worry about whether the install set everything up properly. This doesn't mean you shouldn't do that creation during the install, especially if it's slow.

I know that a lot of the apps I develop these days have built in migrations based on app version. Basically, when the app starts up it goes through a DB validation/verification cycle, determines the state of the database, and performs associated migrations based on database version and code version.

This requires you to be diligent with up and down migrations (or just up, if you never need to rev back) but I've found it to be a very slick and useful solution. Of course, you need it to be robust against multiple startups, user configuration differences, and be able to handle error situations gracefully, but that's par for the course, in my opinion.