Versioning Databases – Views, Stored Procedures, and the Like. What started as a short brain dump is tuning in to a longer series of posts thanks to all the feedback and questions.
In this post, I want to explain some of my thoughts on controlling objects like database views, stored procedures, functions, and triggers. But first... I haven't actually used a trigger in years. This isn't to say that triggers aren't valuable, but I've tended to shy away. Jon Galloway has posted a good example of what you can do with triggers. Secondly, stored procedures have fallen out of favor in my eyes. One File per Object My strategy is to script every view, stored procedure, and function into a separate file, then commit the files to source control. The magic happens when a developer, tester, or installer updates from source control and runs a tool that updates their local database. The tool applies new schema changes by comparing the available schema change files to the SchemaChangeLog records in the database. Say What? Summary. Versioning Databases – Change Scripts. After considering the three rules and creating a baseline, an entire team can work with a database whose definition lives safely in a source control repository.
The day will come, however, when the team needs to change the schema. Each change creates a new version of the database. In my plan, the baseline scripts created a schema change log to track these changes. By "change", I mean a change to a table, index, key, constraint, or any other object that requires DDL, with the exception of views, stored procedures, and functions.
I treat those objects differently and we'll cover those in the next post. Alternatives Before jumping into an example, I just wanted to point out there are many ways to manage database changes and migrations. Example, Please The team just baselined their database that includes a Customers table, but now wants to add a new column to store a Customer's shoe size. File: sc.01.00.0001.sql ALTER TABLE Customer ADD ShoeSize int NOT NULL DEFAULT 0 GO Summary. Versioning Databases – The Baseline. Continuing from the last post (Three Rules for Database Work), I wanted to drill into some database versioning strategies that have worked well for me.
Caveats and Considerations As a preface, let me say there are many different strategies that can work. I'm not presenting the one true way. My goal is to roll out database changes in a consistent, testable, reproducible manner. I'm used to working with largish databases that are packaged to install behind a customer's firewall. The Baseline The first step in versioning a database is to generate a baseline schema. You are probably not going to baseline the database on day 1 of a project. On the other hand, maybe your project and database have already been around a couple years. But How? If you want to do things the hard way, then open a new file in a text editor and write all the SQL commands that will create every table, constraint, function, view, index, and every other object in your database. I Almost Forgot the Most Important Ingredient. Three Rules for Database Work. Some developers love working with relational databases, and other developers can't stand to touch them.
Either way - if your application uses a database, you have to treat the database with some respect. The database is as much a part of an application as the code and the models inside the software. Here are three rules I've learned to live by over the years of working with relational databases. 1. Never use a shared database server for development work. The convenience of a shared database is tempting. Like many conveniences in software development, a shared database is a tar pit waiting to fossilize a project.
Avoid using a shared database at all costs, as they ultimately waste time and help produce bugs. 2. Ideally, this single source will be your source control repository (see rule #3). Developer 1: It's time to push the app into testing. Developer 2: Ummmmmmmm, I don't remember which one is up to date. Developer 1: We're screwed.