Node.js / 系统课时

实现数据增删改查

实现数据增删改查

本课用一张 inventory_items 表完成新增、查询、更新和删除,并把所有外部值放进参数数组,而不是拼进 SQL 字符串。示例每次运行都会创建一条记录、读取、更新、删除并再次确认不存在,因此可以直接观察 rows、rowCount 与 RETURNING 如何驱动 REST API 的成功或 404 分支。

学习目标

  • 使用 $1、$2 等占位符编写参数化查询,避免把用户输入直接拼接进 SQL。
  • 用 INSERT/UPDATE/DELETE ... RETURNING 直接取得被修改的记录,减少额外查询。
  • 根据 rows[0] 与 rowCount 判断资源是否存在,并映射为稳定的 API 响应语义。

先定义可检查的数据约束

示例资源包含名称与库存数量。名称限定为 1~100 个字符,数量必须是大于等于 0 的整数;数据库层再用 NOT NULL 与 CHECK 重复保护,避免绕过 Node.js 校验的脚本或后台任务写入非法数据。教学示例会在程序启动时执行 CREATE TABLE IF NOT EXISTS,生产项目应改为版本化迁移。

字段 类型 约束 示例
id integer Identity 主键 <ID>
name text 非空,长度 1~100 keyboard
quantity integer 非空,且 quantity >= 0 12
created_at timestamptz 默认 now() 由数据库生成

这种约束分工可以直接用于 API:Node.js 负责尽早返回清晰的 400 错误,PostgreSQL 负责最终一致性。只做应用层校验而不给表加约束,会让其他写入入口成为数据质量漏洞。

用参数化查询完成四类操作

四类 CRUD 虽然 SQL 不同,但数据路径一致:调用方把值放入 values 数组,pg 把 SQL 文本与参数分别发送给 PostgreSQL,数据库执行后再通过 rows 或 rowCount 返回结果。$1 只代表数据值,不能拿来替换表名或列名。

Node.js 参数化 CRUD 到 PostgreSQL 的数据流

下面是完整可运行的 .mjs 示例。先在项目中安装 pg 并设置 PGHOST、PGPORT、PGDATABASE、PGUSER、PGPASSWORD;程序插入的演示记录会在同一次运行中删除,因此重复运行不会留下业务行,只会让 Identity 主键继续递增。

js
import { Pool } from 'pg';

const pool = new Pool({
  max: 5,
  connectionTimeoutMillis: 3000,
});

function validateItem(name, quantity) {
  if (typeof name !== 'string' || name.length < 1 || name.length > 100) {
    throw new TypeError('name must contain 1 to 100 characters');
  }
  if (!Number.isInteger(quantity) || quantity < 0) {
    throw new TypeError('quantity must be a non-negative integer');
  }
}

async function createItem(name, quantity) {
  validateItem(name, quantity);
  const result = await pool.query(
    `INSERT INTO inventory_items (name, quantity)
     VALUES ($1, $2)
     RETURNING id, name, quantity`,
    [name, quantity],
  );
  return result.rows[0];
}

async function getItem(id) {
  const result = await pool.query(
    `SELECT id, name, quantity
     FROM inventory_items
     WHERE id = $1`,
    [id],
  );
  return result.rows[0] ?? null;
}

async function updateItem(id, name, quantity) {
  validateItem(name, quantity);
  const result = await pool.query(
    `UPDATE inventory_items
     SET name = $1, quantity = $2
     WHERE id = $3
     RETURNING id, name, quantity`,
    [name, quantity, id],
  );
  return result.rows[0] ?? null;
}

async function deleteItem(id) {
  const result = await pool.query(
    `DELETE FROM inventory_items
     WHERE id = $1
     RETURNING id`,
    [id],
  );
  return result.rows[0] ?? null;
}

async function main() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS inventory_items (
      id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      name text NOT NULL CHECK (char_length(name) BETWEEN 1 AND 100),
      quantity integer NOT NULL CHECK (quantity >= 0),
      created_at timestamptz NOT NULL DEFAULT now()
    )
  `);

  const created = await createItem('keyboard', 12);
  console.log('created', JSON.stringify(created));

  const found = await getItem(created.id);
  console.log('read', JSON.stringify(found));

  const updated = await updateItem(created.id, 'keyboard', 9);
  console.log('updated', JSON.stringify(updated));

  const deleted = await deleteItem(created.id);
  console.log('deleted', JSON.stringify(deleted));

  const afterDelete = await getItem(created.id);
  console.log('afterDelete', afterDelete);
}

try {
  await main();
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await pool.end();
}

预期输出如下,<ID> 是数据库生成的主键,重复运行时通常会变化。

text
created {"id":<ID>,"name":"keyboard","quantity":12}
read {"id":<ID>,"name":"keyboard","quantity":12}
updated {"id":<ID>,"name":"keyboard","quantity":9}
deleted {"id":<ID>}
afterDelete null

四条业务语句都把外部数据放进参数数组。即使名称里出现单引号,例如 Kid's keyboard,也不需要手工转义字符串;驱动与 PostgreSQL 会按参数值处理,而不是把这段内容重新解释成 SQL 语法。

把查询结果映射为 REST 响应

数据库函数最好返回“资源对象或 null”,不要在数据访问层直接拼 HTTP 响应。Express 路由拿到结果后再决定状态码,这样同一组查询还能被任务脚本、队列消费者或测试复用。新增成功常用 201,查询和更新成功用 200,删除成功且不返回正文时可用 204。

操作 数据库判断 建议 HTTP 结果
新增 RETURNING 得到新行 201 Created,响应新资源
查询 rows[0] 存在 200 OK;不存在则 404 Not Found
更新 RETURNING 得到更新后行 200 OK;没有匹配行则 404
删除 RETURNING id 得到一行 204 No Content;没有匹配行则 404

不要先执行 SELECT 判断存在,再执行 UPDATE 或 DELETE。两条语句之间其他请求可能改掉数据;直接执行带 WHERE id = $1 与 RETURNING 的修改语句,就能让“是否存在”和“完成修改”由同一条 SQL 给出答案。

检查参数化查询的边界

参数化查询能保护“值”,但不能替你决定 SQL 结构。错误做法是把 name、id 或排序字段直接拼进 SQL;正确做法是固定 SQL 文本,并把数据值放到 values。如果确实允许客户端选择排序列,应先用白名单把 name、quantity 等外部字符串映射到程序内固定列名。

问题 错误做法 正确做法
按名称查询 拼接 WHERE name = '${name}' WHERE name = $1,参数为 [name]
按 ID 删除 拼接 WHERE id = ${id} WHERE id = $1,参数为 [id]
动态排序 直接拼接任意 sort 只允许白名单中的固定列名
修改后再查询 UPDATE 后额外 SELECT 使用 UPDATE ... RETURNING

当 CRUD 进入真实 REST API 后,还应把数据库约束错误映射成稳定的 4xx 响应,并为需要同时修改多张表的操作加入事务。单表单语句 CRUD 则优先保持“一次请求对应一条参数化 SQL”,这样最容易验证影响行数和资源不存在的分支。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

实现数据增删改查

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通