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.
Finally, I would like to offer a full recipe for solving the problem.
sqlfolder in the
appfolder with the required file structure:
app/ sql/ application.sql user.sql product.sql
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.
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.