SQLite3 Node.js JSON

Question

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?

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
– jonrsharpe, Commented Nov 1, 2018 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
– Hysii, Commented Nov 1, 2018 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
– jonrsharpe, Commented Nov 1, 2018 at 23:33
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
– Mike Hardy, Commented Aug 22, 2019 at 14:36

mkingston · Accepted Answer · 2019-08-22 12:06:30Z

11

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.

edited Aug 22, 2019 at 12:06

answered Jan 29, 2019 at 19:54

mkingston

2,74820 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Hardy Over a year ago

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: github.com/mapbox/node-sqlite3/blob/master/scripts/…

mkingston Over a year ago

Thanks, that's useful, I'll incorporate that into my answer somehow.

Mike Hardy Over a year ago

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 :-)

Collectives™ on Stack Overflow

SQLite3 Node.js JSON

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related