3 ms·
You start with an initial table schema definition in a file, the base schema. The next batch of altering sql statements are put in a file with an explicit vers
by ww520 5y ago
You start with an initial table schema definition in a file, the base schema. The next batch of altering sql statements are put in a file with an explicit version in the file name. The version in the file name can just be the current date. The next batch of changes are put in another file with its own version name in the file name. And so on. These are called delta files. All the delta files are checked into the source repository.
To recreate the database upto the current schema, just check out the list of schema delta files. Play them back one by one in the order of the version in the file names.
Track any prepopulating table data the same way.
Edit: having a table called schema_version to track the current applied schema version of the database is very helpful. Then you can upgrade the db schema from any version rather than from the base schema by checking the schema_version table before applying the versioned schema delta files. Be sure to put an insert statement to the end of every versioned schema script file adding a row containing the version of the file to the schema_version table.
- dijit 5y agoThe issue with reconciling state is that you can have multiple branches at different times. Forgive the crude diagram but I’m on my phone. MAIN.1->MAIN.2 \ FEAT.? If you have many branches and you want to bug fix on one or your schema needs to stay how it is for a certain release window then you quickly run into issues with the serialised version approach.
- deleted 5y ago[deleted]
- ww520 5y agoAll the schema delta files are checked into repository. They themselves can be version tracked. Different branches can have different versions of a delta file. Merging branches need to merge the changes of the delta file. Might need special attention since merging sql statements needs special care. Edit: Or you might choose to be explicit with the version of the delta files in a branch. Each branch has its own delta file naming format. When merging into main, roll the changes from all delta files into a new main delta file. If main has moved forward a lot, you have to insert the changes carefully.