Writing SQL UPDATE statements with Arel

12 Feb 2016

Arel is a relational algebra library in Ruby. It powers Rails’s ActiveRecord query interface and can be used to build complex SQL programatically, without the need for error-prone string manipulation.

We can use Arel outside of Rails models to build and execute a simple SQL UPDATE statement.

Assume we have a database table, users, with a column name that we’d like to update for the user with an ID of 42.

Here we use the Arel::UpdateManager class, which knows how to build and manipulate SQL UPDATE queries:

table = Arel::Table.new(:users)
update_manager = Arel::UpdateManager.new table.engine
update_manager.set [[table[:id], 42], [table[:name], "Jane Smith"]]
ActiveRecord::Base.connection.execute update_manager.to_sql

Notice we use the table object to refer to columns of the users table.

This can be especially useful when writing Rake tasks to manipulate data, as the SQL that Arel produces is more likely to be compatible with your database than strings of SQL produced by hand.