CODEWITHSUNDEEP
node.jssqlitesequelize.js
Felix Schütz
Felix Schütz

Reputation: 1377

Get the ID of a new entry using Sequelize and SQLite without auto increment

I want to use Node.js with Sequelize and SQLite. I have the following model for users:

const User = sequelize.define('user', {
  rowid: {
    type: 'INTEGER',
    primaryKey: true,
  },
  // other properties
});

If I now execute const newUser = await User.create({ /* properties */ }), I would expect to be able to access the ID of the new user with newUser.rowid. But this is not the case, only if I add autoIncrement: true to the specification. Following https://www.sqlite.org/autoinc.html, I don't want to do this. Are there any other possibilities?

Edit

As it turns out, this is only possible by creating the table without autoIncrement: true and only afterward add it to the column definition. The much more practical way is probably to just use autoincrement, the performance decrease won't matter for most small applications.

Upvotes: 0

Views: 1195

Answers (1)

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

You should not have to use autoincrement to access rowid in the table User. I would expect to see it this way User.rowid not newUser.rowid as in the example, since the table name is (apparently) User.

Also from the sqlite doc:

if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER".

And finally, you might consider a different name than rowid for the PK, since sqlite already has a rowid.

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

Upvotes: 2

Related Questions