Writing SQL in Pure Ruby

Ruby is a flexible language that allows you to create various DSLs on your basis . There was an idea to write DSL for SQL queries (for SELECTs), as close as possible to the original. It seems to work out. The resulting piece is called Boroda . The following are code examples.

require 'boroda'
sql = Boroda.build do
  from: posts,: users
  select posts. *
  where (posts.author_id == users.id) & (users.name == 'Vlad Semenov')
end

We get : If someone does not understand, we write SQL queries in pure Ruby. Now let's try something more complicated.

SELECT posts.*
FROM posts, users
WHERE (posts.author_id = users.id) AND (users.name = 'Vlad Semenov')

min_rating = 5
sql = Boroda.build do
  from: posts =>: p
  left join: comments =>: c
  on c.post_id == p.id
  select p.id, p.title, p.content, c.id.count =>: comment_count
  group by p.id
  where (p.title.like '% programming%') | # Select all posts containing the title 'programming'
        (p.rating> min_rating) # Or with a rating of more than 5
  order by p.created_at.desc
  limit 10
  offset 20
end

We get:

SELECT p.id, p.title, p.content, COUNT (c.id) AS comment_count
FROM posts AS p
LEFT JOIN comments AS c
ON c.post_id = p.id
WHERE (p.title LIKE '% programming%') OR (p.rating> 5)
GROUP BY p.id
ORDER BY p.created_at DESC
LIMIT 10
OFFSET 20

Now about how to write queries using Boroda. Due to some technical difficulties, the order of SQL statements had to be changed. First you must go from. In it, the table name is indicated as a symbol. You can set aliases for tables by specifying their list as a hash, as is done in the second example. Join should follow. It will be easier to write down the possible order of calling the methods in the following form:

from tables
[[left | right] [outer | inner] join tables
on condition | using columns
[..]]
[select columns]
[where condition
| group by columns
| having condition
| order by columns
| limit number
| offset number] *

In other words, you can call all methods from the last group in any order. Boroda will take care of getting the correct SQL query.

Now more about the condition that is used in where and having. Using the following statements has exactly the same meaning as they have in SQL:
+, -, *, /,>, <,> =, <=.

Due to some of Ruby’s restrictions on operator overloading, I had to twist a little:

a == b # => a = b
a <=> b # => a <> b
(a) & (b) # => (a) AND (b)
(a) | (b) # => (a) OR (b)

It is very important: when using the last two operators, operands MUST be enclosed in brackets, otherwise the SQL query may not produce exactly what you expect. This is because these two operators have high priority in Ruby.

Boroda is currently a small module generating SQL for SELECT queries. The module code is on GitHub . If someone has a desire, then Boroda can become a gem.

I warn you in advance that I do not recommend using this code in production'e, because it has not yet been tested enough and may be unstable to SQL injections.

It is very interesting to hear your opinion about this DSL and get useful tips for improvement :)