Files

ethan.chen 2b5b2f1d97 迁移数据库从 SQLite 到 PostgreSQL

- 更新 deno.json 添加 postgres 依赖
- 重构 db/index.ts 使用 PostgreSQL 连接和适配器
- 更新所有路由文件支持异步数据库操作
- 将 SQLite 语法转换为 PostgreSQL 语法
- 添加数据库迁移文档和 schema 文件

2026-01-08 14:26:27 +08:00

3.3 KiB

Raw Permalink Blame History

SQLite 到 PostgreSQL 迁移指南

本文档说明如何将项目从 SQLite 迁移到 PostgreSQL。

主要变更

1. 依赖更新

添加了 postgres 库用于 PostgreSQL 连接
移除了 node:sqlite 依赖

2. 数据库连接

数据库连接现在通过环境变量配置，支持以下方式：

方式一：使用 DATABASE_URL

export DATABASE_URL="postgresql://user:password@localhost:5432/dbname"

方式二：使用独立的环境变量

export DB_USER=postgres
export DB_PASSWORD=postgres
export DB_HOST=localhost
export DB_PORT=5432
export DB_NAME=media

3. SQL 语法变更

占位符

SQLite: 使用 ? 作为占位符
PostgreSQL: 使用 $1, $2, $3... 作为占位符
解决方案: 代码中已创建适配器自动转换

日期时间函数

SQLite: datetime('now')
PostgreSQL: NOW() 或 CURRENT_TIMESTAMP
已更新: 所有 datetime('now') 已替换为 NOW()

获取插入的 ID

SQLite: result.lastInsertRowid
PostgreSQL: 使用 RETURNING id 子句
已更新: INSERT 语句已添加 RETURNING id

4. 数据库 Schema

需要创建以下表结构：

media 表

CREATE TABLE media (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL,
  rating INTEGER,
  notes TEXT,
  platform VARCHAR(100),
  date DATE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

users 表

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

迁移步骤

1. 安装 PostgreSQL

确保已安装并运行 PostgreSQL 数据库。

2. 创建数据库

createdb media
# 或使用 psql
psql -U postgres
CREATE DATABASE media;

3. 创建表结构

使用上面提供的 SQL 语句创建表。

4. 迁移数据（可选）

如果需要从 SQLite 迁移现有数据，可以使用以下方法：

# 导出 SQLite 数据
sqlite3 media.db .dump > data.sql

# 手动转换并导入到 PostgreSQL
# 注意：需要修改 SQL 语法以兼容 PostgreSQL

5. 配置环境变量

设置数据库连接信息（见上面的环境变量配置）。

6. 测试连接

运行应用并测试数据库连接是否正常。

注意事项

数据类型差异:
- SQLite 的 INTEGER 对应 PostgreSQL 的 INTEGER 或 SERIAL
- SQLite 的 TEXT 对应 PostgreSQL 的 VARCHAR 或 TEXT
- SQLite 的 REAL 对应 PostgreSQL 的 REAL 或 DOUBLE PRECISION
事务处理:
- PostgreSQL 支持更完善的事务和并发控制
- 代码中的适配器已处理基本的异步操作

性能优化:

PostgreSQL 支持索引优化

建议为常用查询字段添加索引：

CREATE INDEX idx_media_type ON media(type);
CREATE INDEX idx_media_rating ON media(rating);
CREATE INDEX idx_media_date ON media(date);

连接池:
- postgres 库默认使用连接池
- 可以通过配置选项调整连接池大小

回滚方案

如果需要回滚到 SQLite：

恢复 db/index.ts 中的 SQLite 连接代码
恢复所有 SQL 查询中的 datetime('now')
移除 INSERT 语句中的 RETURNING id
更新 deno.json 移除 postgres 依赖

3.3 KiB Raw Permalink Blame History Unescape Escape