Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Looking for solution for database versioning

Problem description:

In our project we have one "production database" and many "devs". We want to know how we can manage and install the changes. We already have some procedures but it takes a lot of time and sometimes causes errors.

We can't lose data - so we can't use "drop table". We can only use "alter table".

Our actual "db versioning procedure" is like this:

  • We have a table named [actual_version] that contains the actual version of the installed db schema
  • We have a file named "changes_script.sql" that contains all database changes - this file is stored on SVN

When a developer wants to commit a new revision on SVN he needs to change_script.sql by adding a block:

if ([acctual_version].version < "23")) {
    --- sql script ----
updateVersionTo("23")
end if 

When we want to upgrade the database schema we just "execute" the change_scripts.sql

Does anyone have a better idea?

like image 332
itdebeloper Avatar asked Feb 17 '10 16:02

itdebeloper


People also ask

Is there a version control for database?

Database version control is the practice of tracking every change made to the database by every team member. Like application version control, database version control acts as a single source of truth. It empowers you with complete visibility, traceability, and continuous monitoring of the changes in your database.

What is DB versioning?

What is database versioning? Database versioning begins with database schema, the structure of the database. In order to effectively version a database, you need to track and understand the changes that are happening.

Which of these tools would you use to maintain different versions of you data and model?

Pachyderm is a data and model versioning platform that helps data scientists and Machine Learning engineers store different versions of training data in an orderly fashion, offering you traceability through the different changes your data goes through.


2 Answers

Red Gate's Sql Compare tool can compare two tables (or two collections of DDL scripts, or one set of scripts to a table, etc) and generate the migration script for you.

If you're working in Ruby you can look into "Ruby Migrations", which is a formalized way of doing DB versioning in code. (There are similar things in .NET such as RikMigrations and Fluent Migrator, and I'm sure similar things exist for other platforms as well).

As Randy Minder said, you can also use VS DB Edition to manage your schema for you, although I think the RedGate tools work just as easily and don't tie you to a specific platform or IDE.

like image 178
Seth Petry-Johnson Avatar answered Oct 21 '22 02:10

Seth Petry-Johnson


I do it very similarly, but use database extended properties instead of a table to track metadata version and the upgrade steps are defined in application, rather than a single script. Most upgrade steps are just execute script Upgrade.vX.Y.sql.

I actually find your approach superior to schema comparison tools (and that includes VS DB deployment) for several reasons:

  • I don't trust schema comparison tools changing very large tables, I rather have a tested script specifically designed for my table of 150B records.
  • Schema comparison doesn't handle removal of obsolete objects
  • If the application schema was modified at a client site a comparison tool will blindly attempt to upgrade it, but a modified schema may need special treatment: inspection of the changes, evaluation of impact, billing of extra work.
like image 40
Remus Rusanu Avatar answered Oct 21 '22 02:10

Remus Rusanu