Bulk insert class object in sqllite database in node js

Question

I have the following class called Label:

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
    }
  }

I have a list of these class objects called labels that I want to insert, or rather bulk insert into my sqllite database, which looks like the following:

I tried to insert this array of class objects with:

function addRowsToDb(){
    console.log("Adding rows to the db")
    db = new sqlite3.Database('./annotator.db');
    db.serialize(function() {

        var stmt = db.prepare("INSERT INTO Label(ImageID, PlotID, Camera, Date, x, y, w, h, LabelHash) VALUES (?,?,?,?,?,?,?,?,?)");
        labels.forEach((label) => {
            stmt.run(label.imageid, label.plotid, label.camera, label.date, label.x, label.y, label.w, label.h, label.hash);
        })
        stmt.finalize();
    });
    db.close();
}

Which gave me the following errors in the console:

Uncaught Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: Label.ImageID

Uncaught Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: Label.LabelHash

Could someone explain to me how to do this properly and how to do this with bulk data, so not the forEach with the labels array?

Tudor Constantin · Accepted Answer · 2019-10-30 14:10:49Z

1

I think the error is pretty straight forward - you try to insert NULL values into non-nullable db columns (Label.ImageID and Label.LabelHash).

How to solve this depends on what makes the most sense for your app:

remove the NOT NULL constraint on those columns
skip the labels that don't have ImageID and LabelHash set
provide a default value (empty string for example) for the ImageID and LabelHash cols

answered Oct 30, 2019 at 14:10

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Barbieri Over a year ago

That was it, pretty stupid of me not to see that while debugging. Do you know any way though to do a bulk insert?

Collectives™ on Stack Overflow

Bulk insert class object in sqllite database in node js

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related