Node.js 数据库 CRUD 项目示例详解(完美解决方案)

nodejs-crud-example/
├── config/
│   └── db.js          # 数据库连接配置
├── controllers/
│   └── usercontroller.js # 业务逻辑
├── models/
│   └── usermodel.js   # 数据库操作
├── routes/
│   └── userroutes.js  # 路由定义
├── app.js             # 应用入口
├── package.json
└── readme.md

mkdir nodejs-crud-example
cd nodejs-crud-example
npm init -y
npm install express mysql2 body-parser cors

const mysql = require('mysql2/promise');
const pool = mysql.createpool({
  host: 'localhost',
  user: 'root',
  password: 'yourpassword',
  database: 'testdb',
  waitforconnections: true,
  connectionlimit: 10,
  queuelimit: 0
});
module.exports = pool;

const pool = require('../config/db');
class user {
  static async getall() {
    const [rows] = await pool.query('select * from users');
    return rows;
  }
  static async getbyid(id) {
    const [rows] = await pool.query('select * from users where id = ?', [id]);
    return rows[0];
  }
  static async create(name, email) {
    const [result] = await pool.query(
      'insert into users (name, email) values (?, ?)',
      [name, email]
    );
    return { id: result.insertid, name, email };
  }
  static async update(id, name, email) {
    await pool.query(
      'update users set name = ?, email = ? where id = ?',
      [name, email, id]
    );
    return { id, name, email };
  }
  static async delete(id) {
    await pool.query('delete from users where id = ?', [id]);
    return true;
  }
}
module.exports = user;

const user = require('../models/usermodel');
exports.getallusers = async (req, res) => {
  try {
    const users = await user.getall();
    res.json(users);
  } catch (err) {
    res.status(500).json({ message: err.message });
  }
};
exports.getuser = async (req, res) => {
  try {
    const user = await user.getbyid(req.params.id);
    if (!user) {
      return res.status(404).json({ message: 'user not found' });
    }
    res.json(user);
  } catch (err) {
    res.status(500).json({ message: err.message });
  }
};
exports.createuser = async (req, res) => {
  try {
    const { name, email } = req.body;
    const newuser = await user.create(name, email);
    res.status(201).json(newuser);
  } catch (err) {
    res.status(400).json({ message: err.message });
  }
};
exports.updateuser = async (req, res) => {
  try {
    const { name, email } = req.body;
    const updateduser = await user.update(req.params.id, name, email);
    res.json(updateduser);
  } catch (err) {
    res.status(400).json({ message: err.message });
  }
};
exports.deleteuser = async (req, res) => {
  try {
    await user.delete(req.params.id);
    res.json({ message: 'user deleted successfully' });
  } catch (err) {
    res.status(500).json({ message: err.message });
  }
};

const express = require('express');
const router = express.router();
const usercontroller = require('../controllers/usercontroller');
// crud 路由
router.get('/', usercontroller.getallusers);
router.get('/:id', usercontroller.getuser);
router.post('/', usercontroller.createuser);
router.put('/:id', usercontroller.updateuser);
router.delete('/:id', usercontroller.deleteuser);
module.exports = router;

const express = require('express');
const bodyparser = require('body-parser');
const cors = require('cors');
const userroutes = require('./routes/userroutes');
const app = express();
// 中间件
app.use(cors());
app.use(bodyparser.json());
app.use(bodyparser.urlencoded({ extended: true }));
// 路由
app.use('/api/users', userroutes);
// 错误处理
app.use((err, req, res, next) => {
  console.error(err.stack);
  res.status(500).json({ message: 'something broke!' });
});
// 启动服务器
const port = process.env.port || 3000;
app.listen(port, () => {
  console.log(`server is running on port ${port}`);
});

create table if not exists users ( id int auto_increment primary key, name varchar(100) not null, email varchar(100) not null unique, created_at timestamp default current_timestamp );

curl -x post -h "content-type: application/json" -d '{"name":"john doe","email":"john@example.com"}' http://localhost:3000/api/users

curl -x put -h "content-type: application/json" -d '{"name":"john updated","email":"john.new@example.com"}' http://localhost:3000/api/users/1

curl -x delete -h "content-type: application/json" http://localhost:3000/api/users/1