Summary: in this tutorial, you will learn how to interact with SQLite from a Node.js application using a built-in SQLite module.
Node.js 22.5.0 brought an experimental SQLite module under the name node:sqlite
. In this tutorial, you will learn how to use the built-in node:sqlite
module to insert, update, delete, and select data from a table in SQLite.
Prerequisites
- Experienced with JavaScript.
- Basic understanding of Node.js.
- Familiar with SQLite (If not check out this SQLite tutorial).
Creating a new project
Step 1. Open your terminal and create a new directory to store the project:
mkdir sqlite-demo
Code language: JavaScript (javascript)
Step 2. Navigate to the project directory:
cd sqlite-demo
Code language: JavaScript (javascript)
Step 3. Initialize the project using the npm init
command:
npm init --y
Code language: JavaScript (javascript)
This command will create a package.json
file in the project directory.
Step 4. Create an index.js
file within the project directory. This will be the entry point of the Node.js application.
Step 5. Change the package.json
file to support ES6 modules:
"type": "module",
Code language: JavaScript (javascript)
And configure the npm start
command in the scripts
section:
"scripts": {
"start": "node --experimental-sqlite index.js"
},
Code language: JavaScript (javascript)
Note that the flag --experimental-sqlite
is required because the built-in SQLite module is still experimental.
Step 6. Run the application using the npm start
:
npm start
Code language: JavaScript (javascript)
It’ll run the index.js
with the --experimental-sqlite
flag.
Opening a database connection
To open a connection to an SQLite database file, you use the DatabaseSync
constructor:
const db = new DatabaseSync(location[, options])
Code language: JavaScript (javascript)
The DatabaseSync
constructor accepts two arguments:
location
is a string that specifies the path to an SQLite file. If you want to use an in-memory database, you can pass the::memory::
string.options
is a JavaScript object that species the connection options. It has anopen
property that accepts a boolean value. If theopen
is true, the database is opened by theDatabaseSync
constructor. However, if it is false, you need to open the database manually y calling theopen()
method of theDatabaseSync
object.
The following example shows how to open a connection to a database file (db.sqlite
) located in the project directory:
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('db.sqlite');
Code language: JavaScript (javascript)
How it works.
First, import the DatabaseSync
class from the node:sqlite
module:
import { DatabaseSync } from 'node:sqlite';
Code language: JavaScript (javascript)
Second, create a new instance of the DatabaseSync
object to open a connection to the db.sqlite
database file:
const db = new DatabaseSync('db.sqlite');
Code language: JavaScript (javascript)
If you want to open a database connection explicitly, you can use the second parameter of the DatabaseSync
constructor:
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('contacts.db', { open: false });
db.open();
Code language: JavaScript (javascript)
Closing the database connection
To close an open database connection, you can call the close()
method of the DatabaseSync
object:
db.close();
Code language: JavaScript (javascript)
If you call the close()
method when the database is not open, it’ll throw an exception.
Here’s the complete code that connects to an SQLite file and closes the database connection:
import { DatabaseSync } from 'node:sqlite';
// open a database connection
const db = new DatabaseSync('db.sqlite');
// interact with the database
// ..
// close the database
if (db) db.close();
Code language: JavaScript (javascript)
Creating a new table
To create a new table, you can execute a CREATE TABLE statement using exec()
method of the DatabaseSync
object.
Note that the exec()
method can execute any SQL statement without returning any results.
For example, the following shows how to create a new table named contacts
:
import { DatabaseSync } from 'node:sqlite';
// open a database connection
const db = new DatabaseSync('contacts.db');
// create a new table
db.exec(`
create table if not exists contacts (
id integer primary key,
firstName text not null,
lastName text not null,
email text not null
)`);
// close the database
if (db) db.close();
Code language: JavaScript (javascript)
The contacts table has four columns:
id
is the auto-increment primary key.firstName
stores the contact’s first name.lastName
stores the contact’s last name.email
stores the contact’s email.
Inserting a row into a table
To insert a row into a table, you follow these steps:
First, create a prepared statement by calling the prepare() method of the DatabaseSync
object:
const stmt = db.prepare(sql)
Code language: JavaScript (javascript)
The prepare()
method accepts an SQL string and returns a prepared statement object with the type StatementSync
.
The sql
statement can be an insert, update, or delete statement. Typically, it includes parameter placeholders defined as question marks (?
) such as INSERT INTO tableName(c1, c2) VALUES(?,?)
.
Second, execute the prepared statement with values:
const result = stmt.run(value1, value2, ...)
Code language: JavaScript (javascript)
The run()
method executes the prepared statement and returns an object that has two properties:
changes
: returns the number of rows inserted, updated, or deleted depending on the SQL statement.lastInsertRowid
: returns the most recently insertedrowid
. It is only relevant if the sql statement is theinsert
statement and the table has anINTEGER
PRIMARY
KEY
field.
For example, the following example shows how to insert a new row into the contacts
table:
import { DatabaseSync } from 'node:sqlite';
// open a database connection
const db = new DatabaseSync('contacts.db');
// create a new table
db.exec(`
create table if not exists contacts (
id integer primary key,
firstName text not null,
lastName text not null,
email text not null
)`
);
// insert a new row
const stmt = db.prepare(
`INSERT INTO contacts (first_name, last_name, email)
VALUES (?, ?, ?)`
);
const { lastInsertRowid } = stmt.run('Jane', 'Doe', '[email protected]');
console.log(`Inserted contact id: ${lastInsertRowid}`);
// close the database
if (db) db.close();
Code language: JavaScript (javascript)
How it works.
First, create a prepared statement that inserts a new row into the contacts
table:
const stmt = db.prepare(
`INSERT INTO contacts (first_name, last_name, email)
VALUES (?, ?, ?)`
);
Code language: JavaScript (javascript)
Second, execute the prepared statement with the first name, last name, and email values:
const { lastInsertRowid } = stmt.run('Jane', 'Doe', '[email protected]');
Code language: JavaScript (javascript)
Note that we destructure the lastInsertRowid
property from the result object using object destructuring syntax.
Third, display the inserted id in the console:
console.log(`Inserted contact id: ${lastInsertRowid}`);
Code language: JavaScript (javascript)
Reorganizing modules
Step 1. Create a new file DB.js
with the following code:
import { DatabaseSync } from 'node:sqlite';
export class DB {
constructor(pathToSQLiteFile) {
this.conn = new DatabaseSync(pathToSQLiteFile);
this.#init();
}
#init() {
this.conn.exec(`
create table if not exists contacts (
id integer primary key,
firstName text not null,
lastName text not null,
email text not null
)`);
}
close() {
if (this.conn) this.conn.close();
}
}
Code language: JavaScript (javascript)
In the DB.js
file, define the DB
class that does the following:
- First, open a database connection to an SQLite database file in the constructor and call the
init()
method to create thecontacts
table if it does not exist. Note that theinit()
is a private method so it can be called within theDB
class. - Second, close the database connection in the
close()
method.
Step 2. Create a ContactDB.js
file within the same directory:
export class ContactDB {
constructor(conn) {
this.conn = conn;
}
create({ firstName, lastName, email }) {
const stmt = this.conn.prepare(
`INSERT INTO contacts (first_name, last_name, email)
VALUES (?, ?, ?)`
);
const { lastInsertRowid } = stmt.run(firstName, lastName, email);
return { id: lastInsertRowid, firstName, lastName, email };
}
}
Code language: JavaScript (javascript)
How it works.
First, initialize the conn
in the constructor:
constructor(conn) {
this.conn = conn;
}
Code language: JavaScript (javascript)
The conn
is an instance of the DatabaseSync
class.
Second, define the create()
method that inserts a new row into the contacts
table:
create({ firstName, lastName, email }) {
const stmt = this.conn.prepare(
`INSERT INTO contacts (first_name, last_name, email)
VALUES (?, ?, ?)`
);
const { lastInsertRowid } = stmt.run(firstName, lastName, email);
return { id: lastInsertRowid, firstName, lastName, email };
}
Code language: JavaScript (javascript)
The create()
method accepts an object with three properties firstName
, lastName
, and email
.
The create()
method creates a prepared statement, executes the prepared statement with arguments, and returns a newly inserted contact.
Third, modify the index.js
to use the DB.js
and ContactDB.js
modules:
import { DB } from './DB.js';
import { ContactDB } from './ContactDB.js';
// open a database connection
const db = new DB('db.sqlite');
const contactDB = new ContactDB(db.conn);
const contact = contactDB.create({
firstName: 'Jane',
lastName: 'Doe',
email: '[email protected]',
});
console.log(contact);
// close the database
db.close();
Code language: JavaScript (javascript)
If you run the program, it should display the newly inserted contact with the following information:
{
id: 1,
firstName: 'Jane',
lastName: 'Doe',
email: '[email protected]'
}
Code language: JavaScript (javascript)
Reading data from a table
Step 1. Add the findById
method to the contact module to find the contact by id:
findById(id) {
const stmt = this.conn.prepare(`SELECT * FROM contacts WHERE id = ?`);
return stmt.get(id);
}
Code language: JavaScript (javascript)
How it works.
First, create a prepared statement that selects a row from the contacts table based on an id:
const stmt = this.conn.prepare(`SELECT * FROM contacts WHERE id = ?`);
Code language: JavaScript (javascript)
Second, execute the get()
method with the input id:
return stmt.get(id);
Code language: JavaScript (javascript)
Step 2. Modify the index.js
to use the findById
method:
import { DB } from './DB.js';
import { ContactDB } from './ContactDB.js';
// open a database connection
const db = new DB('db.sqlite');
const contactDB = new ContactDB(db.conn);
const contact = contactDB.findById(1);
console.log(contact);
// close the database connection
db.close();
Code language: JavaScript (javascript)
Step 3. Run the index.js
file:
npm start
Code language: JavaScript (javascript)
Output:
{
id: 1,
firstName: 'Jane',
lastName: 'Doe',
email: '[email protected]'
}
Code language: JavaScript (javascript)
Updating data
Step 1. Modify the ContactDB.js
module to add the update()
method that updates a contact:
update({ id, firstName, lastName, email }) {
const stmt = this.conn.prepare(
`UPDATE contacts
SET firstName = ?, lastName = ?, email = ?
WHERE id = ?`
);
const { changes } = stmt.run(firstName, lastName, email, id);
return changes;
}
Code language: JavaScript (javascript)
How it works.
First, create a prepared statement that updates the first name, last name, and email of a contact specified by an id:
const stmt = this.conn.prepare(
`UPDATE contacts
SET firstName = ?, lastName = ?, email = ?
WHERE id = ?`
);
Code language: JavaScript (javascript)
Second, execute the UPDATE
statement and get the number of rows updated:
const { changes } = stmt.run(firstName, lastName, email, id);
Code language: JavaScript (javascript)
Third, return the number of rows updated:
return changes;
Code language: JavaScript (javascript)
Step 2. Modify the index.js
to use the update()
method:
import { DB } from './DB.js';
import { ContactDB } from './ContactDB.js';
// open a database connection
const db = new DB('db.sqlite');
const contactDB = new ContactDB(db.conn);
// find the contact with id 1
const contact = contactDB.findById(1);
// update the last name and email
contact.lastName = 'Smith';
contact.email = '[email protected]';
// apply the changes to the database
contactDB.update(contact);
// close the database connection
db.close();
Code language: JavaScript (javascript)
How it works.
First, find the contact with id 1:
const contact = contactDB.findById(1);
Code language: JavaScript (javascript)
Second, update the last name and email:
contact.lastName = 'Smith';
contact.email = '[email protected]';
Code language: JavaScript (javascript)
Third, apply the changes to the database:
contactDB.update(contact);
Code language: JavaScript (javascript)
Deleting data from a table
Step 1. Add the delete()
method to the ContactDB
class:
function delete(id) {
const stmt = this.conn.prepare(`DELETE FROM contacts WHERE id = ?`);
const { changes } = stmt.run(id);
return changes;
}
Code language: JavaScript (javascript)
How it works.
First, create a prepared statement that deletes a row from the contacts
table by id:
const stmt = this.conn.prepare(`DELETE FROM contacts WHERE id = ?`);
Code language: JavaScript (javascript)
Second, execute the DELETE
statement with the input id and get the number of rows deleted:
const { changes } = stmt.run(id);
Code language: JavaScript (javascript)
Third, return the number of rows deleted:
return changes;
Code language: JavaScript (javascript)
Step 2. Modify the index.js
file to use the delete()
method of the ContactDB
class:
import { DB } from './DB.js';
import { ContactDB } from './ContactDB.js';
// open a database connection
const db = new DB('db.sqlite');
const contactDB = new ContactDB(db.conn);
// delete contact with id 1
contactDB.delete(1);
// close the database connection
db.close();
Code language: JavaScript (javascript)
Step 3. Run the index.js
to delete the contact with id:
contactDB.delete(1);
Code language: JavaScript (javascript)
If you open the SQLite file, you’ll see that the contacts table has no rows.
Reading all rows from a table
Step 1. Define the findAll()
method in the ContactDB
class to return all rows from the contacts
table:
findAll() {
const stmt = this.conn.prepare(`SELECT * FROM contacts`);
return stmt.all();
}
Code language: JavaScript (javascript)
How it works.
First, create a prepared statement that returns all rows from the contacts
table:
const stmt = this.conn.prepare(`SELECT * FROM contacts`);
Code language: JavaScript (javascript)
Second, call the all()
method to get the result set as an array of contact objects:
return stmt.all();
Code language: JavaScript (javascript)
Step 2. Modify the index.js
file to use the findAll()
method:
import { DB } from './DB.js';
import { ContactDB } from './ContactDB.js';
// open a database connection
const db = new DB('db.sqlite');
const contactDB = new ContactDB(db.conn);
// insert 2 contacts
const contacts = [
{
firstName: 'John',
lastName: 'Doe',
email: '[email protected]',
},
{
firstName: 'Jane',
lastName: 'Smith',
email: '[email protected]',
},
];
for (const contact of contacts) {
contactDB.create(contact);
}
// find all contacts
contactDB.findAll().forEach(console.log);
// close the database connection
db.close();
Code language: JavaScript (javascript)
How it works.
First, insert two new rows into the contacts
table:
const contacts = [
{
firstName: 'John',
lastName: 'Doe',
email: '[email protected]',
},
{
firstName: 'Jane',
lastName: 'Smith',
email: '[email protected]',
},
];
for (const contact of contacts) {
contactDB.create(contact);
}
Code language: JavaScript (javascript)
Second, find all contacts and display each in the console:
contactDB.findAll().forEach(console.log);
Code language: JavaScript (javascript)
Step 3. Run the index.js
file:
npm start
Code language: JavaScript (javascript)
Output:
[
{
id: 2,
firstName: 'John',
lastName: 'Doe',
email: '[email protected]'
},
{
id: 3,
firstName: 'Jane',
lastName: 'Smith',
email: '[email protected]'
}
]
Code language: JavaScript (javascript)
Download the project source code
Download the project source code
Summary
- Create a new instance of the
DatabaseSync
class to open a connection to an SQLite database file. - Call the
close()
method of theDatabaseSync
object to close the database connection. - Call the
prepare()
method of theDatabaseSync
object to create a new prepared statement and call therun()
method of theDatabaseSync
to execute the prepared statement. - Use the
get()
method to get the first row returned by a prepared statement. - Use the
all()
method to get all rows of a prepared statement as an array of objects.