How do you version your database schema? [closed]

Question

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 4 months ago.

How do you prepare your SQL deltas? do you manually save each schema-changing SQL to a delta folder, or do you have some kind of an automated diffing process?

I am interested in conventions for versioning database schema along with the source code. Perhaps a pre-commit hook that diffs the schema?

Also, what options for diffing deltas exist aside from DbDeploy?

EDIT: seeing the answers I would like to clarify that I am familiar with the standard scheme for running a database migration using deltas. My question is about creating the deltas themselves, preferably automatically.

Also, the versioning is for PHP and MySQL if it makes a difference. (No Ruby solutions please).

Answer 1

You didn't mention which RDBMS you're using, but if it's MS SQL Server, Red-Gate's SQL Compare has been indispensable to us in creating deltas between object creation scripts.

Answer 2

I make sure that schema changes are always additive. So I don't drop columns and tables, because that would zap the data and cannot be rolled back later. This way the code that uses the database can be rolled back without losing data or functionality.

I have a migration script that contains statements that creates tables and columns if they don't exist yet and fills them with data.

The migration script runs whenever the production code is updated and after new installs.

When I would like to drop something, I do it by removing them from the database install script and the migration script so these obsolete schema elements will be gradually phased out in new installs. With the disadvantage that new installs cannot downgrade to an older version before the install.

And of course I execute DDLs via these scripts and never directly on the database to keep things in sync.

Answer 3

I am interested in this topic too.

There are some discussions on this topic in the Django wiki.

Interestingly, it looks like CakePHP has schema versioning built-in using just cake schema generate command.

Answer 4

After long investigation, I figured out that there are some 3rd party tools or Visual Studio project types that does not satisfy me, or just blogs about the theory but no implementation. So I implemented a working system, which is used almost a year, and explained here:

http://nalgorithm.com/2015/11/09/database-versioning-part-1/

depending on the interest, will continue writing more.

Answer 5

http://bitbucket.org/idler/mmp - schema versioning tool for mysql, writed in PHP

Answer 6

You might take a look at another, similar thread: How do I version my MS SQL database in SVN?.