knex
Knex.js 是一个用于 Node.js 的 SQL 查询构建器,它提供了一种灵活且高效的方式来与 SQL 数据库进行交互。它支持多种关系型数据库,如 PostgreSQL、MySQL、SQLite3 等。
官网:https://knexjs.org/guide/
安装
#安装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
连接数据库
import knex from "knex"; const db = knex({ client: "mysql2", connection: config.db, });
yaml
db: host: localhost port: 3306 user: root password: '123456' database: zxd
定义表的结构
db.schema .createTableIfNotExists("list", (table) => { table.increments("id"); //id 主键 自增 table.string("name"); // 字符串 table.integer("age"); //整数 table.string("hobby"); // 字符串 table.timestamps(true, true); //创建时间,更新时间 }) .then(() => { console.log("创建表成功"); });
增删改查
import fs from "fs"; import jsyaml from "js-yaml"; import express from "express"; import knex from "knex"; const yaml = fs.readFileSync("./db.config.yaml", "utf8"); const config = jsyaml.load(yaml); const db = knex({ client: "mysql2", connection: config.db, }); //knex db.schema .createTableIfNotExists("list", (table) => { table.increments("id"); //id 主键 自增 table.string("name"); // 字符串 table.integer("age"); //整数 table.string("hobby"); // 字符串 table.timestamps(true, true); //创建时间,更新时间 }) .then(() => { console.log("创建表成功"); }); const app = express(); app.use(express.json()); app.get("/", async (req, res) => { const data = await db("user").select("*").orderBy("id", "desc"); const count = await db("list").count("* as total"); res.json({ data, // table, total: count[0].total, sql: db("list").select().toSQL().sql, }); }); app.get("/user/:id", async (req, res) => { const data = await db("list").select("*").where({ id: req.params.id }); const count = await db("list") .where({ id: req.params.id }) .count("* as total"); res.json({ data, total: count[0].total, }); }); app.post("/create", async (req, res) => { const { name, age, hobby } = req.body; await db("list").insert({ name, age }); res.send({ ok: 1 }); }); app.post("/update", async (req, res) => { const { id, name, age, hobby } = req.body; await db("list").update({ name, age }).where({ id }); res.send({ ok: 1 }); }); app.post("/delete", async (req, res) => { const { id } = req.body; await db("list").delete().where({ id }); res.send({ ok: 1 }); }); const port = 3000; app.listen(port, () => { console.log("Server is running on port 3000"); });
事物
原子性
要么都成功 要么都失败
//伪代码 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() //回滚事务 } })