How I manage updates on my database against application versions

This is how I manage to roll out database upgrades and relate them to a specific application release such that when upgrading from an older release all updates of subsequent releases will be applied.  This article uses mysql for demonstration purposes but it might be applied to any database engine.

1. I usually have two sql script files for database deployement - one that handles upgrades called update.sql and another for fresh deployment create.sql.  
2. The first thing needed is a way to track the database version.  For this I added a table called dbversion in my schema that looks like this:
   
   create table IF NOT EXISTS  dbVersion
   (
      version  int(11)   not null DEFAULT 1,
      releaseDate TIMESTAMP   default CURRENT_TIMESTAMP
   )
   Engine= InnoDB;
   /***NOTE: please make sure dbVersion table value below is updated with every release that has db changes
    * Format is 2 digits for each version section
    * eg. version 2.2.4.2 has a value of 02020402
    * eg. version 2.2.5 has a value of 02020500  */
   INSERT INTO `dbVersion` (version) VALUES (03020401);
   
   This would be the first table created in the create.sql script.
3. Then comes the update.sql file which handles validating the existing database version and the required updates on that database.  This is done in a procedure that is created on the fly.  My update.sql looks like this:
   
   use <>;
   delimiter | -- changing the delimiter so procedure has no issues
   Drop PROCEDURE if exists updateScript |
   CREATE PROCEDURE updateScript()
   BEGIN
        
     
     DECLARE newVersion INT;
     DECLARE oldVersion INT;
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
     ROLLBACK;
     SHOW ERRORS LIMIT 3;
   END;
     START TRANSACTION;
     SET oldVersion =  GetLastVersion();
   
   
   
   
   
   
   /*==============================================================*/
   /*==============================================================*/
   /*==============================================================*/
   -- -------------------------Version 3.1.1.1
     SET newVersion = 03010101 ;
     IF ( oldVersion < newVersion)
     THEN  
    
       /* Database update statements for version 3.1.1.1 go here */
      
       Set oldVersion = InsertVersion(newVersion);
     END IF;
   -- -------------------------Version  3.1.1.1
    
   /*==============================================================*/
   -- -------------------------Version 3.1.2.1
     SET newVersion = 03010201 ;
     IF ( oldVersion < newVersion)
     THEN
        
         /* Database update statements for version 3.1.2.1 go here */
   
   
                      
       Set oldVersion = InsertVersion(newVersion);
     END IF;
   -- -------------------------Version  3.1.2.1
   /*==============================================================*/
   /*==============================================================*/
   /*==============================================================*/
   
   
   -- Add new version section above this comment block
   -- NO EDITING BEYOND THIS COMMENT BLOCK --
   COMMIT;
   /*==============================================================*/
   END |
   /*==============================================================*/
   create table IF NOT EXISTS  dbVersion
   (
      version  int(11)   not null DEFAULT 1,
      releaseDate TIMESTAMP   default CURRENT_TIMESTAMP
   )
   Engine= InnoDB|
   /*==============================================================*/
   DROP Function if exists GetLastVersion |
   CREATE FUNCTION GetLastVersion()
       RETURNS int(11)
       NOT DETERMINISTIC
       CONTAINS SQL
   BEGIN
        DECLARE lastVersion INT;
        SET lastVersion =  (select max(version) from dbVersion) ;
          -- if this is the first time we update the database there will be no version
        IF lastVersion is null THEN
              SET lastVersion = 0;
        END IF;
        RETURN lastVersion;
   END|
   /*==============================================================*/
   DROP Function if exists InsertVersion |
   CREATE FUNCTION InsertVersion(newVersion int(11))
       RETURNS int(11)
       NOT DETERMINISTIC
       CONTAINS SQL
   BEGIN
        INSERT INTO `dbVersion` (version) VALUES (newVersion); 
        RETURN newVersion;
   END|
   /*==============================================================*/
   CALL updateScript() |
   DROP Function if exists InsertVersion |
   DROP Function if exists GetLastVersion |
   DROP PROCEDURE if exists updateScript |
   delimiter ;