How to Connect SQLite3 Database using Node.js ?

Connecting SQLite3 database with Node.js involves a few straightforward steps to set up and interact with the database. SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine, making it ideal for small to medium-sized applications. Here’s how you can connect and work with SQLite3 using Nodejs:

Approach

To connect to an SQLite3 database in Node.js, install the sqlite3 package, then use it to create a database connection. Execute SQL queries using the db.run, db.get, or db.all methods for inserting, retrieving, and managing data.

Installation Steps

Step 1: Create a folder application by using this command

mkdir myapp

Step 2: Navigate to the project directory

cd myapp

Step 3: Initialize Node Project

NPM, Create and Locate your project folder in the terminal & type the command

npm init -y

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

npm install express sqlite3

Project Structure:

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

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

Example: Implementation to create express server which renders GeeksforGeeks.

// filename index.js

const express = require('express');
const app = express();
   
app.get('/' , (req , res)=>{
    res.send("GeeksforGeeks");
})
  
app.listen(4000 , ()=>{
    console.log("server started");
})

Output:

Importing sqlite3 into our project

Syntax:

const sqlite3 = require('sqlite3');

Example: Implementation to use Database method which is available in sqlite3 which helps us to connect with database.

// filename - 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");
})

Step to Run Server: Run the server using the following command from the root directory of the project:

node index.js

Output:

Additional Tips

• Use Database Transactions: SQLite supports transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) for handling multiple operations atomically.
• Parameterized Queries: Use parameterized queries (? placeholders) to prevent SQL injection attacks and ensure secure data handling.
• Async/Await with SQLite3: You can also use async/await syntax with SQLite3 operations for cleaner and more readable asynchronous code.

Conclusion

Connecting SQLite3 database with Node.js involves setting up the database connection, performing operations, and handling errors effectively. SQLite is lightweight, efficient, and suitable for applications that require a simple, embedded database solution. By following the steps and examples outlined above, you can start building robust Node.js applications with SQLite as the backend database.