HOWTO: Use a Rails model with an Oracle public synonym

Nov 11 ’08

The title of this post is somewhat misleading. It turns out that you can't use Oracle public synonyms with Rails, because of how ActiveRecord obtains information about the fields in its models.

The problem

Let's say you have the following model:

class User < ActiveRecord::Base
end

If you have a public synonym in your database called users AR will fail, with an error message about how it can't get column information for the users table.

Why is that?

Well, AR queries the all_tab_columns view to get the column list, with something like this:

SELECT * FROM all_tab_columns WHERE table_name = 'users'

Unfortunately, public synonyms don't have rows in all_tab_column, so no rows are retrieved.

The solution

The solution here is quite simple. You need to create a view into the public synonym:

CREATE VIEW users_v AS SELECT * FROM users

All user views have rows in all_tab_columns, so the query to pull column information will work. If you're feeling extra-special-clever, you'll put the CREATE VIEW statement into a migration.

I highly recommend giving the view a name that is different from the public synonym, unless you want a big ole' mess. If you're like me and work against legacy databases, you can take this opportunity to straighten out the naming conventions to match what Rails is looking for. If your table is already playing nicely with rails, just do this in your model:

set_table_name 'users_v'

Then everything will work as intended.

Why the solution is a problem

Having a view into another table, just for the sake of making Rails work, isn't quite ideal, especially if the view will be heavily queried by your app. The view creates a layer of database inefficiency, and will slow down all of your db requests. Thankfully, it's not huge; in my experience, we're never close to maxing out the database anyway, so the performance hit is negligible.