How to obtain last insert rowid using sqlite3 WASM
(1) By Mark Hessling (rexxman) on 2025-02-08 23:08:33 [source]
I am migrating a WebSQL app to SQlite3 WASM and cannot determine how to obtain the last insert rowid from an INSERT statement.
I have a parent table with a number of child tables which have a reference to the parent table. create table parent (parent_id integer primary key, name text ) create table child1 (child1_id integer primary key, parent_id integer, name text)
I have a need to duplicate a parent and all children. The WebSQL code is:
var cmd = 'insert into parent (name )select name from parent where parent_id = ?'
t.executeSql(cmd,[id],
function(t,results)
{
var lastId = results.insertId
var newname = name + ' (Copy)'
t.executeSql('update parent set name = ? where parent_id = ?',[newname,lastId])
// children...
t.executeSql("insert into child1 (parent_id, name) select '" + lastId + "', name from child1 where parent_id = ?",[id],
function(t,err)
{
// error
})
I'm using the Worker 1 Promiser interface and use a helper function:
const sqlexec = async function(msgType, msgArgs, callback)
{
if (arguments.length === 2 && typeof msgArgs === 'function' )
{
callback = msgArgs
msgArgs = undefined
}
const p = g_workerPromise({type: msgType, args:msgArgs})
if ( callback )
return p.then( callback )
else
return p
}
The equivalent to WebSQL code above:
var cmd = 'insert into parent (name )select name from parent where parent_id = ?'
sqlexec('exec',
{
sql: cmd,
bind: [id],
rowMode: 'object',
resultRows: [],
columnNames: []
},
function(ev)
{
// need to get last insert rowid here
}).catch((e)=>
{
// error
})
I've tried various options, but not all and none of them return the last insert rowid when the insert is successful. Is it possible to get the last insert rowid?
Thanks, Mark
(2) By Stephan Beal (stephan) on 2025-02-08 23:30:51 in reply to 1 [link] [source]
// need to get last insert rowid here
i don't know off-hand that there is a way to get it via the Worker1 API or its Promiser wrapper. That API is extremely limited in what it can do and it's really not useful for much more than toy applications (as its docs warn up front). (Sidebar: i say that as its author!)
The full-featured approach to using the wasm bits is to import it into your thread and operate it directly. It can run from the main thread or a worker, but OPFS is only available from a worker. That provides full access to the API without any of Worker1's limitations (all side effects of it "remote-controlling" sqlite, running in a separate thread, via postMessage()).
That said: we have a fundamentally similar capability for getting the change count and can probably add this in the same way. i will look into that in the next couple of days.
(3) By Mark Hessling (rexxman) on 2025-02-08 23:48:16 in reply to 2 [link] [source]
Thanks for the quick reply Stephan.
As a casual Javascript developer, I chose the interface that most closely resembled the WebSQL interface which has worked well for another app that doesn't need the parent/child relationship. The app also runs as a Cordova mobile app, so have to support the Cordova sqlite3 plugin in the same code.
Thank you for looking into the possibility of adding this capability.
Cheers, Mark
(4) By Stephan Beal (stephan) on 2025-02-09 02:42:42 in reply to 3 [link] [source]
Thank you for looking into the possibility of adding this capability.
This is now in the trunk, so will be part of 3.50, but is not a bug fix so it won't be part of any as-yet-hypothetical 3.49.1:
If the config object passed to exec has a truthy property named lastInsertRowId then the result object will contain a BigInt-type property of that same name, holding the result of sqlite3_last_insert_rowid(). The caveat is that sqlite3_last_insert_rowid() is called after all of the SQL is run and Worker1 has no idea whether the SQL contained any INSERT statements. If multiple INSERTs are executed in a single call, only the rowid of the last one executed is returned.
The fact that the rowid is a BigInt is a slightly annoying because those works every-so-slightly different than the Number type, but the native type is int64, which translates to a BigInt in JS. If that distinction is significant for your case, it can be converted to type Number with Number(theRowId) (with the caveat that if your JS db somehow has rowids numbering in the bazillions, that may misbehave).
If you are not beholden to npm for your JS-fetching needs, a snapshot build with this feature can be found on the wasm site.
We have time before 3.50 to change this interface, but after that release the interface will be set in stone forever. i.e. if you have suggestions for changes, please make them sooner rather than later.
(6) By Mark Hessling (rexxman) on 2025-02-09 21:12:46 in reply to 4 [link] [source]
Fortunately I don't use npm for this project so I downloaded the snapshot and tested it out.
Works perfectly!
Thank you so much Stephan not only for the enhancement, but the timeliness of it!
(5) By Stephan Beal (stephan) on 2025-02-09 02:47:23 in reply to 2 [link] [source]
i don't know off-hand that there is a way to get it via the Worker1 API or its Promiser wrapper.
BTW: there is actually a way to fetch the last insert ID via that API but it may be slightly awkward or require another round-trip through the worker, depending on how the client-side JS code is structured:
sqlite> create table t(a); insert into t(a) values(1),(2),(3); select last_insert_rowid(); last_insert_rowid() 3
The just-checked-in approach is likely more convenient.