search

crystal-lang / crystal-sqlite3

SQLite3 bindings for Crystal
MIT License
139 stars 30 forks source link

atomic last_insert_rowid #69

Closed ghost closed 3 years ago

ghost commented 3 years ago

last_insert_rowid() can be used to get the autoincrement-generated ID of a row: https://stackoverflow.com/a/30715960/12211329

But this has to be atomic with the insert statement to be useful, and I'm seeing that if I execute a combined statement like db.scalar("INSERT INTO table (col1, col2) VALUES ('blah', 'blah'); SELECT last_insert_rowid()"), I get a DB::NoResultsError. It works if I execute the insert with db.exec and then the select with db.scalar, but I don't believe that's atomic.

bcardiff commented 3 years ago

You can use db.exec(query) that will return an ExecResult with the last row id directly thanks to https://www.sqlite.org/c3ref/last_insert_rowid.html

That C function work per connection. If the same applies to the sql version you should also be able to do it from sql if you checkout a connection (or use a transaction) instead of db directly which has a pool of connections.

ghost commented 3 years ago

Thanks, I believe that solves my problem.