路由设置

修改 src/app.ts，为 Book 添加 CRUD 路由：

添加 CRUD 操作

import express, { Request, Response } from "express";
import sqlite3 from "sqlite3";

import { Book } from "./model/book";

const app = express();
const port = process.env.PORT || 3000;

// 解析 JSON 包体的中间件
app.use(express.json());

// 数据库实例
let db: sqlite3.Database;

function initDB() {
  db = new sqlite3.Database("./test.db", (err) => {
    if (err) {
      console.error("Error opening database:", err.message);
    } else {
      console.log("Connected to the database.");
      db.exec(
        `CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            published_at TEXT NOT NULL,
            description TEXT NOT NULL,
            isbn TEXT NOT NULL,
            total_pages INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          )`,
        (err) => {
          if (err) {
            console.error("Error opening database:", err.message);
          } else {
            console.log("Successfully initialized tables.");
          }
        }
      );
    }
  });
}

initDB();

app.get("/ping", (req: Request, res: Response) => {
  res.json({ message: "pong" });
});

// GET all books
app.get("/books", (req: Request, res: Response) => {
  db.all("SELECT * FROM books", (err, rows) => {
    if (err) {
      console.error("Error getting books:", err.message);
      res.status(500).json({ error: "Internal Server Error" });
    } else {
      res.json(rows);
    }
  });
});

// GET a single book by ID
app.get("/books/:id", (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  db.get("SELECT * FROM books WHERE id = ?", [id], (err, row) => {
    if (err) {
      console.error("Error getting book:", err.message);
      res.status(500).json({ error: "Internal Server Error" });
    } else if (row) {
      res.json(row);
    } else {
      res.status(404).json({ message: "Book not found" });
    }
  });
});

// POST a new book
app.post("/books", (req: Request, res: Response) => {
  const newBook: Book = req.body;
  const { title, author, published_at, description, isbn, total_pages } =
    newBook;
  db.run(
    `INSERT INTO books (title, author, published_at, description, isbn, total_pages) VALUES (?, ?, ?, ?, ?, ?)`,
    [title, author, published_at, description, isbn, total_pages],
    function (err) {
      if (err) {
        console.error("Error creating book:", err.message);
        res.status(500).json({ error: "Internal Server Error" });
      } else {
        newBook.id = this.lastID;
        res.status(201).json(newBook);
      }
    }
  );
});

// PUT (update) an existing book by ID
app.put("/books/:id", (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  const updatedBook: Book = req.body;
  const { title, author, published_at, description, isbn, total_pages } =
    updatedBook;
  db.run(
    `UPDATE books SET title = ?, author = ?, published_at = ?, description = ?, isbn = ?, total_pages = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
    [title, author, published_at, description, isbn, total_pages, id],
    (err) => {
      if (err) {
        console.error("Error updating book:", err.message);
        res.status(500).json({ error: "Internal Server Error" });
      } else {
        res.json(updatedBook);
      }
    }
  );
});

// DELETE a book by ID
app.delete("/books/:id", (req: Request, res: Response) => {
  const id = parseInt(req.params.id);
  db.run("DELETE FROM books WHERE id = ?", [id], (err) => {
    if (err) {
      console.error("Error deleting book:", err.message);
      res.status(500).json({ error: "Internal Server Error" });
    } else {
      res.sendStatus(204); // Send 204 status (No Content)
    }
  });
});

app.listen(port, () => {
  console.log(`Listening on port ${port}`);
});

此刻，暂使用 SQLite¹ 数据库做演示。

sqlite3 是一个为 Node.js 提供的异步非阻塞的 SQLite3 库。

如下命令安装：

npm i sqlite3

curl 测试

创建一本新图书：

curl -X POST \
  http://localhost:3000/books \
  -H 'Content-Type: application/json' \
  -d '{
    "title": "Sample Book",
    "author": "John Doe",
    "published_at": "2023-01-01",
    "description": "A sample book description",
    "isbn": "1234567890",
    "total_pages": 200
}'

应如下响应：

{"title":"Sample Book","author":"John Doe","published_at":"2023-01-01","description":"A sample book description","isbn":"1234567890","total_pages":200,"id":1}

根据 ID 获取一本图书：

curl -X GET http://localhost:3000/books/1

结果：

{
  "id": 1,
  "title": "Sample Book",
  "author": "John Doe",
  "published_at": "2023-01-01",
  "description": "A sample book description",
  "isbn": "1234567890",
  "total_pages": 200,
  "created_at": "2024-02-29 11:39:17",
  "updated_at": "2024-02-29 11:39:17"
}

列出所有图书：

curl -X GET http://localhost:3000/books

结果列表：

[
  {
    "id": 1,
    "title": "Sample Book",
    "author": "John Doe",
    "published_at": "2023-01-01",
    "description": "A sample book description",
    "isbn": "1234567890",
    "total_pages": 200,
    "created_at": "2024-02-29 11:39:17",
    "updated_at": "2024-02-29 11:39:17"
  },
  {
    "id": 2,
    "title": "Great Book II",
    "author": "Rob Smith",
    "published_at": "2003-01-01",
    "description": "A sample book description",
    "isbn": "1234567880",
    "total_pages": 1200,
    "created_at": "2024-02-29 11:41:23",
    "updated_at": "2024-02-29 11:41:23"
  }
]

更新一本已有的图书：

curl -X PUT \
  http://localhost:3000/books/1 \
  -H 'Content-Type: application/json' \
  -d '{
    "title": "Updated Book Title",
    "author": "Jane Smith",
    "published_at": "2023-01-01",
    "description": "A new description",
    "isbn": "1234567890",
    "total_pages": 200
}'

结果：

{
  "title": "Updated Book Title",
  "author": "Jane Smith",
  "published_at": "2023-01-01",
  "description": "A new description",
  "isbn": "1234567890",
  "total_pages": 200
}

删除一本已有的图书：

curl -X DELETE http://localhost:3000/books/1

服务端返回 code 204 以表示成功删除。

此刻 REST API 服务器已经初具雏形。不错！

Footnotes

1. SQLite: https://www.sqlite.org/index.html ↩