sea_orm/query/
insert.rs

1use crate::{
2    ActiveModelTrait, ActiveValue, ColumnTrait, EntityName, EntityTrait, IntoActiveModel, Iterable,
3    PrimaryKeyTrait, QueryTrait,
4};
5use core::marker::PhantomData;
6use sea_query::{Expr, InsertStatement, Keyword, OnConflict, SimpleExpr, Value, ValueTuple};
7
8/// Performs INSERT operations on a ActiveModel
9#[derive(Debug)]
10pub struct Insert<A>
11where
12    A: ActiveModelTrait,
13{
14    pub(crate) query: InsertStatement,
15    pub(crate) primary_key: Option<ValueTuple>,
16    pub(crate) model: PhantomData<A>,
17}
18
19/// Performs INSERT operations on many ActiveModels
20#[derive(Debug)]
21pub struct InsertMany<A>
22where
23    A: ActiveModelTrait,
24{
25    pub(crate) query: InsertStatement,
26    pub(crate) primary_key: Option<ValueTuple>,
27    pub(crate) empty: bool,
28    pub(crate) model: PhantomData<A>,
29}
30
31/// Wrapper of [`Insert`] / [`InsertMany`], treats "no row inserted/id returned" as a normal outcome.
32///
33/// Its `exec*` methods return [`crate::TryInsertResult`].
34/// Mapping empty input to [`crate::TryInsertResult::Empty`] (no SQL executed) and
35/// `DbErr::RecordNotInserted` to [`crate::TryInsertResult::Conflicted`].
36///
37/// Useful for idempotent inserts such as `ON CONFLICT ... DO NOTHING` (Postgres / SQLite) or the
38/// MySQL polyfill (`ON DUPLICATE KEY UPDATE pk = pk`).
39#[derive(Debug)]
40pub struct TryInsert<A>
41where
42    A: ActiveModelTrait,
43{
44    pub(crate) insert_struct: Insert<A>,
45    pub(crate) empty: bool,
46}
47
48impl<A> Insert<A>
49where
50    A: ActiveModelTrait,
51{
52    /// Insert one Model or ActiveModel
53    ///
54    /// Model
55    /// ```
56    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
57    ///
58    /// assert_eq!(
59    ///     Insert::one(cake::Model {
60    ///         id: 1,
61    ///         name: "Apple Pie".to_owned(),
62    ///     })
63    ///     .build(DbBackend::Postgres)
64    ///     .to_string(),
65    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
66    /// );
67    /// ```
68    /// ActiveModel
69    /// ```
70    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
71    ///
72    /// assert_eq!(
73    ///     Insert::one(cake::ActiveModel {
74    ///         id: NotSet,
75    ///         name: Set("Apple Pie".to_owned()),
76    ///     })
77    ///     .build(DbBackend::Postgres)
78    ///     .to_string(),
79    ///     r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie')"#,
80    /// );
81    /// ```
82    pub fn one<M>(m: M) -> Self
83    where
84        M: IntoActiveModel<A>,
85    {
86        let mut query = InsertStatement::new();
87        query
88            .into_table(A::Entity::default().table_ref())
89            .or_default_values();
90
91        let mut am: A = m.into_active_model();
92        let primary_key =
93            if !<<A::Entity as EntityTrait>::PrimaryKey as PrimaryKeyTrait>::auto_increment() {
94                am.get_primary_key_value()
95            } else {
96                None
97            };
98        let mut columns = Vec::new();
99        let mut values = Vec::new();
100
101        for col in <A::Entity as EntityTrait>::Column::iter() {
102            let av = am.take(col);
103
104            match av {
105                ActiveValue::Set(value) | ActiveValue::Unchanged(value) => {
106                    columns.push(col);
107                    values.push(col.save_as(Expr::val(value)));
108                }
109                ActiveValue::NotSet => {}
110            }
111        }
112
113        query.columns(columns);
114        query.values_panic(values);
115
116        Self {
117            query,
118            primary_key,
119            model: PhantomData,
120        }
121    }
122
123    /// Insert many Model or ActiveModel.
124    /// Alias to [`InsertMany::many`].
125    ///
126    /// ```
127    /// use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::cake};
128    ///
129    /// assert_eq!(
130    ///     Insert::many([
131    ///         cake::Model {
132    ///             id: 1,
133    ///             name: "Apple Pie".to_owned(),
134    ///         },
135    ///         cake::Model {
136    ///             id: 2,
137    ///             name: "Orange Scone".to_owned(),
138    ///         }
139    ///     ])
140    ///     .build(DbBackend::Postgres)
141    ///     .to_string(),
142    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie'), (2, 'Orange Scone')"#,
143    /// );
144    /// ```
145    pub fn many<M, I>(models: I) -> InsertMany<A>
146    where
147        M: IntoActiveModel<A>,
148        I: IntoIterator<Item = M>,
149    {
150        InsertMany::many(models)
151    }
152
153    /// Set ON CONFLICT logic
154    ///
155    /// on conflict do nothing
156    /// ```
157    /// use sea_orm::{DbBackend, entity::*, query::*, sea_query::OnConflict, tests_cfg::cake};
158    ///
159    /// let orange = cake::ActiveModel {
160    ///     id: ActiveValue::set(2),
161    ///     name: ActiveValue::set("Orange".to_owned()),
162    /// };
163    /// assert_eq!(
164    ///     cake::Entity::insert(orange)
165    ///         .on_conflict(
166    ///             OnConflict::column(cake::Column::Name)
167    ///                 .do_nothing()
168    ///                 .to_owned()
169    ///         )
170    ///         .build(DbBackend::Postgres)
171    ///         .to_string(),
172    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
173    /// );
174    /// ```
175    ///
176    /// on conflict do update (upsert)
177    /// ```
178    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
179    ///
180    /// let orange = cake::ActiveModel {
181    ///     id: ActiveValue::set(2),
182    ///     name: ActiveValue::set("Orange".to_owned()),
183    /// };
184    /// let query = cake::Entity::insert(orange)
185    ///     .on_conflict(
186    ///         OnConflict::column(cake::Column::Name)
187    ///             .update_column(cake::Column::Name)
188    ///             .to_owned()
189    ///     );
190    /// assert_eq!(
191    ///     query
192    ///         .build(DbBackend::MySql)
193    ///         .to_string(),
194    ///     "INSERT INTO `cake` (`id`, `name`) VALUES (2, 'Orange') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`)"
195    /// );
196    /// assert_eq!(
197    ///     query
198    ///         .build(DbBackend::Postgres)
199    ///         .to_string(),
200    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
201    /// );
202    /// assert_eq!(
203    ///     query
204    ///         .build(DbBackend::Sqlite)
205    ///         .to_string(),
206    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
207    /// );
208    /// ```
209    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Self {
210        self.query.on_conflict(on_conflict);
211        self
212    }
213
214    /// Set ON CONFLICT do nothing, but with MySQL specific polyfill.
215    pub fn on_conflict_do_nothing_on<I>(mut self, columns: I) -> TryInsert<A>
216    where
217        I: IntoIterator<Item = <A::Entity as EntityTrait>::Column>,
218    {
219        let primary_keys = <A::Entity as EntityTrait>::PrimaryKey::iter();
220        let mut on_conflict = OnConflict::columns(columns);
221        on_conflict.do_nothing_on(primary_keys);
222        self.query.on_conflict(on_conflict);
223        TryInsert::from_one(self)
224    }
225
226    /// Allow insert statement to return without error if nothing's been inserted.
227    #[deprecated(
228        since = "2.0.0",
229        note = "Please use [`TryInsert::one`] or `on_conflict_do_nothing*` methods that return [`TryInsert`], or [`Insert::try_insert`]."
230    )]
231    pub fn do_nothing(self) -> TryInsert<A>
232    where
233        A: ActiveModelTrait,
234    {
235        TryInsert::from_one(self)
236    }
237
238    /// Convert self into a `TryInsert`. It is just a wrapper for converting `DbErr::RecordNotInserted` -> `TryInsertResult::Conflicted`.
239    pub fn try_insert(self) -> TryInsert<A>
240    where
241        A: ActiveModelTrait,
242    {
243        TryInsert::from_one(self)
244    }
245
246    /// Alias to [`Insert::do_nothing`].
247    #[deprecated(
248        since = "2.0.0",
249        note = "Please use [`TryInsert::one`] or `on_conflict_do_nothing*` methods that return [`TryInsert`]"
250    )]
251    pub fn on_empty_do_nothing(self) -> TryInsert<A>
252    where
253        A: ActiveModelTrait,
254    {
255        TryInsert::from_one(self)
256    }
257
258    /// Set ON CONFLICT on primary key do nothing, but with MySQL specific polyfill.
259    ///
260    /// ```
261    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
262    ///
263    /// let orange = cake::ActiveModel {
264    ///     id: ActiveValue::set(2),
265    ///     name: ActiveValue::set("Orange".to_owned()),
266    /// };
267    ///
268    /// assert_eq!(
269    ///     cake::Entity::insert(orange.clone())
270    ///         .on_conflict_do_nothing()
271    ///         .build(DbBackend::MySql)
272    ///         .to_string(),
273    ///     r#"INSERT INTO `cake` (`id`, `name`) VALUES (2, 'Orange') ON DUPLICATE KEY UPDATE `id` = `id`"#,
274    /// );
275    /// assert_eq!(
276    ///     cake::Entity::insert(orange.clone())
277    ///         .on_conflict_do_nothing()
278    ///         .build(DbBackend::Postgres)
279    ///         .to_string(),
280    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("id") DO NOTHING"#,
281    /// );
282    /// assert_eq!(
283    ///     cake::Entity::insert(orange)
284    ///         .on_conflict_do_nothing()
285    ///         .build(DbBackend::Sqlite)
286    ///         .to_string(),
287    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("id") DO NOTHING"#,
288    /// );
289    /// ```
290    pub fn on_conflict_do_nothing(mut self) -> TryInsert<A>
291    where
292        A: ActiveModelTrait,
293    {
294        self.query.on_conflict(on_conflict_primary_key::<A>());
295
296        TryInsert::from_one(self)
297    }
298}
299
300impl<A> InsertMany<A>
301where
302    A: ActiveModelTrait,
303{
304    /// Insert many Model or ActiveModel
305    pub fn many<M, I>(models: I) -> Self
306    where
307        M: IntoActiveModel<A>,
308        I: IntoIterator<Item = M>,
309    {
310        let mut query = InsertStatement::new();
311        query.into_table(A::Entity::default().table_ref());
312
313        let mut columns: Vec<_> = <A::Entity as EntityTrait>::Column::iter()
314            .map(|_| None)
315            .collect();
316        let mut null_value: Vec<Option<Value>> = std::iter::repeat_n(None, columns.len()).collect();
317        let mut all_values: Vec<Vec<SimpleExpr>> = Vec::new();
318        let mut primary_key = None;
319
320        for model in models.into_iter() {
321            let mut am: A = model.into_active_model();
322            primary_key =
323                if !<<A::Entity as EntityTrait>::PrimaryKey as PrimaryKeyTrait>::auto_increment() {
324                    am.get_primary_key_value()
325                } else {
326                    None
327                };
328            let mut values = Vec::with_capacity(columns.len());
329            for (idx, col) in <A::Entity as EntityTrait>::Column::iter().enumerate() {
330                let av = am.take(col);
331                match av {
332                    ActiveValue::Set(value) | ActiveValue::Unchanged(value) => {
333                        columns[idx] = Some(col); // mark the column as used
334                        null_value[idx] = Some(value.as_null()); // store the null value with the correct type
335                        values.push(col.save_as(Expr::val(value))); // same as add() above
336                    }
337                    ActiveValue::NotSet => {
338                        values.push(SimpleExpr::Keyword(Keyword::Null)); // indicate a missing value
339                    }
340                }
341            }
342            all_values.push(values);
343        }
344
345        let empty = all_values.is_empty();
346
347        if !all_values.is_empty() {
348            // filter only used column
349            query.columns(columns.iter().cloned().flatten());
350        }
351
352        for values in all_values {
353            // since we've aligned the column set, this never panics
354            query.values_panic(values.into_iter().enumerate().filter_map(|(i, v)| {
355                if columns[i].is_some() {
356                    // only if the column is used
357                    if !matches!(v, SimpleExpr::Keyword(Keyword::Null)) {
358                        // use the value expression
359                        Some(v)
360                    } else {
361                        // use null as standin, which must be Some
362                        null_value[i].clone().map(SimpleExpr::Value)
363                    }
364                } else {
365                    None
366                }
367            }));
368        }
369
370        Self {
371            query,
372            primary_key,
373            empty,
374            model: PhantomData,
375        }
376    }
377
378    /// Set ON CONFLICT logic
379    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Self {
380        self.query.on_conflict(on_conflict);
381        self
382    }
383
384    /// Set ON CONFLICT do nothing, but with MySQL specific polyfill.
385    /// ```
386    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
387    /// let orange = cake::ActiveModel {
388    ///     id: ActiveValue::set(2),
389    ///     name: ActiveValue::set("Orange".to_owned()),
390    /// };
391    /// assert_eq!(
392    ///     cake::Entity::insert(orange.clone())
393    ///         .on_conflict_do_nothing_on([cake::Column::Name])
394    ///         .build(DbBackend::Postgres)
395    ///         .to_string(),
396    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
397    /// );
398    /// assert_eq!(
399    ///     cake::Entity::insert(orange)
400    ///         .on_conflict_do_nothing_on([cake::Column::Name])
401    ///         .build(DbBackend::MySql)
402    ///         .to_string(),
403    ///     r#"INSERT INTO `cake` (`id`, `name`) VALUES (2, 'Orange') ON DUPLICATE KEY UPDATE `id` = `id`"#,
404    /// );
405    /// ```
406    pub fn on_conflict_do_nothing_on<I>(mut self, columns: I) -> TryInsert<A>
407    where
408        I: IntoIterator<Item = <A::Entity as EntityTrait>::Column>,
409    {
410        let primary_keys = <A::Entity as EntityTrait>::PrimaryKey::iter();
411        let mut on_conflict = OnConflict::columns(columns);
412        on_conflict.do_nothing_on(primary_keys);
413        self.query.on_conflict(on_conflict);
414        TryInsert::from_many(self)
415    }
416
417    /// Allow insert statement to return without error if nothing's been inserted.
418    #[deprecated(
419        since = "2.0.0",
420        note = "Please use [`TryInsert::many`] or `on_conflict_do_nothing*` methods that return [`TryInsert`], or [`InsertMany::try_insert`]"
421    )]
422    pub fn do_nothing(self) -> TryInsert<A>
423    where
424        A: ActiveModelTrait,
425    {
426        TryInsert::from_many(self)
427    }
428
429    /// Convert self into a `TryInsert`. It is just a wrapper for converting `DbErr::RecordNotInserted` -> `TryInsertResult::Conflicted`.
430    pub fn try_insert(self) -> TryInsert<A>
431    where
432        A: ActiveModelTrait,
433    {
434        TryInsert::from_many(self)
435    }
436
437    /// Alias to [`InsertMany::do_nothing`].
438    #[deprecated(
439        since = "2.0.0",
440        note = "Empty input is already handled by [`InsertMany::exec`] (no SQL executed). For conflict handling, use [`InsertMany::on_conflict_do_nothing`] or [`InsertMany::on_conflict_do_nothing_on`]."
441    )]
442    pub fn on_empty_do_nothing(self) -> TryInsert<A>
443    where
444        A: ActiveModelTrait,
445    {
446        TryInsert::from_many(self)
447    }
448
449    /// Set ON CONFLICT on primary key do nothing, but with MySQL specific polyfill.
450    /// See also [`Insert::on_conflict_do_nothing`].
451    pub fn on_conflict_do_nothing(mut self) -> TryInsert<A>
452    where
453        A: ActiveModelTrait,
454    {
455        self.query.on_conflict(on_conflict_primary_key::<A>());
456
457        TryInsert::from_many(self)
458    }
459
460    /// panic when self is empty
461    pub(crate) fn into_one(self) -> Insert<A> {
462        assert!(!self.empty);
463
464        let Self {
465            query,
466            primary_key,
467            empty: _,
468            model,
469        } = self;
470
471        Insert {
472            query,
473            primary_key,
474            model,
475        }
476    }
477}
478
479impl<A> QueryTrait for Insert<A>
480where
481    A: ActiveModelTrait,
482{
483    type QueryStatement = InsertStatement;
484
485    fn query(&mut self) -> &mut InsertStatement {
486        &mut self.query
487    }
488
489    fn as_query(&self) -> &InsertStatement {
490        &self.query
491    }
492
493    fn into_query(self) -> InsertStatement {
494        self.query
495    }
496}
497
498impl<A> QueryTrait for InsertMany<A>
499where
500    A: ActiveModelTrait,
501{
502    type QueryStatement = InsertStatement;
503
504    fn query(&mut self) -> &mut InsertStatement {
505        &mut self.query
506    }
507
508    fn as_query(&self) -> &InsertStatement {
509        &self.query
510    }
511
512    fn into_query(self) -> InsertStatement {
513        self.query
514    }
515}
516
517impl<A> TryInsert<A>
518where
519    A: ActiveModelTrait,
520{
521    fn from_one(insert: Insert<A>) -> Self {
522        Self {
523            insert_struct: insert,
524            empty: false,
525        }
526    }
527
528    fn from_many(insert: InsertMany<A>) -> Self {
529        let InsertMany {
530            query,
531            primary_key,
532            empty,
533            model,
534        } = insert;
535
536        Self {
537            insert_struct: Insert {
538                query,
539                primary_key,
540                model,
541            },
542            empty,
543        }
544    }
545
546    /// Try insert one item
547    pub fn one<M>(m: M) -> Self
548    where
549        M: IntoActiveModel<A>,
550    {
551        Self::from_one(Insert::one(m))
552    }
553
554    /// Try insert many items
555    pub fn many<M, I>(models: I) -> Self
556    where
557        M: IntoActiveModel<A>,
558        I: IntoIterator<Item = M>,
559    {
560        Self::from_many(Insert::many(models))
561    }
562
563    /// Set ON CONFLICT logic
564    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Insert<A> {
565        self.insert_struct.query.on_conflict(on_conflict);
566        self.insert_struct
567    }
568
569    /// Set ON CONFLICT on primary key do nothing, but with MySQL specific polyfill.
570    pub fn on_conflict_do_nothing(mut self) -> Self {
571        self.insert_struct
572            .query
573            .on_conflict(on_conflict_primary_key::<A>());
574
575        self
576    }
577
578    /// Set ON CONFLICT do nothing, but with MySQL specific polyfill.
579    pub fn on_conflict_do_nothing_on<I>(mut self, columns: I) -> Self
580    where
581        I: IntoIterator<Item = <A::Entity as EntityTrait>::Column>,
582    {
583        let primary_keys = <A::Entity as EntityTrait>::PrimaryKey::iter();
584        let mut on_conflict = OnConflict::columns(columns);
585        on_conflict.do_nothing_on(primary_keys);
586        self.insert_struct.query.on_conflict(on_conflict);
587        self
588    }
589}
590
591impl<A> QueryTrait for TryInsert<A>
592where
593    A: ActiveModelTrait,
594{
595    type QueryStatement = InsertStatement;
596
597    fn query(&mut self) -> &mut InsertStatement {
598        &mut self.insert_struct.query
599    }
600
601    fn as_query(&self) -> &InsertStatement {
602        &self.insert_struct.query
603    }
604
605    fn into_query(self) -> InsertStatement {
606        self.insert_struct.query
607    }
608}
609
610fn on_conflict_primary_key<A: ActiveModelTrait>() -> OnConflict {
611    let primary_keys = <A::Entity as EntityTrait>::PrimaryKey::iter();
612    let mut on_conflict = OnConflict::columns(primary_keys.clone());
613    on_conflict.do_nothing_on(primary_keys);
614    on_conflict
615}
616
617#[cfg(test)]
618mod tests {
619    use sea_query::OnConflict;
620
621    use crate::tests_cfg::{cake, cake_filling};
622    use crate::{
623        ActiveValue, DbBackend, DbErr, EntityTrait, Insert, IntoActiveModel, NotSet, QueryTrait,
624        Set,
625    };
626
627    #[test]
628    fn insert_1() {
629        assert_eq!(
630            Insert::<cake::ActiveModel>::one(cake::ActiveModel {
631                id: ActiveValue::not_set(),
632                name: ActiveValue::set("Apple Pie".to_owned()),
633            })
634            .build(DbBackend::Postgres)
635            .to_string(),
636            r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie')"#,
637        );
638    }
639
640    #[test]
641    fn insert_2() {
642        assert_eq!(
643            Insert::<cake::ActiveModel>::one(cake::ActiveModel {
644                id: ActiveValue::set(1),
645                name: ActiveValue::set("Apple Pie".to_owned()),
646            })
647            .build(DbBackend::Postgres)
648            .to_string(),
649            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
650        );
651    }
652
653    #[test]
654    fn insert_3() {
655        assert_eq!(
656            Insert::<cake::ActiveModel>::one(cake::Model {
657                id: 1,
658                name: "Apple Pie".to_owned(),
659            })
660            .build(DbBackend::Postgres)
661            .to_string(),
662            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
663        );
664    }
665
666    #[test]
667    fn insert_many_1() {
668        assert_eq!(
669            Insert::<cake::ActiveModel>::many([
670                cake::Model {
671                    id: 1,
672                    name: "Apple Pie".to_owned(),
673                },
674                cake::Model {
675                    id: 2,
676                    name: "Orange Scone".to_owned(),
677                }
678            ])
679            .build(DbBackend::Postgres)
680            .to_string(),
681            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie'), (2, 'Orange Scone')"#,
682        );
683    }
684
685    #[test]
686    fn insert_many_2() {
687        assert_eq!(
688            Insert::<cake::ActiveModel>::many([
689                cake::ActiveModel {
690                    id: NotSet,
691                    name: Set("Apple Pie".to_owned()),
692                },
693                cake::ActiveModel {
694                    id: NotSet,
695                    name: Set("Orange Scone".to_owned()),
696                }
697            ])
698            .build(DbBackend::Postgres)
699            .to_string(),
700            r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie'), ('Orange Scone')"#,
701        );
702    }
703
704    #[test]
705    fn insert_many_3() {
706        let apple = cake_filling::ActiveModel {
707            cake_id: ActiveValue::set(2),
708            filling_id: ActiveValue::NotSet,
709        };
710        let orange = cake_filling::ActiveModel {
711            cake_id: ActiveValue::NotSet,
712            filling_id: ActiveValue::set(3),
713        };
714        assert_eq!(
715            Insert::<cake_filling::ActiveModel>::many([apple, orange])
716                .build(DbBackend::Postgres)
717                .to_string(),
718            r#"INSERT INTO "cake_filling" ("cake_id", "filling_id") VALUES (2, NULL), (NULL, 3)"#,
719        );
720    }
721
722    #[test]
723    fn insert_6() {
724        let orange = cake::ActiveModel {
725            id: ActiveValue::set(2),
726            name: ActiveValue::set("Orange".to_owned()),
727        };
728
729        assert_eq!(
730            cake::Entity::insert(orange)
731                .on_conflict(
732                    OnConflict::column(cake::Column::Name)
733                        .do_nothing()
734                        .to_owned()
735                )
736                .build(DbBackend::Postgres)
737                .to_string(),
738            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
739        );
740    }
741
742    #[test]
743    fn insert_7() {
744        let orange = cake::ActiveModel {
745            id: ActiveValue::set(2),
746            name: ActiveValue::set("Orange".to_owned()),
747        };
748
749        assert_eq!(
750            cake::Entity::insert(orange)
751                .on_conflict(
752                    OnConflict::column(cake::Column::Name)
753                        .update_column(cake::Column::Name)
754                        .to_owned()
755                )
756                .build(DbBackend::Postgres)
757                .to_string(),
758            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
759        );
760    }
761
762    #[test]
763    fn test_on_conflict_do_nothing_on() {
764        let orange = cake::ActiveModel {
765            id: ActiveValue::set(2),
766            name: ActiveValue::set("Orange".to_owned()),
767        };
768
769        assert_eq!(
770            cake::Entity::insert(orange.clone())
771                .on_conflict_do_nothing_on([cake::Column::Name])
772                .build(DbBackend::Postgres)
773                .to_string(),
774            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
775        );
776        assert_eq!(
777            cake::Entity::insert(orange)
778                .on_conflict_do_nothing_on([cake::Column::Name])
779                .build(DbBackend::MySql)
780                .to_string(),
781            r#"INSERT INTO `cake` (`id`, `name`) VALUES (2, 'Orange') ON DUPLICATE KEY UPDATE `id` = `id`"#,
782        );
783    }
784
785    #[smol_potat::test]
786    async fn insert_8() -> Result<(), DbErr> {
787        use crate::{DbBackend, MockDatabase, Statement, Transaction};
788
789        mod post {
790            use crate as sea_orm;
791            use crate::entity::prelude::*;
792
793            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
794            #[sea_orm(table_name = "posts")]
795            pub struct Model {
796                #[sea_orm(primary_key, select_as = "INTEGER", save_as = "TEXT")]
797                pub id: i32,
798                pub title: String,
799                pub text: String,
800            }
801
802            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
803            pub enum Relation {}
804
805            impl ActiveModelBehavior for ActiveModel {}
806        }
807
808        let model = post::Model {
809            id: 1,
810            title: "News wrap up 2022".into(),
811            text: "brbrbrrrbrbrbrr...".into(),
812        };
813
814        let db = MockDatabase::new(DbBackend::Postgres)
815            .append_query_results([[model.clone()]])
816            .into_connection();
817
818        post::Entity::insert(model.into_active_model())
819            .exec(&db)
820            .await?;
821
822        assert_eq!(
823            db.into_transaction_log(),
824            [Transaction::many([Statement::from_sql_and_values(
825                DbBackend::Postgres,
826                r#"INSERT INTO "posts" ("id", "title", "text") VALUES (CAST($1 AS TEXT), $2, $3) RETURNING CAST("id" AS INTEGER)"#,
827                [
828                    1.into(),
829                    "News wrap up 2022".into(),
830                    "brbrbrrrbrbrbrr...".into(),
831                ]
832            )])]
833        );
834
835        Ok(())
836    }
837
838    #[smol_potat::test]
839    async fn insert_9() -> Result<(), DbErr> {
840        use crate::{DbBackend, MockDatabase, MockExecResult, Statement, Transaction};
841
842        mod post {
843            use crate as sea_orm;
844            use crate::entity::prelude::*;
845
846            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
847            #[sea_orm(table_name = "posts")]
848            pub struct Model {
849                #[sea_orm(
850                    primary_key,
851                    auto_increment = false,
852                    select_as = "INTEGER",
853                    save_as = "TEXT"
854                )]
855                pub id_primary: i32,
856                #[sea_orm(
857                    primary_key,
858                    auto_increment = false,
859                    select_as = "INTEGER",
860                    save_as = "TEXT"
861                )]
862                pub id_secondary: i32,
863                pub title: String,
864                pub text: String,
865            }
866
867            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
868            pub enum Relation {}
869
870            impl ActiveModelBehavior for ActiveModel {}
871        }
872
873        let model = post::Model {
874            id_primary: 1,
875            id_secondary: 1001,
876            title: "News wrap up 2022".into(),
877            text: "brbrbrrrbrbrbrr...".into(),
878        };
879
880        let db = MockDatabase::new(DbBackend::Postgres)
881            .append_query_results([[model.clone()]])
882            .into_connection();
883
884        post::Entity::insert(model.into_active_model())
885            .exec(&db)
886            .await?;
887
888        assert_eq!(
889            db.into_transaction_log(),
890            [Transaction::many([Statement::from_sql_and_values(
891                DbBackend::Postgres,
892                r#"INSERT INTO "posts" ("id_primary", "id_secondary", "title", "text") VALUES (CAST($1 AS TEXT), CAST($2 AS TEXT), $3, $4) RETURNING CAST("id_primary" AS INTEGER), CAST("id_secondary" AS INTEGER)"#,
893                [
894                    1.into(),
895                    1001.into(),
896                    "News wrap up 2022".into(),
897                    "brbrbrrrbrbrbrr...".into(),
898                ]
899            )])]
900        );
901
902        Ok(())
903    }
904}
sea_orm/query/insert.rs

sea_orm/query/
insert.rs
