SQLite3 in Node JS
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.