SQLite Node.js: Updating Data

Summary: In this tutorial, you will learn how to update data in a table of an SQLite database from a Node.js application.

This tutorial begins where the “Inserting Data into a Table in Node.js” tutorial left off.

Steps for updating data in a table in Node.js

To update data in an SQLite table from a Node.js app, you follow these steps:

First, import sqlite3 from the sqlite3 module:

import sqlite3 from "sqlite3";Code language: JavaScript (javascript)

Second, open a database connection to an SQLite database:

const db = new sqlite3.Database(filename);Code language: JavaScript (javascript)

Third, execute an UPDATE statement statement using the run() method of the Database object:

db.run(sql, params, callback);Code language: JavaScript (javascript)

Finally, close the database connection:

db.close()Code language: JavaScript (javascript)

If you have not followed the previous tutorial, you can download the my.db database here and copy it to the project directory.

Updating data in a table

Step 1. Change the update.js file to update the data in the products table:

import sqlite3 from "sqlite3";
import { execute } from "./sql.js";
const main = async () => {
  const db = new sqlite3.Database("my.db");
  const sql = `UPDATE products SET price = ? WHERE id = ?`;
  try {
    await execute(db, sql, [1000.99, 1]);
  } catch (err) {
    console.log(err);
  } finally {
    db.close();
  }
};
main();Code language: SQL (Structured Query Language) (sql)

Step 2. Open the terminal and execute the following command to run the Node.js app:

node update.jsCode language: CSS (css)

Step 3. Verify the update:

First, open a new terminal and use the sqlite3 tool to connect to the my.db database:

sqlite3 my.dbCode language: JavaScript (javascript)

Second, format the output:

.header on
.mode column
.nullvalue nullCode language: CSS (css)

Second, query data from the products table:

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Output:

id  name    price
--  ------  -------
1   iPhone  1000.99Code language: JavaScript (javascript)

The output indicates that the price has been updated to 1000.99 successfully.

Summary

• Use the run() method of the Database object to execute an UPDATE statement to update data in a table.