# New official direct SQL access API for Discourse (mini\_sql)
**URL:** https://meta.discourse.org/t/new-official-direct-sql-access-api-for-discourse-mini-sql/90441
**Category:** Development
**Created:** [June 22, 2018, 2:14am UTC](https://meta.discourse.org/t/new-official-direct-sql-access-api-for-discourse-mini-sql/90441 "2018-06-22T02:14:17Z")
**Posts on this page:** 1
**Page:** 1
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [June 22, 2018, 2:14am UTC](https://meta.discourse.org/t/new-official-direct-sql-access-api-for-discourse-mini-sql/90441/1 "2018-06-22T02:14:17Z")
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.
```ruby
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 ❤ with the broader community of Ruby developers.
[https://twitter.com/DatabaseSponge/status/1009776476290502657](https://twitter.com/DatabaseSponge/status/1009776476290502657)
1. Clean up out internal usage to reduce some of the duplicated patterns we found. (Yay for less typing 🎊 )
2. Address [some potential memory issues](https://samsaffron.com/archive/2018/06/13/ruby-x27-s-external-malloc-problem) I talked about on my blog.
3. Provide a dedicated place for tuning performance and API.
### Enter MiniSql
[https://github.com/discourse/mini\_sql](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: [DEV: remove exec\_sql and replace with mini\_sql · discourse/discourse@5f64fd0 · GitHub](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:
```ruby
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:
```ruby
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:
```ruby
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:
```ruby
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:
```ruby
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:
>
An important note here is our Discourse specific `#secure_category` helper that saves us some typing:
>
### 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](https://github.com/discourse/discourse-assign/commit/ffd4bd1a4a04b9ff443546ecec18740125c7d1fd)
```plaintext
# 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`