How to Use the Built-in SQLite Module in Node.js

Node.js now includes a native, built-in SQLite module (node:sqlite) that allows developers to work with SQLite databases directly without installing third-party dependencies like sqlite3 or better-sqlite3. This article provides a quick guide on how to import the module, establish a database connection, execute SQL commands, and use prepared statements to interact with your data safely and efficiently.

Prerequisites

The built-in SQLite module is available as an experimental feature starting in Node.js v22.5.0. To use it, ensure your Node.js runtime is updated to this version or later.

Importing the Module and Opening a Database

The module exposes a DatabaseSync class for synchronous database operations. You can instantiate it to open an existing database file, create a new one, or set up an in-memory database.

import { DatabaseSync } from 'node:sqlite';

// Open a file-based database
const db = new DatabaseSync('my_database.db');

// Alternatively, open an in-memory database for temporary storage
// const db = new DatabaseSync(':memory:');

Creating Tables and Executing SQL

To execute raw SQL queries that do not return rows (such as schema definitions or table creations), use the exec() method on the database instance.

// Create a new table
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`);

Inserting Data with Prepared Statements

To prevent SQL injection attacks, never concatenate values directly into your SQL queries. Instead, use prepared statements. You can prepare a statement with db.prepare() and then execute it using the run() method.

// Prepare an INSERT statement with placeholders
const insertUser = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');

// Execute the statement with specific values
insertUser.run('Jane Doe', 'jane.doe@example.com');
insertUser.run('John Smith', 'john.smith@example.com');

Querying Data

To retrieve data from the database, prepare a SELECT statement and use the all() method to get an array of results. Each row is returned as a plain JavaScript object.

// Prepare a SELECT statement
const selectAllUsers = db.prepare('SELECT * FROM users');

// Retrieve all rows
const users = selectAllUsers.all();
console.log(users);

If you need to query specific records using parameters, pass the arguments to the all() method:

// Prepare a query with a WHERE clause
const selectUserByEmail = db.prepare('SELECT * FROM users WHERE email = ?');

// Retrieve the matching records
const user = selectUserByEmail.all('jane.doe@example.com');
console.log(user);

Closing the Database Connection

When you are finished working with the database, close the connection to free up system resources.

db.close();