PostgreSQL 与 Node.js 集成

后端核心 PostgreSQL Node.js

一、为什么选择 PostgreSQL

1.1 行业地位

StackOverflow 调查中 专业开发者最常用 的数据库
Apple、Instagram、Twitch、NASA 都在使用
免费开源，社区支持强大

1.2 在架构中的位置

客户端 ──请求──→ Express 服务器 ──查询──→ PostgreSQL 数据库
  (前端)          (app.js)               (数据持久化)
       ←──响应──          ←──结果──

二、核心工具

2.1 两个必备软件

软件	作用
PostgreSQL Server	运行数据库服务（本地或远程）
PgAdmin	图形界面管理工具（查看/编辑/查询）

2.2 关键配置信息

安装时需记住：

用户名: postgres          ← 默认用户
密码:   你设置的密码        ← 必须记住！
端口:   5432              ← PostgreSQL 默认端口
主机:   localhost          ← 本地开发

三、PostgreSQL 数据类型

3.1 常用类型

类型	说明	示例
`SERIAL`	自增整数（适合 PRIMARY KEY）	`id SERIAL PRIMARY KEY`
`INT` / `INTEGER`	整数	`age INT`
`FLOAT`	浮点数	`price FLOAT`
`VARCHAR(n)`	可变长字符（最大 n）	`name VARCHAR(50)`
`CHAR(n)`	固定长度字符	`code CHAR(2)`
`TEXT`	无限长文本	`description TEXT`
`BOOLEAN`	布尔值	`is_cool BOOLEAN`
`DATE`	日期	`created_at DATE`
`BIGSERIAL`	大自增整数	超大数据量

3.2 VARCHAR vs TEXT

特征	VARCHAR(n)	TEXT
最大长度	必须指定（如 45）	无限
效率	略高（已知大小）	几乎相同
灵活性	可能不够长	完全灵活
推荐	已知最大长度时	不确定长度时

💡 现代实践中，TEXT 和 VARCHAR 性能差异极小。不确定时用 TEXT 更安全。

3.3 约束（Constraints）

sql

CREATE TABLE visited_countries (
  id SERIAL PRIMARY KEY,       -- 自增 + 主键
  country_code CHAR(2) NOT NULL UNIQUE
  --                    ↑ 不能为空  ↑ 不能重复
);

约束	作用
`PRIMARY KEY`	唯一标识每条记录
`NOT NULL`	不接受空值
`UNIQUE`	所有值必须唯一
`REFERENCES`	外键引用
`SERIAL`	自动递增

四、PgAdmin 操作

4.1 创建数据库

展开 PostgreSQL → 右键 Databases → Create → Database
输入名称（如 world）→ Save

4.2 创建表（SQL 方式）

点击查询工具 → 输入 SQL：

sql

CREATE TABLE capitals (
  id SERIAL PRIMARY KEY,
  country VARCHAR(45),
  capital VARCHAR(45)
);

4.3 导入 CSV 数据

右键表 → Import/Export Data
选择 Import
选择 CSV 文件路径
Options → Header 勾选为 True
Columns → 确认列名匹配
点击 OK

注意

CSV 的列名必须与表的字段名 完全匹配（大小写一致）。

如果 id 设为 SERIAL，导入时要 排除 id 列（让 PostgreSQL 自动生成）。

五、Node.js 集成（pg 包）

5.1 安装

bash

npm i pg

5.2 连接数据库

javascript

import pg from "pg";

const db = new pg.Client({
  user: "postgres",
  host: "localhost",
  database: "world",
  password: "your-password",
  port: 5432,
});

db.connect();

5.3 查询数据（SELECT）

javascript

app.get("/", async (req, res) => {
  const result = await db.query("SELECT * FROM capitals");
  console.log(result.rows);
  // result.rows = [
  //   { id: 1, country: "France", capital: "Paris" },
  //   { id: 2, country: "UK", capital: "London" },
  //   ...
  // ]
  res.render("index.ejs", { data: result.rows });
});

5.4 参数化查询（防 SQL 注入）

javascript

// ⚠️ 使用 $1, $2... 占位符 + 值数组
const result = await db.query(
  "SELECT country_code FROM countries WHERE country_name = $1",
  [userInput]
);

// 插入数据
await db.query(
  "INSERT INTO visited_countries (country_code) VALUES ($1)",
  [countryCode]
);

为什么用参数化查询？

javascript

// ❌ 直接拼接 — SQL 注入风险！
db.query(`SELECT * FROM users WHERE name = '${userInput}'`);

// ✅ 参数化 — 安全
db.query("SELECT * FROM users WHERE name = $1", [userInput]);

占位符 $1 会被数组中对应位置的值安全替换。

5.5 模糊查询（LIKE）

javascript

// 查找包含用户输入的国家名
const result = await db.query(
  "SELECT country_code FROM countries WHERE LOWER(country_name) LIKE '%' || $1 || '%'",
  [userInput.toLowerCase()]
);
// LOWER() → 忽略大小写
// % ... % → 前后都可以有任意内容
// 例：输入 "tanzania" → 匹配 "Tanzania, United Republic of"

5.6 关闭连接

javascript

db.end();

六、完整 CRUD 实战

6.1 CREATE — 创建记录

javascript

app.post("/add", async (req, res) => {
  const country = req.body.country;

  try {
    // 1. 查找国家对应的 country_code
    const result = await db.query(
      "SELECT country_code FROM countries WHERE LOWER(country_name) LIKE '%' || $1 || '%'",
      [country.toLowerCase()]
    );

    if (result.rows.length > 0) {
      const code = result.rows[0].country_code;

      // 2. 插入到 visited_countries 表
      await db.query(
        "INSERT INTO visited_countries (country_code) VALUES ($1)",
        [code]
      );
    }

    res.redirect("/");
  } catch (err) {
    console.log(err);
    // 处理重复插入或无效输入
    const countries = await checkVisited();
    res.render("index.ejs", {
      countries: countries,
      total: countries.length,
      error: "Country has already been added, try again."
    });
  }
});

6.2 READ — 读取数据

javascript

async function checkVisited() {
  const result = await db.query("SELECT country_code FROM visited_countries");
  let countries = [];
  result.rows.forEach((row) => {
    countries.push(row.country_code);
  });
  return countries;
}

app.get("/", async (req, res) => {
  const countries = await checkVisited();
  res.render("index.ejs", {
    countries: countries,
    total: countries.length,
  });
});

6.3 UPDATE — 更新记录

javascript

app.post("/edit", async (req, res) => {
  const { id, newTitle, newBody } = req.body;

  await db.query(
    "UPDATE posts SET title = $1, content = $2 WHERE id = $3",
    [newTitle, newBody, id]
  );

  res.redirect("/");
});

6.4 DELETE — 删除记录

javascript

app.post("/delete", async (req, res) => {
  const id = req.body.deleteId;

  await db.query("DELETE FROM posts WHERE id = $1", [id]);

  res.redirect("/");
});

七、PostgreSQL 关系实战

7.1 一对一 — 学生 + 联系方式

sql

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

CREATE TABLE contact_details (
  id INT REFERENCES students(id) UNIQUE,
  tel VARCHAR(15),
  address TEXT
);

-- 查询：JOIN 合并两张表
SELECT *
FROM students
JOIN contact_details
ON students.id = contact_details.id;

7.2 一对多 — 学生 + 作业

sql

CREATE TABLE homework_submissions (
  id SERIAL PRIMARY KEY,
  mark INT,
  student_id INT REFERENCES students(id)
);

-- 查询某学生的所有作业
SELECT students.first_name, homework_submissions.mark
FROM students
JOIN homework_submissions
ON students.id = homework_submissions.student_id;

7.3 多对多 — 学生 + 课程

sql

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(45)
);

-- 中间表（Junction Table）
CREATE TABLE enrollments (
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

-- 查询某学生的所有课程
SELECT students.first_name, courses.title
FROM enrollments
JOIN students ON enrollments.student_id = students.id
JOIN courses ON enrollments.course_id = courses.id;

八、JOIN 类型对比

sql

-- INNER JOIN：只返回两表都有匹配的记录
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

-- LEFT JOIN：返回左表所有记录 + 右表匹配（无匹配则 NULL）
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

-- RIGHT JOIN：返回右表所有记录 + 左表匹配
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

-- FULL JOIN：返回两表所有记录
SELECT * FROM A FULL JOIN B ON A.id = B.a_id;

JOIN 类型	返回内容
`INNER JOIN`	只有双方都匹配的行
`LEFT JOIN`	左表所有行 + 右表匹配行
`RIGHT JOIN`	右表所有行 + 左表匹配行
`FULL JOIN`	两表所有行

九、项目：Travel Tracker

9.1 架构

用户输入国家名 → POST /add
  → 查 countries 表获取 country_code
  → 插入 visited_countries 表
  → 重定向 GET /
  → 查 visited_countries 表
  → 渲染世界地图（高亮已访问国家）

9.2 错误处理

javascript

try {
  // 查询 + 插入
} catch (err) {
  if (/* 国家不存在 */) {
    error = "Country name does not exist, try again.";
  }
  if (/* 国家已添加（UNIQUE 约束冲突） */) {
    error = "Country has already been added, try again.";
  }
  res.render("index.ejs", { error, countries, total });
}

十、速查表

操作	代码
安装 pg	`npm i pg`
连接数据库	`new pg.Client({...})` → `db.connect()`
查询	`db.query("SELECT * FROM table")`
参数化查询	`db.query("... WHERE id = $1", [value])`
关闭连接	`db.end()`
模糊匹配	`LIKE '%' \|\| $1 \|\| '%'`
忽略大小写	`LOWER(column)`
自增主键	`id SERIAL PRIMARY KEY`
非空约束	`column TYPE NOT NULL`
唯一约束	`column TYPE UNIQUE`
外键	`column INT REFERENCES table(pk)`

← 返回 Web 开发研究

PostgreSQL 与 Node.js 集成 ​

一、为什么选择 PostgreSQL ​

1.1 行业地位 ​

1.2 在架构中的位置 ​

二、核心工具 ​

2.1 两个必备软件 ​

2.2 关键配置信息 ​

三、PostgreSQL 数据类型 ​

3.1 常用类型 ​

3.2 VARCHAR vs TEXT ​

3.3 约束（Constraints） ​

四、PgAdmin 操作 ​

4.1 创建数据库 ​

4.2 创建表（SQL 方式） ​

4.3 导入 CSV 数据 ​

五、Node.js 集成（pg 包） ​

5.1 安装 ​

5.2 连接数据库 ​

5.3 查询数据（SELECT） ​

5.4 参数化查询（防 SQL 注入） ​

5.5 模糊查询（LIKE） ​

5.6 关闭连接 ​

六、完整 CRUD 实战 ​

6.1 CREATE — 创建记录 ​

6.2 READ — 读取数据 ​

6.3 UPDATE — 更新记录 ​

6.4 DELETE — 删除记录 ​

七、PostgreSQL 关系实战 ​

7.1 一对一 — 学生 + 联系方式 ​

7.2 一对多 — 学生 + 作业 ​

7.3 多对多 — 学生 + 课程 ​

八、JOIN 类型对比 ​

九、项目 ：Travel Tracker ​

9.1 架构 ​

9.2 错误处理 ​

十、速查表 ​

PostgreSQL 与 Node.js 集成

一、为什么选择 PostgreSQL

1.1 行业地位

1.2 在架构中的位置

二、核心工具

2.1 两个必备软件

2.2 关键配置信息

三、PostgreSQL 数据类型

3.1 常用类型

3.2 VARCHAR vs TEXT

3.3 约束（Constraints）

四、PgAdmin 操作

4.1 创建数据库

4.2 创建表（SQL 方式）

4.3 导入 CSV 数据

五、Node.js 集成（pg 包）

5.1 安装

5.2 连接数据库

5.3 查询数据（SELECT）

5.4 参数化查询（防 SQL 注入）

5.5 模糊查询（LIKE）

5.6 关闭连接

六、完整 CRUD 实战

6.1 CREATE — 创建记录

6.2 READ — 读取数据

6.3 UPDATE — 更新记录

6.4 DELETE — 删除记录

七、PostgreSQL 关系实战

7.1 一对一 — 学生 + 联系方式

7.2 一对多 — 学生 + 作业

7.3 多对多 — 学生 + 课程

八、JOIN 类型对比

九、项目：Travel Tracker

9.1 架构

9.2 错误处理

十、速查表