Gem SQLBuilder - 一个简单的 SQL 生成器

huacnlee · 2020年01月03日 · 最后由 luikore 回复于 2020年01月06日 · 9108 次阅读

最近公司的 Rails 项目里面有非 ActiveRecord 的数据库需要用 SQL 查询,比如 Amazon Redshift 之类的,这些表没有 Model,实现的时候查询基本上是手写 SQL,纯字符串拼接那种。

随着业务越来越复杂,SQL 拼接也越来越复杂,于是开始质疑为何不用类似 AcitveRecord 那种 DSL 的查询方式 where("name = ?", params[:name]).where(...).limit(100)

然而我查询了 ActiveRecord 以及 Ruby 社区现成的 Gem 发现,干这个事情的居然没一个能满足的,我一开始本以为 AcitveRecord 的 Arel 可以干这个事情,结果尝试下来看,那个查询方式和 ActiveRecord 的 DSL 用法不一样,而其他社区里面做这类事情的比如 mini_sql 也是以执行为主的,需要依赖 SQL Connection。

于是我按照我们的场景构造了一个简化的 SQL Builder,让它只干一件事情:“基于 DSL 生成 SQL 语句”

功能特点

  • 针对纯写 SQL 的场景设计,某些复杂业务查询你可能不一定想创建出 ActiveRecord Model;
  • 仅做 SQL Builder 的事情;
  • 类似 ActiveRecord style 的 DSL;
  • 用 ActiveRecord 内置的 Sanitize 函数来处理参数,确保安全;
  • 更简单的逻辑,以适应各种奇怪的 SQL 数据库,sql-builder 没有复杂的 SQL 生成,仅仅帮你做拼接简化。

安装

往 Gemfile 增加:

gem "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"

结合实际业务场景,我们的 SQL 条件可能会很复杂:

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")

# conditions by 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

=> "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"

从上面这段你可以看到,那些复杂的 SQL 依然还是你自己写的,SQLBuilder 只是在 whereorderlimit 这些场景可以帮你处理复杂逻辑,因此,它几乎不会因为内部生成了某些 SQL 数据库不支持的语法。

项目地址

https://github.com/huacnlee/sql-builder

这个 SQLBuilder 确实很好用,select count(*) 的时候可以复用 query,

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

count_query = SQLBuilder.new("SELECT count(*) FROM users")
count_query.where(query)

处理过类似的查询,从使用 AR 到 raw SQL,最后又回到了 AR 😄

  1. 其实各个数据库都有 adapter,AR 完全可以支持,比如 redshift:https://github.com/ConsultingMD/activerecord5-redshift-adapter TiDB 这种,只用 mysql adapter 就行
  2. AR 也可以只做生成 SQL,query.to_sql 就行,甚至可以自己组合,比如 query1 = xxx; query2 = User.joins("INNER JOIN (#{query1.to_sql}) as t")
  3. 二楼说的复用,AR 也可以:
query = User.where("id > 1").order("xx").limit("xx")
count = query.unscope(:select).unscope(:order).select("count(*)").group("id")
hooopo 回复

好像只能用在 select 上,Insert Delete Update 调用链最后不是 AR 的 Query 了,没法调 to_sql

jasl 回复

update/delete其实不复杂 没必要 DSL 了

hooopo 回复

(我不会用,但你得有,逃~

hooopo 回复

那得构建 User model,我们的场景一些表没 Model 了

另外,多表查询,且条件复杂的时候,就明显了(比如一些复杂的报表)。

huacnlee 回复

为什么不建一个 model 呢,也不复杂;多表用 AR 只会更简单,查询场景越复杂用 AR 越简单。如果有场景的话,我可以用 AR 来演示一下。

hooopo 回复

一个大系统,10 个数据库,180 多表,其中一些是特定场景的业务表,使用量少,仅仅是需要查询出报表,这些场景都是查询,没有更新。

复杂性高,又用得少,加 Model 涉及到增加 Schema、Migration 什么的,图省事,这类直接连外部数据库,这部分业务不开发的时候就不用在意。

所以这种场景我们考虑以后就只做简单 SQL 查询,开发好自测就可以,测试也不写,因为他们都是一些查询,场景难以模拟,就算模拟出来也会让整体测试慢下来。

所以,我感觉还是我 Demo 举例不够准确 😂

启动的时候,把所有表读出来,然后动态创建 model 呢?

好吧,Schema、Migration 其实不是必须的,establish_connection 一下就好,不过即使写报表的话,也需要知道各个表的关联关系,有一份映射其实省去很多重复劳动。

之前做过报表需求,几个外部数据库,查询还是用 AR,一个数据库对应一个目录和一个数据库连接配置,模型层设为 read only,AR 不需要的功能可以不用。

没有 ar, 为什么不直接 arel table 呢?

关联表应该根据 where 条件自动关联,实际业务可能要根据参数来决定查询条件,查询条件又决定了关联表

wpzero 回复

Arel 写出来和 ActiceRecord 的用法不一样,另外你可以试试,实际上没那么好写

可以稍微再简单点

def SQLBuilder s
  SQLBuilder.new s
end

SQLBuilder("SELECT * FROM users")...
需要 登录 后方可回复, 如果你还没有账号请 注册新账号