What are the best practices for database scripts under code control

After a few iterations, the approach we took was roughly like this:

One file per table and per stored procedure. Also separate files for other things like setting up database users, populating look-up tables with their data.

The file for a table starts with the CREATE command and a succession of ALTER commands added as the schema evolves. Each of these commands is bracketed in tests for whether the table or column already exists. This means each script can be run in an up-to-date database and won't change anything. It also means that for any old database, the script updates it to the latest schema. And for an empty database the CREATE script creates the table and the ALTER scripts are all skipped.

We also have a program (written in Python) that scans the directory full of scripts and assembles them in to one big script. It parses the SQL just enough to deduce dependencies between tables (based on foreign-key references) and order them appropriately. The result is a monster SQL script that gets the database up to spec in one go. The script-assembling program also calculates the MD5 hash of the input files, and uses that to update a version number that is written in to a special table in the last script in the list.

Barring accidents, the result is that the database script for a give version of the source code creates the schema this code was designed to interoperate with. It also means that there is a single (somewhat large) SQL script to give to the customer to build new databases or update existing ones. (This was important in this case because there would be many instances of the database, one for each of their customers.)

There is an interesting article at this link:https://blog.codinghorror.com/get-your-database-under-version-control/

It advocates a baseline 'create' script followed by checking in 'alter' scripts and keeping a version table in the database.

The upgrade script option

Store each change in the database as a separate sql script. Store each group of changes in a numbered folder. Use a script to apply changes a folder at a time and record in the database which folders have been applied.

Pros:Fully automated, testable upgrade path

Cons:Hard to see full history of each individual elementHave to build a new database from scratch, going through all the versions