Raw SQL，Query Builder与ORM

一.三层抽象

为了便于操作数据库，聪明的人类添了一些抽象层：

• 底层抽象：Database Driver，连接数据库并与之通信，发出操作指令，取回操作结果

• 中层抽象：SQL Query Builder，生成操作指令

• 高层抽象：ORM，建立模型对象到关系型数据库的映射，之后，对模型对象的操作自动映射到数据库中

三者之中，Driver 几乎是必须的，除非想要控制 TCP 连接、数据库通信协议等底层细节。Query Builder 是可选的，手写 SQL 区别不大。ORM 自然也是可选的，毕竟不一定需要这种“重武器”

二.Database Driver

Database Driver 负责连接数据库，并实现客户端与数据库之间的数据传输协议：

The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

有了 Database Driver 就可以很方便地连接数据库，并执行后续查询操作了。例如：

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

（摘自 mysqljs/mysql ）

美中不足的是 需要手搓 SQL 语句（Raw SQL） ，拼字符串的操作一来繁琐容易出错，二来不太安全（存在 SQL 注入的隐患）

于是，出现了一种叫 Query Builder 的东西

三.Query Builder

Query Builder 是针对 SQL 的抽象，用来快速生成 SQL 语句，避免手搓：

You can quickly create or edit SQL statements without actually typing any SQL code.

例如，要从 users 表查询 id 为 9527 的记录的 name 字段的话，用 Query Builder 可以这样描述（以 Knex 为例）：

knex.select('name').from('users').where('id', '=', 9527)
// 或
knex('users').select('name').where('id', '=', 9527)
// 或
knex('users').select('name').where({id: 9527})

生成的 SQL 语句为：

select "name" from "users" where "id" = 9527

Schema Builder

以上提及的针对表记录的操作，如增删改查，我们称为 Query。此外，还有针对表、视图、存储过程等的操作，比如建表、加索引、创建视图/存储过程等，称之为 Schema

有 Query Builder，自然也有 Schema Builder，用来生成 Schema 相关的 SQL 语句。例如（仍以 Knex 为例）：

knex.schema.createTable('users', (table) => {
  table.increments();
  table.string('username').unique().notNullable();
  table.string('password').notNullable();
});

生成的 SQL 语句为：

create table `users` (
  `id` int unsigned not null auto_increment primary key,
  `username` varchar(255) not null,
  `password` varchar(255) not null
);
alter table
  `users`
add
  unique `users_username_unique`(`username`)

共同特点是能够通过我们所熟悉的编程语言（上例中是 JavaScript）语法来描述数据库操作，但这并 不是说有了 SQL Query/Schema Builder 就不再需要了解 SQL 了 ，毕竟 Builder 只是能够生成 SQL 语句，而不是替代它，就像 Emmet 之于 HTML

P.S.Knex Query Builder 与 Schema Builder 都可以在线试玩：

• Knex-playground

• Knex Query Lab

优势

Query Builder 确有其优点：

• 跨数据库：这层抽象能够抹平数据库特定的 SQL 语法差异，例如 MSSQL 不支持 limit 子句

• 松耦合：解除 Query 创建与序列化的紧耦合，尤其在动态创建查询的场景，比手动拼接 SQL 字符串友好很多

• 安全：Query Builder 会对参数进行转义，能在一定程度上防范 SQL 注入等潜在安全问题

• 轻量：毕竟主要工作是拼接字符串，不会很重

• 语法“自然”：与 SQL 相比，Query Builder 的语法更贴近我们所熟悉的编程语言

• 可读：勉强算是优势，因为 SQL 本身可读性也不差

跨数据库算是在 Database Driver 之上加一层抽象带来的灵活性好处，在处理多个项目/多个数据库时很有用，比如经常需要针对不同数据库写 SQL 时，这层“沥青”能够抹平差异

另一个重要作用是解耦， 面向裸 SQL 的字符串拼接中，Query 的创建与序列化耦合在一起 。例如在嵌套子查询之类的组合场景下，需要按顺序拼接字符串，我们在考虑创建查询的同时，还要关注其序列化细节，确保关联查询在结果 SQL 中的顺序正确：

select
  *
from
  `accounts`
where
  `id` in (
    select
      `id`
    from
      `users`
    where
      `votes` > 100
      and `status` = 'active'
      or `name` = 'John'
  )

而 Query Builder 将 Query 的创建与序列化分离开了 ：

var subquery = knex('users').where('votes', '>', 100).andWhere('status', 'active').orWhere('name', 'John').select('id');

knex('accounts').where('id', 'in', subquery)

创建 Query 时不必关注 SQL 的序列化细节，不用再小心地控制嵌套和顺序

缺点

主要缺点在于：

• 限制：Query Builder 一般无法覆盖 SQL 的所有用法，一些场景下仍然需要手搓 SQL 语句

• 性能：工具按既定规则生成的 SQL，简洁程度和性能都比不了人工思考优化过的产物

比如 Knex 并未对 View （视图）和 Stored Procedure （存储过程）提供 Builder 支持，相关操作仍通过写裸 SQL（ knex.schema.raw(rawSql) ）来完成，其它 Query Builder 也存在类似的问题

另一方面，无论 Query Builder 能否覆盖 SQL 的所有特性，都会提供一个 raw(rawSql) 接口作为出口（escape hatches），应对复杂或性能要求严苛的场景

四.ORM

类似于 Query Builder，在 Database Driver 之上的另一种抽象是 ORM ( Object-Relational Mapping )：

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.

简言之，ORM 是一种数据转换机制，用来解决 OOP 中不同类型系统间的数据转换问题。实现上，是建立了一个能在编程语言中使用的虚拟对象数据库

比如关系型数据库的类型系统与编程语言的类型系统，前者只允许存取 标量值 （如整数、字符串等），而后者倾向于操作非标量值（如对象等），这种类型冲突一般有两种解决方式：

The programmer must either convert the object values into groups of simpler values for storage in the database (and convert them back upon retrieval), or only use simple scalar values within the program. Object-relational mapping implements the first approach.

• 存的时候把对象值转换为数据库中存储的简单值组，取的时候再转换回来

• 或者只在程序中使用简单的标量值

ORM 采用的是第一种方式，提供双向转换能力，进而 将编程语言中方便操作的数据模型与数据库中方便存储的数据模型关联起来（映射） ，简化数据库操作：

The point of an ORM, as the name implies, is to map a record in a relational database to an object (typically, but not always, a class instance) in our application. What this means is that we’re defining the structure of these objects, as well as their relationships, in our application code.

例如查库操作：

String sql = "SELECT id, first_name, last_name, phone, birth_date, sex FROM persons WHERE id = 10";
Result res = db.execSql(sql);
String name = res[0]["first_name"];

可以通过 ORM 简化成：

Person p = repository.GetPerson(10);
String name = p.getFirstName();

省去了创建数据模型（ Result 或 Person ）的工作，相当简洁

结合 Query Builder

特殊的，还 可以基于 Query Builder 实现 ORM，以获得跨数据库的优势 ：

The best thing in knex is that it abstracts away most of the differences between different database drivers so you can develop your own higher level database libraries on top of it with less work.

比如基于 Knex 的bookshelf：

var knex = require('knex')({
  // 可替换为Knex支持的其它数据库
  client: 'mysql',
  connection: process.env.MYSQL_DATABASE_CONNECTION
});
var bookshelf = require('bookshelf')(knex);

var User = bookshelf.Model.extend({
  tableName: 'users',
  posts: function() {
    return this.hasMany(Posts);
  }
});

var Posts = bookshelf.Model.extend({
  tableName: 'messages',
  tags: function() {
    return this.belongsToMany(Tag);
  }
});

var Tag = bookshelf.Model.extend({
  tableName: 'tags'
})

User.where('id', 1).fetch({withRelated: ['posts.tags']}).then(function(user) {
  console.log(user.related('posts').toJSON());
}).catch(function(err) {
  console.error(err);
});

（摘自 Examples ）

优势

与 Query Builder 相比，ORM 想要解决的问题更大一些：

• 生产力提升：ORM 能够极大地缩减代码量，进而提高生产力

• OOP 友好：通过 ORM 创建的数据模型与 OOP 中的数据模型的访问/操作方式完全一致（比如 getFirstName ）

• 跨数据库：类似于 Query Builder，多一层抽象能够抹平不同数据库 SQL 语法之间的差异

毫无疑问，ORM 在缩减业务代码量上有着巨大优势，用性能换取生产力：

With each added layer of abstraction we attempt to trade a decrease in performance with an increase in developer productivity (though this is not always the case).

另一大特点是 OOP 友好，如果说 Query Builder 的语法“自然”，那么 ORM 的语法算是地道了：

It fits in your natural way of coding (it’s your language!), using prepared statements or transactions are as easy as calling a method.

缺点

其缺点集中在：

• 通用性：ORM 是面向特定（编程）语言的，不同语言下需要使用不同的 ORM，API 也各不相同

• 高度抽象：SQL 等细节被隐藏起来了，如果不清楚背后发生了什么，很容易产生性能问题

• 限制：一些操作无法通过 ORM 完成，比如子查询

• 性能：ORM 更“重”一些，性能代价也更大，复杂场景下尤为明显

比起 Query Builder，通用性问题在 ORM 中更突出一些，因为 ORM 更复杂，API 设计也更加差异化，相应的，不通用带来的学习成本问题也更严重：

Once you have learned a particular ORM this knowledge likely won’t transfer that well. This is true if you switch from one platform to another, such as JS/Node.js to C#/.NET.

更糟糕的是，ORM 的能力限制意味着 重度使用 ORM 的项目中可能还存在一部分手搓的 SQL，这要求维护人员同时掌握 ORM 和 SQL ：

This often means a codebase with heavy ORM usage will still have a few handwritten queries strewn about it. The implications here are that, as a developer working on one of these projects, we end up needing to know BOTH the ORM syntax as well as some underlying SQL syntax.

另外，由于 ORM 的高度抽象，实现需求可能不难，要保证性能却不容易（仍然需要了解其 SQL 细节）：

It’s a trap for new programmers that can write very greedy statements, like a heavy hit in a for loop.

五.取舍：用 Query Builder 还是 ORM？

单从回报率上看，耗费时间去学习一个不那么通用的东西（无论 Query Builder 还是 ORM），不如熟练掌握万能的 SQL，虽然不同数据库间存在一些语法差异，但大同小异

而 Query Builder 和 ORM 确有其优势与适用场景，比如生产力优先的场景，上 ORM 不失为一个正确的选择，而如果只是想快速创建 SQL 的话，Query Builder 就很合适：

Using a Query Build is a fine solution as long as you fully understand the underlying SQL it is generating.

但无论使用 Query Builder 还是 ORM，都应该充分了解其下层细节， 清楚 工具 实际上在做什么 ：

Never use it as a tool to hide from what is happening at a lower layer. Only use it as a matter of convenience and in situations where you know exactly what it’s doing.

参考资料

• Why you should avoid ORMs (with examples in Node.js)

• Stop using Knex.js – Using SQL query builder is an anti-pattern

• Database schema

• 13.1.9 ALTER TABLE Syntax

• What is an ORM, how does it work, and how should I use one? [on hold]