Migrating a Rails Application to Oracle

Rails is a database-agnostic web development framework implemented in the Ruby programming language. Having said that, using it with some databases is easier than with others. In late 2015, my team had the task of migrating a critical production Rails application from an open source Postgres database to an Oracle database.

By default, Rails applications use the SQLite database, which is a simple, light-weight, serverless database that requires zero configuration and stores its database locally. It’s eminently useful for development tasks. However, it isn’t even remotely suitable for production use. Accordingly, every serious Rails application needs to be able to migrate to a more robust database for its production environment, and also for its test environment (which should mimic production as much as possible).

My team was using Postgres, an open source database, for its development, test, and production environments. It’s a robust and production-worthy database with plenty of community support, so there was no need to pay any sort of enterprise licensing fee for another solution. At this point, the application had already been in production for three years, and without any significant problems or performance bottlenecks.

However, sometime in 2014 or so, our customer had finally succumbed to Oracle’s concerted sales efforts and purchased a high performance, super-robust, inordinately expensive Oracle Rack capability that they mandated every project now had to use. There’s nothing wrong with Oracle per se, but it was pretty much overkill for our project, plus needlessly adding the networking latency of accessing a database in another state.

So, we had to migrate from Postgres to Oracle. Or, more factually, from a friendly, permissive database to a solid enterprise database plagued by annoying limitations Oracle wasn’t in any hurry to fix, no matter how much developers clamored for the changes.

Database Migration Changes

Some of the application’s database elements translated into Oracle quite differently:

Data Types: Most of the necessary database changes had to do with data types.
- All Integers became N(38).
- Booleans became N(1), with values of 0 or 1 to indicate FALSE or TRUE.
- Text columns became CLOBs, i.e. any column that exceed 255 characters.
- Most other string columns became varchar(255), variable length character columns.
- Date values were stored as the Date type in Oracle.
Triggers: We had Postgres triggers that functioned like auto-increments on table ID’s. Neither Postgres or Oracle supported Identity columns at the time (like SQLite and MySQL). The triggers weren’t ported. Instead, we leveraged Oracle sequences and created simple PL/SQL triggers to auto-increment the table ID’s when rows were inserted.

Note: Oracle didn’t add Identity columns until Oracle 12c Release 1 in 2013, which wasn’t available to us in production until 2016.
Columns Named After Reserved Words: Some of our Postgres column names corresponded to Oracle reserved words, mandating some minor column name changes. For example: COMMENT => COMMENTS, SIZE => SIZES, etc.
Table Name Length: Oracle database element names were limited to 30 characters at the time. In order to simulate Identity columns, we needed Oracle sequences that had the same name as the corresponding tables, but with a “_seq” extension. Any table name longer than 26 characters needed to be changed to something shorter.

Note: This ridiculous limitation didn’t change until Oracle 12c Release 2 in 2017, which wasn’t available to us in production until 2019.
Pre-Processing the Database Dump File: Upon generating the Postgres dump of the production data, “\\n” values in columns needed to be removed. As best we could tell, they represented empty strings. By removing them, we ensured that the column was NULL after the data was loaded into Oracle.

Basic Migration Strategy

Our migration strategy was a simple seven-step process:

Create an empty production Oracle schema for the application and a temporary Oracle “scratch” schema for the initial data move.
Create a data dump of the Postgres data.
Do some pre-processing on the dump file.
Migrate the Postgres data to the scratch schema, focusing only on table data.
Move the data from the scratch schema to production schema using a SQL script.
Adjust all sequence values after the data has been moved using a custom-built one-time-use PL/SQL script.
Add the PL/SQL script with all of the triggers that will auto-increment ID columns after the data has been loaded (this saved on the performance hit for running triggers for every single row added during data loading).