Introduction

In applications that interact with a relational database, it is often necessary to update certain data when some other data is changed. The examples of such tasks are counters, updating statistics and synchronization of data in a denormalized database. In Ruby on Rails development the standard tools for this are callbacks in ActiveRecord models or observers . Sometimes it makes sense to move the logic of this kind to the database. The classic reasons are optimization, reducing the load on the application and improvement of the app overall performance.

Another example of the necessity to have a similar logic in the database is when the data is loaded by another application/script and, therefore, it is impossible to use the callbacks. In the last case, it is definitely a bad idea to allow managing the database structure by several applications. This makes it inconvenient to develop such application: the logic is getting dispersed across the applications, it is not clear where to make changes and where to write a new code. That's why it is preferable to keep the logic of the database-level callbacks in the application that manages the scheme.

Active Record migrations in Rails

In the Rails applications there is a standard tool for managing the database structure - migrations ( ActiveRecord :: Migration ). In addition to providing DSL for defining the structure, it allows executing a raw SQL. It means that you can add, edit or delete the necessary triggers, stored procedures, etc. This approach has some cons:

The first, any change of trigger or stored procedure causes creation of a new migration.

The second, when there are logically related procedures or triggers, you have to search among previous migrations or open a file with structure, where the logically related parts are not always located beside each other.

The third, the callbacks' logic is a part of the application logic and so it is much more handy to keep it beside the rest of logic.

How to cope with the cons?

To handle the given disadvantages, you can create sql folder in app folder for sql-files with the triggers and stored procedures, grouped in the way convenient for developers. It is much better to have several files with the logically grouped code snippets than to pile up all your thoughts into one file, isn't it?

app / assets / controllers / models / sql / ← our folder for sql - files views /

We still have to resolve a question of importing sql-files to the database. To do this, we can write a custom rake task and put it on the application deploy.

task :import_sql do # The logic of importing sql to database end

There is a couple of issues. First of all, you should not forget to use rake each time when you change sql. Besides, you have to inform all new developers about this task. A more simple method is based on the fact that rake-tasks with the same names line up in a chain and do not overwrite each other. So, we can write something like this:

namespace :db do task :migrate do # The logic of importing sql to database end end

With this approach, you may not change your deploy-scripts (running migrations during the deploy is a common thing). New developers also will not have to guess why the application behaves differently for them, as they will most likely run rake db:migrate during the setup on a local machine.

The direct implementation of importing sql to the database looks like this:

Dir [ Rails . root + "/app/sql/*.sql" ]. each do | file | sql = File . read ( file ) ActiveRecord :: Base . connection . execute ( sql ) end

But there is a more optimal solution: merge all sql-files and upload them with a single query. Fortunately, the necessary tools exist and widely used by the Rails community. You can deal with sql-files in the same way as it is done with the assets, i.e. merge with the help of Sprockets.

Summary

Finally, I would like to offer a full recipe for solving the problem.

Create sql folder in the app folder with the required file structure:

app / sql / application . sql user . sql product . sql

File application.sql looks approximately like this:

/* *= require user.sql *= require product.sql */

Create rake-task with the following contents:

namespace :db do task :migrate do environment = Sprockets :: Environment . new do | env | env . register_mime_type ( 'text/sql' , '.sql' ) env . register_processor ( 'text/sql' , Sprockets :: DirectiveProcessor ) env . append_path 'app/sql' end ActiveRecord :: Base . connection . execute environment [ 'application.sql' ]. to_s end end

Well, that's actually it.