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 RoR development the standard tools for this are callbacks in ActiveRecord models or observers. Moving this type of logic to the database can be beneficial for a few reasons. One classic reason is to optimize performance and reduce the load on the application, leading to an overall improvement in app performance.

Another situation where it may be necessary to have similar logic in the database is when the data is loaded by a different application or script, making it impossible to use callbacks. In such cases, allowing multiple applications to manage the database structure can be problematic. It can result in scattered logic across various applications, making it unclear where changes should be made or new code should be written. Therefore, it is preferable to keep the logic for database-level callbacks in the application responsible for managing the schema.

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.

1. 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
*/

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

There is, however, a small drawback of managing database logic in rails application with this approach. Before each declaration of a new function, stored procedure or creation of a trigger, there must be its own DROP (FUNCTION / TRIGGER) iF EXIST and/or CREATE OR REPLACE. There is no versioning and the migration mechanism does not know if there were changes in files and if they are new or were imported in the database a long time ago. Therefore, all the existing functions will be overwritten at every rake db: migrate.