Node and SQLite tutorial

SQLite is a self-contained database engine which require no server to run (for MySQL,Oracle we require Database Server). SQLite is most popular in developing mobile apps and it is consider as most widely deployed database engine in the world.

Download code from Github.

In this tutorial i am going to explain how to use SQLite with Node.js. Let’s create project named as “node-sqlite” and in that create package file as “package.json”. Paste following code in it.

{
  "name": "node-sqlite",
  "version": "1.0.1",
  "dependencies": {
    "sqlite3": "~2.2.3"
  }
}

Install the project by typing “npm install” in terminal or command prompt. It will install all dependencies and make your project ready to work.

NPM INSTALL

SQLite is transaction database engine, it means you can run DDL and DML queries of SQL. DDL queries deal with creation of database, tables, view etc where as DML queries are responsible for CRUD operation (insert,delete,update) etc. Here is a code to create SQLite database and performing SQL operations.

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('mydb.db');
var check;
db.serialize(function() {

  db.run("CREATE TABLE if not exists user_info (info TEXT)");
  var stmt = db.prepare("INSERT INTO user_info VALUES (?)");
  for (var i = 0; i < 10; i++) {
      stmt.run("Ipsum " + i);
  }
  stmt.finalize();

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

db.close();

To run and analyse the code, type node filename.js (in my case app.js) and see whether it creates “mydb.sql” file in your folder or not. This code will create table in database and add some numbers and displays it in console.

SQLite output

This is tutorial is for beginners. In further series i will post login-signup system and other useful tutorials about Node.js and SQLite.

SQL Queries using NodeJS:

To perform SELECT query you should use .all function and for other operations such as DELETE, UPDATE, INSERT you should use .run function. Here have a look to simple code.

//Load modules
var sqlite3         =       require('sqlite3').verbose();
var db              =       new sqlite3.Database('./database_name.db');

//Perform SELECT Operation
db.all("SELECT * from blah blah blah where this="+that,function(err,rows){
//rows contain values while errors, well you can figure out.
});

//Perform INSERT operation.
db.run("INSERT into table_name(col1,col2,col3) VALUES (val1,val2,val3)");

//Perform DELETE operation
db.run("DELETE * from table_name where condition");

//Perform UPDATE operation
db.run("UPDATE table_name where condition");

Further reading:

Shahid (UnixRoot) Shaikh

Hey there,

This is Shahid, an Engineer and Blogger from Bombay. I am also an Author and i wrote a programming book on Sails.js, MVC framework for Node.js.

Related Posts

23 Comments

  1. Thanks. This was just enough to break the ice for me. I’m in the middle of porting/translating a bunch of RUNbasic to Node.js. This helps a lot.

  2. I receive the following error during the installation:
    [email protected] install c:node\node_modulessqlite3
    node-pre-gyp install –fallback-to-buld

    I work with:
    node v.0.11.14
    npm v.1.4.12

    Would appreciate some help.

          1. Thank you for your responses.
            Finally, I can install and successfully run sqlite3 v.2.2.3 with node.js v10.36. All this happens on an Intel Galileo running 32bit Windows (a tailored version of the os for this specific board). That means a lot of missing dependencies.
            Again, thank you for your help ­čÖé

  3. I’m fairly new to a lot of this so thanks for this helpful post!

    One thing I don’t understand: why do you declare a variable “check”? you don’t employ it later and I haven’t found anything else online to suggest its use.

    thanks!

  4. Hi, it looks like you have some really fine software to offer.

    But could you please clean up the English grammar on this page. It is truly atrocious!

    Good luck with everything.

    Todd CS
    Houston, Texas, USA

  5. This is a great article..Do you have an idea as to how can i have this piece of code called from angular js or any html?

  6. Hi,
    db.run(“DELETE * from table_name where condition”);
    =>
    db.run(“DELETE from table_name where condition”);

    Bye,
    Martin.

  7. How I could check the number of records returned by a select query. in your example you are directly iterating.

  8. i received this error when i tried to run the command in my pc

    npm ERR! install Couldn’t read dependencies
    npm ERR! Windows_NT 10.0.10240
    npm ERR! argv “C:\Program Files\nodejs\node.exe” “C:\Program Files\nodejs\node_modules\npm\bin\npm-cli.js” “install”
    npm ERR! node v4.1.1
    npm ERR! npm v2.14.4
    npm ERR! path C:UsersBiniampackage.json
    npm ERR! code ENOPACKAGEJSON
    npm ERR! errno -4058
    npm ERR! syscall open

    npm ERR! package.json ENOENT: no such file or directory, open ‘C:UsersBiniampackage.json’
    npm ERR! package.json This is most likely not a problem with npm itself.
    npm ERR! package.json npm can’t find a package.json file in your current directory.

    npm ERR! Please include the following file with any support request:
    npm ERR! C:UsersBiniamnpm-debug.log

  9. How to INSERT a javascript variable in SQLite?
    I tried:

    var smth1 = 1.2;
    var smth2 = 3.4;

    db.run(“CREATE TABLE if not exists mote1 (info TEXT)”);
    var stmt = db.prepare(“INSERT INTO mote1 (UV_index, UV_risk) VALUES (‘+ smth1 +’, ‘+ smth2 +’)”);
    stmt.run();
    stmt.finalize();

    The result is not 1.2 and 3.4 as required but +smth1+ and +smth2+
    What is the correct syntax? (in nodejs)

Leave a Reply

Your email address will not be published. Required fields are marked *