CODEWITHSUNDEEP
sqloracle-databasemigration
JustAMartin
JustAMartin

Reputation: 13743

Implementing simple migration script support for Oracle

We have a Java system that is using Oracle database. I've had previous experience with Laravel and I really like it's idea of migration scripts and wanted to implement something like that in our system (but without migration rollback support for now).

Essentially, this would be based on a migrations table that contains an unique name of specific migration script and also the date of insertion event (just for administrative purposes). Each migration script would be based on a template that contains wrapping code for first checking existence of its name in migrations table and skip the script if the name is found. At the end of successful execution, the script inserts its name into migrations table.

I'm new to Oracle and I have doubts about correct implementation of this feature.

I will need to execute each migration script in separate transaction and roll back entire migration (including schema changes) safely in case if something fails.

A single SQL file will contain multiple migration scripts.

I don't want to reinvent the wheel - maybe this has already been done and there exists a code piece in Github or Gist? I could not find it, though.

Here is the pseudocode I would like to turn into Oracle-compatible SQL:

START TRANSACTION
SET @SCRIPT_NAME = 'myUniqueMigrationScript'
IF NOT EXISTS (SELECT 1 FROM migrations WHERE migrations.name = @SCRIPT_NAME) 
BEGIN
   TRY
   BEGIN
       -- the migration itself - create/update/insert records, modify schema etc.

       INSERT INTO migrations VALUES (name, created_at) VALUES (@SCRIPT_NAME, CURRENT_TIMESTAMP)
       COMMIT TRANSACTION
   END
   CATCH error
   BEGIN  
      ROLLBACK TRANSACTION 
      PRINT 'Error while executing migration ' + @SCRIPT_NAME + ': ' + error.message
   END
ELSE
BEGIN
   PRINT 'Skipping migration ' + @SCRIPT_NAME + ' - it had been already applied.'
END
-- possibly other similar wrapped script pieces follow in the same file

Is this possible in Oracle at all? What are possible caveats?

I have seen something similar for Microsoft SQL Server and it had lots of caveats for correct exception handling and rollback (correct order of GO statements, BEGIN CATCH, IF @@TRANCOUNT, unsafe behavior of set xact_abort, @@ERROR etc. specific syntactic elements).

I would like to avoid dealing with these nuances for Oracle - I'm sure Oracle experts know how to do it right from the start.

Upvotes: 0

Views: 378

Answers (1)

oreh
oreh

Reputation: 1191

There are few notes which can be useful:

it's possible to create shell scripts to support database baselines. baseline is a database/schema state at beginning of a new development cycle. for instance it can be the state of the database at beginning of a scrum sprint. oracle Data Pump utility can be used for that(dbexp, dpimp). these scripts should perform the following tasks: export a schema into a flat file(for example dev_schema_v2.4), drop all objects in a schema, import the dump file into specified schema, etc.

Using these scripts can help with rollback as you just need to clean the schema, import one of the baseline files and apply last migration script(s). Flyway can be used to apply migration scripts.

Upvotes: 1

Related Questions