Easier batching of bulk inserts

[SebastienGllmt]

Summary

Postgres only allows a maximum of u16::MAX input parameters. If you exceed this amount, you will get an error in sqlx. Getting around this limitation while using entities is kind of ugly

Motivation

sea-orm provides a function on entities called insert_many such as the the following example

let apple = cake::ActiveModel {
    name: Set("Apple Pie".to_owned()),
    height: Set(0.5),
    ..Default::default()
};

let insert_result = cake::Entity::insert_many(vec![apple])
     .exec(&db)
     .await?;

This insertion requires two SQL parameters because insert_many uses the VALUES syntax

INSERT INTO `cake` (`name`) VALUES (?), (?)

This works fine up until you hit the u16 limit of parameters. The naive workaround for the Postgres limit would be to do something like this

let inserts = vec![apple];
for chunk in inserts.chunks(u16::MAX as usize) {
    cake::Entity::insert_many(chunk.to_vec()).exec(&db).await?;
}

However, this won't work because we have to divide by the number of columns!

That means the proper solution (as far as I can tell) ends up being

use entity::sea_orm::Iterable;

for chunk in transactions
        .chunks((u16::MAX / <cake::Entity as EntityTrait>::Column::iter().count() as u16) as usize)
    {
    cake::Entity::insert_many(chunk.to_vec()).exec(&db).await?;
}

This is already kind of ugly for a one-time patch in your codebase, but having this kind of chunk logic all over the place makes things even worse

It would be nice if insert_many either handled this for you or if there was some other utility function for this.

[billy1624]

Hey @SebastienGllmt, thanks for the suggestions! I think we can have a insert_many_chunk method in EntityTrait which takes an iterator of ActiveModel and the chunk size.

Basically, it will perform... for you

for chunk in inserts.chunks(chunk_size) {
    Self::insert_many(chunk.to_vec()).exec(&db).await?;
}

Regarding parameters limit with number of column take into account, it's db dependent and it should be provided by the user. User can substitute it in the batch size just like what you have done.

PR are welcomed! :P