npm install sqlite3 sqlite3-admin

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

// Opens database file
// if file not exists → it will create automatically
const db = new sqlite3.Database('./app.db');

node db.js

SQLite3 is the file based database

• No server needed.
• Just one file → .db

You can embed this DB inside your desktop app, CLI tool or small web backend.

For using SQLite3 we need a SQLite3 package and web admin panel. You can install it from NPM

npm install sqlite3 sqlite3-admin

Making a database connection

First of all for using SQLite3 database you need a making connection (for instance create a file db.js):

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

// Opens database file
// if file not exists → it will create automatically
const db = new sqlite3.Database('./app.db');

Run your db.js:

node db.js

And then we’ll connect it to sqlite3 admin panel for viewing it visually (for checking that our codes runs correctly or not). For connecting your db you need to prompt your CLI like this command:

npx sqlite3-admin app.db

If your database’s name isn’t app.db you can change it.

Let’s check:

So we made our SQLite3 database and connected it to web admin panel.

Creating a table

const sqlite3 = require(‘sqlite3’).verbose(); const db = new sqlite3.Database(‘./app.db’);

db.run(CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT ), (err)=>{ if(err) console.log(“Error: “, err); else console.log(“Table created ✅”) }); Run:

node db.js

Let’s check:

So we created our first table successfully!

Insert data

Now we’ll try to insert data to our users table:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./app.db');

const name = "John";
const email = "johndoe@example.com";

db.run(`INSERT INTO users(name, email) VALUES(?, ?)`, [name, email], function(err){
if(err) console.log("Error: ", err);
console.log("Inserted row ID:", this.lastID);
});

Important:

• ? placeholders avoid SQL injection
• this.lastID gives auto incremented ID

Read (SELECT) data

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./app.db');

db.all("SELECT * FROM users", [], (err, rows) => {
if (err) throw err;

rows.forEach(row => {
console.log(row.id, row.name, row.email);
});
});
db.all() returns array of rows

But if you want get only one row like using this query SELECT * FROM users WHERE id = 1 you should use db.get().

Update row

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./app.db');

db.run(
`UPDATE users SET name=? WHERE id=?`,
["Mike Tyson", 1],
function(err) {
if (err) throw err;
console.log("Rows changed:", this.changes);
}
);

this.changes shows how much rows changed

Delete row

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./app.db');

db.run(
`DELETE FROM users WHERE id=?`,
[1],
function(err) {
if (err) throw err;
console.log("Rows deleted:", this.changes);
}
);

Close the database

db.close();

Best practice → close DB only when app exit.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./app.db');

db.serialize(() => {
db.run("CREATE TABLE IF NOT EXISTS posts(id INTEGER PRIMARY KEY, title TEXT)");

db.run("INSERT INTO posts(title) VALUES(?)", ["Hello world"]);

db.all("SELECT * FROM posts", [], (err, rows)=>{
console.log(rows);
});
});

// db.close()

db.serialize() run all queries inside it one-by-one in order (synchronously guaranteed order).

Without serialize() → sqlite3 runs queries in parallel (async) and order isn’t guaranteed.