Batched inserts

[mclrc]

Hello! Loving the library, thank you so much!

The only thing I'm missing for my use case is batched inserts. I need to ingest millions of rows into a DB as quickly as possible, and non-batched inserts would be an issue. I know sqlx supports it, so I imagine it shouldn't be that difficult to implement. I could take a crack at it myself, if you're interested - although I am not very experienced with sqlx.

What do you think? Personally, it would mean I would never have to look at other ORMs again :)

Cheers, have a great day!

[RimaitosLab]

Hi,

I would also appreciate a feature like this.
For a current project I need to import a lot of data to sql databases for further processing.
As I didn't want to write long queries (hundreds of columns) and I already had the struct definition I though that ormx was a good fit. Sady I only noticed after the implementation that no batch insert is available and it takes ages to import the data, that I thing it is faster to replace ormx with sqlx than to wait for all inserts.

[NyxCode]

Hey!

I have not yet found a nice way to reliably generate code for it, so I have not.
If what you end up with looks general enough to be includes in ormx, i'd definetely appreciate a PR.

that I thing it is faster to replace ormx with sqlx than to wait for all inserts.

that's the beauty of this setup, i think. You can still use ormx like before for everything else, and drop down to using sqlx for that specific query.

[NyxCode]

As far as I can tell, the only nice way of doing batch inserts is on Postgres, by binding arrays and using UNNEST (docs). Some testing would need to be done to see if that works as-is for custom types, and if we can emit the correct SQL at all.

For example, it seems like the data needs to be "flat", every field in its own array. Not sure how to do that in the ormx API without being disgusting to use. We could accept a &[Table] and copy the fields over into temporary vectors, but that doesn't seem great either.

[belovaf]

Other languages support batching queries, for example:
go: https://github.com/jackc/pgx/blob/master/batch_test.go#L34C3-L34C8
java: https://gist.github.com/madan712/4369233#file-batchprocess-java-L62