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.

HolmanAuto/sql-builder