Starting with SQLite


I work with NodeJS , for a while, I've been working with JSON files to store information from many users, but now I'm facing a problem, the corruption of the data (the file is being rewritten almost constantly), so I want to move all functions to SQLite to avoid data corruption.

My JSON file has the following fields:

  "161104821108080640": {
    "points": 0,
    "level": 0,
    "time": 1483466910038,
    "color": "ff239d",
    "money": 0,
    "rep": 0,
    "timerep": 1483466910038,
    "timemoney": 1483466910038,
    "id": "161104821108080640"

I want the SQLite file to have the same data, for this, a new entry must be created when a user's ID is not found. I'll also need to know how I could access that data later.

At the moment, this is the way I work with my JSON file:

let points = JSON.parse(fs.readFileSync('./points.json', 'utf8'));
let userData = points[];
if (!userData) {
  userData = {
    points: 0,
    level: 0,
    time: msg.createdTimestamp,
    color: "ff239d",
    money: 0,
    rep: 0,
    timerep: msg.createdTimestamp,
    timemoney: msg.createdTimestamp
  points[] = userData;

In which, your information is updated automatically with the following code:

if (msg.createdTimestamp - userData.time <= 60000) return;
var addin = Math.max(Math.ceil(Math.random() * 10), 5);
userData.points = userData.points + addin;
userData.time = msg.createdTimestamp;

What it does is, verify the timestamp of the last time you received the points, if you have spent more than 1 minuto (60000 ms) , then add a random number of points defined by the variable addin .

How could I do the same, but using SQLite instead of JSON ?

asked by Antonio Roman 03.01.2017 в 21:15

2 answers


The recommended package for working with SQLite is sqlite3 , the big drawback is that it's based on callbacks and not on promises, which makes the code more difficult to maintain if you do nested queries. On top, members are not interested in using promises in future releases. However, there is a package created by the well-known Koistya , sqlite which is nothing more than an ES6 + wrapper for sqlite3. Another that I personally use a lot is sequelize , but this is an ORM and is meant for medium / large applications.


The API is the same as sqlite3 but instead of callbacks, you must use promises

The first thing you should do is create a table for the users:


Note: If you use Node v7.x or Babel, you can benefit from async / await:    const expectedResult = await methodThatReturnPromise();

Connect to SQLite

import db fron 'sqlite';'estadisticas.db')
  .then(() => {
    console.log('SQLite connected');
  .catch(e => {
    console.log('Cannot connect to SQLite');

Queries in SQLite

Queries are in ordinary SQL. For example:

Create a user:

function createUser ({
  uid, username, points, money, level,
  rep, color, time, timerep, timemoney
}) {
  const sql = 'INSERT INTO users (uid, username, points, money' +
              ', level, rep, color, time, timerep, timemoney) ';
  sql += 'VALUES (${uid}, ${username}, ${points}, ${money}, ${level},
         ${rep}, ${color}, ${time}, ${timerep}, ${timemoney})';


createUser({ uid: 'dfudif3', username: 'johndoe', ...)
  .then(() => {
    console.log('Usuario creado');
  .catch(e => {
    console.log('No se pudo crear el usuario: ${e.message}');

The logic you have implemented should not change much, just adapt it to SQL queries.

answered by 05.01.2017 / 15:02

First of all, your question is very wide, but I will try to help ... First of all it's almost the same as what you do in your json that I guess you use MongoDb but with sql commands.

First you must install the library of sqlite3 with npm, I leave you the link of the official documentation.


var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database(':memory:');

db.serialize(function() {"CREATE TABLE lorem (info TEXT)");

  var stmt = db.prepare("INSERT INTO lorem VALUES (?)");
  for (var i = 0; i < 10; i++) {"Ipsum " + i);

  db.each("SELECT rowid AS id, info FROM lorem", function(err, row) {
console.log( + ": " +;


You use it like this, you initialize the libraries, you use db run Create to create the db

And you use INSERT to insert, UPDATE to update the data and DELETE to eliminate them, aaah and SELECT to search for them, it's the same as you have, only you have to save them with sql .

Finally, do not forget to close the db with db.close() after each use.



Here is an example

var db = new sqlite3.Database(ruta);
db.each("SELECT _id AS _id,huellac FROM personal WHERE _id = $useraux", {
$useraux: id
  function(err, row) {
huellac = row.huellac; //esto es si encuentra uno
  function(err, rows) {
if (rows != 0) { //al terminar la busqueda si no encuentra nada

function insertarBd() {
  var insertar = db.prepare("INSERT INTO personal VALUES (?,?,?,?,?,?,?,?,?,?,?)");, fechaact, nombre, apellido, cedula, fechanac, subempresa, dpto, acceso, habkyb, huellac);

In the example you can see that I do a search by id , you can do it by the field you want, if it finds a record it is marked there that I show it by console, if not at the end, rows is 0 already that does not find anything and therefore I do a INSERT of the data (I in the example insert data of an employee).


answered by 03.01.2017 в 23:53