SQLite3 Node.js JSON

问题:

I am using the sqlite3 NPM package. I would like store JSON in one of my database columns. I understand that SQLite itself is able to store JSON https://www.sqlite.org/json1.html, but I am not necessarily sure how I would do this through Node.js.

Has anybody ran into this scenario before, using the sqlite3 NPM package to store JSON? Would I be better off using a lightweight NoSQL database?

回答1:

The sqlite3 package supports the Sqlite JSON1 extension by default. Riffing slightly on the example provided by the sqlite3 package:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(function() {
    db.run('CREATE TABLE lorem (info TEXT)');
    let stmt = db.prepare('INSERT INTO lorem VALUES(json(?))');
    for (let i=0; i<10; i++) {
        stmt.run(JSON.stringify({ a: i }));
    }
    stmt.finalize();

    db.each('SELECT rowid AS id, json_extract(info, \'$.a\') AS info FROM lorem', function(err, row) {
        console.log(row.id + ": " + row.info);
    });
});

Note that for some package configurations or installations, the JSON1 extension may not be included by default. If this is not working for you, see the comment on this answer from Mike Hardy.