7

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?

jonrsharpe
  • 115,751
  • 26
  • 228
  • 437
Hysii
  • 702
  • 2
  • 10
  • 23
  • Do you actually want to *do* anything with the JSON in the database beyond storing and retrieving it? If not, you can just treat it as text or a blob. – jonrsharpe Nov 01 '18 at 23:17
  • @jonrsharpe no this will be used as an express api. But eventually, the format will need to be pure JSON, as opposed to text, was just wondering if there was a way to do it in nodejs. – Hysii Nov 01 '18 at 23:29
  • Perhaps you could give more context. You should probably be *parsing* the JSON and storing the structured result in your DB. Also clarify what you think "pure JSON" means. – jonrsharpe Nov 01 '18 at 23:33
  • Have you tried to stringify your JSON? – node_modules Nov 01 '18 at 23:46
  • 2
    Everyone trying to say "you don't need it" or "don't do it", assume the questioner needs it, right? In my case a software package's demo had a JSON column where they put arbitrary key/value things you might need, and it turns out I needed to select on one. '"SELECT *, json_extract(locations.extras, '$.device.uuid')" + " FROM locations, json_tree(locations.extras, '$.device') " + " WHERE json_tree.value='" + uniqueID + "'";' - done. JSON in a SQL database can be an abuse, but it's a handy tool when you need it. – Mike Hardy Aug 22 '19 at 14:36

1 Answers1

10

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.

mkingston
  • 2,678
  • 16
  • 26
  • 4
    This is both true - for the binaries that they pre-build and distribute via node-pre-gyp for most platforms - and not true - when you end up compiling the sqlite3 module from source. When you compile from source on linux in my experience you need to `export NODE_SQLITE3_JSON1=yes` prior to doing `npm install sqlite3 --build-from-source=sqlite3` - Source: https://github.com/mapbox/node-sqlite3/blob/master/scripts/build_against_node.sh#L35 – Mike Hardy Aug 22 '19 at 07:11
  • Thanks, that's useful, I'll incorporate that into my answer somehow. – mkingston Aug 22 '19 at 12:04
  • 3
    I think the key is - if node-pre-gyp has a pre-built binary for you, the json1 extension will be in it, but if you did a source fallback or force it to build from source when you 'npm install sqlite3', you have to export NODE_SQLITE3_JSON1=yes to do the build the way they do for pre-built binaries - with json1 included. The failure mode is you will get a 'syntax error at '('' if you try to use a json function (since they are unknown to sqlite3 without the extension). Hope this helps people :-) – Mike Hardy Aug 22 '19 at 14:33