Add new comment

I am evaulating some schema migration tools for a project I am working on. I want some basic help organizing scads of changes that we will make during development. I also want the ability to produce generic SQL scripts as output to pass along for review / execution by folks that are not knee deep in our development environment.

The SQL Script output requirement cut out a lot of options. For a comprehensive list of options check out the feature matrix on the Flyway page which compares 9 popular schema migration tools.

The reality is we would like to leverage the tool for development, but the DBAs that maintain the production databases are requiring straight SQL scripts for their involvement. I can't say I blame them.

LiquiBase was high on the list but I am looking for something more lightweight... I don't need the abstraction layer of a Domain Specific Language for generically specifying schema changes for multiple targets. Neat yes. Necessary no.

I am targeting Oracle. I am only targeting Oracle. I already know I have to make heavy use of Oracle specific features and I am betting the DSL will end up with me using the LiquiBase custom SQL method all the time anyway. Too much friction.

So 2nd on my list is the myBatis Migration Tool. It is a glorified script manager/runner with plain SQL input. It will manage running and tracking the scripts for you (great for development) and it will produce plain SQL output when going to Pre-prod or Prod environments (great for cranky DBAs*).

For anyone looking to incorporate myBatis Migration Tool with Oracle I wanted to put this out there. So far I have not gotten normal statements with semicolons to be accepted gracefully by myBatis Migrations. I have backed off to SQL scripts with no semicolons after statements (but OK for PL/SQL)... and use a slash on a line by itself as the command separator. This reminds me more of the SQL Server 'GO' scripts... But SQL*Plus will accept this output just fine.

In your environment file, set the following:

send_full_script=false
delimiter=/
full_line_delimiter=true

And in your scripts, supply changes like this:

--// My migration.

create table TAB1 (TAB1_ID NUMBER, TEXT_FIELD VARCHAR2(255), NUMBER_FIELD NUMBER)
/

ALTER TABLE TAB1
ADD CONSTRAINT PK_TAB1
PRIMARY KEY (TAB1_ID)
/

CREATE OR REPLACE PROCEDURE UPDATE_TAB1 AS
BEGIN
  UPDATE TAB1 SET NUMBER_FIELD = NUMBER_FIELD + 1;
  COMMIT;
END;
/

--//@UNDO

DROP TABLE TAB1
/

DROP PROCEDURE UPDATE_TAB1
/

-Dave

* Speaking from experience here, I have been included in that population more times than I would like to admit.