I am new to a team developing a very large web application. Prior to my start, the team had 0 automation in place and just did everything by hand. This was possible because they were only a team of 2. I am now gradually changing this but obviously I can't just make everyone start from scratch and "do it right".

A problem I'm struggling with are changes to data (not schema) in the database. There are quite a few things that are configured in the database, such as translations, navigations, roles/permissions and mail templates. Luckily these are usually just defined by the team and pushed to staging/production, not both ways.

Our setup is every dev has their own dev environment and their own database. Then there is a staging and production environment. The idea is that a dev can create his new navigation entries, permissions and translations on his dev box then somehow apply these changes to a) the other dev's boxes and b) staging and production.

The obvious way to monitor these changes is to store them as code or SQL files, however this gets quite tricky considering a mail template stores it's data in 3-4 tables and has foreign keys. Another way would be to create export/import functionality, but that would be quite time consuming.

I know this topic has been discussed before but usually the discussion ends with "track your changes in sequential sql or code files". I'm hoping to hear what other teams might be doing and maybe there's even a really great tool for handling this somewhat generically?

  • Can you not just switch where your application points to in order to use another developer's database? It seems like a much more straightforward solution without the hassle of importing or exporting. If you're worried about damaging the other developer's database, perform a backup prior. – Neil Aug 18 '15 at 12:37
  • @Neil I'm not sure I understand, why would I want to use another developer's database? – Christof Aug 18 '15 at 12:51
  • 1
    there are tools, that do this sort of thing by magic... what happens the day the magic doesn't work? Scripts are the way to go. Its pain, but its manageable pain. Doing a significant live update, and the magic fails, leaves you knackered. At least with scripts, you can write another script to fix it. – Michael Shaw Aug 18 '15 at 12:55
  • Maybe I misunderstood your problem. You mean to ask how to distribute database changes associated with changes to the program? – Neil Aug 18 '15 at 12:58
  • If I understand you correctly, yes. We configure some parts of our application in the database and we need to have some sort of release management for those changes. – Christof Aug 18 '15 at 13:21

SQL scripts are the right way to go:

  • The information was originally created via a sql statements, most reliable repeat-ability would come from the same script
  • Import/export can also get tricky if two devs are modifying the same table.
  • It's hard to code review an export file.
  • It's hard to debug an export file

We have 5 database environments in our world: dev, staging, alpha, beta, production. Developers only run their own scripts against dev. They are responsible for checking these scripts into source control, just like the rest of our source. We have an automated deployment system which retrieves those scripts from source control and runs them against each successive environment.

  • Thanks for your comment, figured I would these :). Do you actually have tools that you configure in your db or are you just tracking schema changes? – Christof Aug 18 '15 at 12:53
  • Many of the apps are workflow systems, where the state machine is defined in the database. Also, look-ups and other reference tables are maintained this way. So, new states or modified reference data are just scripts with inserts, updates or deletes. This is where we are better off than import/export. How would you handle deletes or updates via import/export when multiple devs modifying data in the same table? – cdkMoose Aug 18 '15 at 12:57
  • What's your dev workflow like? Do you modify your workflow systems in your web app, then copy out the data it creates/modifies/deletes as SQL statements? The risk of missing something when moving over to the db seems high. – Christof Aug 18 '15 at 13:20
  • 1
    Ours is not a web app, it is thick client with Java based services and Oracle back-end. We have tools for visualizing the workflow state machine, but the data is scripted by the developers. We have a single dev database, each developer does not have their own. – cdkMoose Aug 18 '15 at 13:56

As you figured out by yourself, SQL is the way to go, but if you put the SQL commands into scripts or embed them in code does actually not make a big difference. What matters is that you make the upgrade process robust. Therefore, I would recommend to consider the following improvement over

track your changes in sequential sql or code files

Instead, track your changes (in sql and/or code files) which work even when they are not executed in the original sequence, or when they are executed twice. Make them idempotent. In one of our systems, we use a flag table for this purpose, where each change to the data of the database is marked by a flag. The pseudocode for upgrading the database then looks like this

 ' part added by developer A during development of feature "Foo"
 IF NOT CheckFlagTableIfUpgradeFooAlreadyApplied() THEN
     IF ApplyUpgradeFoo() = SUCCESS THEN
     END IF

 ' part added by developer B during development of feature "Bar"
 IF NOT CheckFlagTableIfUpgradeBarAlreadyApplied() THEN
     IF ApplyUpgradeBar() = SUCCESS THEN
     END IF

That way, you can always take an old database with some changes applied, and others not, and upgrade it to the newest state. For example, the above script will work when the changes "Foo" and "Bar" are missing, when only the change "Foo" was applied, only the change "Bar", or both. It also very easy to add postconditions that way, to deal with cases of interdependent updates. Schema changes can be handled similar, but you often do not even need a flag table for this: just implement a test method like DoesColumnXYZExist() for verifying if a specific column was already added to the schema.

This makes it much easier and more reliable when people work in parallel to integrate they database changes at different times to all their different development databases, and to staging, testing or production databases as well.

See also this former question on Programmers, where a tool named Liquibase was mentioned for implementing this process.

  • Thanks for your reply. By flag you probably mean just any kind of number or digest? That's kind of what I'm doing already for the schema. I don't know if applying the updates in any order would actually work as some will depend on others. I like the idea of always checking for something first (if columnExists) but in a database with a long history of changes, this could lead to some false positives. I don't know if I'll just stick with sequential upgrading. Anyway, the answers I got were pretty much what I expected: track the changes in sql/code files. – Christof Aug 18 '15 at 14:31
  • 1
    By "flag" I mean a boolean indicator, not a number. Applying the updates in any order does not work in general for dependent updates, but a robust update routine should be able to deal with that (if "UpdateFooV2" depends on "UpdateFooV1", test that UpdateFooV1 has has taken place before UpdateFooV2 is executed). But to my experience there are typically only a few updates which really depend on each other, the majority are independent from each other, and my suggestion is you can use this as an advantage for parallel development. – Doc Brown Aug 18 '15 at 18:46
  • .. I have edited my answer and added pseudo code for error handling to highlight the difference to a pure "sequential" upgrading. – Doc Brown Aug 18 '15 at 18:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.