Home » Node.js Tutorial » Node.js SQLite

Node.js SQLite

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-demoCode language: JavaScript (javascript)

Step 2. Navigate to the project directory:

cd sqlite-demoCode language: JavaScript (javascript)

Step 3. Initialize the project using the npm init command:

npm init --yCode 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 startCode 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 an open property that accepts a boolean value. If the open is true, the database is opened by the DatabaseSync constructor. However, if it is false, you need to open the database manually y calling the open() method of the DatabaseSync 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 inserted rowid. It is only relevant if the sql statement is the insert statement and the table has an INTEGER 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 the contacts table if it does not exist. Note that the init() is a private method so it can be called within the DB 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 startCode 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 startCode 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 the DatabaseSync object to close the database connection.
  • Call the prepare() method of the DatabaseSync object to create a new prepared statement and call the run() method of the DatabaseSync 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.
Was this tutorial helpful ?
Npm Publish
Previously
Npm Publish
Up Next
A Complete Guide to Node Version Manager (NVM) on Windows
A Complete Guide to Node Version Manager (NVM) on Windows