Writing SQL in pure Ruby

Ruby is a flexible language that allows you to create different DSLs on its basis. An idea appeared to write DSL for SQL queries (for SELECTs), as close as possible to the original. It seems to work out. The resulting thing 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:

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

If someone does not understand, we write SQL queries in pure Ruby. Now try something more complicated.
')

  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 that contain in the header '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, it was necessary to change the order of SQL statements. First you must go from. In it, the table name is indicated as a symbol. You can set aliases for tables, if you specify their list in the form of a hash, as is done in the second example. Next must be join's. It will be easier to write down the possible order of calling 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 takes care of getting the correct SQL query.

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

Due to some restrictions on Ruby overloading the operators, I had to do a little bit more:

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

Very important: when using the last two operators, the operands MUST be enclosed in brackets, otherwise in a SQL query it may not turn out at all what you expect. This is because these two operators have high priority in Ruby.

Boroda is currently a small module that generates 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 yet, because it has not been tested enough yet and may be unstable to SQL injections.

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