I have a problem. I’m making an application for run over a Raspberry PI, I initially chose Mongo DB (I really like it!), but in a Raspberry PI this program is slow.

I was looking for a solution and I thought about SQLite. There’s a module maintained by Mapbox that you can find it here.

Let’s do a fast test:

express -e sqlite_express
cd sqlite_express

Open package.json and add a line in dependences:

"sqlite3":""

Install packages:

npm install

We’ll put dabase into a dir.

mkdir database

This is app.js code:

var express = require('express');
var path = require('path');
var favicon = require('static-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

var app = express();

//SQLite
var sqlite3 = require('sqlite3').verbose();
var sqlite3_file=__dirname+'/database/sqlite_express.sqlite3';
var db = new sqlite3.Database(sqlite3_file);

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(favicon());
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded());
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', routes);
app.use('/users', users);

app.get('/data/start', function (req, res, next) {
  db.serialize(function() {
  	db.run("CREATE TABLE IF NOT EXISTS users (name TEXT)");
  	db.all("SELECT rowid AS id, name FROM users", function(err, rows) {
    	if (err) {
	    	res.send('err: '+err);
    	} else {
	    	if (rows.length>0) {
		    	res.send('No users added');
	    	} else {
		    	var stmt = db.prepare("INSERT INTO users VALUES (?)");
		    	stmt.run("Neo");
		    	stmt.run("Trinity");
		    	stmt.run("Morfeo");
		    	stmt.finalize();
		    	res.send('Users added');
	    	}
    	}
  	});
  });
});

app.get('/show', function (req, res, next) {
	db.all("SELECT rowid AS id, name FROM users", function(err, rows) {
    	if (err) {
	    	res.send('err: '+err);
    	} else {
	    	console.log(rows);
	    	res.render('show', { title: 'Show users', users: rows });
    	}
  	});
});

/// catch 404 and forward to error handler
app.use(function(req, res, next) {
    var err = new Error('Not Found');
    err.status = 404;
    next(err);
});

/// error handlers

// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
    app.use(function(err, req, res, next) {
        res.status(err.status || 500);
        res.render('error', {
            message: err.message,
            error: err
        });
    });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
        message: err.message,
        error: {}
    });
});

module.exports = app;</code></pre>

First we started database going to http://localhost:3000/data/start.

This is the template for view users (views/show.ejs):

<!DOCTYPE html>
  <html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
    <h1><%= title %></h1>
    <ul>
      <\% for(var i=0; i<users.length; i++) {\%>
        <li><\%= users[i].name \%></li>
      <\% } \%>
    </ul>
  </body>
</html>

This is a very simple sample but it’s a good start.