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
)
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 = "[email protected]";
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.lastIDgives 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.
SOCIAL SHARE CARD GENERATOR