TOP后端项目：用PostgreSQL设计关系型数据库
项目地址: https://gitcode.com/GitHub_Trending/cu/curriculum 引言：从数据混乱到结构化存储的革命
你是否曾面临这样的困境：用户数据散落在多个JSON文件中，查询一个简单的用户订单需要遍历数十个文件？当用户规模增长到数千人时，手动管理数据变得几乎不可能。关系型数据库（Relational Database）正是为解决这类问题而生，而PostgreSQL作为功能最强大的开源关系型数据库，已成为后端开发的首选工具。
读完本文你将掌握：
- 关系型数据库设计的核心原则与实践方法
- PostgreSQL特有功能提升数据处理效率的技巧
- 从需求分析到数据库实现的完整工作流
- 高并发场景下的数据库性能优化策略
- 与Node.js/Express应用无缝集成的最佳实践
关系型数据库基础：数据组织的科学方法
什么是关系型数据库？
关系型数据库（Relational Database）是一种以表格形式组织数据的数据库，它通过预定义的关系将不同表格中的数据关联起来。这种结构允许高效的数据查询、插入、更新和删除操作。
关系型 vs 非关系型：如何选择？
| 特性 | 关系型数据库 | 非关系型数据库 | 适用场景 |
|---|---|---|---|
| 数据模型 | 表格、行、列 | 文档、键值、图形等 | 关系型：结构化数据，复杂查询；非关系型：非结构化数据，高写入场景 |
| 查询语言 | SQL（标准化） | 各产品自定义 | 关系型：需要复杂查询；非关系型：简单查询，高吞吐量 |
| 事务支持 | 完全ACID支持 | 部分支持或不支持 | 关系型：金融、电商等关键业务；非关系型：日志、社交数据 |
| 扩展性 | 垂直扩展为主 | 水平扩展优秀 | 关系型：中等规模数据；非关系型：超大规模数据 |
| 数据一致性 | 强一致性 | 最终一致性 | 关系型：数据准确性优先；非关系型：可用性优先 |
核心概念解析
- 数据库（Database）：相关表的集合，是数据库管理系统中的最高级容器
- 表（Table）：具有特定结构的数据集合，由行和列组成
- 行（Row/Tuple）：表中的一条记录
- 列（Column/Attribute）：表中的一个字段
- 主键（Primary Key）：唯一标识表中每条记录的列或列组合，如用户ID
- 外键（Foreign Key）：用于关联两个表的字段，指向另一个表的主键
- 索引（Index）：提高查询速度的数据结构，类似于书籍的目录
- 事务（Transaction）：一组作为单个逻辑单元执行的操作，要么全部成功，要么全部失败
PostgreSQL核心特性：企业级开源解决方案
PostgreSQL是一个功能强大的开源对象-关系型数据库系统，它使用并扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能。
PostgreSQL的独特优势
- 强大的SQL兼容性：完全遵循SQL标准，并提供许多扩展功能
- 高级数据类型：支持JSON、数组、地理信息、网络地址等特殊类型
- 可扩展性：支持自定义数据类型、操作符、函数和存储过程
- 强大的索引功能：支持B树、哈希、GiST、SP-GiST、GIN等多种索引类型
- 全文搜索：内置全文搜索功能，无需额外组件
- 并发控制：采用多版本并发控制（MVCC），提供高性能读写操作
- 可靠性：强大的事务支持和故障恢复能力
- 安全性：细粒度的访问控制、列级加密、行级安全策略
PostgreSQL vs MySQL：关键差异对比
| 特性 | PostgreSQL | MySQL | 影响 |
|---|---|---|---|
| 标准合规性 | 高度符合SQL标准 | 部分符合，有扩展 | PostgreSQL在复杂查询上更规范 |
| 高级数据类型 | 丰富支持JSON、数组、地理信息等 | 基础支持JSON，其他有限 | PostgreSQL处理复杂数据结构更灵活 |
| 事务和ACID | 完全支持，包括嵌套事务 | InnoDB支持ACID，其他引擎有限 | PostgreSQL在数据一致性要求高的场景更可靠 |
| 并发性能 | MVCC实现优秀，读写互不阻塞 | InnoDB也使用MVCC，但实现不同 | PostgreSQL在高并发读写场景表现更稳定 |
| 全文搜索 | 内置强大的全文搜索 | 基础全文搜索功能 | PostgreSQL无需额外组件实现复杂搜索 |
| 存储过程 | 支持多种语言编写存储过程 | 仅支持SQL存储过程 | PostgreSQL业务逻辑实现更灵活 |
| 扩展性 | 高度可扩展，支持自定义函数和数据类型 | 扩展能力有限 | PostgreSQL适合定制化需求高的场景 |
数据库设计流程：从概念到实现
数据库设计六步法
1. 需求分析
在设计数据库之前，首先需要明确业务需求：
- 系统需要存储哪些数据？
- 数据之间有什么关系？
- 用户将如何查询和操作数据？
- 数据量和访问模式如何？
需求文档示例：
用户管理系统需求：
1. 存储用户基本信息（姓名、邮箱、密码、创建时间）
2. 存储用户地址信息（可以有多个地址）
3. 存储用户角色（管理员、普通用户、访客）
4. 记录用户登录日志
5. 支持按邮箱、姓名搜索用户
6. 预计用户量：10万级
2. 概念设计
概念设计阶段使用实体关系图（ER图）表示数据实体及其关系：
3. 逻辑设计
将ER图转换为关系模式，设计表结构和关系：
- 用户表（users）：存储用户基本信息
- 地址表（addresses）：存储用户地址，通过user_id关联用户
- 角色表（roles）：存储角色信息
- 用户角色关联表（user_roles）：多对多关系，用户可以有多个角色
- 登录日志表（login_logs）：记录用户登录信息
4. 物理设计
确定数据库的物理存储结构：
- 选择合适的数据类型
- 设计索引策略
- 确定存储位置和分配空间
- 设置分区策略（大规模数据）
5. 实现与测试
创建数据库和表结构，编写测试用例验证设计：
- 创建表和关系
- 插入测试数据
- 执行典型查询
- 验证性能和完整性
6. 部署与维护
将数据库部署到生产环境并持续优化：
- 数据迁移策略
- 备份和恢复计划
- 性能监控和调优
- 安全管理
表结构设计：PostgreSQL实践指南
数据类型选择
PostgreSQL提供了丰富的数据类型，选择合适的类型对性能和数据完整性至关重要：
| 数据类别 | 常用类型 | 应用场景 | 存储空间 | 注意事项 |
|---|---|---|---|---|
| 数值类型 | integer | 计数、ID等整数 | 4字节 | -2^31到2^31-1范围 |
| bigint | 大整数，如行数统计 | 8字节 | -2^63到2^63-1范围 | |
| numeric(precision, scale) | 精确小数，如货币 | 可变 | precision指定总位数，scale指定小数位数 | |
| real | 单精度浮点数 | 4字节 | 约6位十进制精度 | |
| 字符串类型 | varchar(n) | 可变长度字符串 | 1字节+实际字符 | n指定最大长度 |
| text | 长文本 | 1字节+实际字符 | 无长度限制 | |
| char(n) | 定长字符串 | n字节 | 自动填充空格，适合固定长度数据 | |
| 日期时间 | timestamp | 日期和时间 | 8字节 | 不带时区，适合本地时间 |
| timestamptz | 带时区的日期时间 | 8字节 | 推荐使用，自动转换时区 | |
| date | 仅日期 | 4字节 | 存储年月日 | |
| time | 仅时间 | 8字节 | 存储时分秒 | |
| 布尔类型 | boolean | 真/假值 | 1字节 | 可以存储true/false或1/0 |
| 二进制类型 | bytea | 二进制数据 | 1字节+实际数据 | 存储图像、文件等二进制数据 |
| 特殊类型 | json/jsonb | JSON数据 | 可变 | jsonb支持索引，查询性能更好 |
| array | 数组 | 可变 | 存储相同类型的多个值 | |
| hstore | 键值对 | 可变 | 存储简单的键值集合 | |
| geometry | 地理信息 | 可变 | 存储地理位置数据 |
表定义最佳实践
创建用户表的示例：
-- 创建用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100) NOT NULL,
bio TEXT,
avatar_url VARCHAR(255),
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMPTZ,
-- 检查约束示例
CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建更新时间触发器
CREATE TRIGGER update_users_modtime
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
约束设计
约束确保数据符合业务规则，维护数据完整性：
-- 主键约束 (PRIMARY KEY)
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
-- 唯一约束 (UNIQUE)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) NOT NULL UNIQUE
);
-- 外键约束 (FOREIGN KEY)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(10, 2) NOT NULL
);
-- 检查约束 (CHECK)
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0)
);
-- 非空约束 (NOT NULL)
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20) -- 可选字段，允许为NULL
);
-- 默认值约束 (DEFAULT)
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
status VARCHAR(20) DEFAULT 'draft',
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
外键级联操作选项：
ON DELETE CASCADE：删除主表记录时，自动删除关联的从表记录ON DELETE SET NULL：删除主表记录时，将从表关联字段设为NULLON DELETE RESTRICT：如果有从表记录关联，禁止删除主表记录ON UPDATE CASCADE：更新主表主键时，自动更新从表关联字段
高级关系设计：处理复杂数据关联
一对一关系
一对一关系适用于将大型表拆分为较小的表，或存储可选的扩展信息：
-- 用户基本信息表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- 用户详细信息表（一对一关系）
CREATE TABLE user_profiles (
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
full_name VARCHAR(100) NOT NULL,
bio TEXT,
avatar_url VARCHAR(255),
phone VARCHAR(20),
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- 查询用户及其详细信息
SELECT u.id, u.email, p.full_name, p.bio
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = 1;
一对多关系
一对多关系是最常见的关系类型，如一个用户可以有多个订单：
-- 用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
);
-- 订单表（一对多关系）
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(10, 2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending'
);
-- 查询用户及其所有订单
SELECT u.id, u.name, o.id AS order_id, o.order_date, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = 1
ORDER BY o.order_date DESC;
多对多关系
多对多关系需要通过中间表实现，如用户和角色的关系：
-- 用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE
);
-- 角色表
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
description TEXT
);
-- 用户角色关联表（多对多关系）
CREATE TABLE user_roles (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
granted_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id) -- 复合主键
);
-- 插入测试数据
INSERT INTO roles (name, description) VALUES
('admin', '系统管理员'),
('editor', '内容编辑'),
('viewer', '只读用户');
-- 给用户分配角色
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1), (1, 2), (2, 3);
-- 查询用户及其所有角色
SELECT u.id, u.username, array_agg(r.name) AS roles
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE u.id = 1
GROUP BY u.id, u.username;
自引用关系
自引用关系用于表示同一实体间的关系，如员工和经理的关系：
-- 员工表（自引用关系）
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(100) NOT NULL,
manager_id INTEGER REFERENCES employees(id) ON DELETE SET NULL,
hire_date DATE NOT NULL
);
-- 插入测试数据
INSERT INTO employees (name, position, manager_id, hire_date) VALUES
('张三', 'CEO', NULL, '2020-01-15'),
('李四', '技术总监', 1, '2020-03-20'),
('王五', '高级工程师', 2, '2020-05-10'),
('赵六', '高级工程师', 2, '2020-06-01'),
('钱七', '初级工程师', 3, '2021-01-15');
-- 查询员工及其经理
SELECT e.id, e.name, e.position, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 查询部门结构（递归查询）
WITH RECURSIVE employee_hierarchy AS (
-- 根节点：没有经理的员工（CEO）
SELECT id, name, position, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归查询：查找下属员工
SELECT e.id, e.name, e.position, e.manager_id, eh.level + 1 AS level
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, position, level
FROM employee_hierarchy
ORDER BY level, id;
索引优化：提升查询性能
索引基础
索引是提高数据库查询性能的关键，它类似于书籍的目录，可以快速定位数据：
-- 创建基本索引
CREATE INDEX idx_users_email ON users(email);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- 创建复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
-- 创建部分索引（只索引满足条件的行）
CREATE INDEX idx_orders_pending ON orders(id) WHERE status = 'pending';
-- 创建表达式索引（对计算结果建立索引）
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- 删除索引
DROP INDEX IF EXISTS idx_users_email;
索引类型选择
PostgreSQL提供多种索引类型，适用于不同场景：
| 索引类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| B-tree | 等值查询、范围查询、排序 | 最常用，支持各种比较操作 | 不适合全文搜索或复杂几何查询 |
| Hash | 简单等值查询 | 等值查询速度快 | 不支持范围查询和排序 |
| GiST | 全文搜索、地理数据、数组 | 支持复杂数据类型和查询 | 创建和维护成本较高 |
| SP-GiST | 非平衡数据结构，如IP地址、范围 | 空间效率高，适合特定分布数据 | 适用场景有限 |
| GIN | JSONB、数组、全文搜索 | 多值数据类型查询高效 | 写入性能较差，索引较大 |
| BRIN | 大型时序数据，如日志表 | 空间占用极小，维护成本低 | 仅适用于有序存储的数据 |
索引最佳实践
-- 1. 为外键创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 2. 为频繁过滤条件创建索引
CREATE INDEX idx_products_category ON products(category_id);
-- 3. 为排序字段创建索引
CREATE INDEX idx_articles_published_date ON articles(published_date DESC);
-- 4. 为JSONB字段创建GIN索引
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
-- 5. 为全文搜索创建GIN索引
CREATE INDEX idx_articles_content_fts ON articles USING GIN(to_tsvector('english', content));
-- 6. 监控索引使用情况
-- 启用pg_stat_user_indexes扩展
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- 查找未使用的索引
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey%'; -- 排除主键索引
避免索引滥用
虽然索引能提高查询性能，但过多的索引会降低写入性能：
- 不要为小表创建索引：全表扫描可能比使用索引更快
- 避免过度索引：每个索引都会增加写入和更新的开销
- 不要索引频繁更新的字段：会导致频繁的索引维护
- 不要索引低基数列：如性别（只有男/女/其他几个值）
- 定期审查索引使用情况：删除未使用或低效的索引
事务与并发控制
ACID特性
事务是数据库操作的基本单位，具有ACID特性：
- 原子性(Atomicity)：事务中的所有操作要么全部完成，要么全部不完成
- 一致性(Consistency)：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态
- 隔离性(Isolation)：多个事务并发执行时，一个事务的操作不会影响其他事务
- 持久性(Durability)：事务提交后，对数据库的修改是永久性的
事务控制
-- 基本事务
BEGIN;
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO user_profiles (user_id, full_name) VALUES (CURRVAL('users_id_seq'), '张三');
COMMIT;
-- 回滚事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 检查余额是否足够
SELECT balance INTO account_balance FROM accounts WHERE id = 1;
IF account_balance < 0 THEN
ROLLBACK;
RAISE EXCEPTION '账户余额不足';
END IF;
COMMIT;
-- 保存点
BEGIN;
INSERT INTO orders (user_id, total_amount) VALUES (1, 100);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (CURRVAL('orders_id_seq'), 1, 2, 50);
-- 如果需要，可以回滚到保存点
-- ROLLBACK TO order_created;
COMMIT;
并发控制与隔离级别
PostgreSQL提供多种事务隔离级别，平衡一致性和并发性能：
-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 默认级别
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- PostgreSQL中实际表现为READ COMMITTED
BEGIN;
-- 事务操作...
COMMIT;
隔离级别对比：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 并发性能 | 适用场景 |
|---|---|---|---|---|---|
| Read Uncommitted | 可能 | 可能 | 可能 | 最高 | 极少使用，只用于对数据一致性要求极低的场景 |
| Read Committed | 不可能 | 可能 | 可能 | 高 | 默认级别，大多数Web应用场景 |
| Repeatable Read | 不可能 | 不可能 | 可能 | 中 | 需要一致读取的报表生成 |
| Serializable | 不可能 | 不可能 | 不可能 | 低 | 关键业务，如金融交易 |
与应用集成：Node.js/Express项目实践
环境设置与依赖安装
# 创建项目并安装依赖
mkdir postgres-demo && cd postgres-demo
npm init -y
npm install express pg dotenv cors
npm install --save-dev nodemon
项目结构
postgres-demo/
├── .env # 环境变量配置
├── .gitignore # Git忽略文件
├── package.json # 项目依赖
├── server.js # 应用入口
├── db/
│ ├── index.js # 数据库连接配置
│ ├── queries/ # SQL查询函数
│ │ ├── users.js # 用户相关查询
│ │ └── products.js # 产品相关查询
│ └── migrations/ # 数据库迁移文件
├── routes/ # API路由
├── controllers/ # 路由处理逻辑
└── middleware/ # 中间件
数据库连接配置
// db/index.js
const { Pool } = require('pg');
require('dotenv').config();
// 创建连接池
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: process.env.NODE_ENV === 'production'
? { rejectUnauthorized: false }
: false
});
// 测试连接
pool.query('SELECT NOW()', (err, res) => {
if (err) {
console.error('Database connection error:', err.stack);
} else {
console.log('Database connected successfully');
}
});
module.exports = {
query: (text, params) => pool.query(text, params),
getClient: async () => {
const client = await pool.connect();
const query = client.query;
const release = client.release;
// 超时处理
const timeout = setTimeout(() => {
console.error('A client has been checked out for more than 5 seconds!');
console.error(`The last executed query on this client was: ${client.lastQuery}`);
}, 5000);
// 重写query方法以跟踪最后执行的查询
client.query = (...args) => {
client.lastQuery = args;
return query.apply(client, args);
};
client.release = () => {
clearTimeout(timeout);
client.query = query;
client.release = release;
return release.apply(client);
};
return client;
}
};
环境变量配置 (.env)
# 数据库连接配置
DATABASE_URL=postgresql://username:password@localhost:5432/top_project
# 应用配置
PORT=3000
NODE_ENV=development
数据库查询封装
// db/queries/users.js
const db = require('../index');
/**
* 获取所有用户
* @param {Object} options - 查询选项
* @param {number} options.limit - 限制返回数量
* @param {number} options.offset - 偏移量，用于分页
* @returns {Promise<Array>} 用户列表
*/
const getAllUsers = async ({ limit = 10, offset = 0 }) => {
const query = {
text: 'SELECT id, email, full_name, created_at FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2',
values: [limit, offset]
};
const result = await db.query(query);
return result.rows;
};
/**
* 根据ID获取用户
* @param {number} id - 用户ID
* @returns {Promise<Object>} 用户信息
*/
const getUserById = async (id) => {
const query = {
text: 'SELECT id, email, full_name, bio, created_at FROM users WHERE id = $1',
values: [id]
};
const result = await db.query(query);
return result.rows[0];
};
/**
* 创建新用户
* @param {Object} user - 用户信息
* @param {string} user.email - 邮箱
* @param {string} user.password_hash - 密码哈希
* @param {string} user.full_name - 姓名
* @param {string} [user.bio] - 个人简介
* @returns {Promise<Object>} 创建的用户信息
*/
const createUser = async (user) => {
const query = {
text: `INSERT INTO users (email, password_hash, full_name, bio, created_at)
VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
RETURNING id, email, full_name, created_at`,
values: [user.email, user.password_hash, user.full_name, user.bio || null]
};
const result = await db.query(query);
return result.rows[0];
};
/**
* 更新用户信息
* @param {number} id - 用户ID
* @param {Object} updates - 要更新的字段
* @returns {Promise<Object>} 更新后的用户信息
*/
const updateUser = async (id, updates) => {
// 动态构建更新查询
const fields = Object.keys(updates);
const setClauses = fields.map((field, index) => `${field} = $${index + 2}`).join(', ');
const values = [...Object.values(updates), id];
const query = {
text: `UPDATE users SET ${setClauses}, updated_at = CURRENT_TIMESTAMP
WHERE id = $${fields.length + 1}
RETURNING id, email, full_name, bio, updated_at`,
values
};
const result = await db.query(query);
return result.rows[0];
};
/**
* 删除用户
* @param {number} id - 用户ID
* @returns {Promise<boolean>} 删除成功返回true
*/
const deleteUser = async (id) => {
const query = {
text: 'DELETE FROM users WHERE id = $1',
values: [id]
};
const result = await db.query(query);
return result.rowCount > 0;
};
module.exports = {
getAllUsers,
getUserById,
createUser,
updateUser,
deleteUser
};
数据库迁移脚本
// db/migrations/01_create_users_table.js
const db = require('../index');
async function runMigration() {
const client = await db.getClient();
try {
await client.query('BEGIN');
// 创建用户表
await client.query(`
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100) NOT NULL,
bio TEXT,
avatar_url VARCHAR(255),
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMPTZ,
CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
)
`);
// 创建索引
await client.query('CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)');
await client.query('CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at)');
// 创建更新时间触发器函数
await client.query(`
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql
`);
// 创建触发器
await client.query(`
CREATE TRIGGER update_users_modtime
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column()
`);
await client.query('COMMIT');
console.log('Migration completed successfully');
} catch (e) {
await client.query('ROLLBACK');
console.error('Migration failed:', e);
throw e;
} finally {
client.release();
}
}
runMigration();
API实现示例
// routes/users.js
const express = require('express');
const router = express.Router();
const userQueries = require('../db/queries/users');
const { hashPassword, verifyPassword } = require('../utils/auth');
// 获取所有用户
router.get('/', async (req, res) => {
try {
const limit = parseInt(req.query.limit) || 10;
const offset = parseInt(req.query.offset) || 0;
const users = await userQueries.getAllUsers({ limit, offset });
res.json({
success: true,
count: users.length,
data: users
});
} catch (error) {
res.status(500).json({
success: false,
error: 'Failed to fetch users',
details: error.message
});
}
});
// 获取单个用户
router.get('/:id', async (req, res) => {
try {
const id = parseInt(req.params.id);
if (isNaN(id)) {
return res.status(400).json({
success: false,创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
