I imagine we can have a bulk insert API that bypasses ActiveModel.

Something like:

MyEntity::bulk_insert([
    (my_entity::Column::Id, &[1,2,3,4]),
    (my_entity::Column::Name, &["a","b","c","d"]),
])

That'd build:

INSERT INTO my_entity (id, name) VALUES UNNEST ARRAY($1, $2);
# where $1 is i32[1,2,3,4], $2 is text["a","b","c","d"]

If you're OK with bypassing ActiveModel and spelling out the columns like that, then you don't need any new sea_orm APIs.

This can be achieved with raw SQL today:

let values = [
    Value::from(vec![1, 2, 3, 4]),
    Value::from(Vec::from(["a", "b", "c", "d"].map(|s| s.to_string()))),
];
let statement = Statement::from_sql_and_values(
    DatabaseBackend::Postgres,
    "INSERT INTO my_entity (id, name) VALUES UNNEST ARRAY($1, $2);",
    values,
);
tx.execute(statement).await?;

And if you need a dynamic query or want to use Rust identifiers, you could render the SQL string from a sea_query::InsertStatement instead of hardcoding it:

let values = [
    Value::from(vec![1, 2, 3, 4]),
    Value::from(Vec::from(["a", "b", "c", "d"].map(|s| s.to_string()))),
];
let query = Query::insert()
    .into_table(MyEntity::Table)
    .columns([MyEntity::Id, MyEntity::Name])
    .values_panic([Expr::cust_with_values("UNNEST ARRAY($1, $2)", values).into_simple_expr()])
    .to_owned();
let (sql, Values(values)) = query.build_any(&PostgresQueryBuilder);
let statement = Statement::from_sql_and_values(DatabaseBackend::Postgres, sql, values);
tx.execute(statement).await?;

This code compiles, but I haven't actually run it to check whether this "cust_with_values -> values_panic -> build_any" pipeline is smart enough to preserve and return the same values that we've put in.

This example hardcodes the number of columns in the raw "UNNEST ARRAY($1, $2)", but it can probably be changed to something dymanic like

Func::cust("UNNEST").arg(Func::cust("ARRAY").args(values.map(|v| v.into())))

I agree that it would be better to have an ergonomic API that accepts ActiveModels and does all of this under the hood.

I think, we don't need a new API for that either. As described in #710, the existing insert_many should do this by default to avoid suprising parameter limit errors (which are usually not hit during development and are discovered in production)

Uh oh!

postgres: bulk insert API with UNNEST and ARRAYs #1922

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions