How to make connection to SQLite3 via Node.js

Question

I am following The Node Craftsman Book and have found myself stuck on connecting to the SQLite3 database.

According to the book, I should be able to use DBWrapper and include a path as shown in the screencap of dbSessions.js below:

I know the database exists, because I'm able to open it using DB Browser for SQLite.

I know the server is running:

Server started and listening on port 8080

But when I run my test, it times out. How can I even tell if I am connecting? Even though I don't believe I am connecting.

The closest information I could fine is in mlaccetti's answer here. Perhaps the instructions in the book are incorrect? Or what am I missing?

Here is my code:

index.js

'use strict';

var percolator = require('percolator').Percolator;
var dbSession = require('./dbSessions.js');

var port = 8080;
var server = percolator({'port': port, 'autoLink': false});

server.route('/api/keywords',
    {
        GET: function(req, res) {
            dbSession.fetchAll('SELECT id, value, categoryID FROM keyword ORDER BY id',
            function(err, rows) {
                if(err) {
                    console.log(err);
                    res.status.internalServerError(err);
                } else {
                    res.collection(rows).send();
                }

            });
        }
    }
);

server.listen(function(req, res) {
    console.log('Server started and listening on port', port);
});

dbSessions.js

'use strict';

var DBWrapper = require('node-dbi').DBWrapper;
var dbWrapper = new DBWrapper('sqlite3', {'path': '../../data/keyword-wrangler2.test.sqlite'});

dbWrapper.connect();

module.exports = dbWrapper;

apiSpec.js

'use strict';

var request = require('request');
var dbSession = require('../../src/backend/dbSessions.js');
var resetDatabase = require('../resetDatabase.js');
var async = require('async');

describe('The API', function() {
    it('should respond to a GET request at/api/keywords', function(done) {
        var expected = {
            "_items": [
                {'id': 1, 'value': 'Aubergine', 'categoryID': 1},
                {'id': 2, 'value': 'Onion', 'categoryID': 1},
                {'id': 3, 'value': 'Knife', 'categoryID': 2}
            ]
        };

        async.series(
                [

                    function(callback) {
                        resetDatabase(dbSession, callback);
                    },

                    function(callback) {
                        dbSession.insert(
                                'keyword',
                                {'value': 'Aubergine', 'categoryID': 1},

                        function(err) { callback(err); });
                    },

                    function(callback) {
                        dbSession.insert(
                                'keyword',
                                {'value': 'Onion', 'categoryID': 1},

                        function(err) { callback(err); });
                    },

                    function(callback) {
                        dbSession.insert(
                                'keyword',
                                {'value': 'Knife', 'categoryID': 2},

                        function(err) { callback(err); });
                    }

                ],

                function(err, results) {
                    request.get(
                            {
                                'url': 'http://localhost:8080/api/keywords/',
                                'json': true
                            },
                            function(err, res, body) {
                                expect(res.statusCode).toBe(200);
                                expect(body).toEqual(expected);
                                done();
                            }
                        );
                }
            );

    });
});

Answer 1

Before I get to trying to help, it might be better for you to put up a git repo somewhere with the code; trying to re-create your project from a few files (i.e. the package.json, test, etc.) was time consuming and not something I'm likely to do again.

That said, I did take it for a spin, and think that the most likely scenario is that there is a problem opening the database file. The dbWrapper.connect() is not actually synchronous, so it might make sense to use a callback when establishing the connection to ensure that things are actually alive. Just for testing purposes, I did the following:

dbWrapper.connect(function(err) { console.log('Connected to DB: ', err); });

While I had created the sqlite DB file, the path reference was wrong based on how I was running the application (i.e. node src/index.js made the ../data path that I was using incorrect, and the following error was reported: Connected to DB: { [Error: SQLITE_CANTOPEN: unable to open database file] errno: 14, code: 'SQLITE_CANTOPEN' }

Once I modified the path, it could find the file, and that seems to have worked.

PS - node-dbi hasn't been touched in two years, so might not be the best choice.