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 只代表数据值,不能拿来替换表名或列名。

下面是完整可运行的 .mjs 示例。先在项目中安装 pg 并设置 PGHOST、PGPORT、PGDATABASE、PGUSER、PGPASSWORD;程序插入的演示记录会在同一次运行中删除,因此重复运行不会留下业务行,只会让 Identity 主键继续递增。
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> 是数据库生成的主键,重复运行时通常会变化。
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”,这样最容易验证影响行数和资源不存在的分支。