Using Source Control to Manage Stored Procedures
by Olivia Glenn-Han, on Mar 5, 2015 10:30:00 AM
It goes without saying that source code files belong in source control. Using your production environment as the repository is not only difficult (for compiled applications, at least) but is in fact one of the worst possible practices. Code has to start in source control and move its way up to production.
Of course, the opposite is true for data: it has to live in production. That means changes to your data structures are tricky, as you can't just compile a new database and push it to production. But remember, not everything in a database is data. In fact, just about every object in the database except tables don't actually store data
Unlike tables, you can DROP and CREATE stored procedures, triggers, views, and functions a hundred times over without impacting data. These objects are code that just happen to exist inside of a database instead of a file system (and you know where code belongs).
The rules of database objects in source control are simple: one file per object (named after the object) that will DROP and then CREATE the object. You can use a tool like Scriptio to extract existing objects into files. Throw in a little file organization, and you're ready for check-in:
1.FUNCTIONS\ 1.FormatOrderNumber.sql 2.VIEWS\ 1.OrdersWithTotals.sql 1.SalesReport.sql 2.OrdersPastDue.sql 3.PROCS\ 1.AddItemToOrder.sql 1.ValidateOrder.sql 2.ProcessOrder.sql
Note that the numeric prefixes ensure proper execution order. Procedures can use Views, which in turn can use functions, but usually not the other way around. Since a handful database objects depend on other objects, those can prefixed with "2." instead of "1.".
Execution of these scripts is just as easy and can be accomplished with a simple batch file:
FOR /R %%f IN (*.sql) DO ( ECHO Running %%f OSQL -E -i "%%f" -n -b -d myDatabase -S myServer )
With scripts and a batch script set-up in source control, building your database code is as easy as grabbing from source control and clicking "execute scripts."
BuildMaster makes it easy to execute these scripts. Check out our Database features to learn more.