Batch insert to provide insert IDs

[gsouf]

Hi,

I was playing with the batch insert when I figured that it won't provide a way to get inserted IDs at the method db.batch is a simple Future<void>. I found that sqlite provides a way to return inserted IDs via the clause Returning:

INSERT INTO "Foo" ("Name") VALUES
('A'),
('B'),
('C')
RETURNING "Id";

Would you like to see this supported in Drift?

I'm happy to work on it and send PR

[simolus3]

Would you like to see this supported in Drift?

👍 I think this would be a good addition, and we get the required information on all backends (including drift_sqflite where the batch API is already returning data).

This is likely a bit complicated to implement with backwards compatibility - we can't change the return type of DatabaseDelegate/QueryExecutor.runBatched without making it a breaking change. However, we could introduce a BatchResult class or something and have the implementations return a Future<BatchResult> since it's assignable to Future<void>. The batch API would check whether the implementation supports this feature and reports the returned rows in that case.

I'm happy to work on it and send PR

That would be great, thanks! Let me know if you run into any more issues along the way

[gsouf]

@simolus3 I started looking at it just now, but I'm getting a bit confused. I think the batch method of drift is not doing what I expected exactly.

So in a batch operation in drift you can mix various operations, such as inserting, updating, deleting, etc.. it's not just for insertAll as I initially thought.

So what I'm trying to achieve is just to insert many records at once and grab their generated IDs.

Any thought about that? Is the batch method the right place to go or is there something more appropriate for this scenario ?

Thanks!

[simolus3]

Is the batch method the right place to go or is there something more appropriate for this scenario ?

Ideally, batches should be able to report results for all of these statements (and then there could be an insertReturning on batches as well). So yes, I think batches shuld be the right tool when running lots of statements with the same SQL but different parameters.

If the key you're interested in is auto-incrementing, you could also start a transaction and select the highest id before and after the batch (all inside of the transaction). Since they are sequential, this would give you ids in the middle as well.