sea_orm/executor/
insert.rs

1use super::ReturningSelector;
2use crate::{
3    ActiveModelTrait, ColumnTrait, ConnectionTrait, DbBackend, EntityTrait, Insert, InsertMany,
4    IntoActiveModel, Iterable, PrimaryKeyToColumn, PrimaryKeyTrait, SelectModel, TryFromU64,
5    TryInsert, error::*,
6};
7use sea_query::{FromValueTuple, Iden, InsertStatement, Query, ReturningClause, ValueTuple};
8use std::marker::PhantomData;
9
10type PrimaryKey<A> = <<A as ActiveModelTrait>::Entity as EntityTrait>::PrimaryKey;
11
12/// Defines a structure to perform INSERT operations in an ActiveModel
13#[derive(Debug)]
14pub struct Inserter<A>
15where
16    A: ActiveModelTrait,
17{
18    primary_key: Option<ValueTuple>,
19    query: InsertStatement,
20    model: PhantomData<A>,
21}
22
23/// The result of an INSERT operation on an ActiveModel
24#[derive(Debug)]
25#[non_exhaustive]
26pub struct InsertResult<A>
27where
28    A: ActiveModelTrait,
29{
30    /// The primary key value of the last inserted row
31    pub last_insert_id: <PrimaryKey<A> as PrimaryKeyTrait>::ValueType,
32}
33
34/// The result of an INSERT many operation for a set of ActiveModels
35#[derive(Debug)]
36#[non_exhaustive]
37pub struct InsertManyResult<A>
38where
39    A: ActiveModelTrait,
40{
41    /// The primary key value of the last inserted row
42    pub last_insert_id: Option<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>,
43}
44
45/// The result of executing a [`crate::TryInsert`].
46///
47/// This enum represents no‑op inserts (e.g. conflict `DO NOTHING`) without treating
48/// them as errors.
49#[derive(Debug)]
50pub enum TryInsertResult<T> {
51    /// There was nothing to insert, so no SQL was executed.
52    ///
53    /// This typically happens when creating a [`crate::TryInsert`] from an empty iterator or None.
54    Empty,
55    /// The statement was executed, but SeaORM could not get the inserted row / insert id.
56    ///
57    /// This is commonly caused by `ON CONFLICT ... DO NOTHING` (Postgres / SQLite) or the MySQL
58    /// polyfill (`ON DUPLICATE KEY UPDATE pk = pk`).
59    ///
60    /// Note that this variant maps from `DbErr::RecordNotInserted`, so it can also represent other
61    /// situations where the backend/driver reports no inserted row (e.g. an empty `RETURNING`
62    /// result set or a "no-op" update in MySQL where `last_insert_id` is reported as `0`). In rare
63    /// cases, this can be a false negative where a row was inserted but the backend did not report
64    /// it.
65    Conflicted,
66    /// Successfully inserted
67    Inserted(T),
68}
69
70impl<A> TryInsertResult<InsertResult<A>>
71where
72    A: ActiveModelTrait,
73{
74    /// Extract the last inserted id.
75    ///
76    /// - [`TryInsertResult::Empty`] => `Ok(None)`
77    /// - [`TryInsertResult::Inserted`] => `Ok(Some(last_insert_id))`
78    /// - [`TryInsertResult::Conflicted`] => `Err(DbErr::RecordNotInserted)`
79    pub fn last_insert_id(
80        self,
81    ) -> Result<Option<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>, DbErr> {
82        match self {
83            Self::Empty => Ok(None),
84            Self::Inserted(v) => Ok(Some(v.last_insert_id)),
85            Self::Conflicted => Err(DbErr::RecordNotInserted),
86        }
87    }
88}
89
90impl<A> TryInsert<A>
91where
92    A: ActiveModelTrait,
93{
94    /// Execute an insert operation
95    pub fn exec<C>(self, db: &C) -> Result<TryInsertResult<InsertResult<A>>, DbErr>
96    where
97        C: ConnectionTrait,
98    {
99        if self.empty {
100            return Ok(TryInsertResult::Empty);
101        }
102        let res = self.insert_struct.exec(db);
103        match res {
104            Ok(res) => Ok(TryInsertResult::Inserted(res)),
105            Err(DbErr::RecordNotInserted) => Ok(TryInsertResult::Conflicted),
106            Err(err) => Err(err),
107        }
108    }
109
110    /// Execute an insert operation without returning (don't use `RETURNING` syntax)
111    /// Number of rows affected is returned
112    pub fn exec_without_returning<C>(self, db: &C) -> Result<TryInsertResult<u64>, DbErr>
113    where
114        C: ConnectionTrait,
115    {
116        if self.empty {
117            return Ok(TryInsertResult::Empty);
118        }
119        let res = self.insert_struct.exec_without_returning(db);
120        match res {
121            Ok(res) => Ok(TryInsertResult::Inserted(res)),
122            Err(DbErr::RecordNotInserted) => Ok(TryInsertResult::Conflicted),
123            Err(err) => Err(err),
124        }
125    }
126
127    /// Execute an insert operation and return the inserted model (use `RETURNING` syntax if supported)
128    pub fn exec_with_returning<C>(
129        self,
130        db: &C,
131    ) -> Result<TryInsertResult<<A::Entity as EntityTrait>::Model>, DbErr>
132    where
133        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
134        C: ConnectionTrait,
135    {
136        if self.empty {
137            return Ok(TryInsertResult::Empty);
138        }
139        let res = self.insert_struct.exec_with_returning(db);
140        match res {
141            Ok(res) => Ok(TryInsertResult::Inserted(res)),
142            Err(DbErr::RecordNotInserted) => Ok(TryInsertResult::Conflicted),
143            Err(err) => Err(err),
144        }
145    }
146
147    /// Execute an insert operation and return primary keys of inserted models
148    pub fn exec_with_returning_keys<C>(
149        self,
150        db: &C,
151    ) -> Result<TryInsertResult<Vec<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>>, DbErr>
152    where
153        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
154        C: ConnectionTrait,
155    {
156        if self.empty {
157            return Ok(TryInsertResult::Empty);
158        }
159
160        let res = self.insert_struct.exec_with_returning_keys(db);
161        match res {
162            Ok(res) => Ok(TryInsertResult::Inserted(res)),
163            Err(DbErr::RecordNotInserted) => Ok(TryInsertResult::Conflicted),
164            Err(err) => Err(err),
165        }
166    }
167
168    /// Execute an insert operation and return all inserted models
169    pub fn exec_with_returning_many<C>(
170        self,
171        db: &C,
172    ) -> Result<TryInsertResult<Vec<<A::Entity as EntityTrait>::Model>>, DbErr>
173    where
174        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
175        C: ConnectionTrait,
176    {
177        if self.empty {
178            return Ok(TryInsertResult::Empty);
179        }
180
181        let res = self.insert_struct.exec_with_returning_many(db);
182        match res {
183            Ok(res) => Ok(TryInsertResult::Inserted(res)),
184            Err(DbErr::RecordNotInserted) => Ok(TryInsertResult::Conflicted),
185            Err(err) => Err(err),
186        }
187    }
188}
189
190impl<A> Insert<A>
191where
192    A: ActiveModelTrait,
193{
194    /// Execute an insert operation
195    pub fn exec<'a, C>(self, db: &'a C) -> Result<InsertResult<A>, DbErr>
196    where
197        C: ConnectionTrait,
198        A: 'a,
199    {
200        // so that self is dropped before entering await
201        let mut query = self.query;
202        if db.support_returning() {
203            query.returning(returning_pk::<A>(db.get_database_backend()));
204        }
205        Inserter::<A>::new(self.primary_key, query).exec(db)
206    }
207
208    /// Execute an insert operation without returning (don't use `RETURNING` syntax)
209    /// Number of rows affected is returned
210    pub fn exec_without_returning<'a, C>(self, db: &'a C) -> Result<u64, DbErr>
211    where
212        C: ConnectionTrait,
213        A: 'a,
214    {
215        Inserter::<A>::new(self.primary_key, self.query).exec_without_returning(db)
216    }
217
218    /// Execute an insert operation and return the inserted model (use `RETURNING` syntax if supported)
219    ///
220    /// + To get back all inserted models, use [`exec_with_returning_many`].
221    /// + To get back all inserted primary keys, use [`exec_with_returning_keys`].
222    pub fn exec_with_returning<'a, C>(
223        self,
224        db: &'a C,
225    ) -> Result<<A::Entity as EntityTrait>::Model, DbErr>
226    where
227        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
228        C: ConnectionTrait,
229        A: 'a,
230    {
231        Inserter::<A>::new(self.primary_key, self.query).exec_with_returning(db)
232    }
233
234    /// Execute an insert operation and return primary keys of inserted models
235    pub fn exec_with_returning_keys<'a, C>(
236        self,
237        db: &'a C,
238    ) -> Result<Vec<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>, DbErr>
239    where
240        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
241        C: ConnectionTrait,
242        A: 'a,
243    {
244        Inserter::<A>::new(self.primary_key, self.query).exec_with_returning_keys(db)
245    }
246
247    /// Execute an insert operation and return all inserted models
248    pub fn exec_with_returning_many<'a, C>(
249        self,
250        db: &'a C,
251    ) -> Result<Vec<<A::Entity as EntityTrait>::Model>, DbErr>
252    where
253        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
254        C: ConnectionTrait,
255        A: 'a,
256    {
257        Inserter::<A>::new(self.primary_key, self.query).exec_with_returning_many(db)
258    }
259}
260
261impl<A> InsertMany<A>
262where
263    A: ActiveModelTrait,
264{
265    /// Execute an insert operation
266    pub fn exec<C>(self, db: &C) -> Result<InsertManyResult<A>, DbErr>
267    where
268        C: ConnectionTrait,
269    {
270        if self.empty {
271            return Ok(InsertManyResult {
272                last_insert_id: None,
273            });
274        }
275        let res = self.into_one().exec(db);
276        match res {
277            Ok(r) => Ok(InsertManyResult {
278                last_insert_id: Some(r.last_insert_id),
279            }),
280            Err(err) => Err(err),
281        }
282    }
283
284    /// Execute an insert operation without returning (don't use `RETURNING` syntax)
285    /// Number of rows affected is returned
286    pub fn exec_without_returning<C>(self, db: &C) -> Result<u64, DbErr>
287    where
288        C: ConnectionTrait,
289    {
290        if self.empty {
291            return Ok(0);
292        }
293        self.into_one().exec_without_returning(db)
294    }
295
296    /// Execute an insert operation and return all inserted models
297    pub fn exec_with_returning<C>(
298        self,
299        db: &C,
300    ) -> Result<Vec<<A::Entity as EntityTrait>::Model>, DbErr>
301    where
302        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
303        C: ConnectionTrait,
304    {
305        if self.empty {
306            return Ok(Vec::new());
307        }
308
309        self.into_one().exec_with_returning_many(db)
310    }
311
312    /// Alias to [`InsertMany::exec_with_returning`].
313    #[deprecated(
314        since = "2.0.0",
315        note = "Please use [`InsertMany::exec_with_returning`]"
316    )]
317    pub fn exec_with_returning_many<C>(
318        self,
319        db: &C,
320    ) -> Result<Vec<<A::Entity as EntityTrait>::Model>, DbErr>
321    where
322        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
323        C: ConnectionTrait,
324    {
325        if self.empty {
326            return Ok(Vec::new());
327        }
328
329        self.into_one().exec_with_returning_many(db)
330    }
331
332    /// Execute an insert operation and return primary keys of inserted models
333    pub fn exec_with_returning_keys<C>(
334        self,
335        db: &C,
336    ) -> Result<Vec<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>, DbErr>
337    where
338        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
339        C: ConnectionTrait,
340    {
341        if self.empty {
342            return Ok(Vec::new());
343        }
344
345        self.into_one().exec_with_returning_keys(db)
346    }
347}
348
349impl<A> Inserter<A>
350where
351    A: ActiveModelTrait,
352{
353    /// Instantiate a new insert operation
354    pub fn new(primary_key: Option<ValueTuple>, query: InsertStatement) -> Self {
355        Self {
356            primary_key,
357            query,
358            model: PhantomData,
359        }
360    }
361
362    /// Execute an insert operation, returning the last inserted id
363    pub fn exec<'a, C>(self, db: &'a C) -> Result<InsertResult<A>, DbErr>
364    where
365        C: ConnectionTrait,
366        A: 'a,
367    {
368        exec_insert(self.primary_key, self.query, db)
369    }
370
371    /// Execute an insert operation
372    pub fn exec_without_returning<'a, C>(self, db: &'a C) -> Result<u64, DbErr>
373    where
374        C: ConnectionTrait,
375        A: 'a,
376    {
377        exec_insert_without_returning(self.query, db)
378    }
379
380    /// Execute an insert operation and return the inserted model (use `RETURNING` syntax if supported)
381    pub fn exec_with_returning<'a, C>(
382        self,
383        db: &'a C,
384    ) -> Result<<A::Entity as EntityTrait>::Model, DbErr>
385    where
386        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
387        C: ConnectionTrait,
388        A: 'a,
389    {
390        exec_insert_with_returning::<A, _>(self.primary_key, self.query, db)
391    }
392
393    /// Execute an insert operation and return primary keys of inserted models
394    pub fn exec_with_returning_keys<'a, C>(
395        self,
396        db: &'a C,
397    ) -> Result<Vec<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>, DbErr>
398    where
399        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
400        C: ConnectionTrait,
401        A: 'a,
402    {
403        exec_insert_with_returning_keys::<A, _>(self.query, db)
404    }
405
406    /// Execute an insert operation and return all inserted models
407    pub fn exec_with_returning_many<'a, C>(
408        self,
409        db: &'a C,
410    ) -> Result<Vec<<A::Entity as EntityTrait>::Model>, DbErr>
411    where
412        <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
413        C: ConnectionTrait,
414        A: 'a,
415    {
416        exec_insert_with_returning_many::<A, _>(self.query, db)
417    }
418}
419
420fn exec_insert<A, C>(
421    primary_key: Option<ValueTuple>,
422    statement: InsertStatement,
423    db: &C,
424) -> Result<InsertResult<A>, DbErr>
425where
426    C: ConnectionTrait,
427    A: ActiveModelTrait,
428{
429    type ValueTypeOf<A> = <PrimaryKey<A> as PrimaryKeyTrait>::ValueType;
430
431    let db_backend = db.get_database_backend();
432
433    let last_insert_id = match (primary_key, db.support_returning()) {
434        (_, true) => {
435            let mut rows = db.query_all(&statement)?;
436            let row = match rows.pop() {
437                Some(row) => row,
438                None => return Err(DbErr::RecordNotInserted),
439            };
440            let cols = PrimaryKey::<A>::iter()
441                .map(|col| col.to_string())
442                .collect::<Vec<_>>();
443            row.try_get_many("", cols.as_ref())
444                .map_err(|_| DbErr::UnpackInsertId)?
445        }
446        (Some(value_tuple), false) => {
447            let res = db.execute(&statement)?;
448            if res.rows_affected() == 0 {
449                return Err(DbErr::RecordNotInserted);
450            }
451            FromValueTuple::from_value_tuple(value_tuple)
452        }
453        (None, false) => {
454            let res = db.execute(&statement)?;
455            if res.rows_affected() == 0 {
456                return Err(DbErr::RecordNotInserted);
457            }
458            let last_insert_id = res.last_insert_id();
459            // For MySQL, the affected-rows number:
460            //   - The affected-rows value per row is `1` if the row is inserted as a new row,
461            //   - `2` if an existing row is updated,
462            //   - and `0` if an existing row is set to its current values.
463            // Reference: https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html
464            if db_backend == DbBackend::MySql && last_insert_id == 0 {
465                return Err(DbErr::RecordNotInserted);
466            }
467            ValueTypeOf::<A>::try_from_u64(last_insert_id).map_err(|_| DbErr::UnpackInsertId)?
468        }
469    };
470
471    Ok(InsertResult { last_insert_id })
472}
473
474fn exec_insert_without_returning<C>(insert_statement: InsertStatement, db: &C) -> Result<u64, DbErr>
475where
476    C: ConnectionTrait,
477{
478    let exec_result = db.execute(&insert_statement)?;
479    Ok(exec_result.rows_affected())
480}
481
482fn exec_insert_with_returning<A, C>(
483    primary_key: Option<ValueTuple>,
484    mut insert_statement: InsertStatement,
485    db: &C,
486) -> Result<<A::Entity as EntityTrait>::Model, DbErr>
487where
488    <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
489    C: ConnectionTrait,
490    A: ActiveModelTrait,
491{
492    let db_backend = db.get_database_backend();
493    let found = match db.support_returning() {
494        true => {
495            let returning = Query::returning().exprs(
496                <A::Entity as EntityTrait>::Column::iter()
497                    .map(|c| c.select_as(c.into_returning_expr(db_backend))),
498            );
499            insert_statement.returning(returning);
500            ReturningSelector::<SelectModel<<A::Entity as EntityTrait>::Model>, _>::from_query(
501                insert_statement,
502            )
503            .one(db)?
504        }
505        false => {
506            let insert_res = exec_insert::<A, _>(primary_key, insert_statement, db)?;
507            <A::Entity as EntityTrait>::find_by_id(insert_res.last_insert_id).one(db)?
508        }
509    };
510    match found {
511        Some(model) => Ok(model),
512        None => Err(DbErr::RecordNotFound(
513            "Failed to find inserted item".to_owned(),
514        )),
515    }
516}
517
518fn exec_insert_with_returning_keys<A, C>(
519    mut insert_statement: InsertStatement,
520    db: &C,
521) -> Result<Vec<<PrimaryKey<A> as PrimaryKeyTrait>::ValueType>, DbErr>
522where
523    <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
524    C: ConnectionTrait,
525    A: ActiveModelTrait,
526{
527    let db_backend = db.get_database_backend();
528    match db.support_returning() {
529        true => {
530            insert_statement.returning(returning_pk::<A>(db_backend));
531            let rows = db.query_all(&insert_statement)?;
532            let cols = PrimaryKey::<A>::iter()
533                .map(|col| col.to_string())
534                .collect::<Vec<_>>();
535            let mut keys = Vec::new();
536            for row in rows {
537                keys.push(
538                    row.try_get_many("", cols.as_ref())
539                        .map_err(|_| DbErr::UnpackInsertId)?,
540                );
541            }
542            Ok(keys)
543        }
544        false => Err(DbErr::BackendNotSupported {
545            db: db_backend.as_str(),
546            ctx: "INSERT RETURNING",
547        }),
548    }
549}
550
551fn exec_insert_with_returning_many<A, C>(
552    mut insert_statement: InsertStatement,
553    db: &C,
554) -> Result<Vec<<A::Entity as EntityTrait>::Model>, DbErr>
555where
556    <A::Entity as EntityTrait>::Model: IntoActiveModel<A>,
557    C: ConnectionTrait,
558    A: ActiveModelTrait,
559{
560    let db_backend = db.get_database_backend();
561    match db.support_returning() {
562        true => {
563            let returning = Query::returning().exprs(
564                <A::Entity as EntityTrait>::Column::iter()
565                    .map(|c| c.select_as(c.into_returning_expr(db_backend))),
566            );
567            insert_statement.returning(returning);
568            ReturningSelector::<SelectModel<<A::Entity as EntityTrait>::Model>, _>::from_query(
569                insert_statement,
570            )
571            .all(db)
572        }
573        false => Err(DbErr::BackendNotSupported {
574            db: db_backend.as_str(),
575            ctx: "INSERT RETURNING",
576        }),
577    }
578}
579
580fn returning_pk<A>(db_backend: DbBackend) -> ReturningClause
581where
582    A: ActiveModelTrait,
583{
584    Query::returning().exprs(<A::Entity as EntityTrait>::PrimaryKey::iter().map(|c| {
585        c.into_column()
586            .select_as(c.into_column().into_returning_expr(db_backend))
587    }))
588}
sea_orm/executor/insert.rs

sea_orm/executor/
insert.rs
