Database Migration tools

There are tools to help you manage the process of updating the structure of your database as your app evolves. ”Database migration" tools seems to be the emerging technical term for this, though imprecise/ambiguous.

Examples include Flyway (my favorite) and Liquibase. By the way, Flyway is Java-based but can be used by non-Java programmers via command-line utilities that wrap the Java classes.

The idea is that you must be disciplined in making no as-hoc changes at all. Every change to the database structure must be written in a script to be managed by the migration tool.

You can run the tool wherever makes sense to you. If you have a single monolithic app, you could run the migration tool every time the app launches. Or you could call the tool from the command-line, or from a hook in your build tool or deployment tool.

The tool automatically detects which scripts have been applied already and which need to be applied. So you are accumulating scripts over time, never deleting them. Great for testing too, as you can recreate the database and take it to any point in the history of your project.

The scripts may be simply SQL code, as is the case with Flyway, plus optional Java classes you can write for complicated cases beyond the capability of SQL. Liquibase uses XML-based scripts.

These tools are best used from the start of a project but you can start with a 'baseline'. Personally, I suggest rebuilding your database from scratch using the tool to recreate the database entirely from your new organized scripts.

Consistent use of a database migration tool eliminates your questions. For example, ”What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors?“, your migration scripts would alter the table to drop or modify the column.