Postgres JSONB类型不兼容SQLite测试环境的解决方法咨询

核心报错原因是JSONB是PostgreSQL专属方言类型，SQLite对应的SQLAlchemy方言默认没有实现该类型的编译逻辑，建表时无法渲染出合法的SQL语句。以下是实测可用的解决路径，按实现成本从低到高排序：

方案1：注册SQLite方言的JSONB编译规则（推荐，零业务侵入） #

不需要修改现有模型定义，也不需要写环境判断分支，只需要在测试启动入口（比如pytest的conftest.py、测试初始化脚本）加一段类型编译注册代码，让SQLite遇到JSONB类型时自动渲染为自身原生支持的JSON类型即可：

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.types import JSON

# SQLite环境下将JSONB编译为通用JSON类型
@compiles(JSONB, "sqlite")
def _compile_jsonb_sqlite(type_, compiler, **kwargs):
    return compiler.visit_JSON(type_, **kwargs)

SQLite 3.9.0及以上版本原生支持JSON存储和配套的路径查询、字段提取函数，常规存非结构化JSON配置块的场景下，和PostgreSQL JSONB的基础读写行为差异极小，完全能覆盖单元测试、集成测试的验证需求。如果用到了JSONB专属操作符（比如@>包含判断、?键存在判断），可以用同样的编译注册方式补全对应操作符的SQLite实现即可。
这段兼容逻辑仅在连接SQLite时生效，完全不影响生产环境PostgreSQL的行为，也不会干扰Alembic生成迁移——只要生成、执行迁移时连接的是PostgreSQL，就会正确创建JSONB类型的列。

方案2：模型层用with_variant做内置方言适配 #

如果不想额外加编译注册逻辑，可以调整字段定义，用SQLAlchemy的with_variant方法在类型定义层面直接做好方言映射，不需要任何环境判断：

from sqlalchemy import Column
from sqlalchemy.types import JSON
from sqlalchemy.dialects.postgresql import JSONB

class Application(Base):
    __tablename__ = "applications"
    # 其他字段...
    config = Column(
        JSON(none_as_null=True).with_variant(
            JSONB(none_as_null=True), 
            "postgresql"
        ),
        nullable=False,
        default=dict
    )

这种写法下，SQLAlchemy会自动根据连接的数据库方言选择对应类型：连PostgreSQL时用原生JSONB，连SQLite/MySQL等其他数据库时用通用JSON类型，适配逻辑内聚在字段定义里，维护成本很低。

方案3：测试环境直接使用PostgreSQL（最贴近生产） #

如果业务重度依赖JSONB的专属特性（比如GIN索引、JSONB专属操作符、特殊的排序/去重逻辑），前两种模拟方案可能和生产行为存在细微差异，最稳妥的方式是测试环境直接用PostgreSQL。可以借助测试插件自动拉起临时PostgreSQL容器做测试库，测试结束自动销毁，不需要手动维护本地数据库环境，能100%对齐生产行为，避免方言差异导致的“测试过了线上挂”问题。

避坑提醒 #

不要写散落在业务代码里的全局环境判断逻辑，比如通过读取环境变量判断是测试环境就把列类型改成JSON、生产环境就切回JSONB。这种写法维护成本极高，一旦执行迁移时环境变量配置错误，就会生成错误的表结构，给生产埋下隐患。

内容的提问来源于stack exchange，提问作者Eric31