Basic of node-sqlite3

Mohit

2 min readJan 23, 2019

Sqlite is a perfect standalone database layer for projects that require a minimal database connectivity.

sqlite does not requires any server all it does is create a single “.db” file and contains all the data it that file.

implementing using node

npm i sqlite3

create a index.js file and then import it

var sqlite3 = require('sqlite3').verbose();

to create a new database object

var db = new Sqlite3.Database('dbName.db');

this returns a database object which can be accessed by using the dbvariable.

a better way to do the same thing

var db = new Sqlite3.Database('dbName.db', (err) =>{
    if (err){
        return console.log(err.message);
    }
});

closing the connection is also same

db.close();

or a more proper way will be

db.close((err) => {
    if (err) {
        return console.error(err.message);
    }
});

Important Tip

if you want to perform series of operation like create table, update it etc.

then don’t forget to enclose your code in db.serialize() function, what it does is execute statements in the order in which they are written, and using it avoids conflicts due to race condition.

db.serialize(function() {//db.run("CREATE TABLE if not exist TABLE_NAME (col1 DATA_TYPE)");// insert data here}

To Create a New Table

db.run("CREATE TABLE if not exists users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");

users is table name

info is column name and TEXT is column type/data type.

To add value to Table

db.run("INSERT INTO users(name) VALUES ('lorem ipsum')");

to use variable in INSERT statements do this

var statement = db.prepare("INSERT INTO uses VALUES (?)");for (var i=0; i<5; i++){
    statement.run("Lorem Ipsum " + i);
}statement.finalize();

and done now our users table is populated with some data fields. the statement.finalize(); destroy’s the prepared statement.

To Perform Delete Operation

db.run("DELETE * from users where id=2");

to delete a table with all its data

db.run("DROP TABLE users");

To check if some data in column exist, and add it if it does not exist

var name = "randomName";db.serialize(function(){db.run("CREATE TABLE if not exists forms(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE)");db.run("insert or replace into forms(id, name) values((select id from forms where name='"+name+"'), '"+name+"')");db.all("SELECT * FROM forms", function(err, row) {
    console.log(row);
});})