4 ms·

I think this post only gives half of the story. The thing is you need to manage not just your schema (and reference data) but also your DB code - i.e. stored pr

by benmos 11y ago

I think this post only gives half of the story. The thing is you need to manage not just your schema (and reference data) but also your DB code - i.e. stored proc etc. For schema, the approach recommended by the OP seems sensible - check diff scripts into VC and consider them immutable (see also http://www.depesz.com/2010/08/22/versioning/ http://www.depesz.com/2010/08/22/versioning/ for a lightweight Postgres approach). For stored procs etc, I think you need an approach much more akin to normal code - here you want to be able to leverage your VCS just as you do with normal code - so for these you want to consider them mutable. One of the best presentations I've seen on this topic is: http://www.slideshare.net/OleksiiKliukin/pgconf-us-2015-alter-database-add-more-sanity http://www.slideshare.net/OleksiiKliukin/pgconf-us-2015-alte...

eterm 11y ago
Stored procedures are usually considered part of the schema. They can be diffed. I know the Visual Studio comparison tool is able to diff databases but also generates code to check affected stored procedures for errors after a table schema change. I would still recommend changing stored procedures through UPDATE scripts and having this change scripts immutable. (Ideally not just immutable but also idempotent and with forward and backward scripts to undo change.)
- benmos 11y ago
  Curious why you'd recommend that. It seems to me that it costs you something (convenience of standard VC practice on code) and gains you little. I'd recommend checking out the linked presentation above - for things like stored procs you have the option of installing multiple versions simultaneously (e.g. under different names or 'Schemas') - obviously you can't do that for the main schema itself. This is why I think a hybrid approach makes more sense.
  - eterm 11y ago
    It's something I've seen working in a few places, but I'll take your recommendation and review the slides for a better approach. You're right that this approach does lose some of the benefits of version control but then typically the SP schema changes are very closely linked to table schema changes anyway.

by benmos 11y ago

eterm 11y ago

Stored procedures are usually considered part of the schema. They can be diffed. I know the Visual Studio comparison tool is able to diff databases but also generates code to check affected stored procedures for errors after a table schema change. I would still recommend changing stored procedures through UPDATE scripts and having this change scripts immutable. (Ideally not just immutable but also idempotent and with forward and backward scripts to undo change.)

benmos 11y ago
Curious why you'd recommend that. It seems to me that it costs you something (convenience of standard VC practice on code) and gains you little. I'd recommend checking out the linked presentation above - for things like stored procs you have the option of installing multiple versions simultaneously (e.g. under different names or 'Schemas') - obviously you can't do that for the main schema itself. This is why I think a hybrid approach makes more sense.
- eterm 11y ago
  It's something I've seen working in a few places, but I'll take your recommendation and review the slides for a better approach. You're right that this approach does lose some of the benefits of version control but then typically the SP schema changes are very closely linked to table schema changes anyway.