Testing and Managing database versions against code versions

As you develop an application database changes inevitably pop up. The trick I find is keeping your database build in step with your code. In the past I have added a build step that executed SQL scripts against the target database but that is dangerous in so much as you could inadvertanly add bogus data or worse.

My question is what are the tips and tricks to keep the database in step with the code? What about when you roll back the code? Branching?

  • Can I have a workspace that is both a git workspace and a svn workspace?
  • Jenkins “Git plugin” neither ignores commits with certain messages nor commits with from certain users
  • Versioning Build numbers with TortoiseSVN
  • put build date in about box
  • Jenkins - How can i pass parameters from the Upstream to Downstream
  • How do I count the number of git commits affecting a given subtree?
  • Storing Drupal SQL in Git
  • Jenkins - Dont build for specific commiter or commit message
  • How to run GUI tests on a jenkins windows slave without remote desktop connection?
  • How should I handle database schema changes when switching branches in Rails?
  • git workflows: how to integrate and test feature branches without continuous delivery?
  • Stress Testing with git
  • 8 Solutions collect form web for “Testing and Managing database versions against code versions”

    Version numbers embedded in the database are helpful. You have two choices, embedding values into a table (allows versioning multiple items) that can be queried, or having an explictly named object (such as a table or somesuch) you can test for.

    When you release to production, do you have a rollback plan in the event of unexpected catastrophe? If you do, is it the application of a schema rollback script? Use your rollback script to rollback the database to a previous code version.

    You should be able to create your database from scratch into a known state.

    While being able to do so is helpful (especially in the early stages of a new project), many (most?) databases will quickly become far too large for that to be possible. Also, if you have any BLOBs then you’re going to have problems generating SQL scripts for your entire database.

    I’ve definitely been interested in some sort of DB versioning system, but I haven’t found anything yet. So, instead of a solution, you’ll get my vote. 😛

    You really do want to be able to take a clean machine, get the latest version from source control, build in one step, and run all tests in one step. Making this fast makes you produce good software faster.

    Just like external libraries, database configuration must also be in source control.

    Note that I’m not saying that all your live database content should be in the same source control, just enough to get to a clean state. (Do back up your database content, though!)

    Define your schema objects and your reference data in version-controlled text files. For example, you can define the schema in Torque format, and the data in DBUnit format (both use XML). You can then use tools (we wrote our own) to generate the DDL and DML that take you from one version of your app to another. Our tool can take as input either (a) the previous version’s schema & data XML files or (b) an existing database, so you are always able to get a database of any state into the correct state.

    I like the way that Django does it. You build models and the when you run a syncdb it applies the models that you have created. If you add a model you just need to run syncdb again. This would be easy to have your build script do every time you made a push.

    The problem comes when you need to alter a table that is already made. I do not think that syncdb handles that. That would require you to go in and manually add the table and also add a property to the model. You would probably want to version that alter statement. The models would always be under version control though, so if you needed to you could get a db schema up and running on a new box without running the sql scripts. Another problem with this is keeping track of static data that you always want in the db.

    Rails migration scripts are pretty nice too.

    A DB versioning system would be great, but I don’t really know of such a thing.

    While being able to do so is helpful (especially in the early stages of a new project), many (most?) databases will quickly become far too large for that to be possible. Also, if you have any BLOBs then you’re going to have problems generating SQL scripts for your entire database.

    Backups and compression can help you there. Sorry – there’s no excuse not to be able to get a a good set of data to develop against. Even if it’s just a sub-set.

    Put your database developments under version control. I recommend to have a look at neXtep designer :
    http://www.nextep-softwares.com/wiki

    It is a free GPL product which offers a brand new approach to database development and deployment by connecting version information with a SQL generation engine which could automatically compute any upgrade script you need to upgrade any version of your database into another. Any existing database could be version controlled by a reverse synchronization.

    It currently supports Oracle, MySql and PostgreSql. DB2 support is under development. It is a full-featured database development environment where you always work on version-controlled elements from a repository. You can publish your updates by simple synchronization during development and you can generate exportable database deliveries which you will be able to execute on any targetted database through a standalone installer which validates the versions, performs structural checks and applies the upgrade scripts.

    The IDE also offers you SQL editors, dependency management, support for modular database model components, data model diagrams, SQL clients and much more.

    All the documentation and concepts could be found in the wiki.

    You should be able to create your database from scratch into a known state. Why would you be adding bogus data?

    Git Baby is a git and github fan, let's start git clone.