2026-09-20 · 数据库

SQLite 到 PostgreSQL：迁移中真正会咬人的地方

开发期用 SQLite 太舒服了：一个文件、零配置、随时删掉重来。等到要上生产换 PostgreSQL，才发现"能跑"和"能跑对"之间隔着一堆方言。

一、SQLite 是弱类型，弱到会替你兜底

这是所有问题的源头。SQLite 里一个声明为 BOOLEAN 的列，你往里写 1、写 '1'、甚至写 'true'，它都不太在意；比较的时候 is_default = 1 也能过。

到了 PostgreSQL，类型是硬的：

-- SQLite: 正常
-- PostgreSQL: ERROR: operator does not exist: boolean = integer
SELECT id FROM workflow_definitions WHERE is_default = 1 LIMIT 1;

-- 正确写法（两种库都能跑）
SELECT id FROM workflow_definitions WHERE is_default = TRUE LIMIT 1;

这类问题最阴险的地方在于：它藏在迁移脚本里。你本地测试跑的是已经建好的库，迁移早就跑过了，永远不会重新执行那一段。只有在一个全新的空库上从头 upgrade head，才会暴露。

所以迁移测试必须用空库全量跑一遍，而不是"在现有库上再升一版"。这是我从这件事里学到的最贵的一条。

二、SELECT 别名不能用在 HAVING 里

SQLite 允许在 HAVING 里直接引用 SELECT 中定义的别名，PostgreSQL 要求用聚合表达式本身：

-- SQLite: 通过
SELECT phone, COUNT(*) c FROM users GROUP BY phone HAVING c > 1;

-- PostgreSQL: ERROR: column "c" does not exist
-- 改成
SELECT phone, COUNT(*) FROM users GROUP BY phone HAVING COUNT(*) > 1;

同样的规则也适用于 ORDER BY 在某些情况下的别名引用。写迁移脚本时，凡是涉及聚合，直接把表达式写全，别图省事。

三、PRAGMA 是 SQLite 专有的元数据接口

写"字段不存在才添加"这种幂等迁移时，SQLite 的写法是：

cols = [r[1] for r in conn.execute(sa.text("PRAGMA table_info(drawings)"))]

PostgreSQL 直接报语法错误。正确做法是走 SQLAlchemy 的 inspector，它是方言无关的：

cols = [c["name"] for c in sa.inspect(conn).get_columns("drawings")]
if "folder" not in cols:
    op.add_column("drawings", sa.Column("folder", sa.String(255), nullable=True))
    op.create_index("ix_drawings_folder", "drawings", ["folder"])

顺带一个规范：能用 op.add_column / op.create_index 就不要手写 DDL。ORM 层已经帮你处理了方言差异，手写 ALTER TABLE 只是在给自己找麻烦。

四、比方言更坑的：建表顺序

这个坑和数据库无关，但只有换库的时候才会撞上。如果应用启动时有一句 Base.metadata.create_all() 兜底建表，而迁移脚本又是从头开始建表，就会撞车：

psycopg2.errors.DuplicateTable: relation "users" already exists

原因是容器启动很快，应用先跑起来把 20 张表建好了，然后你手动执行 alembic upgrade head，第一条 create_table 就撞上了。

正确顺序是死的：

建空库（确保没人连进来）
alembic upgrade head
才启动应用

如果条件允许，更好的做法是把 create_all 从启动流程里去掉，改成只在测试 fixture 里用。生产环境的表结构，只应该由迁移脚本决定。

五、换库之后才显现的其它差异

项目	SQLite	PostgreSQL
字符串大小写	`LIKE` 默认不区分（ASCII）	`LIKE` 区分大小写，要 `ILIKE`
并发写	库级锁，写多会 `database is locked`	行级锁，但要注意长事务
连接池	基本不用管	必须配 `pool_size` / `max_overflow` / `pool_pre_ping`
时区	没有真正的时间类型	`timestamptz` 会做转换，naive/aware 混用会报错
唯一索引与 NULL	多个 NULL 可共存	同样可共存（这点一致，容易误判）
自增主键	`AUTOINCREMENT`	`SERIAL` / `IDENTITY`

连接池这一条特别值得单独说。SQLite 时代没人关心连接数，换成 PostgreSQL 之后，一个没配 pool_pre_ping 的服务在空闲一夜之后，第一批请求全部会因为"连接已被服务端关闭"而失败。

create_engine(
    url,
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=1800,
    pool_pre_ping=True,      # 这一行救过命
)

六、时区：从"没有"到"必须有"

SQLite 存时间就是存字符串，怎么写怎么读，看不出问题。PostgreSQL 分 timestamp 和 timestamptz，一旦一个字段是 aware、另一个是 naive，相减直接抛异常。

我的约定是：库内统一存 UTC 的 naive 时间，序列化时带 Z。听起来很土，但它避免了最多的一类麻烦——服务器时区和用户时区都不参与计算，只在前端展示时做一次转换。

七、怎么保证下次不再踩

三件事，做完之后这类问题基本绝迹：

把 CI 里加一个 PostgreSQL 的 job，每次提交都在空库上跑一次 upgrade head。SQLite 的 job 保留，两个都过才算绿。
迁移里禁止出现方言专有语法：PRAGMA、INSERT OR REPLACE、strftime、AUTOINCREMENT，用一条 lint 规则直接拦。
本地开发默认连 PostgreSQL，SQLite 只留给"想快速跑个测试"的场景。默认值决定了你会踩哪个坑。

八、小结

换数据库不是换个连接串。真正的工作量在于：把所有"SQLite 帮你兜住的错误"找出来，一个个改成两边都成立的写法。

好消息是这类问题总量有限，一轮扫下来基本就干净了。坏消息是，如果不主动用空库 + 目标方言去跑一遍，你永远不知道自己还有多少没扫到。