node-sqlite3 icon indicating copy to clipboard operation
node-sqlite3 copied to clipboard

NOT NULL constraint failed with bulk insert

Open Rosthouse opened this issue 6 years ago • 2 comments

Hello everyone

I found that I have troubles using parametrized insert statements with version 4.1.0. Here's a basic example demonstrating the error:

let sqlite3 = require("sqlite3");

let data = [
    [1, 1, 1],
    [2, 2, 2],
    [3, 3, 3]
];

let db = new sqlite3.Database(":memory:")
    .run(`CREATE TABLE TEST(
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    count INTEGER NOT NULL,
    xPos REAL NOT NULL,
    yPos REAL NOT NULL
)`, (err) => {
        if (err) {
            console.log(err);
        } else {
            insertRecords();
        }
    });

insertRecords = () => {
    db.all(`INSERT INTO TEST(count, xPos, yPos) VALUES(?,?,?)`, data, (err, rows) => {
        if (err) {
            console.log(err);
        } else {
            for (let i = 0; i < rows.length; i++) {
                console.log(rows[i]);
            }
        }
    });
};

If I execute that code, I will get errors along the following lines:

[Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: TEST.count] {
  errno: 19,
  code: 'SQLITE_CONSTRAINT'
}

It seems that no matter what I do, the parameter data doesn't seem to be parsed correctly. Now it's obviously very possible that I'm doing something stupid, but I tested quite a few things and I'm out of ideas. Does anybody else have the same issue?

Rosthouse avatar Oct 28 '19 06:10 Rosthouse