How to update data in sqlite3 using Node.js ?

Last Updated : 29 Jul, 2024

In this article, we are going to see how to update data in the sqlite3 database using node.js. So for this, we are going to use the run function which is available in sqlite3. This function will help us to run the queries for updating the data.

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 update data in a sqlite3 database using Node.js.

Below is the step by step implementation:

Step 1: Setting up the NPM package of the project using the following command:

npm init -y

Step 2: Install Dependencies using the following command:

npm install express sqlite3

Project structure: It will look like the following.

Step 3: Here, we created a basic express server that renders GeeksforGeeks on the browser screen.

JavaScript

const express = require('express');
const sqlite3 = require('sqlite3').verbose();

const app = express();
const PORT = 3000;

// Open a connection to the SQLite database
const db = new sqlite3.Database(':memory:', (err) => {
  if (err) {
    console.error('Error opening database:', err.message);
  } else {
    console.log('Connected to the SQLite database.');
    createTable(); // Create table when the server starts
  }
});

// Create a sample table
function createTable() {
  db.run(`CREATE TABLE GFG (ID INTEGER PRIMARY KEY, NAME TEXT)`, (err) => {
    if (err) {
      console.error('Error creating table:', err.message);
    } else {
      console.log('Table "GFG" created successfully.');
      insertSampleData(); // Insert initial data
    }
  });
}

// Insert sample data into the table
function insertSampleData() {
  const insertQuery = `INSERT INTO GFG (NAME) VALUES ('GeeksforGeeks')`;
  db.run(insertQuery, (err) => {
    if (err) {
      console.error('Error inserting data:', err.message);
    } else {
      console.log('Sample data inserted successfully.');
    }
  });
}

// Endpoint to update data in the SQLite database
app.get('/update', (req, res) => {
  const updateQuery = `UPDATE GFG SET NAME = "Updated_Name" WHERE ID = 1`;

  db.run(updateQuery, function (err) {
    if (err) {
      console.error('Error updating data:', err.message);
      res.status(500).send('Failed to update data.');
    } else {
      console.log(`Row(s) updated: ${this.changes}`);
      res.send('Data updated successfully.');
    }
  });
});

// Endpoint to get data from the SQLite database
app.get('/data', (req, res) => {
  const selectQuery = `SELECT * FROM GFG`;

  db.all(selectQuery, [], (err, rows) => {
    if (err) {
      console.error('Error retrieving data:', err.message);
      res.status(500).send('Failed to retrieve data.');
    } else {
      res.json(rows);
    }
  });
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});

Step 4: 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 updating data in sqlite3.

/* Here GFG is table name */
var updateQuery = 'UPDATE GFG SET NAME = "Updated_Name"';

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

JavaScript

const updateQuery = `UPDATE GFG SET NAME = "Updated_Name" WHERE ID = 1`;

db.run(updateQuery, function (err) {
  if (err) {
    console.error('Error updating data:', err.message);
    res.status(500).send('Failed to update data.');
  } else {
    console.log(`Row(s) updated: ${this.changes}`);
    res.send('Data updated successfully.');
  }
});

Step 7: Here is the complete index.js.

JavaScript

const express = require('express');
const sqlite3 = require('sqlite3').verbose();

const app = express();
const PORT = 3000;

const db = new sqlite3.Database(':memory:', (err) => {
  if (err) {
    console.error('Error opening database:', err.message);
  } else {
    console.log('Connected to the SQLite database.');
    createTable();
  }
});

function createTable() {
  db.run(`CREATE TABLE GFG (ID INTEGER PRIMARY KEY, NAME TEXT)`, (err) => {
    if (err) {
      console.error('Error creating table:', err.message);
    } else {
      console.log('Table "GFG" created successfully.');
      insertSampleData();
    }
  });
}

function insertSampleData() {
  const insertQuery = `INSERT INTO GFG (NAME) VALUES ('GeeksforGeeks')`;
  db.run(insertQuery, (err) => {
    if (err) {
      console.error('Error inserting data:', err.message);
    } else {
      console.log('Sample data inserted successfully.');
    }
  });
}

app.get('/update', (req, res) => {
  const updateQuery = `UPDATE GFG SET NAME = "Updated_Name" WHERE ID = 1`;
  db.run(updateQuery, function (err) {
    if (err) {
      console.error('Error updating data:', err.message);
      res.status(500).send('Failed to update data.');
    } else {
      console.log(`Row(s) updated: ${this.changes}`);
      res.send('Data updated successfully.');
    }
  });
});

app.get('/data', (req, res) => {
  const selectQuery = `SELECT * FROM GFG`;
  db.all(selectQuery, [], (err, rows) => {
    if (err) {
      console.error('Error retrieving data:', err.message);
      res.status(500).send('Failed to retrieve data.');
    } else {
      res.json(rows);
    }
  });
});

app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});