How to Insert and Select Data in SQLite3 Database using Node.js ?

Last Updated : 31 Jul, 2024

Inserting and selecting data in an SQLite3 database using Node.js involves connecting to the database, running SQL queries, and handling the results. SQLite is an excellent choice for small to medium-sized applications due to its simplicity and lightweight nature. This guide will walk you through the steps to insert and select data from an SQLite3 database using Node.js.

SQLite

SQLite is a self-contained, high-reliability, embedded, public-domain, SQL database engine. It is the most used database engine in the world. Let’s understand how to create a table in a SQLite3 database using Node.js.

Steps to Setup Project

Step 1: Make a folder structure for the project.

mkdir myapp

Step 2: Navigate to the project directory

cd myapp

Step 3: Initialize the NodeJs project inside the myapp folder.

npm init -y

Step 4: Install the necessary packages/libraries in your project using the following commands.

npm install express sqlite3

Project Structure:

Screenshot-2024-06-21-163207 — Project Structure | How to Insert and Select Data in SQLite3 Database using Node.js

The updated dependencies in package.json file will look like:

"dependencies": {
    "express": "^4.19.2",
    "sqlite3": "^5.1.7",  
}

Example: Implementatin to first create a server

Node

// index.js

const express = require('express');
const app = express();
const sqlite3 = require('sqlite3');

// Connecting Database
let db = new sqlite3.Database(":memory:" , (err) => {
    if(err) {
        console.log("Error Occurred - " + err.message);
    }
    else {
        console.log("DataBase Connected");
    }
})


app.get('/' , (req , res)=>{
    res.send("GeeksforGeeks");
});

// Server Running
app.listen(4000 , () => {
    console.log("Server started");

    // Query
    var createQuery = 
'CREATE TABLE GFG ( ID NUMBER , NAME VARCHAR(100));';
    var insertQuery = 
'INSERT INTO GFG (ID , NAME) VALUES (1 , "GeeksforGeeks");'
    var selectQuery = 'SELECT * FROM GFG ;'

    // Running Query
    db.run(createQuery , (err) => {
        if(err) return;

        // Success
        console.log("Table Created");
        db.run(insertQuery , (err) => {
            if(err) return;

            // Success
            console.log("Insertion Done");
            db.all(selectQuery , (err , data) => {
                if(err) return;

                // Success
                console.log(data);
            });
        });
    });
})

Step 5: Importing 'sqlite3' into our project using the following syntax. There are lots of features in the sqlite3 module.

const sqlite3 = require('sqlite3');

Step 5: Now write a query for inserting and selecting data in sqlite3.

/* Here GFG is table name */ var insertQuery = 'INSERT INTO GFG (ID , NAME) VALUES (1 , "GeeksforGeeks");' var selectQuery = 'SELECT * FROM GFG ;' /* Here GFG is table name */

Step 6: Here we are going to use a Run and All method which is available in sqlite3.

Node

// index.js

const express = require('express');
const app = express();
const sqlite3 = require('sqlite3');

// Connecting Database
let db = new sqlite3.Database(":memory:" , (err) => {
    if(err) {
        console.log("Error Occurred - " + err.message);
    }
    else {
        console.log("DataBase Connected");
    }
})


app.get('/' , (req , res)=>{
    res.send("GeeksforGeeks");
});

// Server Running
app.listen(4000 , () => {
    console.log("Server started");

    // Query
    var createQuery = 
'CREATE TABLE GFG ( ID NUMBER , NAME VARCHAR(100));';
    var insertQuery = 
'INSERT INTO GFG (ID , NAME) VALUES (1 , "GeeksforGeeks");'
    var selectQuery = 'SELECT * FROM GFG ;'

    // Running Query
    db.run(createQuery , (err) => {
        if(err) return;

        // Success
        console.log("Table Created");
        db.run(insertQuery , (err) => {
            if(err) return;

            // Success
            console.log("Insertion Done");
            db.all(selectQuery , (err , data) => {
                if(err) return;

                // Success
                console.log(data);
            });
        });
    });
})