Introduction to node-sqlite3

Node-sqlite3 is a Node.js package that allows you to interact with SQLite databases. SQLite is a lightweight, disk-based database that is self-contained and serverless, making it an excellent choice for applications that need to store data locally. With node-sqlite3, you can easily integrate SQLite into your Node.js applications using its rich set of APIs. In this guide, we will explore a variety of node-sqlite3 APIs along with code snippets and an example application to demonstrate how to use these APIs in practice.

Getting Started

First, you need to install node-sqlite3. You can do this using npm:

  
    npm install sqlite3
  

Basic Usage

  
    const sqlite3 = require('sqlite3').verbose();
    const db = new sqlite3.Database(':memory:');

    db.serialize(() => {
      db.run("CREATE TABLE lorem (info TEXT)");

      const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
      for (let i = 0; i < 10; i++) {
        stmt.run("Ipsum " + i);
      }
      stmt.finalize();

      db.each("SELECT rowid AS id, info FROM lorem", (err, row) => {
        console.log(row.id + ": " + row.info);
      });
    });

    db.close();
  

API Examples

Run SQL Queries

  
    db.run("CREATE TABLE user (id INT, name TEXT)");

    db.run("INSERT INTO user VALUES (1, 'John Doe')");
    db.run("INSERT INTO user VALUES (2, 'Jane Doe')");
  

Querying Data

  
    db.each("SELECT id, name FROM user", (err, row) => {
      if (err) {
        throw err;
      }
      console.log(row.id + ": " + row.name);
    });
  

Parameterized Queries

  
    const sql = "INSERT INTO user (id, name) VALUES (?, ?)";
    db.run(sql, [3, 'Alice']);
    db.run(sql, [4, 'Bob']);
  

Transaction Handling

  
    db.serialize(() => {
      db.run("BEGIN TRANSACTION");
      db.run("INSERT INTO user VALUES (5, 'Charlie')");
      db.run("INSERT INTO user VALUES (6, 'Dave')");
      db.run("COMMIT");
    });
  

Reading All Rows

  
    db.all("SELECT * FROM user", (err, rows) => {
      if (err) {
        throw err;
      }
      rows.forEach((row) => {
        console.log(`${row.id}: ${row.name}`);
      });
    });
  

Reading a Single Row

  
    db.get("SELECT * FROM user WHERE id = ?", [1], (err, row) => {
      if (err) {
        throw err;
      }
      console.log(row.id + ": " + row.name);
    });
  

Example Application

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

    const app = express();
    const db = new sqlite3.Database(':memory:');

    db.serialize(() => {
      db.run("CREATE TABLE user (id INT, name TEXT)");

      const stmt = db.prepare("INSERT INTO user VALUES (?, ?)");
      stmt.run(1, 'John Doe');
      stmt.run(2, 'Jane Doe');
      stmt.finalize();
    });

    app.get('/users', (req, res) => {
      db.all("SELECT * FROM user", (err, rows) => {
        if (err) {
          res.status(500).send(err.message);
          return;
        }
        res.json(rows);
      });
    });

    app.listen(3000, () => {
      console.log('Server running on port 3000');
    });
  

As shown in the example above, node-sqlite3 effectively integrates with an Express application to handle database operations. This makes it a powerful tool for creating lightweight and efficient applications that require local storage capabilities.

Hash: e1308ce8beecd011f671c1b71407f89fab9e167c779bdc2032cd5ead6d60382e