如何让knex在sqlite中正常使用JSON类型的存储与查询功能

问题排查 #

首先注意代码中存在字段名不匹配的问题：建表时定义的JSON字段名为json_test，插入操作中使用的字段名为test_json，该问题会直接导致插入的JSON值无法匹配到对应字段，返回null结果，需要先修正该低级错误。

无侵入解决方案（无需修改业务代码） #

仅需要调整测试环境的Knex初始化配置，利用Knex的生命周期钩子自动完成JSON序列化、反序列化，同时启用SQLite的JSON扩展支持，配置代码如下：

import knexBuilder = require("knex");

const theDb = knexBuilder({
    client: 'sqlite3',
    connection: {
        filename: ":memory:"
    },
    useNullAsDefault: true,
    pool: {
        min: 1,
        max: 1,
        idleTimeoutMillis: 360000 * 1000
    },
    // 连接创建后初始化逻辑，启用SQLite JSON扩展支持
    afterCreate: (conn, done) => {
        // 验证并激活JSON扩展，当前主流版本sqlite3 npm包已默认内置该扩展
        conn.get('SELECT json_valid(?) AS valid', ['{}'], (err) => {
            done(err, conn);
        });
    },
    // 写入数据前自动序列化JSON对象为字符串
    onQueryBuild: (query) => {
        if (query.method === 'insert' || query.method === 'update') {
            query.bindings = query.bindings.map(item => {
                if (typeof item === 'object' && item !== null && !Array.isArray(item) && !(item instanceof Date)) {
                    return JSON.stringify(item);
                }
                return item;
            });
        }
    },
    // 查询结果返回前自动反序列化JSON字符串为对象
    postProcessResponse: (result) => {
        const parseJsonIfNeed = (row) => {
            const processed = {...row};
            Object.keys(processed).forEach(key => {
                if (typeof processed[key] === 'string') {
                    try {
                        processed[key] = JSON.parse(processed[key]);
                    } catch (e) {
                        // 非JSON格式字符串不处理
                    }
                }
            });
            return processed;
        };
        return Array.isArray(result) ? result.map(parseJsonIfNeed) : parseJsonIfNeed(result);
    }
});

配置说明 #

• 当前npm官方发布的sqlite3依赖包已默认编译内置JSON1扩展，无需额外安装、编译扩展
• 配置中的两个钩子会自动处理JSON类型的读写转换，业务代码完全不需要做适配调整，和Postgres环境的使用逻辑保持一致
• 如果需要使用SQLite原生的JSON查询语法，可直接在Knex语句中调用json_extract等JSON扩展函数，和Postgres的JSON查询语法差异可通过测试用例的自定义封装处理

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