{"id":212,"date":"2014-07-31T23:59:28","date_gmt":"2014-07-31T18:29:28","guid":{"rendered":"http:\/\/codeforgeek.com\/?p=212"},"modified":"2023-12-22T19:02:31","modified_gmt":"2023-12-22T13:32:31","slug":"node-sqlite-tutorial","status":"publish","type":"post","link":"https:\/\/codeforgeek.com\/node-sqlite-tutorial\/","title":{"rendered":"NodeJS SQLite Tutorial"},"content":{"rendered":"\n

SQLite is a self-contained database engine that requires no server to run (for MySQL<\/a>, Oracle we require a Database Server). SQLite is most popular in developing mobile apps and it is considered as most widely deployed database engine in the world.<\/p>\n\n\n\n

\n

Download code from Github<\/a>.<\/p>\n<\/blockquote>\n\n\n\n

NodeJS SQLite Tutorial<\/h2>\n\n\n\n

In this tutorial, I am going to explain how to use SQLite with NodeJS. Let’s create a project named “node-sqlite<\/strong>” and in that create a package file as “package.json”. Paste the following code in it.

\n{
\n\"name\": \"node-sqlite\",
\n\"version\": \"1.0.1\",
\n\"dependencies\": {
\n\"sqlite3\": \"~2.2.3\"
\n}
\n}
\n<\/code><\/p>\n\n\n\n

Install the project by typing “npm install<\/strong>” in the terminal or command prompt. It will install all dependencies and make your project ready to work.<\/p>\n\n\n\n

\"NPM<\/figure>\n\n\n\n

SQLite Database Schema Setup<\/h2>\n\n\n\n

SQLite is a transactional database engine, which means you can run DDL and DML queries of SQL. DDL queries deal with the creation of database, tables, view etc. whereas DML queries are responsible for CRUD<\/strong><\/a> operation (insert, delete, update, etc.). Here is the code to create an SQLite database and performing SQL operations.

\nvar sqlite3 = require('sqlite3').verbose();
\nvar db = new sqlite3.Database('mydb.db');
\nvar check;
\ndb.serialize(function() {<\/code><\/p>\n\n\n\n


\n<\/code><\/p>\n\n\n\n

db.run(\"CREATE TABLE if not exists user_info (info TEXT)\");
\nvar stmt = db.prepare(\"INSERT INTO user_info VALUES (?)\");
\nfor (var i = 0; i < 10; i++) {
\nstmt.run(\"Ipsum \" + i);
\n}
\nstmt.finalize();<\/code><\/p>\n\n\n\n

<\/code><\/code><\/p>\n\n\n\n

db.each(“SELECT rowid AS id, info FROM user_info”, function(err, row) {
console.log(row.id + “: ” + row.info);
});
});<\/p>\n\n\n\n


\n<\/code><\/p>\n\n\n\n

db.close();
\n<\/code><\/p>\n\n\n\n

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

\"SQLite<\/figure>\n\n\n\n

SQLite Queries using NodeJS<\/h2>\n\n\n\n

To perform the SELECT query<\/a> you should use the .all<\/strong> function and for other operations such as DELETE, UPDATE, INSERT you should use the .run<\/strong> function. Here have a look at simple code.

\n\/\/Load modules
\nvar sqlite3 = require('sqlite3').verbose();
\nvar db = new sqlite3.Database('.\/database_name.db');<\/code><\/p>\n\n\n\n

<\/code><\/code><\/p>\n\n\n\n

\/\/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.
});<\/p>\n\n\n\n

<\/code><\/code><\/p>\n\n\n\n

\/\/Perform INSERT operation.
db.run(“INSERT into table_name(col1,col2,col3) VALUES (val1,val2,val3)”);<\/p>\n\n\n\n

<\/code><\/code><\/p>\n\n\n\n

\/\/Perform DELETE operation
db.run(“DELETE * from table_name where condition”);<\/p>\n\n\n\n


\n<\/code><\/p>\n\n\n\n

\/\/Perform UPDATE operation
\ndb.run(\"UPDATE table_name where condition\");
\n<\/code><\/p>\n\n\n\n

Further reading:<\/h2>\n\n\n\n