YAGNI and database creation scripts

Question

Right now, I have code which creates the database (just a few CREATE queries on a SQLite database) in my main database access class. This seems unnecessary as I have no intention of ever using the code. I would just need it if something went wrong and I needed to recreate the database. Should I...

1. Leave things as they are, even though the database creation code is about a quarter of my file size.
2. Move the database-creation code to a separate script. It's likely I'll be running it manually if I ever need to run it again anyway, and that would put it out-of-sight-out-of-mind while working on the main code.
3. Delete the database-creation code and rely on revision control if I ever find myself needing it again.

Answer 1

I think it is best to keep the code. Even more importantly, you should maintain this code (or generate it) every time the database schema changes.

It is important for the following reasons.

1. You will probably be surprised how many times you need it. If you need to migrate your server, or setup another environment (e.g. TEST or DEMO), and so on.
2. I also find that I refer to the DDL SQL quite often when coding, particularly if I have not touched the system for a while.
3. You have a reference for the decisions you made, such as the indexes you created, unique keys, etc etc.

If you do not have a disciplined approach to this, I have found that the database schema can drift over time as ad-hoc changes are made, and this can cause obscure issues that are not found until you hit the database. Even worse without a disciplined approach (i.e. a reference definition of the schema) you may find that different databases have subtly different schema.

Answer 2

I would just need it if something went wrong and I needed to recreate the database.

Recreating the database is absolutely not an exceptional case. That code is part of your deployment process on a new / different system, and it represents the DB structure your code expects to work with. You should actually have integration tests that confirm this. Working indefinitely with a single DB server whose schema was created incrementally via manually dispatched SQL statements during development is not something you should rely on.

But yes, it should be separated from the access code; so option 2 is correct. The separate script can then be used by tests as well as for deployment.