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() //回滚事务
    }
   
})