Provide tools to create and manage database view through your Rails projects.

Database views can be really useful providing kind of read only models. They can help you make your SQL code more reusable, clear, and sometimes are priceless to reduce complexity into models relations.

However, there's no useful tools offered by Ruby On Rails to deal with database views. Using the classic migration system can be fastidious if you want to update one of your view. Also, rollback/migrate a view is a non-sense, since there's no data integrity to deal with.

Finally, whenever you migrate and remove a field from a table used by a view, postgresql will throw an error, so removing views before applying migration is not an option.

We are proud to announce than rails-db-views works well into Rails 5 project. We are using it in already two differents projects, and everything works.

Due to another project, we needed to add the string replacement functionnality. Because we are using dblink extension, and we don't want to share the parameters like password or dbname straight into the view file.

  dblink('dbname=database user=user password=password', 'SELECT * FROM SOME_SQL')

Then become:

  dblink('dbname=#{ENV['DB_NAME']} user=#{ENV['DB_USER']} password=#{ENV['DB_PASSWD']}', 'SELECT * FROM SOME_SQL')

I fixed issues making the gem not working + added a more complex example/test. You can call "rake test" into the dummy to check (even if it's not automated unit test...). The example use heavily postgres functionnality, so it works only on postgres.

You can now create functions, just putting your .sql files into the db/functions directory. The functions will be created and/or replaced before all pending migration.

Moreover, you can force the deletion of a view using the new directive remove. Getting bored about this top_30_articles_in_belgium view and want a top_50_articles_in_belgium instead? Just set --!deleted into the file which defined the old view, and that's it! The view will be removed and not created again!

Be aware this is not working with function yet.

The configuration change a little bit. If you update, please check the instructions below.

I've made a refactoring into the gem, to make it more understandable and easy to fork. Also two views with the same name in differents paths will trigger an AmbigousNameError.

Errors and error messages are more clear (one type per kind of error).

First working commit I've fixed rails version to be able to use with rails >4.

Quite simple. Add rails_db_view in your Gemfile:

gem 'rails_db_views', github: 'anykeyh/rails_db_views'

Note: Don't use the rubygem one, it's probably outdated...

You create your views into db/views directory (create the directory if needed). All your views are files with "sql" extensions, without the directive CREATE VIEW xxx AS. Just the SQL (like classic SQL request then!) rails_db_views will use the filename without extension as view name (so avoid special characters like "." or "-" into filename...).

Whenever you'll do rake db:migrate or rake db:rollback, prior to applying the migrations, rails_db_views will remove all referenced views. After all migrations are done, rails_db_views will reconstruct each views.

You create your function without the directive CREATE FUNCTION xxx

Here's a simple example of the function add:

(x integer, y integer) RETURNS integer AS $$
  BEGIN
    RETURN x + y;
  END;
$$ LANGUAGE plpgsql;

IMPORTANT NOTICE FOR POSTGRES USERS:

If you're using function to build indexes in postgresql, please consider to reconstruct the index once you've changed your function:

reindex index $index_name

postgres is smart enough to don't replace the function if it's exactly the same function than before, so no need to rebuild your indexes at every migrations! :) See more informations here

I've tested the gem only on postgres and sqlite. Use it on mysql or else at your own risks.

Obviously, beware function definition doesn't work on sqlite, because the engine doesn't allow it. Hey, it's lite ok? :-)

In some case we need to build view using another view as target:

CREATE VIEW view_a AS SELECT 1 as id;
CREATE VIEW view_b AS SELECT id FROM view_a;

In this case, the view creation/drop order is important. I've made a directive system to manage view order:

In db/views/view_a.sql:

SELECT 1 as id;

In db/views/view_b.sql:

--!require view_a
SELECT id FROM view_a

That's it! You can also use #!require ... instead of --. But avoid space between the commentaries characters and the directive (!require). Now you can put any space you want!

For the functions, they will be installed before the views, so you can use them in your views without problems!

You can add/remove new paths in the initializers of Rails:

Rails.configure do |config|
  config.rails_db_views.views_path += %w( /some/view/path )
  config.rails_db_views.views_extension = "*.sql"

  config.rails_db_views.functions_path += %w( /some/function/path )
  config.rails_db_views.functions_extension = "*.sql"

  config.rails_db_views.migration_tasks += %w( db:migrate:with_data )
end

A project Kosmogo. Based on an idea of Jimmy Wang of Ekohe

MIT. Use it, fork it, have fun!

Happy coding!

Yacine.