SQLite Nodejs Tutorial – Install and Create Basic Nodejs API. For applications that don’t require a full client-server scenario, SQLite is a great stand alone database. Nodejs and SQLite3 go hand in hand. Without a DBA or DevOps crew, it may deliver all the advantages of a SQL database persistence layer. In this tutorial, we will perform a simple CRUD operation using API.
Follow this article about SQLite Nodejs Tutorial – Install and Create Basic Nodejs API. Read about Node.js first.
What is Nodejs
Nodejs Benefits
Node.js is an open source JavaScript runtime environment built on Chrome’s V8 engine. It is event driven with non blocking I/O to make developing web apps fast. Some of the benefits are below:
- High performance tool meant for real time applications
- Cost effective with Fullstack JS.
- Reduces Loading Time by Quick Caching and event based model.
- Non blocking Input/Output model as well as asynchronous request handling.
- Robust technology stack with code sharing and reusing it leads to faster speed and performance.
REST At a Glance
In here the application program interface (API) is designed in the RESTful architectural style uses HTTP requests to fetch and process data. The GET, PUT, POST, and DELETE data types can read, update, create, and delete resource-related activities in the database. A request is sent from the client to the server as a web URL. This is followed by an output from the server, which can be anything from HTML to XML to an image or even JSON. However, JSON is now the most widely used format for Web Services. These methods perform CRUD operations (create, read, update and delete).
Key Elements of REST Architecture
- Client – Any device or software that sends a request to the server is considered a client. In this case, a browser can be regarded as a client.
- Server – A software that processes client requests and provides access to datasource via API.
- Resource – Content that is offered to the client.
CRUD Application
What is a CRUD
Interestingly CRUD is an acronym for Create, Read, Update and Delete. It is a set of operations we get servers to execute (POST, GET, PUT and DELETE requests respectively). This is what each operation do:
Anatomy of a REST API
Let’s go through five HTTP methods:
- GET – This method is used to fetch or read the resource information. The method returns status code 200 (OK), indicating the read was successful. It throws error status code 404 (NOT FOUND) or 400 (BAD REQUEST) if the resource is not reachable or a bad request has been made.
2. POST – This is used to create new resources and subsequent child resources. It returns status code 201 and the status header containing the link to the created resource.
3. PUT – Updates the resources. It is also sometimes used to create new resources where the client and not the server choose the resource id. It returns a status code of 200 on successful update operation, and a status of 201 on successful create operation. If the method does not return any content in the body, it will return the status code of 204. It is considered not such a safe method.
4. DELETE – As the name indicates, it is used to delete the resource and returns the status code of 200 on successful deletion.
5. PATCH – It is another method used to modify a resource. It is similar to PUT, but its body contains instructions on the current status of the resources and how they should be updated to generate a new resource.
What is SQLite
SQLite benefits
The SQLite server has multiple benefits but some of the below are:
- Improves Performance and no installation is required.
- Fast reading and writing operations and efficient way for data storage.
- Less bug prone instead of a custom written I/O code files.
- Cross platform DBMS and makes work possible on multiple databases on the same session at the same time.
- Facilitates API for a large range of programming languages.
Architectural Constraints
There are six architectural constraints for the REST API. They are:
Uniform Interface
- It is a critical factor in determining if an API is RESTful or non RESTful. According to this theory, all devices and applications should have the same interface to a specific server (website, mobile app).
Stateless
2. It indicates that the request has all of the information the server needs to process it, and the server does not need to store anything relevant to the session. In REST, the client is responsible for providing the server with all the necessary information, including query parameters, headers, and URIs. Because the server doesn’t have to keep track of or broadcast the state of each session, statelessness increases uptime. When a client sends too much data to the server, network optimization is limited, and additional bandwidth is required.
Cacheable
3. If a response may be cached, it should indicate whether or not the response is cacheable and for how long. In subsequent requests, the client will return the data from its cache, and the server will not have to submit the request again. Effective use of caching reduces the number of client-server interactions, enhancing both availability and performance. However, there is a possibility that the user may receive outdated information.
Client-Server
4. A client server architecture is required for REST applications. A server is someone who retains resources and is not concerned with the user interface or state of the user, whereas a client requests resources but is unconcerned with data storage, which is kept within each server. They have the ability to develop on their own. Server and client don’t need to know one another’s business logic or frontend UI.
Layered System:
5. There should be several levels to an application’s architecture. There can be a lot of intermediary servers between the client and the end server, and each layer is only aware of the layer directly in front of its own. Load balancing and shared caches on intermediary servers can increase system uptime.
Code on Demand
6. It’s an optional extra. It follows from this that the servers can also offer the client with executable code. Java applets and client-side scripts like JavaScript are examples of code that can be compiled on demand.
SQLite Nodejs Tutorial – Install and Create Basic Nodejs API
Prerequisites
Install NodeJS and Package Manager
Firstly, we will install the Nodejs and the nodejs package manager, popularly known as NPM. To install these two following commands can be executed in the terminal.
$ sudo apt install nodejs
$ sudo apt install npm
Create a Dev Directory
Secondly we create an empty directory and initialize with npm.
$ mkdir node-api
$ cd node-api
$ npm init
It will set up a base project and ask for basic information. You have to fill in the package name and entry point information. Others are optional.
package name: (nodeapitutorial) node-api
version: (1.0.0)
description:
entry point: (index.js) samplenodeapi.js
test command:
git repository:
keywords:
author: Bhaskar
license: (ISC)
Install Project Dependency
Once the base project is set up, proceed to install the following:
$ npm install express
$ npm install sqlite3
$ npm install md5
Create a Database
Next, we will create a database. For this purpose you can create a db.js file in the development folder
var sqlite3 = require('sqlite3').verbose()
var md5 = require('md5')
const DATASOURCE = "sample.sqlite"
let db = new sqlite3.Database(DATASOURCE, (err) => {
if (err) {
// Cannot open database
console.error(err.message)
throw err
}else{
console.log('Connected to the sample database.')
db.run('CREATE TABLE student ( \
id INTEGER PRIMARY KEY AUTOINCREMENT,\
name text, \
userid text , \
password text \
)',(err) => {
if (err) {
console.log("Table already exists.");
}else{
// Table just created, creating some rows
var insert = 'INSERT INTO student (name, userid, password) VALUES (?,?,?)';
db.run(insert, ["John Smith","john01",md5("john@123")]);
db.run(insert, ["Mary Jones","mary02",md5("mary@34")]);
}
});
}
});
module.exports = db
The next thing to do is to create a file inside the dev folder with the name sample node api.js. Add the following code to the file.
var express = require("express");
var app = express();
var db = require("./db.js")
const HTTP_PORT = 8080
app.listen(HTTP_PORT,()=> {
console.log("Server is listening on port " + HTTP_PORT);
});
app.get("/student/", (req, res, next) => {
var sql = "select * from student"
var params = []
db.all(sql, params, (err, rows) => {
if (err) {
res.status(400).json({"error":err.message});
return;
}
res.json({
"message":"success",
"data":rows
})
});
});
CRUD Operations
GET Operation
The first API is the read operation using the GET method. Add the following line of the code in the above file.
app.get("/student/", (req, res, next) => {
var sql = "select * from student"
var params = []
db.all(sql, params, (err, rows) => {
if (err) {
res.status(400).json({"error":err.message});
return;
}
res.json({
"message":"success",
"data":rows
})
});
});
POST Operation
The next API will be to Create a record by using POST.
app.post("/student/", (req, res, next) => {
var requestBody = re.body;
db.run(`INSERT INTO student (name, userid, password) VALUES (?,?,?)`,
[requestBody.name, requestBody.userid, requestBody.password],
function (err, result) {
if (err) {
res.status(400).json({ "error": err.message })
return;
}
res.status(201).json({
"id": this.lastID
})
});
});
Update using PATCH
Let’s update it. To do this update, we will use the PATCH operation.
app.patch("/student/", (req, res, next) => {
var requestBody = re.body;
db.run(`UPDATE student set name = ?, userid = ?, password = ? WHERE id = ?`,
[requestBody.name, requestBody.userid, requestBody.password, requestBody.id],
function (err, result) {
if (err) {
res.status(400).json({ "error": res.message })
return;
}
res.status(200).json({ updatedID: this.changes });
});
});
Delete Operation
In here will use the delete operation to delete the record from the database.
app.delete("/student/:id", (req, res, next) => {
db.run(`DELETE FROM student WHERE id = ?`,
req.params.id,
function (err, result) {
if (err) {
res.status(400).json({ "error": res.message })
return;
}
res.status(200).json({ deletedID: this.changes })
});
});
Now run the above file using the node command
$ node samplenodeapi.js
You can now fetch the API using the http://localhost:8080/student This will list all the resources from student database. You can also use Postman which is the best API tool. Further you can use various endpoints to fetch different resources. Here are the list of endpoints:
Get all the resources --> GET /student/
Get a single resource by id-->GET /student/{id}
Create resource --> POST /student/
Update resource by id --> PATCH /student/{id}
Delete resource by id--> DELETE /student/{id}
The output should look like this:
Great effort! Thank you for your time to read about SQLite Nodejs Tutorial – Install and Create Basic Nodejs API.
SQLite Nodejs Tutorial – Install and Create Basic Nodejs API Conclusion
We learned API creation using Nodejs and SQlite. If you are thorough with REST concepts then you can create Rest APIs using Node.JS very easily. There is lot more that is offered by Node.js but it is not possible to cover them in this article. You can create a complex web app using Node.js and sqlite .Various JavaScript frameworks and libraries can be used for creating web apps which can easily run on node.js server. What’s more these days cloud vendors are offering capabilities to develop cloud solutions using nodejs and sqlite.
