Calling SQLite using Javascript

windows cmd line
INPUT

After a bit of google this and google that, I figured out how to submit an SQL call to SQLite.  This code uses node.js and SQLite3 to call a database I created called ‘sandbox.db’ that resides on a Windows machine  – the same setup that the majority of StoreBoss end users are likely to have running in their back office.

It took me a day to fgure out the basics of npm, node, and SQLite3. It’s not too bad, once you get the hang of it.

Next I will figure out how to create an .exe package using node and this js file.

After that, I will figure out how to repeat this process, but with added HTML and CSS files, such that I can display the data in the responsive custom tables layouts I’ve already created.  If I can get all this to work. then I will know that i can produce a compiled StoreBoss executable that runs on the desktop, no fuss, no muss — and without having to resort to some opinionated, overbearing framework..

Here’s how I did it.  I don’t have an error handler at the end, because this is just a demo. proof of concept kind of thing. Pay attention to where the closing paren is for the functions.  This is how I understood how callbacks work.  Now all I have to do is extract the call statements from this file, and place them in a main index.js one, and voila, that’s my ticket out of callback hell: forcing javascript to behave procedurally, which is what it does anyway, underneath all the async gunk.

 

// filename: sqlite.js
// author: gounaman

const sqlite3 = require('sqlite3').verbose();

var testdb = "sandbox.db";

function opendb(testdb) {

var db = new sqlite3.Database(testdb, (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to SQlite database.');
}
);

return db;
}

var db = opendb(testdb);

function getrows(db) {

db.all("SELECT * FROM batch_vw;",
(error, rows) => {
rows.forEach( row => console.log(row));
});

};

getrows(db);

function closedb(db) {
db.close();
}

closedb(db);

windows cmd line
OUTPUT

And the real beauty of this is that I can now write SQL to my heart’s content without hacking it to pieces with ORM and sanitization.

Life is good again.

Advertisements