Continuous Integration testing and validation of DbUP changes to the database

[Kyle Wiering]

Let's call this a sanity check - but, does anyone validate the changes that DBUP made to the database against a pre-defined schema? 

I.E. Maintain a schema definition - then validate the changes DBUP made to the schema against the schema definition?

[Kyle Wiering]

Or would this be something you would test via the integration / regression testing against the database?

[Caio Proiete]

What exactly are you trying to validate in your sanity check?

In my view, if you're using DbUp - which relies on migration scripts for everything (table changes, stored procedures, functions, etc.), it means you don't actually keep a pre-defined schema anywhere, so you have nothing to compare to.

Your migration scripts are the source of truth.

You always run DbUp and let it figure out which scripts to run depending on the target database your are running against.

--

Cheers,

Caio Proiete

http://caioproiete.net

--
You received this message because you are subscribed to the Google Groups "DbUp Discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbup-discuss...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kyle Wiering]

I suspect I'm looking more for reasons why maintaining a separate (source of truth) schema outside of DBUP is not needed. 

I'm looking at migrating from an upgrade script + pre-defined schema approach to using DBUP. This followed the pattern of comparing the results of the upgrade script against the pre-defined schema. I was leaning towards no longer needing the pre-defined schema - but wanted to make certain I wasn't missing anything.

Thank-you, Kyle

[Caio Proiete]

Right.

Pre-defined schemas (in my experience, YMMV) just doesn't work in practice for deployment, because it captures the end state of tables, and not how to get there.

For very simple scenarios where we're just doing simple changes to tables, like adding a nullable field (or a required field with a default value), removing a field, etc. It's OK... A quick diff is good enough.

As soon as you have any kind of more complex scenarios that involves any kind of data migration, such as splitting a table into N, or adding fields where the value is going to be determined via some logic, etc., then we're talking migration scripts.

[Adam K]

Our team has been using a DbUp for about a year and half.  We're doing .NET with SQLServer.  We are kind of doing what you're describing.  We have database projects in VisualStudio to store our schema definitions.  Our official process is to make changes to the objects in the database projects and then create a DbUp script to move them to our local dbs, dev, and beyond.  One developer has the thankless task at the end of each sprint to run a db compare of dev vs the database projects.  It usually uncovers that someone has forgotten to change a handful of things in the database projects.  However, occasionally it uncovers that someone has directly modified a dev db and forgotten to create a DbUp script.  Once or twice we have had a problem where one developer modifies a store procedure via DbUp without modifying the database project.  Soon after, another developer modified the same stored procedure based on the current version in the database project rather than the most recent DbUp script and caused a change to be lost. 

If you check frequently, I think there is some value in doing the comparison you describe.  However, as Caio has stateed, DbUp is the source of truth.  The other is just a mechanism of validation that may or may not be correct depending on your team's diligence.

-Adam