Node.js Query sqlite with 'sqlite`

Question

I'm trying to get a hang of Node (I mainly use python) so I'm working on a small project to read an write data to a sqlite database.

I am having no issue writing to the database luckily, but I cannot seem to get queries to work at all. I've tested the queries in the sql terminal and they are successful.

So far, I have something like

const fs = require("fs");
const util = require("util");
const sqlite = require("sqlite");
const Promise = require("bluebird")


// const DATABASE = ":memory:";
const DATABASE = "./database.sqlite";

function insertDataIntoDatabase(transactions, db) {
    // Write each transaction into the database.
    let sqlStatement = "INSERT INTO Trx \
        (name, address, amount, category) \
        VALUES "
    for (var i = 0; i < transactions.length; ++i) {
        let trx = transactions[i];
        sqlStatement += util.format(
            "('%s', '%s', %d, '%s'), ",
            trx.name,
            trx.address,
            trx.amount,
            trx.category,
        );
    }
    sqlStatement = sqlStatement.substring(0, sqlStatement.length - 2);
    db.then(db => db.run(sqlStatement))
        .catch((err) => console.log(err));
}

function getTransactions (db, category) {
    // Return an array of valid transactions of a given category.
    let where = "";
    if (category) {
        where = util.format("WHERE category='%s'", category);
    }
    let sqlStatement = util.format("SELECT * from Trx %s", where);
    sqlStatement = "SELECT * from Trx";  // Trying to figure out whats happening
    console.log(sqlStatement);
    db.then(db => {
        db.all(sqlStatement)
        .then((err, rows) => {
            console.log(rows);  // undefined
            console.log(err);  // []
        })
    })
}

// Set up the db connection
const db = sqlite.open(DATABASE, { cached: true })
    .then(db => db.migrate({ force: 'last' }));


// Read transactions and write them to the database
fs.readFile("transactions.json", "utf8", (err, data) => {
    let transactions = JSON.parse(data).transactions;
    insertDataIntoDatabase(transactions, db);
})
// Get transaction data
getValidTransactions(db, 'credit');

// Close connection to DB
db.then(db => db.close());

Shatnerz Shatnerz · Accepted Answer · 2018-04-18T02:22:35

Looking at this again, I think the issue is the async nature of Node. The query was successful, but at that point in time, I had not inserted the data from the json file into the database yet, hence the empty query.

Node.js Query sqlite with 'sqlite`

1 Answers