How to bulk inserts returning queries with Diesel, Rocket, and PostgreSQL

Question

I got an HTTP API server made with rocket that handles talking to a PostgreSQL database.

I got an endpoint that receive a list of posts to upsert, who each have their own list of comments to upsert.

I'd like to know how to bulk insert the content of the original JSON into the database

My current database schema is as follows:

create table post 
(
    id   integer not null,
    name text    not null
);

create table comment
(
    id      integer,
    post_id integer,
    content TEXT
);

Here's the struct representation of the data to insert:

#[derive(Serialize, Deserialize, Debug, Clone)]
struct PostJSON {
    name: String,
    comments: Vec<CommentsJSON>,
}

#[derive(Serialize, Deserialize, Debug, Clone)]
struct CommentsJSON {
    content: String
}

And here's some pseudo rust code of what I currently have:

#[database("example_db")]
pub struct ExampleDB(diesel::PgConnection);

fn insert_posts(posts_to_insert: Vec<PostJSON>, db: ExampleDB) {
    db.run(|c| {
        for post_to_insert in posts_to_insert {
            let insertable_post: InsertablePost = post_to_insert.clone().to_insertable();
            let inserted_post: PostModel = insertable_post.upsert_and_return(&mut db).await?;

            for comment_to_insert in post_to_insert.comments {
                let insertable_comment: InsertableComment =
                    comment_to_insert.clone().to_insertable();
                let inserted_comment: CommentModel = insertable_comment
                    .upsert_and_return(&mut db, insertable_post.id)
                    .await?;
            }
        }
    })
}

This is really inefficient as all the inserts are completely synchronous. How can I make it so that it doesn't require as much back and forth from the database? Or at least make all the request be sent at once?

Answer 1

diesel::insert_into(table).values(values) accepts a Vec as argument and performs a batch insert based on the given set of rows. For details see the "Batch Insert" section of the "All about inserts" guide.