How to let knex use JSON in sqlite?

Question

Well say I have a database, which is a specific "mock" for testing purposes, hence an memory based sqlite database -- where to main database uses postgres:

import knexBuilder = require("knex");
const theDb = knexBuilder({
    client: 'sqlite3',
    connection: {
        filename: ":memory:"
    },
    useNullAsDefault: true,
    pool: {
        min: 1,
        max: 1,
        idleTimeoutMillis: 360000 * 1000
    },
});
await theDb.schema.createTable("test", (table) => {
    table.increments('id');
    table.json("json_test").nullable();
});

When I call column info on this await theDb.table('test').columnInfo();

this is the response:

{
    "id":{"type":"integer","maxLength":null,"nullable":false,"defaultValue":null},
    "json_test":{"type":"json","maxLength":null,"nullable":true,"defaultValue":null}
}

However when I try to do an insert-retrieve circle like this:

await theDb.insert({
    id: 1,
    test_json: {
        "abc": 10,
        "def": "hello",
        "ghi": 20,
    },
}).into("test");
const retrieved = await theDb.select('id', 'test_json').from('test').where('id', 1);

The result is: [{id: 1, test_json: null}];. Which seems to indicate that the json is not stored? Printing the query with "toString()" shows it does put the json in the values:

insert into `test` (`id`, `test_json`) values (1, {"abc":10,"def":"hello","ghi":20})

I notice that SQLITE has by default no support for JSON, however there is a supported JSON extension for SQLite: https://www.sqlite.org/json1.html

So how would I tell knex "json is fine, just use the extension"? Or any other idea where I can use json without code breaking for tests? The rest of the code depends on JSON and it would be kind of silly to change it everywhere to use text/JSON serializing "if ran during testing".

How to let knex use JSON in sqlite?

0 Answers