Support returning clause to avoid database hits

[marlon-sousa]

Hello,

I took a look at discussions and haven't found nothing, so I am opening this issue to discuss something important.

We need to support returning clauses in updates and insert clauses.

Rationale

Sometimes, we need to perform batch operations, by means of multiple inserts or updates which affect more than one record and, at the same time, have the newly inserted / the updated records back in the same database operation.

This is needed, for example, when you need to insert records and have their auto incremented generated ids back to store somewhere for latter use, or when you need to update one or more records and, at the same time, get back columns you didn't have when you performed the update operation.

The way sql handles this is by using a returning clause on the query, making the database, in one operation, perform inserts / updates and also returning information you need instead of forcing you to make a latter select.

Proposal

We propose an extension to the current sea-orm api.

update_many

The UpdateMany struct should have another method, called returning.

Kind of code below

pub fn returning<T>(mut self, record_info: RecordInfo) -> Self

RecordInfo should be an enum like

pub enum RecordInfo {
  Columns(Vec<Entity::Columns>,
  FullRecord,
  PrimaryKey,
}

The returning function would generate a returning sql clause, as follows:

let returning_sql = match RecordInfo {
  Columns(c) => c.map(| col | col.to_string()).join(", "),
FullRecord => "*".into(),
  PrimaryKey: // build list of columns making part of the primary key
}

The insert_many would go the very same path.

The ExecResult would need to ave a vector of ActiveModels with the appropriate columns set for each returned record.

What do you think?

I could try to implement that, I would need some mentoring but I am now more focused in stablishing the api, latter we can think in the implementation.

[billy1624]

Sounds good to me! But note that only Postgres support returning syntax. We have to take account of that.

[marlon-sousa]

To deal with that we have some options:

1. Enable the returning only when postgres is being used with features.
2. For non postgresql databases, investigate a way of producing the same results either by using transactions where we can update and then select / insert and then select or issue more than one connection if we don't want to use transactions.

I would first implement strategy one and then with more time implement strategy 2.

Why?

Because postgres users like me are likely to need it soon and we can give the bonus to non postgres users latter.

What do you think?

[nicoulaj]

related: #148

[billy1624]

Reasonable!! @marlon-sousa

[billy1624]

Just to summarise the support of RETURNING syntax across various databases...

Postgres

• All versions

MySQL

• Not supported

MariaDB

• Supported since version 10.5.0
• Have INSERT...RETURNING
• Have REPLACE...RETURNING but doesn't have UPDATE...RETURNING

SQLite

• Supported since version 3.35.0 (2021-03-12)
• RETURNING