I have a database that contains a version table with one value stored in it which is the database version number.

I would like to be able to update the database version automatically using numbered scripts.

If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below:

001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql

What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence?

Should I use an SQL script or create a bash script?

I am a little stuck on how to approach this so any pointers or advice will be much appreciated...

  • 1
    Well, I'd start by renaming the files for consistency - you're asking for trouble otherwise. – Philᵀᴹ Aug 5 '18 at 11:44

Create "main script" which detects current DB version and shells only scripts proper for it. For example, it can look like

drop procedure if exists main_proc;
delimiter @@;
create procedure main_proc
    select version into @version from service_table;
    if @version < 2 then 
        source 001.sql;
        update service_table set version = 2;
    end if;
    if @version < 2.5 then 
        source 02.sql;
        update service_table set version = 2.5;
    end if;
    if @version < 3 then 
        source 3.sql;
        update service_table set version = 3;
    end if;
    -- and so on
delimiter ;
call main_proc;
drop procedure main_proc;

Don't forget error handlers (no script file or access error, error during script execution, etc.).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.