Web Application Deployment - Deploying Database Changes

this article is another work in progress. Comments and feedback are always welcome.

This question keeps coming up at work over and over again. So here we go - deploying database changes 101. Buckle down and do the upfront work, look sexy and awesome when it comes time for deployment >_>

Synopsis
Here it is, this whole post summed up into one key idea:

Always create SQL scripts (insert/update/alter commands) to implement any and all database changes! Add those SQL scripts to your deployment package!

Curious as to what that means? Not sure if you know how to create SQL scripts for deployment? Looking for some tips and tricks on how to do a better job of that? OK! Lets dive in!

Example SQL Script for INITIAL Deployment of a Web Application

Here is an example of a single table "Categories" and its data. Keep in mind that this script is designed to be used only for the initial web application deployment.

NOTE: I'm following the Oracle DB Design Style Guide I wrote up a while back as a guide for my SQL scripts here.

--- Create the Category table
CREATE TABLE Category (
Id INT NOT NULL,
Name VARCHAR2(200) NULL,
Description VARCHAR2(4000) NULL
);

--- Primary Key Constraint
CREATE INDEX CategoryIdIdx ON Category(Id);
ALTER TABLE Category ADD CONSTRAINT CategoryIdPk PRIMARY KEY (Id);

COMMENT ON TABLE Category IS 'A list of Categories for use in a TODO list.';
COMMENT ON COLUMN Category.Id IS 'Primary Key';
COMMENT ON COLUMN Category.Name IS 'A common name for this category.';
COMMENT ON COLUMN Category.Description IS 'A longer description of this category.';

--- Create the oracle sequence for the primary key
CREATE SEQUENCE CategoryIdSeq INCREMENT BY 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

--- Create the oracle trigger for populating the primary key
CREATE OR REPLACE TRIGGER SetCategoryIdPk
BEFORE INSERT ON Category REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
IF :new.Id IS NULL THEN
SELECT CategoryIdSeq.Nextval INTO :new.Id FROM dual;
END IF;
END;
/

--- insert initial categories
INSERT INTO Category (Name, Description)VALUES('Groceries','My grocery list items');
INSERT INTO Category (Name, Description)VALUES('Work','My work related items');
COMMIT;

So what does this deployment script do?
1. Creates a table for us to store categories
2. Creates a primary key column
3. Sets comments on the table and the columns (Comments?! Madness!!)
4. Creates a sequence to drive the primary key value (oracle specific goo)
5. Creates a trigger to populate the primary key value based on the sequence
6. inserts 2 new categories: Groceries and Work.

Basically, it does literally everything that needs to be done to get the Category table setup in the database for my initial web application release to work.

So now, every time we deploy our web application to a new environment, we can run this SQL script to guarantee that a category table is created exactly how we expect it to be, along with its initial data.

Cant I just reverse engineer the database when I'm done my project?
Yes! ... Maybe! >_> just be careful that you don't reverse engineer throw-away testing data. I've seen that happen a lot just because people don't like spending the up front time building SQL scripts.

Example SQL Script for Deployment of a Web Application Update

After deploying our initial version of the web application, we start working on the next version which requires updates to the database. New requirements state that the categories now need to be sorted in an arbitrary order -and- we have additional categories being added to this release. Awesome! Lets script out the updates to our category table so that again we will have a reliable, repeatable process for deploying our changes.

--- Give our table a SortOrder column
ALTER TABLE Category ADD SortOrder INT NULL;
COMMENT ON COLUMN Category.SortOrder IS 'The default order in which categories should be displayed to the user.';

--- Update existing data with sort order values
UPDATE Category SET SortOrder = 1 WHERE Name = 'Work';
UPDATE Category SET SortOrder = 2 WHERE Name = 'Groceries';

--- Insert new data, include sort order values
INSERT INTO Category (Name, Description,SortOrder)VALUES('Errands','Errand that I need to do',3);
COMMIT;

Cool? So this SQL script, to be used for the next deployment of this application, is completely different from the initial deployment SQL Script - We only care about things that changed from the initial deployment script. This new SQL script should be bundled with the deployment package for this version of the web application.

Just to review, These are the changes we just made in english:

1. Gave the Category table a new SortOrder column
2. Updated our existing records in the Category table with a SortOrder value
3. Inserted a new category, including its SortOrder value

And the point of all this is...?

Hopefully it's obvious to you at this point - It's a good practice to Use SQL scripts to deploy ALL database changes for your application. The reward for your up front effort is a repeatable and reliable deployment script. An added bonus - you even can hand off your SQL scripts to a database admin to deploy remotely! (its a good practice to do that anyway!)