Database views and Rails
17 12 2007When it comes to using database views then most of us think that it is difficult to use them with Rails or we may not be able to use ActiveRecord with it. Since I experimented with it in the past so I thought why not share it with you folks.
There are several situations when we would like to use database views with our Rails application like:
Situation 1:
If we have a complex SQL query that we don’t want to code in your Rails app due to performance related or any other issues.
Situation 2:
When our database guys have already spent a great amount of time writing useful views that can be used in the current app
Situation 3:
When we have some repeated query to run in every select statement to fetch data from every table. For instance
Select * from table_name where created_on < today;
In that case we would prefer to have a view with this query and call that view.
In all the above situations, it is pretty easy to use our database views with Rails.
How?
Simply create your model as you would for a table and it will work fine just like your model for a database table.
Interesting thing is, apart from viewing data using views I can even update, delete and insert data in my database tables using my views with all those ActiveRecord/ORM niceties (in Sybase) as long as each database view consists of only one table. If you are using more than one table in your database views then you can only view data but cannot do delete, update and insert operation.
I think this is really useful if we have a database that do not conform to Rails conventions but we would like to follow those conventions without changing the current database if other applications depend on it as well. Simply create views for each table in the database and use those views for our models instead of actual tables.
Those single table views (in other words updatable views) will allow us to follow Rails database conventions without changing our existing database tables while also keeping our database guys happy ;o)
(Though we also have other options if we are not following rails conventions)
NOTE: I have tried updatable views only with Sybase and would love to know about your experience with other databases.
Sign up and be a part of SPhred.com and don’t forget to invite your friends ;o)
Hi Nasir,
thanks for sharing this. I am actually thinking about using views for a different reason: Different users in my app can see different sections of data, even in just the one table. So I thought that creating different views of that table will act a security mechanism for me, hiding the data that I can’t show to certain users.
Regarding the updatable views, although I haven’t tested it yet, I remember from my DB lectures in college that a view can be updated as long as you have all the primary keys of all the tables involved in the view in the view itself. This would also apply to all the “mandatory” fields in the tables involved.
cheers,
José
Hi Jose,
Absolutely right, that’s one of the main reasons behind using database views and thanks for pointing it out.
my friend, you are a God. I’ve been looking at how to use views and stored procedures in Rails for quite sometime and you’re right, there is no documentation out there.
My point in using views is to mash together databases. Using linked servers in SQL Server you can call two different database in a view and combine the results with a union all.
Thanks once again.
No luck for me on SQLLite –
>> OtherPerson.find(:first).save
ActiveRecord::StatementInvalid: SQLite3::SQLException: cannot modify other_people because it is a view
Looking around it appears that it may be possible with a trigger on the view, but that’s too much maintenance for my current needs.
Oops, misspelled SQLite as SQLLite
Never tried it with SQLite so can’t comment on that …..
Hi Nasir,
been doing the same thing on MySQL 5.0 and so far I’ve had no problem either. And yes, views are updatable too on MySQL.
cheers!
Jean-Marc