Bulk Insert Class Object in SQLite Database in Node JS

Introduction

As a developer, it’s not uncommon to encounter scenarios where you need to insert data into a database in bulk. In this article, we’ll explore how to achieve this task using Node.js and SQLite.

We’ll delve into the specifics of handling non-nullable columns, providing default values, and implementing efficient insertion methods. By the end of this tutorial, you’ll have a solid understanding of how to successfully insert class objects into an SQLite database in Node JS.

Prerequisites

Before we begin, make sure you have the following installed:

• Node.js (version 14 or later)
• sqlite3 (a popular SQLite library for Node.js)

You can install sqlite3 using npm by running the following command:

npm install sqlite3

Label Class Definition

Let’s start with defining our Label class, which will serve as the data model for our database table.

class Label {
  constructor(imageid, plotid, camera, date, x, y, w, h, id, hash) {
    this.imageid = imageid;
    this.plotid = plotid;
    this.camera = camera;
    this.date = date;
    this.x = x;
    this.y = y;
    this.w = w;
    this.h = h;
    this.id = id;
    this.hash = hash;
  }
}

Database Setup

Next, let’s set up our SQLite database using the sqlite3 library.

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

// Create a new SQLite database connection
const db = new sqlite3.Database('./annotator.db');

// Create the Label table if it doesn't exist
db.serialize(function () {
  db.run(`
    CREATE TABLE IF NOT EXISTS Label (
      ImageID INTEGER PRIMARY KEY,
      PlotID INTEGER,
      Camera TEXT,
      Date TEXT,
      x REAL,
      y REAL,
      w REAL,
      h REAL,
      id INTEGER,
      LabelHash TEXT
    )
  `);
});

Inserting Data in Bulk

Now that we have our database set up, let’s focus on inserting data into the Label table using a bulk insertion method.

The problem with the provided code snippet is that it tries to insert NULL values into columns marked as PRIMARY KEY, which causes the SQLite error. To resolve this issue, we need to decide how to handle these non-nullable columns when inserting data in bulk.

We have three possible approaches:

1. Remove the NOT NULL constraint

   • This approach involves modifying the table schema by removing the NOT NULL constraint from the primary key columns (ImageID and LabelHash). However, this should be done with caution as it may lead to inconsistent data in your database.

db.run(ALTER TABLE Label DROP CONSTRAINT IF EXISTS PRIMARY KEY;);

2.  **Skip inserting labels without ImageID or LabelHash**

    *   This approach involves skipping the insertion of labels that don't have `ImageID` and `LabelHash` values set before proceeding with the bulk insertion.

    ```javascript
const skippedLabels = labels.filter((label) => !label.imageid || !label.hash);

db.serialize(function () {
  const stmt = db.prepare("INSERT INTO Label(PlotID, Camera, Date, x, y, w, h, id, LabelHash) VALUES (?,?,?,?,?,?,?,?,?)");

  labels.forEach((label) => {
    if (!label.imageid || !label.hash) return; // Skip inserting label without ImageID or LabelHash
    stmt.run(label.plotid, label.camera, label.date, label.x, label.y, label.w, label.h, label.id, label.hash);
  });

  stmt.finalize();
});

3. Provide a default value

   • This approach involves providing a default value for the ImageID and LabelHash columns when inserting data in bulk.

const stmt = db.prepare(“INSERT INTO Label(ImageID, PlotID, Camera, Date, x, y, w, h, id, LabelHash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”);

labels.forEach((label) => { const defaultValue = label.imageid || ‘DEFAULT_IMAGE_ID’; const hashValue = label.hash || ‘DEFAULT_LABEL_HASH’;

stmt.run( defaultValue, label.plotid, label.camera, label.date, label.x, label.y, label.w, label.h, label.id, hashValue ); });

By applying these approaches, you can effectively insert data into your SQLite database in bulk while handling non-nullable columns.

### Conclusion

In this tutorial, we explored how to achieve bulk insertion of class objects into an SQLite database using Node.js and the `sqlite3` library. We discussed three possible approaches for handling non-nullable columns and provided example code snippets for each approach. By applying these techniques, you can efficiently insert data into your database while maintaining data consistency.

### Additional Considerations

*   **Error Handling:** Make sure to implement proper error handling mechanisms in your production code to handle any unexpected errors that may occur during database operations.
*   **Data Validation:** Always validate user input data before inserting it into the database to prevent potential security vulnerabilities and ensure data accuracy.
*   **Indexing:** Consider adding indexes to columns used in WHERE, JOIN, and ORDER BY clauses to improve query performance.

By following these guidelines and applying the approaches discussed in this tutorial, you can create efficient and scalable database systems for your applications.

Last modified on 2025-04-25