It’s 2015, please just let me store data

I just would like to store some data… is that too much to ask for? It doesn’t need to scale, it just needs to be saved for later…

— Ben Hall (@Ben_Hall) January 2, 2015

Looking back at 2014 I worked with CouchDb, MongoDb, LevelDb, Cassandra, ElasticSearch, Redis, Neo4j, Postgresql and MySQL to manage data. Faced with a new prototype I reached the point where I needed to save data. I don’t need it to scale yet, I don’t need it to have map/reduce and storage for billion of records, I don’t even need it to be quick. I just want to store data and in future be able to easily have the data returned.

Turns out my choices are limited to be point of flat files looking like the best option. Before I went down that path I tried one more approach, Sqlite3. This post will investigate how sane Sqlite3 would be given it’s stable and embeddable.

Firstly we need to create the database schema, the solution is already becoming time consuming and boring. The script I created when the application loads is as follows:

var path = require("path");
var fs = require("fs");
var file = path.join(__dirname, "data.db");
var sqlite3 = require("sqlite3").verbose();

function create(cb) {
  var db = new sqlite3.Database(file);

  console.log("Creating db...");
  db.serialize(function() {
    db.run("CREATE TABLE user (id integer primary key, fb_id TEXT, name TEXT, email TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
    console.log("Created db");

    cb();
  });
};

function init(cb) {
  fs.exists(file, function(exist) {
    if(exist) {
      return cb();
    } else {
      create(cb);
    }
  });
};

module.exports = init;

If the schema changes then we’ll need to write an additional script, a problem we can worry about for another day.

Once we’ve created the DB then inserting data becomes straight forward apart from the fact that we might not know the data in advance meaning migration scripts are likely to happen sooner rather than later.

db.run("INSERT INTO user (fb_id, name, email) VALUES (?,?,?)", [fb_id, name, email], function(err) {
  res.status = 201;
 res.end();
});

One nice added bonus is the sqlite3 command line tool.

$ sqlite3 db/data.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from user;

White Sqlite3 works nicely to store data, having to manage a schema is an overhead and additional problems I don’t want to deal with. It’s 2015, why can’t I just store some data?