New official direct SQL access API for Discourse (mini_sql)

Dev
1

I just finished “officializing” and “extracting” our direct DB access helpers in Discourse. I would like to cover what was done and what this means to plugin authors and core contributors.

From the very first version of Discourse, @eviltrout introduced some helpers internally for safely executing direct SQL in Discourse. It looked like this.

ActiveRecord::Base.exec_sql("select * from topics where id = :id", id: 1)

# or

Topic.exec_sql("select * from topics where id = :id", id: 1)

This is a pattern he used for many years dating back to very early Rails projects he had. It served us really well over the years, in fact so well, that we decided to extract it from Discourse together with my SqlBuilder pattern and make it an official gem ™.

This extraction was done for a few reasons:

  1. Share mini_sql love :heart: with the broader community of Ruby developers.

  1. Clean up out internal usage to reduce some of the duplicated patterns we found. (Yay for less typing :confetti_ball: )

  2. Address some potential memory issues I talked about on my blog.

  3. Provide a dedicated place for tuning performance and API.

Enter MiniSql

https://github.com/discourse/mini_sql

Starting from Discourse 2.1 beta 1, MiniSql is the only endorsed way to run direct SQL statements in Discourse.

The majority of the migration was done in: https://github.com/discourse/discourse/commit/5f64fd0a217552d1e031a28732a0e6000b090cc4 .

What this means to you?

In the past if you needed to run some direct SQL in Discourse you would write:

report = []
Topic.exec_sql("select id, title from topic where id < 200").each |row|
   report << " id: #{row["id"]} title #{row["title"]}"
end

MiniSql allows us to avoid needing to use a Hash here and instead:

report = []
DB.query("select id, title from topic where id < 200").each |row|
   report << " id: #{row.id} title #{row.title}"
end

Further more, from a performance perspective the “no hash” version MiniSql provides is actually a bit faster than the usage that involves the hash (and safer)

As a plugin developer if you are using exec_sql anywhere a new deprecation notice will appear, linking to this very post.

The MiniSql toolkit

We now provide a few different APIs for direct SQL access.

DB.exec : run SQL returning row count.
DB.query : run SQL returning an array of objects
DB.query_single: run SQL returning a flat one dimensional array.
DB.query_hash: run SQL returning an array of Hash objects
DB.build: start composing a SQL statement

All methods support parameters (both ? and :key).

So, for example you can use the ? usage per:

n = DB.query_single(
   'select count(*) from users where name ilike ? or name ilike ?',  '%bob%', '%bill%'
).first
# 22
puts n.class
# Integer

Or the hash usage per:

n = DB.query_single(
   'select count(*) from users where name ilike :name1 or name ilike :name2',  
     name1: '%bob%', name2:'%bill%'
).first
# 22
puts n.class
# Integer

Short examples for various helpers:

puts DB.query("select 1 as a").first.a
# 1

puts DB.exec("select 77")
# 1 ... 1 row was selected

puts DB.exec("update topics set title = ? where title = ?", 'old title', 'new title')
# either 1 or 0 depending on if the title existed

p DB.query_single("select 1,2 union all select 3,4")
# [1,2,3,4]

p DB.query_hash("select 1 as a, 2 as b union select :three, :four", three: 3, four: 4)
# [{a: 1, b: 2}, {a: 3, b: 4}]

The query builder

Another rich example from our source code demonstrates usage of the query builder:

https://github.com/discourse/discourse/blob/2d59d06916b6493f7642c50162b0cb30c4b8d97b/app/models/topic_link.rb#L40-L67

An important note here is our Discourse specific #secure_category helper that saves us some typing:

https://github.com/discourse/discourse/blob/2d59d06916b6493f7642c50162b0cb30c4b8d97b/lib/mini_sql_multisite_connection.rb#L9-L16

Dealing with the deprecations as a plugin author

As a plugin author you need to make sure you stop using exec_sql this can be a bit tricky if you want to keep supporting the stable branch that does not have mini_sql.

There are 2 options you have:

  1. You can fork your plugin and create a stable branch and fix the deprecations in the master branch

  2. You can write cross compatible code (something I opted for when there is only small amounts of change needed)

An example of “cross compatible” pattern is:

https://github.com/discourse/discourse-assign/commit/ffd4bd1a4a04b9ff443546ecec18740125c7d1fd

# TODO: remove once Discourse 2.1 is released
if defined? DB
   DB.exec("select 1")
else
   Topic.exec_sql("select 1")
end

Let me know if you have any questions, I did check all the_the_plugins and it does not look like too many changes will be needed as most of our plugins do not use exec_sql

20 Likes