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 dbindex.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"
}部分测试结果：
