a blog for those who code

Thursday 31 March 2016

How to Use SQLite Database in Node.js

In this post we will be discussing about how to use SQLite Database in Node.js. SQLite is a software library that implements a self-contained, server-less, zero-configuration, SQL database engine. The best thing about SQLite is that it is server-less that means there is no separate server process to install, setup, configure, initialize, manage, and troubleshoot (i.e. zero-configuration).

For using SQLite in Node.js we will be using sqlite3 npm module (asynchronous, non-blocking SQLite3 bindings). This module works with all version of Node.js. It also works with node-webkit if node-webkit contains a supported version of Node.js engine.


Installation : You can install sqlite3 module using npm install sqlite3

Code to Create SQLite Database in memory. We can create database on the fly which will be created in the memory using the below command.

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

If you want to store the database in a file then you need to change the ':memory:' to name of the file say 'testDB.db'

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

After this you can create a table using the below command where at first we are serializing the database instance and then running the Create Table and Insert Into commands. We are also selecting the data from the table and showing the values one by one.

db.serialize(function () {
  db.run("CREATE TABLE Test (col1, col2, col3)");

  db.run("INSERT INTO Test VALUES (?, ?, ?)", ['a1', 'b1', 'c1']);
  db.run("INSERT INTO Test VALUES (?, ?, ?)", ['a2', 'b2', 'c2']);
  db.run("INSERT INTO Test VALUES (?, ?, ?)", ['a3', 'b3', 'c3']);

  db.each("SELECT * FROM Test", function (err, row) {
    console.log(row);
  });
});
db.close();



If you want to only select the values then you can run only the below code :

db.serialize(function () {
  db.each("SELECT * FROM Test", function (err, row) {
    console.log(row);
  });
});

Please Like and Share the CodingDefined.com Blog, if you find it interesting and helpful.

No comments:

Post a Comment