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 = 0await 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 + 1console.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"
}

部分测试结果：