CODEWITHSUNDEEP
node.jssqlite
Shane_S
Shane_S

Reputation: 129

Inserting Id field autoincrement with sqlite3 and node.js

I am trying to insert into sqlite3 with node.js. I need a unique id field however I would like it to auto generate so I do not have to insert it when inserting to the table. I am getting connected and creating the table ok however then when I am inserting it is expecting me to supply an id field. Here is the code so far:

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('database/setup.db');


db.serialize(function() {
db.run("CREATE TABLE items (id INTEGER PRIMARY KEY, title TEXT, images TEXT, bid INTEGER, currentBidder TEXT)");

var stmt = db.prepare("INSERT INTO items VALUES(?,?,?,?)");
stmt.run('Title test 123', 'http://testshjshs', 100, 'shane'); 
stmt.finalize();

db.each("SELECT rowid, title FROM test", function(err, row) {
  console.log(row.rowid + ": " + row.title);
});

});

db.close();

This is returning the following error.

Error: SQLITE_ERROR: table items has 5 columns but 4 values were supplied

I understand why it is returning the following error but how do I set it so it inserts auto inserts the field or is this not possible? Thanks

Upvotes: 3

Views: 12692

Answers (2)

Abraham Brookes
Abraham Brookes

Reputation: 1998

If you want an auto incrementing field in your table, leave out the WITHOUT ROWID command when creating your table, and SQLite will create an automatic index for you, under the row name rowid

http://www.sqlitetutorial.net/sqlite-autoincrement/

Upvotes: 1

CL.
CL.

Reputation: 180020

The table has been declared with five columns, so when you give only four values to the INSERT, it doesn't know which column to omit.

Either explicitly say into which columns you want to insert:

INSERT INTO items(title,images,bid,currentBidder) VALUES(?,?,?,?)

Or give a value for all five columns (NULL means autoincrement):

INSERT INTO items VALUES(NULL,?,?,?,?)

Upvotes: 13

Related Questions