Nodejs 第三十九章 knex

Knex

Knex官网地址：Knex Query Builder | Knex.js (knexjs.org)

什么是Knex

Knex.js 是一个功能强大的 SQL 查询构建器，用于 Node.js。它支持多种 SQL 数据库系统，包括 PostgreSQL, MySQL, SQLite3, 和 Oracle。Knex 可以用于灵活地构建 SQL 查询，执行数据库迁移，种子数据填充，并且可以作为有效的数据库接口层在各种应用程序中使用

主要功能

1. 查询构建器： Knex 提供了一个富有表现力的接口，用于构建 SQL 查询。可以用链式调用来创建复杂的 SQL 语句，让代码更加直观易懂
2. 数据库迁移： Knex 支持数据库迁移，这是一种控制数据库版本的方法。开发者可以通过编写迁移脚本来管理数据库结构的更改，这对多环境开发尤其重要
3. 种子数据填充： 提供了一个方便的机制来设置和管理开发或测试环境中的数据库初始数据，称为种子数据填充
4. 事务管理： 支持事务处理，允许开发者封装一系列步骤，确保它们作为一个单一的工作单元执行，如果其中一个步骤失败，所有操作都可以回滚(单机游戏的回档功能有多重要，可以参照)
5. 多数据库支持： 无需更改查询代码即可切换底层数据库，只需更改配置即可适配不同的数据库系统

为什么使用Knex

• 在我们上一章节MySQL实战，增删改查的接口中，SQL命令都是硬写的(字符串形式)，不仅内容不易理解(没内容差异高亮)，而且没有代码提示，写起来其实是不太方便的
  • 要是命令长一点，复杂一点，那可读性和可维护性可就指数级暴跌了
  • 实际使用中，是很少直接使用SQL语句去编写接口的，直接写有繁琐、容易忘记、SQL注入风险等缺点在
• 而Knex是一个小型的ORM框架，可以解决我们的这些问题，允许我们用JavaScript代码来生成和执行SQL语句
  • 换句话说，使用了Knex，我们就不用继续直接的写SQL语句，而是调用对应的API来完成相应的操作

const knex = require('knex')({
  client: 'mysql',//连接的数据库类型(比如我们这里是MySQL)，因为knex支持连接多种数据库
  connection: {
    //连接数据库信息配置，就是我们上一章节中db.config.yaml里的内容
  }
});

//采用链式调用的方式进行(在)
knex.select('*').from('users').where('id', 1)

• 这样实现就非常方便了，先配置设置，连接上了数据库后，就能直接使用各种API直接链式调用的去执行命令，而且由于采用链式调用，所以内容是连贯的，更符合可读性的逻辑

Knex的安装

• 用什么数据库安装对应的数据库就行了

//安装knex
npm install knex --save

#安装你用的数据库
$ npm install pg
$ npm install pg-native
$ npm install sqlite3
$ npm install better-sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install oracledb
$ npm install tedious

Knex设置

• 如何使用Knex呢？我们就把上一章节的代码带过来，在这里进行修改对比一下，也更容易熟悉

//原代码
import mysql2 from "mysql2/promise"
import jsYaml from "js-yaml"

import fs from "fs"

const yaml = fs.readFileSync("./db.config.yaml", "utf8")
const config = jsYaml.load(yaml)

// 创建与数据库的连接
const connection = await mysql2.createConnection({
  ...config.db
});

• 设置Knex后的代码，在这里的示例中，我们去掉了暂时还没有关联的express代码
  • 纯看初始化的Knex设置和mysql2对比，这两者的差别是很小的，在一开始上手的时候也更容易理解

//kenx初始化配置代码
import mysql2 from "mysql2/promise"
import jsYaml from "js-yaml"
import knex from "knex"

import fs from "fs"

const yaml = fs.readFileSync("./db.config.yaml", "utf8")
const config = jsYaml.load(yaml)

// 创建与数据库的连接
const db = knex({
  client:'mysql2',
  connection:config.db
})

//db.config.yaml文件
db:
  host: localhost #主机
  port: 3306 #端口
  user: root #账号
  password: "root" #密码 一定要字符串
  database: xiaoyu # 库

Knex实战

• 我们通过Knex来完整的实现一个流程：
  1. 创建表
  2. 对表进行增删改查
  3. 实现事务(回滚操作)
• 这些操作都非常的经典，但同时也一定会涉及到Knex的多种API，在这里会逐一说明清楚各种API所代表的作用

什么是schema

• 在正式进行操作之前，我们需要先介绍一些概念，这有助于我们等下实践时候的理解

在 Knex.js 中，schema 是一个对象，用于访问和操作数据库的结构，如创建和修改表格、列、索引等。它是 Knex.js 的一个模块，用来定义、更改数据库的架构（Schema），即数据库中的表和字段的结构

• 我们这里要涉及到设计思想上的概念，也就能解答我们为什么需要schema，中文翻译为**"模式"**
  • 命名空间和组织: schema 对象提供了一个命名空间，包含所有用于修改数据库结构的方法。这样做有助于组织代码，让数据库结构操作相关的方法都在一个逻辑分组下。当我们调用 schema.createTable，是明确地在表明这个操作是对数据库的结构进行操作，而非对数据进行操作。能够明确的避免我们混淆API的功能
  • 明确性和可读性: 通过将数据库操作与数据操作明确区分，代码的可读性和维护性提高。开发者可以快速理解 schema 下的方法是用来改变数据库的结构，而非执行查询或数据操作。
  • 功能分隔: 在 Knex.js 中，schema 提供的功能和 query builder(查询构建器) 提供的功能是分开的。query builder 主要用于执行 SQL 语句（如 SELECT, INSERT, UPDATE, DELETE），而 schema 用于定义或修改数据表和数据库架构。这种分隔符合单一职责原则，每个部分专注于它的功能。
  • 链式调用: schema 对象允许链式调用它的方法，如 createTable, alterTable 等。这提供了一种流畅的接口来组织对数据库结构的修改，而不是散布在各个地方，维护和理解都能变得更容易。
• 通过四点进行分析，绝对可以说这是模块化思想的体现。我们在第九章节理解了这个思想概念，此时就能马上理解为什么在下面创建表的时候还要在createTable这个API前面多此一举加上schema了

创建表

• 这个创建表的过程中，表内部的各种API都只是我们以前在MySQL中定义表中数据类型的另一种表达形式，他们的本质是没有任何的区别的

  • 而创建表的操作为什么createTable前面要多加schema也已经在一开始提前说明了
  • 但此时还有一点我们需要理解，那就是创建表在最后面的.then调用，我们是一定不陌生的，这是Promise的写法。而在这里，.then操作是一定要加上去的，不然我们创建表是操作是不会生效的

  为什么不生效

  • 在 Knex.js 中，所有数据库操作，包括表的创建、数据的插入等，都是异步执行的。这意味着这些操作需要时间来与数据库服务器通信，执行相应的 SQL 命令，并等待服务器的响应。因此，Knex.js 返回的是一个 Promise 对象，表示这个数据库操作的最终完成状态和结果
  • 如果我们省略了 .then() 或相关的异步处理方法（如 async/await），那么即使数据库操作被触发，代码的执行也会立即继续，不会等待操作完成。这可能导致在数据还未准备好的情况下就执行依赖于这些数据的操作，从而引发错误或不一致的状态。
  • 这个就是我们前面MySQL实战章节提到的思想，后续的所有操作都是要依靠于数据库的，有一个绝对优先的考虑在这里。所以这里才必须要用上.then

db.schema.createTable('list', (table) => {
    table.increments('id') //id自增
    table.integer('age') //age 整数
    table.string('name') //name 字符串
    table.string('address') //address 地址
    table.timestamps(true,true) //创建时间和更新时间
}).then(() => {
    console.log('创建成功')
})

创建表-API总结

API	描述	对应 MySQL 数据类型
createTable	创建新表，接受表名和回调函数来定义表结构	无直接对应，是表创建命令
increments	创建自增主键列	INT AUTO_INCREMENT PRIMARY KEY
integer	创建整数类型的列	INT
string	创建字符串类型的列	VARCHAR(长度)
timestamps	创建两个时间戳列，通常为创建时间和更新时间	TIMESTAMP 或 DATETIME

增删改查

• 这一部分的内容，是Knex中与传统mysql2编写接口最大的不同，也是我们所要实现的效果
  • 由于上一章节，我们已经详细讲解过增删改查接口的内在逻辑了，我们此时就一口气全部写出来吧！
  • 只要掌握了一个的写法，其他的就都会了
  • 其中，我将上一章节的写法以注释的形式放入代码中进行对比。最大的不同就是在于，使用knex之后，我们每次都是先确定表作为开始。就像我们整体的增删改查功能必须依赖于数据库一样，在这里细分下来，则是必须依靠于数据库中对应的表，才能对表进行操作(不然就会查空气上了，引发不一致的问题)
• 掌握了表为什么写在前面后(db('list'))，后面的查询内容也就顺水推舟了，跟前面所说的SQL书写顺序是一样的

//查询接口 全部
app.get('/search', async (req, res) => {
  //const [data] = await connection.query("SELECT * FROM `users`")
  const data = await db('list').select()
  
  const total = await db('list').count('* as total')//计算当前一共多少条数据，并起别名为total提高可读性
  res.json({
      code: 200,//响应码
      data,
      total: total[0].total,//给前端返回当前一共多少条数据
      sql:db('list').select().toSQL().sql//查看sql语句
  })
})
//单个查询 params
app.get('/api/search/:id', async (req, res) => {
  //const [data] = await connection.query("SELECT * FROM `users` WHERE `id` = ?", [req.params.id])
  const row = await db('list').select().where({ id: req.params.id })
  res.json({
      code: 200,
      data: row
  })
})

//新增接口
app.post('/api/create', async (req, res) => {
  const { name, age, address } = req.body
  //await connection.query("insert into `users`(name,email) values(?,?)", [name, email])
  const detail = await db('list').insert({ name, age, address })
  res.send({
      code: 200,
      data: detail
  })
})

//编辑
app.post('/api/update', async (req, res) => {
  const { name, age, address, id } = req.body
  //await connection.query("update users set name = ?,email = ? where id = ?",[name,email,id])
  const info = await db('list').update({ name, age, address }).where({ id })
  res.json({
      code: 200,
      data: info
  })
})
//删除
app.post('/api/delete', async (req, res) => {
  //await connection.query(`delete from users where id = ?`,[req.body.id])
  const info = await db('list').delete().where({ id: req.body.id })
  res.json({
      code: 200,
      data: info
  })
})

• 对应的测试请求

//index.http文件

# 查询全部
# GET http://localhost:3000/api/search HTTP/1.1

# 查询单个
# GET http://localhost:3000/api/search/3 HTTP/1.1

# 添加内容
# POST http://localhost:3000/api/create HTTP/1.1
# Content-Type: application/json

# {
#     "name":"小余2005",
#     "age":"18",
#     "address":"福建泉州"
# }

# 编辑更新内容
# POST http://localhost:3000/api/update HTTP/1.1
# Content-Type: application/json

# {
#     "name":"XiaoYu2002",
#     "age":"20",
#     "address":"爪哇岛",
#     "id":3
# }

#删除
# POST http://localhost:3000/api/delete HTTP/1.1
# Content-Type: application/json

# {
#     "id":3
# }

增删改查-API总结

• 这一部分上的命令是基本上没有什么改变，所以可以过渡得很丝滑

Knex API	对应 MySQL 命令	API 作用
select()	SELECT	选择查询，用于从数据库中检索数据。
insert()	INSERT INTO	用于向数据库表中插入新的数据行。
update()	UPDATE	更新已存在的数据行。
delete()	DELETE FROM	删除已存在的数据行。
where()	WHERE	指定查询或操作的条件。
count()	COUNT()	计算并返回匹配查询条件的行数。
orderBy()	ORDER BY	指定查询结果的排序方式。
toSQL()	无直接对应	将 knex 构建的查询转换为 SQL 语句字符串，用于调试和验证查询。

自定义命令

• 但我们知道，固定死的API用法，注定是只能满足一些常见的写法，而在实际的开发中，什么样的需求都有可能有，API是不可能完全满足了。此时就需要有自定义的写法：raw这个API
• raw 是一个在 knex 中非常强大的功能，它允许开发者直接书写原始 SQL 语句，并将其嵌入到 knex 的查询构建器中。使用 raw 方法，可以执行几乎任何 SQL 操作，包括那些不直接由 knex API 支持的复杂查询和命令。

功能和用法

• 基本用法：knex.raw(sql, bindings)
  • sql：字符串，表示你想执行的原始 SQL 语句。
  • bindings：可选，一个数组或对象，用于将值绑定到 SQL 语句的占位符，以防止 SQL 注入。

//自定义命令案例
const insertResult = await db.raw('INSERT INTO users (name, email) VALUES (?, ?) RETURNING id', ['John', 'john@example.com']).then((data)=>{
  console.log(data)
})

使用注意事项

• 安全性：使用 raw 时需特别注意避免 SQL 注入攻击，特别是在将用户输入直接用于 SQL 语句时。通过使用参数绑定（如上面示例中的 ?），可以有效减少这种风险。
• 性能：虽然 raw 提供了灵活性，但应谨慎使用，因为复杂的原始 SQL 可能会导致性能问题，特别是在没有充分优化时。
• 可移植性：直接使用原始 SQL 可能会牺牲跨数据库的兼容性，因为不同数据库的 SQL 方言可能有所不同。

连表

• 连表就是我们前面所讲的内连接和外连接(左右外连接)

连表API

连表类型	作用
内连接（INNER JOIN）	只返回匹配的行
左外连接（LEFT JOIN）	返回左表的所有行，以及右表中匹配的行。如果右表没有匹配，则结果中右表的部分将包含空值
右外连接（RIGHT JOIN）	返回右表的所有行，以及左表中匹配的行。如果左表没有匹配，则结果中左表的部分将包含空值
全外连接（FULL OUTER JOIN）	由于 Knex 本身不直接支持全外连接，这通常需要特定数据库支持或使用原始 SQL 实现

knex('users') // 主表名(也就是我们所说的驱动表)
  .join('posts', 'users.id', '=', 'posts.user_id') // 连表方法及关联详情
  .select('users.name', 'posts.title') // 选择需要的字段
  .then(data => console.log(data)); // 处理查询结果

事务

在MySQL中，事务是一种保证数据库操作完整性的方法，使得一系列的操作要么完全执行，要么完全不执行。事务是数据库管理系统执行过程中的一个逻辑单位，由一个或多个SQL语句组成

事务的四大特性（ACID）

1. 原子性（Atomicity）：
   • 确保事务中的所有操作都作为一个整体执行，即所有操作都成功完成，否则都不执行(也就是回滚)
2. 一致性（Consistency）：
   • 事务必须保证数据库从一个一致性状态转换到另一个一致性状态，不会因为事务执行而导致数据不符合预设规则
3. 隔离性（Isolation）：
   • 多个事务同时执行时，一个事务的执行不应该影响其他事务的执行
4. 持久性（Durability）：
   • 一旦事务被提交，它对数据库中数据的改变就是永久的，即使系统发生故障也不会丢失提交后的数据

事务代码

• 事务在knex中，是采用transaction这个API，翻译过来就直接叫做事务处理
  • 通常是这个顺序：启动事务 => 执行数据库操作 => 提交或者回滚

代码步骤

1. 启动事务： 使用 db.transaction() 方法开始一个新的事务。这个方法接收一个异步函数，该函数的参数 trx 是事务对象，用于执行事务中的数据库操作。
2. 执行数据库操作：
   • 操作A：使用事务对象 trx，调用 .update() 方法更新 list 表。具体操作是将 id 为 1 的记录的 money 字段减少 100。代表id为1的用户的账户扣款100操作。
   • 操作B：紧接着操作A，再次使用 trx 对象，调用 .update() 方法更新 list 表，将 id 为 2 的记录的 money 字段增加 100。这可能代表向另一个用户id为2的账户加款100操作。
3. 提交事务：
   • 如果操作A和操作B都成功执行，没有抛出任何异常，则调用 trx.commit() 提交事务。这意味着所有的更改都将被保存到数据库中。
4. 错误处理与回滚：
   • 如果在执行操作A或操作B期间发生任何错误，将捕获到异常，并执行 trx.rollback()。这会撤销事务中的所有操作，恢复到事务开始前的状态。这确保了数据库的一致性，防止了可能的数据错误或不一致。
   • 这样就不会说出现我向你转账，结果你收到了钱，我这边却没扣钱。或者你那边没收到钱，我这边的钱却被扣的问题
     • 通常用在两个或者更多操作具备关联性上，是非常好用的

//伪代码
db.transaction(async (trx) => {
    try {
        await trx('list').update({money: -100}).where({ id: 1 }) //A
        await trx('list').update({money: +100}).where({ id: 2 }) //B
        await trx.commit() //提交事务
    }
    catch (err) {
        await trx.rollback() //回滚事务(原子性-处理意外情况：遇到错误全部回滚)
    }
})