SQLBuilder

A simple SQL builder for generate SQL for non-ActiveRecord supports databases.

中文说明

Features

• ActiveRecord style DSL.
• Sanitize SQL by ActiveRecord methods, keep security.
• Support any SQL databases (MySQL, PostgreSQL, TiDB, Amazon Redshift...)

Installation

Add this line to your application's Gemfile:

gem 'sql-builder'

Usage

More API documents, please visit rdoc.info/gems/sql-builder.

SQLBuilder.new("SELECT * FROM users")
  .where("name = ?", "hello world")
  .where("status != ?", 1)
  .order("created_at desc")
  .order("id asc")
  .page(1)
  .per(20)
  .to_sql

=> "SELECT * FROM users WHERE name = 'hello world' AND status != 1 ORDER BY created_at desc, id asc LIMIT 20 OFFSET 0"

More complex case

query = SQLBuilder.new("SELECT users.name, users.age, user_profiles.bio, user_profiles.avatar FROM users INNER JOIN user_profiles ON users.id = user_profiles.user_id")

Add the conditions by request params:

query.where("age >= ?", params[:age]) unless params[:age].blank?
query.where(status: params[:status]) unless params[:status].nil?
if params[:created_at_from] && params[:created_at_to]
  query.where("created_at >= ? and created_at <= ?", params[:created_at_from], params[:created_at_to])
end
query.order("id desc").limit(100).to_sql

Returns string SQL:

SELECT users.name, users.age, user_profiles.bio, user_profiles.avatar FROM users
INNER JOIN user_profiles ON users.id = user_profiles.user_id
WHERE age >= 18 AND status = 3 AND created_at >= '2020-01-03 10:54:08 +0800' AND created_at <= '2020-01-03 10:54:08 +0800'
ORDER BY id desc LIMIT 100 OFFSET 0

Or

query = SQLBuilder.new("SELECT * FROM users")
  .where("age = ?", 20).where(num: 10)
query = query.or(SQLBuilder.new.where("gender = ? AND name = ?", 1, "hello world"))
query.order("id DESC").limit(100).to_sql

Returns string SQL:

SELECT * FROM users WHERE age = 20 AND num = 10 OR gender = 1 AND name = 'hello world' ORDER BY id DESC LIMIT 100 

Group by, Having

query = SQLBuilder.new("select user_id, name, count(ip) as ip_count from user_visits")
query.where(status: 1).where("created_at > ?", params[:created_at])
query.group("user_id").group("name").having("count(ip) > 2")
query.to_sql

returns

select user_id, name, count(ip) as ip_count from user_visits WHERE status = 1 AND status = 1 AND created_at > '2020-01-03 10:54:08 +0800' GROUP BY user_id, name HAVING count(ip) > 2"

License

The gem is available as open source under the terms of the MIT License.