abstraction for obtaining last insert id

[miguelgrinberg]

It is unfortunate that different database engines implement the "get_last_insert_id()" function in very different ways. The only engine that currently supports this with the query generation API is Postgresql, via the returning() clause. For the others it is necessary to send a hardcoded SQL query, because it seems these cannot be generated via the select() function.

Would you consider it within the scope of your project to create an engine agnostic function to retrieve this id?

I could come up with two possible implementations:

1. Using the returning() clause. Postgresql already has it, sqlite3 and MySQL will need this clause simulated with a SELECT query.
2. Return the last inserted id in the callback. Not sure how this would work when inserting multiple rows at once, maybe it only works when inserting one.

Let me know what you think!

[grncdr]

Hm, yeah that is a tough one. Especially when you consider that (unless you use transactions), there are no guarantees that two queries will even use the same connection (they are executed against an any-db connection pool).

I've had a prototype for an extension system that allows engines to patch (and unpatch) query objects at runtime, this might be an appropriate use case for it. Would you be interested in working on a pull request that adds simulated 'returning' support to the MySQL and SQLite3 drivers?

[miguelgrinberg]

Hmm. Didn't consider the pooling of connections, that certainly complicates things.

I was mostly trying to figure out if you would be interested in this feature, so yes, I'm happy to look into the simulated returning() myself and submit a pull request.

Thanks!

[grncdr]

I've just pushed up the engine-extensions branch with a (non-working) attempt at adding this functionality. Feel free to completely throw out my implementation, but it might serve as inspiration.

[miguelgrinberg]

Thanks, I'll play with this code and try to complete it.

[miguelgrinberg]

Plumbing the last ID for sqlite3 is going to be a challenge. The only node-sqlite3 function that returns the last insert id is run(). Your any-db module uses each() which returns the rows but not the last insert id. run() does not return rows. So it seems any-db will need to know what kind of query it was given to use the proper query function.

Do you have any situations where any-db needs to look at the query string to know how to execute it?

[grncdr]

Do you have any situations where any-db needs to look at the query string to know how to execute it?

Currently there isn't, but there's no hard rule against it, the only thing any-db shouldn't do is modify the query text/params being sent to the backend. I'd definitely accept a pull request against any-db that makes use of .run for insert/update and delete queries and includes the relevant information in the result.

[miguelgrinberg]

If you have a moment please take a look at this commit on my forked any-db and let me know if something like this works for you. This would also be fairly easy to add to MySQL, though it's going to be harder for postgres.

[grncdr]

I commented on the commit, looks good!

though it's going to be harder for postgres.

I don't think any-db needs to add a workaround for Postgres doesn't include generated ids in normal query results, as RETURNING works perfectly well there.

[miguelgrinberg]

Great, thanks for looking at it. I'll implement MySQL as well, since it is equally easy.

Regarding Postgres, I agree it isn't necessary and will probably not worry about it, but it would have been nice to have all drivers return the same response, because then you don't need driver specific extensions in this project.

[grncdr]

Ah, that's a noble goal, but I want to support driver specific extensions in this project anyways; there's a number of SQL extensions that would be useful to support in the query building API. (The extensions system actually started as a way to add support for Postgres' common table expressions).