Database views and Rails

When 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.

Advertisements

33 thoughts on “Database views and Rails

  1. Jose says:

    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é

  2. tony petruzzi says:

    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.

  3. Jordan Brough says:

    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.

  4. abhi says:

    Hi Nasir,

    I am having problems accessing existing database views. These views are based on tables being modified by a different application. eg of a view name is ‘__CC___LONDON_STAFF’ .

    I know that these views don’t adhere to the normal rails convention but these wern’t created by me. i can map the models to similar named tables using the set_table_name method, but this doesn’t seem to work with the views.

    any suggestions would be a great help. I am using Ms-sql server 2000 as the database, and am using rail 2..

    many thanks,
    abhi

  5. nasir says:

    Abhi

    I think the first step would be to create a simple view yourself and check whether it is working or not?

    For instance, for table Article or article create a view with a different name called articles and corresponding model Article.

    See what happens then?

  6. abhi says:

    Hi Nasir,

    I tried creating two different test views using sql enterprise manager. I based one of the views on a single table and the other one on two related tables.. I tried creating a scaffold for each view at a time and then tried migrating to the current version.
    it kept failing saying that a table with that name already exisits. For some reason it was creating a new table with an id and timestamp info.
    i had a look at the schema.rb

  7. abhi says:

    I had a look at the schema.rb file.

    it is listing all the tables but not listing any of the views?
    i am not sure it is accessing any of the views from the database.

  8. nasir says:

    I am not sure why do you need to run the migration for view if your table is already in the database and you are only referring to the view.

    Can I suggest that once your view and model is ready then simply fire up the console and check whether you can do CRUD operations on your view/model?

    If that works then that means it is fine.

    I would also advise to remove any migration file created when you create a model based on the view rather than the table because there is no point in running migration for views or there is?

  9. abhi says:

    hi got it working thanks
    Me trying to migrate to the current version was causing the problem. I removed the migration file that was created and it seems to work like a charm. Thanks for your help.. much appreciated.

    abhi

  10. abhi says:

    hi Nasir,

    I am back with a small problem. The view I was trying at access has a field called Job-Title’. Notice that its a hyphen and not an underscore here.
    In the index.html.erb file I can manage to display all the other fields using the for loop apart from this one.
    Think ruby associates hyphen as minus operator. When I tried displaying t.Job_Title and reloading the page it came up with undefined method ”Job’
    Is there a way to display this column values? If I render it using xml I can see the Job-Title being displayed.
    I know i can create an additional column in the view with a suitable jobtitle name but was interested to know if there was some way to deal with such situations.

    thanks,

    abhi

  11. John Grimes says:

    The problem that I have encountered with using views is that Rails has no language independent way of creating them, so it ignores the fact that it is a view and not a table when it generates the schema.rb file upon migration.
    I use raw SQL (MySQL) to create the view in the migration, but the schema.rb file gets generated with code (create_table) that creates a table, not a view.
    This screws up my tests because when the test database is generated from the schema.rb file, a table is created instead of a view, and as a result of this no data is accessible through that table.
    I agree with you that views would be a really nice thing to be able to use in certain situations, but unless I can figure out a way to use them without screwing up the testing process I don’t think I will be able to use them.

  12. Ajmal says:

    Hello Nasir,

    I am Ajmal from India, we are budding ror developers. We would like to know how we can easily view database content in view ie HTML pages with CRUD functions in ror.

    ie I have a contact us form and, it is regularly populated.

    But we are checking through heidisql but would like to have a page created within the website with database login id and password and from there see the database edit or add few comments on status of thier queries etc.

    I am eagerly awaiting your help in this regard

  13. nasir says:

    Ajmal,

    You can easily do that using the scaffold generator which will give you the ability to do CRUD operations on your model straight away. Here is the tutorial if you are very new to rails http://www.tutorialspoint.com/ruby-on-rails/rails-scaffolding.htm

    But I doubt that you can have a separate section within your site for a database login id and password as in rails the connection to the db is made during app initialization time so obviously you will have your db username and password in the database.yml file.

    However, you can implement some access control to allow access to only certain sets of people. There are plugins for that like authlogic [http://github.com/binarylogic/authlogic].

  14. harchC says:

    Hi Nasir
    I have a ROR project code and there is a schema.rb file. I need to create some links on one of the view.htm.erb files that would create a CSV file showing reports containing data from the views made by tables present in schema.rb. I had SQL queries with me to create those views and the code I have made for using those views to create a CSV file.
    I am really new to ROR. I don’t know where to put the SQL queries in the app code(whether in model files or controller files itself;if in controller then how to run the SQL queries) so that a view will be created somewhere and I can use that view from some place. BTW the project has PostgreSQL kind of database.
    I need to complete this issue ASAP. Could you help me out or give me some pointers on this.

  15. nasir says:

    @harchC

    1. For creating views, the best place would be to add the code to migration, i.e. create a migration and since I dont think that rails provides methods to create views, so in your migration you can do something like

    def self.up
      execute("your create sql view code")
    end
    And dont forget to drop the view in self.down method

    2. Once you have view, lets say cancelled_orders, then generate a AR model CancelledOrder and have all your class / object responsibilities in this class.

    3. Have minimal code in controllers. Use them mainly for routing request

    Hope that helps.

    • harchC says:

      I have two issues:
      1.What is creating a migration means? How should i create a migration?
      2. What is the meaning of dropping the view in self.down method?
      Can the view created in migration(I don’t where) be used in controller file so that i can access its rows in generating CSV files?

      I don’t know whether questions are silly but I am really blank on this issue.

  16. nasir says:

    This is the google link that should help you with migration

    http://www.google.co.uk/search?q=create+rails+migration

    However, I suggest you have a look at some rails beginner guide/tutorials to get upto speed with rails basics since there are lot of good resources on internet and there is no point in me writing here again.

    You can start here http://stackoverflow.com/questions/2666895/beginners-guide-for-rails or http://www.google.co.uk/search?q=rails+beginner+tutorials

  17. harchC says:

    HI Nasir

    I really some urgent help.
    Let’s say I create a migration file create_events_view.Then I create a model fle inn app/model EventsView. Then how to include this view into this model so that I can use this view into controller file. I am in real fix here. Any kind of help will be greatly appreciated.

    • nasir says:

      If your view is called ‘events’ then create a model called ‘Event’ that must inherit ActiveRecord::Base. Thats it and rails will handle everything else.

      Just make sure that your view name is pluralized and your model name is singular

    • nasir says:

      You can but dont ever do it.

      Have you googled about rails tutorials and read some? Have read some basic guide lines about using rails and what is the idea behind rails? Do you know what an MVC pattern is? If the answer to all this is no then first do some reading on these which will help you a lot and then use rails otherwise you are just wasting your time.

  18. harchC says:

    Hi Nasir

    Actually, the query which I am using for fetching records from the view that has more than 1lac 50 k records. So the heroku is giving application crash. Can I give some kind of filed in find(:all,…) statement so that the application will show the entire 1.5 lacs records.
    Some kind of connection timeout or text size..
    Please help me out in this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s