Node.Js+Knex+MySQL增删改查的简单示例(Typescript)

数据库:

CREATE DATABASE `MyDB`;
CREATE TABLE `t_users` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

项目结构:

package.json如下，拷贝并替换你们本地的package.json后运行 npm install 命令安装所需要的依赖。项目使用了nodemon+ts-node方便development

{
  "name": "tsdemo",
  "version": "1.0.0",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "devDependencies": {
    "@types/node": "^22.9.0",
    "nodemon": "^3.1.7",
    "ts-node": "^10.9.2",
    "typescript": "^5.6.3"
  },
  "dependencies": {
    "@types/express": "^5.0.0",
    "express": "^4.21.1",
    "knex": "^3.1.0",
    "mysql": "^2.18.1"
  }
}

nodemon.json：

{
    "watch": ["src/**/*.js", "src/**/*.ts", "util/**/*.ts"],  
    "ext": "js,ts,json",                       
    "ignore": ["node_modules", "dist"],       
    "exec": "ts-node src/index.ts",           
    "delay": "2500"                            
}
  

tsconfig.json:

{
  "compilerOptions": {
    "target": "es2016",     
    "module": "commonjs",                   
    "outDir": "./dist",                               
    "esModuleInterop": true,                             
    "forceConsistentCasingInFileNames": true,            
    "strict": true,             
    "skipLibCheck": true                                
  },
  "include": [
    "src/**/*"
, "util/**/*"  ],
  "exclude": [
    "node_modules",
    "**/*.spec.ts"
  ]
}

代码部分，VS Code推荐使用Fitten Code插件，目前免费的AI编程工具。可以检查错误，智能补全，代码解释等等，极大提高效率。

db.ts:

import { rejects } from "assert"
import { knex } from "knex"
import { resolve } from "path"

const db = knex({
  client: "mysql",
  connection: {
    host: "localhost",
    user: "root",
    password: "root",
    database: "MyDB"
  }
})

type UserRow = {
    user_id:number,
    user_name:string,
}
//增
export async function addUser(user_name:string) : Promise<string | null> {
  let user_id:number = 0
  await getMaxUserId().then((max_id) => {
    console.log("max_id: ", max_id)
    return new Promise((resolve, reject) => {
      if(max_id)
      {
        console.log("current max_id: ", max_id)
        user_id = max_id === null? 0 : max_id + 1
        console.log("new user_id: ", user_id)
        try{
          db("t_users").insert({user_id, user_name})
          console.log("add success")
          resolve("add success")
        }catch(error){
          console.error(error)
          reject("add failed")
        }
      }
    })
  })
  return null
}
//删
export async function deleteUser(user_id:number) : Promise<string | null> {
  const user = await getUserById(user_id);
    
  return new Promise((resolve, reject) => {
      if (user) {
          try {
              db("t_users").where("user_id", user_id).del().then(() => { 
                  console.error("delete success");
                  resolve("delete success");
              }).catch(error => {
                  console.error("delete failed", error);
                  reject("delete failed");
              });
          } catch (error) {
              console.error(error);
              reject("delete failed");
          }
      } else {
          console.error("user not found");
          resolve("user not found"); 
      }
  });
}
//改
export async function updateUser(user_id:number, user_name:string) : Promise<string | null> {
  const user = await getUserById(user_id);
  return new Promise((resolve, reject) => {
      if (user) {
          try {
              db("t_users").where("user_id", user_id).update({user_name}).then(() => { 
                  console.error("update success");
                  resolve("update success");
              }).catch(error => {
                  console.error("update failed", error);
                  reject("update failed");
              });
          } catch (error) {
              console.error(error);
              reject("update failed");
          }
      } else {
          console.error("user not found");
          resolve("user not found"); 
      }
  });
} 
//查
export async function getUsers() : Promise<UserRow[] | null> {
    try {
      const users = await db("t_users").select("*")
      console.log(users)
      return users
    } catch (error) {
      console.error(error)
      return null
    }
}

export async function getMaxUserId() : Promise<number | null> {  
  try {
    const max_id = await db("t_users").max("user_id as max");
    if(max_id && max_id.length > 0) {
      return max_id[0].max;
    } else {
      return null;  
    }
  } catch (error) {
    console.error(error);
    return null;  
  }
}

export async function getUserById(user_id:number) : Promise<UserRow | null> {  
    try{
      const user = await db("t_users").select("*").where("user_id", user_id).first()
      console.log(user)
      return user
    }catch(error){  
        console.error(error)
        return null
    }
}

export default db

index.ts:

import  * as userdb  from '../util/db';
import express, {Express, Request, Response} from 'express'
import bodyParser from 'body-parser';

const app : Express = express();
app.use(bodyParser.json());
//增
app.post('/adduser', (req : Request, res : Response) => {
    const user_name = req.body.user_name;
    userdb.addUser(user_name).then((resolve) => {        
        res.send(resolve);
     }).catch((error) => { res.send(error) });
});
//查
app.get('/getusers', (req : Request, res : Response) => {
    userdb.getUsers().then((user) => { 
        if(user != null)
        {
            res.send(JSON.stringify(user));
        }
        else
        {
            res.send('no user found');
        }
     }).catch((error) => { res.send(error) });  
});
//删
app.post('/deleteuser', (req : Request, res : Response) => {
    const user_id = req.body.user_id;
    userdb.deleteUser(user_id).then((resolve) => {     
        res.send(resolve);
     }).catch((error) => { res.send(error) });
});
//改
app.post('/updateuser', (req : Request, res : Response) => {
    const user_id = req.body.user_id;
    const user_name = req.body.user_name;
    userdb.updateUser(user_id, user_name).then((resolve) => {     
        res.send(resolve);
     }).catch((error) => { res.send(error) });
});



app.listen(3000, () => {
    const currentDate = new Date(); 
    const formattedDate = currentDate.toLocaleString();
    console.log(`server started on port 3000 at ${formattedDate}`);
});

npm start 运行

因为使用了ts-node，所以如果需要生成的js文件，运行tsc命令即可

推荐使用VS Code的插件REST Client进行测试。

测试文件 .http 示例如下：

###
GET http://localhost:3000/getusers

###
POST http://localhost:3000/adduser
Content-Type: application/json

{
    "user_name": "admin"
}

###
POST http://localhost:3000/deleteuser
Content-Type: application/json

{
    "user_id": 1
}

###
POST http://localhost:3000/updateuser
Content-Type: application/json

{
    "user_id": 1,
    "user_name": "admin111"
}

部分测试结果：