SQLite Node.js: Connecting to a SQLite Database

Summary: in this tutorial, you will learn how to connect to an SQLite database from Node.js applications.

Creating a Node.js app

Step 1. Open your terminal and create a new project directory:

mkdir nodejs-sqlite
cd nodejs-sqlite

Step 2. Create a package.json file:

npm init -y

Step 3. Install the sqlite3 package to interact with an SQLite database from the Node.js app:

npm install sqlite3

Step 4. Create a new index.js in the project directory with the following source code:

console.log("Node.js + SQLite");Code language: JavaScript (javascript)

Step 5. Add the type module to the ackage.json file to use the ES module:

"type": "module",Code language: JavaScript (javascript)

Change the scripts section to the following to run the npm start command to start a Node.js app:

"start": "node index.js"Code language: JavaScript (javascript)

Step 6. Open the terminal and run the following command:

npm start

You should see the following message:

Node.js SQLiteCode language: CSS (css)

Creating a new SQLite database file

To create a new SQLite database file, you follow these steps:

First, import the sqlite3 module:

import sqlite3 from "sqlite3";Code language: JavaScript (javascript)

Second, create a new instance of the Database class of the sqlite3 module:

const db = new sqlite3.Database(filename);Code language: JavaScript (javascript)

When creating a Database object, you specify a SQLite database file specified by the filename argument.

If the filename already exists, the app opens a new connection to the SQLite database file. Otherwise, it creates a new SQLite database file and opens a connection.

The Database() constructor returns an object (db) representing the database connection to the SQLite database.

Third, change the index.js to the following and save it:

import sqlite3 from "sqlite3";

const db = new sqlite3.Database("my.db");Code language: JavaScript (javascript)

Finally, run the following command in the terminal:

npm start

You’ll see that the program creates a new SQLite database file my.db within the project directory.

Connecting to an existing SQLite database file

First, download the chinook.db file from the sample database page and copy it to your project directory.

Second, modify the index.js to change the filename tochinook.db to connect to an existing database:

import sqlite3 from "sqlite3";

const db = new sqlite3.Database("chinook.db");Code language: JavaScript (javascript)

When you open a connection to an SQLite database file, you can use one or a combination of the following modes:

The Database() constructor accepts one or more modes as the second argument.

The mode defaults to the OPEN_READWRITE | OPEN_CREATE, meaning that if the database does not exist, it’ll create a new database for reading and writing.

For example, if you want to open the chinook.db database for reading and writing and don’t want to create a new one if it does not exist, you can use the OPEN_READWRITE mode as follows:

import sqlite3 from "sqlite3";

const db = new sqlite3.Database("chinook.db", sqlite3.OPEN_READWRITE);Code language: JavaScript (javascript)

If the chinook.db does not exist in the project directory, the app will issue the following error in the console instead of creating a new one:

[Error: SQLITE_CANTOPEN: unable to open database file
Emitted 'error' event on Database instance at:
] {
  errno: 14,
  code: 'SQLITE_CANTOPEN'
}Code language: JavaScript (javascript)

Connecting to an in-memory SQLite database

To open a database connection to an in-memory SQLite database, you pass the literal string ‘:memory:' to the Database constructor:

const db = new sqlite3.Database(':memory:');Code language: JavaScript (javascript)

Here’s the complete program:

import sqlite3 from "sqlite3";

const db = new sqlite3.Database(":memory:");Code language: JavaScript (javascript)

Note that the in-memory database can be useful in some cases like speeding up tests significantly.

Summary

• Use the sqlite3 module to interact with SQLite from Node.js apps.
• Create a new instance of the Database class to create a new SQLite database file if it does not exist or open a connection to an existing one.