How To Use SQLite with Node.js on Ubuntu 22.04

To use SQLite with Node.js, you need a database client that connects to an SQLite database and sends SQL statements from your application to the database for execution. One of the popular choices is the node-sqlite3 package that provides asynchronous bindings for SQLite 3. In this tutorial, you’ll use node-sqlite3 to create a connection with an SQLite database. Next, you’ll create a Node.js app that creates a table and inserts data into the database. Finally, you’ll modify the app to use node-sqlite3 to retrieve, update, and delete data from the database. Commands Used FIRST mkdir name cd name npm init -y npm install sqlite3 SECOND nano db.js const sqlite3 = require("sqlite3").verbose(); const filepath = "./fish.db"; function createDbConnection() { const db = new sqlite3.Database(filepath, (error) = { if (error) { return console.error(error.message); } createTable(db); }); console.log("Connection with SQLite has been established"); return db; } function createTable(db) { db.exec(` CREATE TABLE sharks ( ID INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL, color VARCHAR(50) NOT NULL, weight INTEGER NOT NULL ); `); } node db.js ls node db.js nano db.js const fs = require("fs"); const sqlite3 = require("sqlite3").verbose(); const filepath = "./fish.db"; function createDbConnection() { if (fs.existsSync(filepath)) { return new sqlite3.Database(filepath); } else { const db = new sqlite3.Database(filepath, (error) = { if (error) { return console.error(error.message); } createTable(db); }); console.log("Connection with SQLite has been established"); return db; } } function createTable(db) { db.exec(` CREATE TABLE sharks ( ID INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL, color VARCHAR(50) NOT NULL, weight INTEGER NOT NULL ); `); } module.exports = createDbConnection(); module.exports = createDbConnection(); rm fish.db node db.js node db.js THIRD nano insertData.js const db = require("./db"); function insertRow() { const [name, color, weight] = process.argv.slice(2); db.run( `INSERT INTO sharks (name, color, weight) VALUES (?, ?, ?)`, [name, color, weight], function (error) { if (error) { console.error(error.message); } console.log(`Inserted a row with the ID: ${this.lastID}`); } ); } insertRow(); node insertData.js name blue 1 node insertData.js max white 2 FOURTH nano listData.js const db = require("./db"); function selectRows() { db.each(`SELECT * FROM sharks`, (error, row) = { if (error) { throw new Error(error.message); } console.log(row); }); } selectRows(); node listData.js FIFTH nano updateData.js const db = require("./db"); function updateRow() { const [id, name] = process.argv.slice(2); db.run( `UPDATE sharks SET name = ? WHERE id = ?`, [name, id], function (error) { if (error) { console.error(error.message); } console.log(`Row ${id} has been updated`); } ); } updateRow(); node updateData.js 2 sonny node listData.js SIXTH nano deleteData.js const db = require("./db"); async function deleteRow() { const [id] = process.argv.slice(2); db.run(`DELETE FROM sharks WHERE id = ?`, [id], function (error) { if (error) { return console.error(error.message); } console.log(`Row with the ID ${id} has been deleted`); }); } deleteRow(); node deleteData.js 2 node listData.js Useful Links VPS/VDS - https://mivocloud.com/