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:

enter image description here

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

enter image description here

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.

enter image description here

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.

• Patricia: Thank you, mlaccetti! I did put up a git repository here: github.com/pdlarue/migration_test According to the tutorial, delete the keyword-wrangler.test.sqlite database (in data folder) and then run the db-migrate up --env test command and the migration should recreate the tables. When I run the migration I get the following error: db-migrate up --env test [ERROR] Error: SQLITE_CANTOPEN: unable to open database file at Error (native). The entire project is up there. Can you try running it and see if you can find what's wrong?
• Patricia: I got totally frustrated with this and created another question here. These two questions may be related. Take a look and if you put an answer there, I'll give you the points for that one too!!! :-) It's here: stackoverflow.com/q/35807783/1735836
• Patricia: My path to the db has got to be the problem. After rewriting everything, I'm hitting your dbWrapper.connect(function(err) { code block and getting Connected to DB: null on the console. I don't know where to put it and how to point to it.
• mlaccetti: Will take a peek tomorrow.