oracleRuby on Rails projects usually use MySQL or PostgreSQL for their database, but in the corporate world, Oracle is king. As much as you might like to have a Postgres backend, the powers-that-be have decreed and you must obey. Don’t worry though, all is not lost, you don’t have to slink back to Java, here is how you can get your Rails app working with an Oracle database. Note: All of this works with Rails 2.3.5, I haven’t tried it with Rails 3, but feel free to give it a go and don’t forget to let me know how it works out.

Getting Rails To Play With Oracle

Alright, first thing first, I hope you’re using RVM, cause it’s awesome and will make your life easier. With that out of the way, you need to install two gems, before trying to use Oracle with Rails.

  1. ruby-oci8 gem – this is the ruby interface for oracle using the OCI8 API. You need to have a version of Oracle installed on your machine for this gem to work, otherwise the OCI8 library will not be available on your machine and this will make your life difficult (i.e. impossible :)), as you might imagine.
  2. activerecord oracle enhanced adapter gem – this is an ActiveRecord adapter that has useful extra methods for working with new and legacy Oracle databases from Rails.

You can potentially vendor the oracle enhanced adapter gem within your rails app, but I wouldn’t vendor the ruby-oci8 gem as it has native extensions. Normally that would be fine, but we’re going to make a Java application out of this in the end (for deployment to Tomcat) and native extensions will once again make life very difficult (i.e. impossible :)).

So, to install these gems we do:

gem install ruby-oci8
gem install activerecord-oracle_enhanced-adapter

This will usually go smoothly, but I’ve had one situation where the native extensions failed to build properly when installing ruby-oci8, and I couldn’t find anything that should have caused that. To fix the issue, I simply compiled it from source and then re-installed it as a gem again, at which point everything went smoothly. Something to keep in mind.

Rails Configuration

As you would expect, we now need to modify our database.yml to configure our Oracle database. It is reasonably straight forward, but there are a few things to be aware of. By default your database.yml file will have something like this:

development:
  adapter: mysql
  database: rails_development
  username: user
  password: pass

Of course there will also be similar entries for test and production.

Oracle has a slightly different model, we don’t have to create different databases, we create different database users, so the username and password are used to distinguish between different Oracle dbs.

Our configuration will now look like this:

development:
  adapter: oracle_enhanced
  database: YOUR_ORACLE_SID
  host: localhost
  username: user
  password: pass
  port: 1522

The differences from the norm are as follows:

  • you need to have an adapter entry and the value will always be _oracleenhanced
  • the database entry should contain the SID of your oracle installation, this too will always remain the same
  • as I mentioned the username and password are not just the credentials but will also identify the Oracle database (as per how Oracle works)
  • if you changed the default port (the default is 1521) when installing Oracle you need to specify that too

You will once again need similar entries for test and production to maintain the standard Rails conventions.

Modifying Your Normal Workflow

Of course things are not quite as simple as that (they never are with Oracle :)). Firstly, some of the rake commands you’re used to will not work with Oracle, such as:

rake db:create
rake db:drop

You will need to create your Oracle databases by hand, which in “Oracle world” means you will need to create some users. You have to log in to Oracle as an admin user, create another user and give them the right privileges. To log in to Oracle using sqlplus, you can do the following:

sqlplus admin_user/admin_password as sysdba

You can then execute a series of SQL commands similar to those below, in order to create a new user and give them the right privileges:

CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE some_tablespace TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON some_tablespace;
CREATE ROLE my_role;
GRANT all privileges TO my_role;
GRANT my_role TO my_user;

The tablespace and quota stuff is optional (I think :)) so feel free to leave it out. If things are still not working after you’ve configured it all in Rails and tried it out, you can also try:

GRANT all privileges TO my_user;

This should fix most of the issues you might encounter. At this point all the migrate commands should work, such as:

rake db:migrate
rake db:migrate:down

These are the important ones as far as I am concerned, so I am not too worried about loosing some of the others.

One last thing to note, if you don’t create a separate development and test user due to laziness or whatever (which is exactly what happened in my case :)), be aware that if you run tests through rake all your dev data will be blown away (Rails blows away the data in the test database every time).

But What If You Need To Deploy To Tomcat?

Once again the corporate overlords make their presence felt :) and you’re only allowed to deploy to Tomcat. You can develop everything in JRuby and then roll a WAR file in the end, but why bother when you have Warbler.

Warbler is a gem that will make a WAR file out of your Rails app – pretty convenient. They way it does this is by bundling a copy of JRuby (i.e. you don’t need to supply it) and then packaging all the resources in your Rails app into a WAR structure in a sensible fashion. To install Warbler all you need to do is:

gem install warbler

There are a few different things you can do with Warbler, but you really just want to create a WAR file, so all you need to do is go to the root of your Rails app and type:

warble

This will produce a WAR file for you which you can deploy directly to Tomcat – easy. There are however a few things to be aware of, especially in our case.

  1. As I mentioned already, make sure you’re not using any gems that have native extensions (e.g. hpricot). Warbler will try to bundle all the gems it needs and it will be pretty difficult (i.e. impossible :)) to do the compilation steps from within a container.
  2. We’re using Oracle, but we will not need the ruby-oci8 gem (which has native extensions), instead we can use the ojdbc14 JAR. All you need to do is place the JAR file in your Rails application’s /lib folder and Warbler will take care of the rest.
  3. Incidentally, if you need to include any other JAR files also put them in the /lib folder of your Rails app.

That’s it, unless you were doing something really fancy, you should be able to produce a WAR file, drop it into Tomcat and have a working Java web application with no extra effort. The good thing about this is that you can develop your application in pure Ruby (i.e. you don’t need JRuby), JRuby only comes into the picture when you need to deploy. I would recommend deploying as frequently as you can though, to make sure you don’t run into any unexpected surprises.

If you think this unlikely scenario (using Ruby on Rails with Oracle for dev and deploying it all as a Java app to Tomcat) can never happen, think again. This is all based on “real world” events and no animals were harmed in the making of it :). There are a couple of other interesting things we did in the course of that project, such as serving user uploaded images from outside the Rails folder structure with the help of quick post about that at a later date, so if you’re interested – stay tuned.

Image by ellen reitman