NodeJS SQLite: A Comprehensive Guide for Beginners

SQLite is a lightweight, serverless SQL database engine that provides an easy way to persist data in local applications. When combined with Node.js, it becomes a powerful tool for building JavaScript-based apps that need simple database functionality.

In this comprehensive beginner’s guide, you will learn how to:

• Install and configure SQLite with Node.js
• Connect to a SQLite database
• Create tables and insert data
• Query, update, and delete data
• Use transactions for data integrity

We will explore all of these topics through detailed examples. By the end, you’ll have the skills to build practical Node.js apps backed by SQLite.

Why Use SQLite with Node.js?

There are a few key reasons why SQLite is a great fit for Node.js applications:

• Serverless – No separate database server process required. The database is stored in a single file on disk.
• Lightweight – SQLite has a small disk and memory footprint, making it ideal for embedded uses.
• Simple – SQL syntax without complex config. It is faster to get up and running than larger databases like MySQL or Postgres.
• Reliable – SQLite is stable, fast, and widely used in many applications.
• Full-featured – Despite its simplicity, SQLite implements most common database features like transactions, data types, indices, etc.

Some example use cases:

• Local or embedded databases for desktop apps
• Prototyping and testing
• Storing user data and preferences
• Data caching
• IoT and mobile apps

For larger or high-traffic apps, you may eventually outgrow SQLite. But it’s a great starting point due to its simplicity and ease of use.

Installing the NodeJS SQLite3 Module

To work with SQLite in Node.js, we need to install the sqlite3 module from NPM:

npm install sqlite3

This will add the module locally so we can require/import it in our code.

Connecting to a SQLite Database using NodeJS

Let’s create a simple Node.js script to connect to a SQLite database.

First, require the sqlite3 module:

const sqlite3 = require('sqlite3').verbose();

Next, open a connection to a database file. This will create the file if it doesn’t exist:

const db = new sqlite3.Database('./mydb.sqlite');

We can also listen for the open event to run code when the connection is ready:

db.on('open', () => {
  // database is ready
});

And close the connection when done:

db.close(); 

Putting this together into a script called app.js:

const sqlite3 = require('sqlite3').verbose();

const db = new sqlite3.Database('./mydb.sqlite', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the database.');
});

db.on('open', () => {
  console.log('Database is ready.');

  // close the database
  db.close((err) => {
    if (err) {
      console.error(err.message);
    }
    console.log('Close the database connection.');
  });
});

If we run this script with node app.js, it will connect to mydb.sqlite, report that the database is ready, then close the connection.

This shows the basic pattern for connecting to a SQLite database with Node.js. Next let’s look at creating tables and inserting data.

Creating SQLite Tables using NodeJS

To create a table in SQLite, we can use standard SQLite CREATE TABLE syntax:

CREATE TABLE table_name (
   column1 datatype PRIMARY KEY,
   column2 datatype NOT NULL,
   column3 datatype DEFAULT value,
   ...
);

For example, a simple table to store blog posts might be:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY, 
  title TEXT NOT NULL,
  content TEXT NOT NULL
);

In Node.js, we can execute this SQLite statement using the run method on the database connection:

db.run(`CREATE TABLE posts ...`);

Let’s see it in context:

const db = new sqlite3.Database('./mydb.sqlite');

db.run(`
  CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL, 
    content TEXT NOT NULL
  )  
`);

db.close();

We can also separate table creation into a helper function:

function createTables(db) {
  db.run(`
    CREATE TABLE posts (
      id INTEGER PRIMARY KEY,
      title TEXT NOT NULL,
      content TEXT NOT NULL 
    )  
  `);
}

// Usage:
createTables(db);

This keeps our database code modular.

Inserting Data with SQLite using NodeJS

To insert data into a SQLite table, we can use the SQLite INSERT statement:

INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

For example, to insert a new post:

db.run(`
  INSERT INTO posts (title, content)
  VALUES ('Post Title', 'My post content')  
`);

Again, we’ll want to parameterize this statement to avoid SQL injection vulnerabilities. We can use ? placeholders for values:

db.run(
  `INSERT INTO posts (title, content) VALUES (?, ?)`,
  ['Post Title', 'My post content']  
);

The second array argument provides values to replace the ? placeholders safely.

Let’s build out a function to encapsulate inserts:

function createPost(db, title, content) {
  const stmt = db.prepare(`
    INSERT INTO posts (title, content) VALUES (?, ?)
  `);
  
  stmt.run(title, content);
  stmt.finalize();
}

// Usage:
createPost(db, 'My Title', 'My Content'); 

Here we use db.prepare() to prepare an insert statement. This helps separate preparing the SQL ite from executing it.

We can also return the insert ID to get the ID of the newly inserted row:

function createPost(db, title, content) {
  const stmt = db.prepare(`
    INSERT INTO posts (title, content) VALUES (?, ?)
  `);
  
  const info = stmt.run(title, content);
  stmt.finalize();

  return info.lastID; 
}

// Usage:
const newPostId = createPost(db, 'My Title', 'My Content');

This insert ID can be useful for lookups later.

Querying Data with NodeJS SQLite

To query data from a SQLite database, we can use the SQLite SELECT statement:

SELECT column1, column2 FROM table_name;

In Node.js, we have a few options for executing SELECT statements:

• db.get() – Fetches a single row
• db.all() – Fetches multiple rows
• db.each() – Iterates through rows one by one

For example, to get all posts:

db.all('SELECT * FROM posts', (err, rows) => {
  if (err) {
    throw err;
  }

  rows.forEach(row => {
    console.log(row); 
  });
});

This will fetch all rows from posts into the rows array that we can iterate through.

To get a single post by ID:

const postId = 1;

db.get(`SELECT * FROM posts WHERE id = ?`, postId, (err, row) => {
  if (err) {
    throw err; 
  }

  console.log(row);
});

And to iterate through rows one by one:

db.each('SELECT * FROM posts', (err, row) => {
  if (err) {
    throw err;
  }
  
  console.log(row);
}, () => {
  // called when all rows have been retrieved
});

The second callback fires after the last row is processed.

Again, we can wrap these into helper functions:

function getAllPosts(db) {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM posts', (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows);
      }
    });
  }); 
}

async function printAllPosts() {
  const rows = await getAllPosts(db);

  rows.forEach(row => {
    console.log(row);
  });
}

This keeps our database logic reusable and isolated.

Updating SQLite Data using NodeJS

To update existing data in a SQLite database, we can use the SQLite UPDATE statement:

UPDATE table_name
SET column1 = value1, 
    column2 = value2
WHERE condition;

For example, to update a post title:

db.run(
  `UPDATE posts SET title = ? WHERE id = ?`,
  ['New Title', postId]  
); 

Again, using parameters to avoid SQL injection.

We can build a helper function:

function updatePost(db, postId, newTitle) {
  return new Promise((resolve, reject) => {
    db.run(
      `UPDATE posts SET title = ? WHERE id = ?`,
      [newTitle, postId],
      (err) => {
        if (err) {
          reject(err);
        } else {
          resolve();
        }  
      }
    );
  });
}

And use it like:

await updatePost(db, 1, 'My New Title');

This will allow us to update existing records in the database easily.

Deleting Data in an SQLite Database Using NodeJS

To delete data from a SQLite database, we use the SQLite DELETE statement:

DELETE FROM table_name WHERE condition;

For example, to delete a post by ID:

db.run(
  `DELETE FROM posts WHERE id = ?`,
  [postId]
);

And similarly, we can build a helper function:

function deletePost(db, postId) {
  return new Promise((resolve, reject) => {
    db.run(
      `DELETE FROM posts WHERE id = ?`, 
      [postId],
      (err) => {
        if (err) {
          reject(err);
        } else {
          resolve();
        }
      }
    );
  });
}

// Usage:
await deletePost(db, 1);

This allows us to easily delete records.

Using Transactions in SQLite Using NodeJS

We may want to use transactions to ensure database consistency when performing multiple operations.

Transactions allow us to execute SQL statements in an atomic manner – either all succeed or all fail. This prevents data corruption from partial updates.

Here is an example wrapped in a transaction:

await db.run('BEGIN TRANSACTION');

try {

  await db.run('INSERT INTO ...');
  
  await db.run('UPDATE ...');

  await db.run('COMMIT');

} catch (err) {
  
  await db.run('ROLLBACK');
  throw err;
  
}

We use BEGIN TRANSACTION to start a transaction. Then COMMIT to commit all changes at the end, or ROLLBACK to undo them if an error occurs.

This ensures the two operations execute together atomically.

We can also wrap helper functions in a transaction:

async function updatePostInTransaction(db, postId, newValues) {

  await db.run('BEGIN');

  try {

    await updatePost(db, postId, newValues);
    
    await updatePostReferences(db, postId);

    await db.run('COMMIT');

  } catch (err) {

    await db.run('ROLLBACK');
    throw err;

  }

}

This allows us to reuse database logic while ensuring atomicity.

Example App: Simple Blog using NodeJS + SQLite

Let’s put what we’ve learned together and build a simple command line Node.js app that simulates a blog site backed by SQLite.

We’ll create methods to:

• Create sample seed data
• List all posts
• Get a single post
• Create a new post
• Update a post
• Delete a post

First, install SQLite:

npm install sqlite3

Then create a database.js module with our database logic:

// database.js

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('./blog.sqlite');

// CREATE TABLE etc...

function listPosts() {
  // SELECT posts...
}

function getPostById(id) {
  // SELECT * FROM posts... 
}

function createPost(title, content) {
  // INSERT INTO posts...
}

function updatePost(id, newTitle) {
  // UPDATE posts...
}

function deletePost(id) {
  // DELETE FROM posts...
}

module.exports = {
  listPosts,
  getPostById,
  createPost,
  updatePost,
  deletePost  
}

This encapsulates all our database logic. Next, app.js which implements the main app functionality:

// app.js

const db = require('./database');

// Seed data
db.createPost('Post 1', 'Content 1'); 
db.createPost('Post 2', 'Content 2');

// List posts
console.log('All Posts:');
db.listPosts().forEach(post => {
  console.log(`- ${post.title}`); 
});

// Get single post
const post = db.getPostById(1);
console.log(`\nPost with ID 1: ${post.title}`);

// Create post
const newPostId = db.createPost('Post 3', 'Content 3');
console.log(`\nCreated Post with ID ${newPostId}`);

// Update post
db.updatePost(newPostId, 'Post 3 Updated');

// Delete post
db.deletePost(newPostId);
console.log('\nDeleted Post 3');

This shows basic CRUD usage of our blog database.

When run with node app.js, we get:

All Posts:
- Post 1  
- Post 2

Post with ID 1: Post 1

Created Post with ID 3

Deleted Post 3

And we have a simple command line blog application with SQLite!

This shows how the pieces fit together for a basic Node.js app with SQLite.

Summary

From here, you can start building practical Node.js applications backed by SQLite for local data persistence.

• SQLite offers a lightweight, embedded database for Node.js apps
• The sqlite3 module provides the interface for working with SQLite
• Use db.run(), db.get(), db.all(), and db.each() for queries
• Abstract database logic into helper modules for reuse
• Use transactions when performing multiple operations

With these building blocks, SQLite and Node.js provide a solid data storage foundation for JavaScript applications.