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_sqlReturns 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 0Or
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_sqlReturns 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_sqlreturns
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.