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) columns: Vec<bool>,
16    pub(crate) primary_key: Option<ValueTuple>,
17    pub(crate) model: PhantomData<A>,
18}
19
20impl<A> Default for Insert<A>
21where
22    A: ActiveModelTrait,
23{
24    fn default() -> Self {
25        Self::new()
26    }
27}
28
29impl<A> Insert<A>
30where
31    A: ActiveModelTrait,
32{
33    pub(crate) fn new() -> Self {
34        Self {
35            query: InsertStatement::new()
36                .into_table(A::Entity::default().table_ref())
37                .or_default_values()
38                .to_owned(),
39            columns: Vec::new(),
40            primary_key: None,
41            model: PhantomData,
42        }
43    }
44
45    /// Insert one Model or ActiveModel
46    ///
47    /// Model
48    /// ```
49    /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
50    ///
51    /// assert_eq!(
52    ///     Insert::one(cake::Model {
53    ///         id: 1,
54    ///         name: "Apple Pie".to_owned(),
55    ///     })
56    ///     .build(DbBackend::Postgres)
57    ///     .to_string(),
58    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
59    /// );
60    /// ```
61    /// ActiveModel
62    /// ```
63    /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
64    ///
65    /// assert_eq!(
66    ///     Insert::one(cake::ActiveModel {
67    ///         id: NotSet,
68    ///         name: Set("Apple Pie".to_owned()),
69    ///     })
70    ///     .build(DbBackend::Postgres)
71    ///     .to_string(),
72    ///     r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie')"#,
73    /// );
74    /// ```
75    pub fn one<M>(m: M) -> Self
76    where
77        M: IntoActiveModel<A>,
78    {
79        Self::new().add(m)
80    }
81
82    /// Insert many Model or ActiveModel
83    ///
84    /// ```
85    /// use sea_orm::{entity::*, query::*, tests_cfg::cake, DbBackend};
86    ///
87    /// assert_eq!(
88    ///     Insert::many([
89    ///         cake::Model {
90    ///             id: 1,
91    ///             name: "Apple Pie".to_owned(),
92    ///         },
93    ///         cake::Model {
94    ///             id: 2,
95    ///             name: "Orange Scone".to_owned(),
96    ///         }
97    ///     ])
98    ///     .build(DbBackend::Postgres)
99    ///     .to_string(),
100    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie'), (2, 'Orange Scone')"#,
101    /// );
102    /// ```
103    pub fn many<M, I>(models: I) -> Self
104    where
105        M: IntoActiveModel<A>,
106        I: IntoIterator<Item = M>,
107    {
108        Self::new().add_many(models)
109    }
110
111    /// Add a Model to Self
112    ///
113    /// # Panics
114    ///
115    /// Panics if the rows have different column sets from what've previously been cached in the query statement
116    #[allow(clippy::should_implement_trait)]
117    pub fn add<M>(mut self, m: M) -> Self
118    where
119        M: IntoActiveModel<A>,
120    {
121        let mut am: A = m.into_active_model();
122        self.primary_key =
123            if !<<A::Entity as EntityTrait>::PrimaryKey as PrimaryKeyTrait>::auto_increment() {
124                am.get_primary_key_value()
125            } else {
126                None
127            };
128        let mut columns = Vec::new();
129        let mut values = Vec::new();
130        let columns_empty = self.columns.is_empty();
131        for (idx, col) in <A::Entity as EntityTrait>::Column::iter().enumerate() {
132            let av = am.take(col);
133            let av_has_val = av.is_set() || av.is_unchanged();
134            if columns_empty {
135                self.columns.push(av_has_val);
136            } else if self.columns[idx] != av_has_val {
137                panic!("columns mismatch");
138            }
139            match av {
140                ActiveValue::Set(value) | ActiveValue::Unchanged(value) => {
141                    columns.push(col);
142                    values.push(col.save_as(Expr::val(value)));
143                }
144                ActiveValue::NotSet => {}
145            }
146        }
147        self.query.columns(columns);
148        self.query.values_panic(values);
149        self
150    }
151
152    /// Add many Models to Self. This is the legacy implementation priori to `1.1.3`.
153    ///
154    /// # Panics
155    ///
156    /// Panics if the rows have different column sets
157    #[deprecated(
158        since = "1.1.3",
159        note = "Please use [`Insert::add_many`] which does not panic"
160    )]
161    pub fn add_multi<M, I>(mut self, models: I) -> Self
162    where
163        M: IntoActiveModel<A>,
164        I: IntoIterator<Item = M>,
165    {
166        for model in models.into_iter() {
167            self = self.add(model);
168        }
169        self
170    }
171
172    /// Add many Models to Self
173    pub fn add_many<M, I>(mut self, models: I) -> Self
174    where
175        M: IntoActiveModel<A>,
176        I: IntoIterator<Item = M>,
177    {
178        let mut columns: Vec<_> = <A::Entity as EntityTrait>::Column::iter()
179            .map(|_| None)
180            .collect();
181        let mut null_value: Vec<Option<Value>> =
182            std::iter::repeat(None).take(columns.len()).collect();
183        let mut all_values: Vec<Vec<SimpleExpr>> = Vec::new();
184
185        for model in models.into_iter() {
186            let mut am: A = model.into_active_model();
187            self.primary_key =
188                if !<<A::Entity as EntityTrait>::PrimaryKey as PrimaryKeyTrait>::auto_increment() {
189                    am.get_primary_key_value()
190                } else {
191                    None
192                };
193            let mut values = Vec::with_capacity(columns.len());
194            for (idx, col) in <A::Entity as EntityTrait>::Column::iter().enumerate() {
195                let av = am.take(col);
196                match av {
197                    ActiveValue::Set(value) | ActiveValue::Unchanged(value) => {
198                        columns[idx] = Some(col); // mark the column as used
199                        null_value[idx] = Some(value.as_null()); // store the null value with the correct type
200                        values.push(col.save_as(Expr::val(value))); // same as add() above
201                    }
202                    ActiveValue::NotSet => {
203                        values.push(SimpleExpr::Keyword(Keyword::Null)); // indicate a missing value
204                    }
205                }
206            }
207            all_values.push(values);
208        }
209
210        if !all_values.is_empty() {
211            // filter only used column
212            self.query.columns(columns.iter().cloned().flatten());
213
214            // flag used column
215            self.columns = columns.iter().map(Option::is_some).collect();
216        }
217
218        for values in all_values {
219            // since we've aligned the column set, this never panics
220            self.query
221                .values_panic(values.into_iter().enumerate().filter_map(|(i, v)| {
222                    if columns[i].is_some() {
223                        // only if the column is used
224                        if !matches!(v, SimpleExpr::Keyword(Keyword::Null)) {
225                            // use the value expression
226                            Some(v)
227                        } else {
228                            // use null as standin, which must be Some
229                            null_value[i].clone().map(SimpleExpr::Value)
230                        }
231                    } else {
232                        None
233                    }
234                }));
235        }
236
237        self
238    }
239
240    /// On conflict
241    ///
242    /// on conflict do nothing
243    /// ```
244    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
245    ///
246    /// let orange = cake::ActiveModel {
247    ///     id: ActiveValue::set(2),
248    ///     name: ActiveValue::set("Orange".to_owned()),
249    /// };
250    /// assert_eq!(
251    ///     cake::Entity::insert(orange)
252    ///         .on_conflict(
253    ///             OnConflict::column(cake::Column::Name)
254    ///                 .do_nothing()
255    ///                 .to_owned()
256    ///         )
257    ///         .build(DbBackend::Postgres)
258    ///         .to_string(),
259    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
260    /// );
261    /// ```
262    ///
263    /// on conflict do update
264    /// ```
265    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
266    ///
267    /// let orange = cake::ActiveModel {
268    ///     id: ActiveValue::set(2),
269    ///     name: ActiveValue::set("Orange".to_owned()),
270    /// };
271    /// assert_eq!(
272    ///     cake::Entity::insert(orange)
273    ///         .on_conflict(
274    ///             OnConflict::column(cake::Column::Name)
275    ///                 .update_column(cake::Column::Name)
276    ///                 .to_owned()
277    ///         )
278    ///         .build(DbBackend::Postgres)
279    ///         .to_string(),
280    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
281    /// );
282    /// ```
283    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Self {
284        self.query.on_conflict(on_conflict);
285        self
286    }
287
288    /// Allow insert statement to return without error if nothing's been inserted
289    pub fn do_nothing(self) -> TryInsert<A>
290    where
291        A: ActiveModelTrait,
292    {
293        TryInsert::from_insert(self)
294    }
295
296    /// Alias to `do_nothing`
297    pub fn on_empty_do_nothing(self) -> TryInsert<A>
298    where
299        A: ActiveModelTrait,
300    {
301        TryInsert::from_insert(self)
302    }
303
304    /// Set ON CONFLICT on primary key do nothing, but with MySQL specific polyfill.
305    ///
306    /// ```
307    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
308    ///
309    /// let orange = cake::ActiveModel {
310    ///     id: ActiveValue::set(2),
311    ///     name: ActiveValue::set("Orange".to_owned()),
312    /// };
313    ///
314    /// assert_eq!(
315    ///     cake::Entity::insert(orange.clone())
316    ///         .on_conflict_do_nothing()
317    ///         .build(DbBackend::MySql)
318    ///         .to_string(),
319    ///     r#"INSERT INTO `cake` (`id`, `name`) VALUES (2, 'Orange') ON DUPLICATE KEY UPDATE `id` = `id`"#,
320    /// );
321    /// assert_eq!(
322    ///     cake::Entity::insert(orange.clone())
323    ///         .on_conflict_do_nothing()
324    ///         .build(DbBackend::Postgres)
325    ///         .to_string(),
326    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("id") DO NOTHING"#,
327    /// );
328    /// assert_eq!(
329    ///     cake::Entity::insert(orange)
330    ///         .on_conflict_do_nothing()
331    ///         .build(DbBackend::Sqlite)
332    ///         .to_string(),
333    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("id") DO NOTHING"#,
334    /// );
335    /// ```
336    pub fn on_conflict_do_nothing(mut self) -> TryInsert<A>
337    where
338        A: ActiveModelTrait,
339    {
340        let primary_keys = <A::Entity as EntityTrait>::PrimaryKey::iter();
341        self.query.on_conflict(
342            OnConflict::columns(primary_keys.clone())
343                .do_nothing_on(primary_keys)
344                .to_owned(),
345        );
346
347        TryInsert::from_insert(self)
348    }
349}
350
351impl<A> QueryTrait for Insert<A>
352where
353    A: ActiveModelTrait,
354{
355    type QueryStatement = InsertStatement;
356
357    fn query(&mut self) -> &mut InsertStatement {
358        &mut self.query
359    }
360
361    fn as_query(&self) -> &InsertStatement {
362        &self.query
363    }
364
365    fn into_query(self) -> InsertStatement {
366        self.query
367    }
368}
369
370/// Performs INSERT operations on a ActiveModel, will do nothing if input is empty.
371///
372/// All functions works the same as if it is Insert<A>. Please refer to Insert<A> page for more information
373#[derive(Debug)]
374pub struct TryInsert<A>
375where
376    A: ActiveModelTrait,
377{
378    pub(crate) insert_struct: Insert<A>,
379}
380
381impl<A> Default for TryInsert<A>
382where
383    A: ActiveModelTrait,
384{
385    fn default() -> Self {
386        Self::new()
387    }
388}
389
390#[allow(missing_docs)]
391impl<A> TryInsert<A>
392where
393    A: ActiveModelTrait,
394{
395    pub(crate) fn new() -> Self {
396        Self {
397            insert_struct: Insert::new(),
398        }
399    }
400
401    pub fn one<M>(m: M) -> Self
402    where
403        M: IntoActiveModel<A>,
404    {
405        Self::new().add(m)
406    }
407
408    pub fn many<M, I>(models: I) -> Self
409    where
410        M: IntoActiveModel<A>,
411        I: IntoIterator<Item = M>,
412    {
413        Self::new().add_many(models)
414    }
415
416    #[allow(clippy::should_implement_trait)]
417    pub fn add<M>(mut self, m: M) -> Self
418    where
419        M: IntoActiveModel<A>,
420    {
421        self.insert_struct = self.insert_struct.add(m);
422        self
423    }
424
425    pub fn add_many<M, I>(mut self, models: I) -> Self
426    where
427        M: IntoActiveModel<A>,
428        I: IntoIterator<Item = M>,
429    {
430        self.insert_struct = self.insert_struct.add_many(models);
431        self
432    }
433
434    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Self {
435        self.insert_struct.query.on_conflict(on_conflict);
436        self
437    }
438
439    // helper function for do_nothing in Insert<A>
440    pub fn from_insert(insert: Insert<A>) -> Self {
441        Self {
442            insert_struct: insert,
443        }
444    }
445}
446
447impl<A> QueryTrait for TryInsert<A>
448where
449    A: ActiveModelTrait,
450{
451    type QueryStatement = InsertStatement;
452
453    fn query(&mut self) -> &mut InsertStatement {
454        &mut self.insert_struct.query
455    }
456
457    fn as_query(&self) -> &InsertStatement {
458        &self.insert_struct.query
459    }
460
461    fn into_query(self) -> InsertStatement {
462        self.insert_struct.query
463    }
464}
465#[cfg(test)]
466mod tests {
467    use sea_query::OnConflict;
468
469    use crate::tests_cfg::{cake, cake_filling};
470    use crate::{
471        ActiveValue, DbBackend, DbErr, EntityTrait, Insert, IntoActiveModel, NotSet, QueryTrait,
472        Set,
473    };
474
475    #[test]
476    fn insert_1() {
477        assert_eq!(
478            Insert::<cake::ActiveModel>::new()
479                .add(cake::ActiveModel {
480                    id: ActiveValue::not_set(),
481                    name: ActiveValue::set("Apple Pie".to_owned()),
482                })
483                .build(DbBackend::Postgres)
484                .to_string(),
485            r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie')"#,
486        );
487    }
488
489    #[test]
490    fn insert_2() {
491        assert_eq!(
492            Insert::<cake::ActiveModel>::new()
493                .add(cake::ActiveModel {
494                    id: ActiveValue::set(1),
495                    name: ActiveValue::set("Apple Pie".to_owned()),
496                })
497                .build(DbBackend::Postgres)
498                .to_string(),
499            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
500        );
501    }
502
503    #[test]
504    fn insert_3() {
505        assert_eq!(
506            Insert::<cake::ActiveModel>::new()
507                .add(cake::Model {
508                    id: 1,
509                    name: "Apple Pie".to_owned(),
510                })
511                .build(DbBackend::Postgres)
512                .to_string(),
513            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
514        );
515    }
516
517    #[test]
518    fn insert_many_1() {
519        assert_eq!(
520            Insert::<cake::ActiveModel>::new()
521                .add_many([
522                    cake::Model {
523                        id: 1,
524                        name: "Apple Pie".to_owned(),
525                    },
526                    cake::Model {
527                        id: 2,
528                        name: "Orange Scone".to_owned(),
529                    }
530                ])
531                .build(DbBackend::Postgres)
532                .to_string(),
533            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie'), (2, 'Orange Scone')"#,
534        );
535    }
536
537    #[test]
538    fn insert_many_2() {
539        assert_eq!(
540            Insert::<cake::ActiveModel>::new()
541                .add_many([
542                    cake::ActiveModel {
543                        id: NotSet,
544                        name: Set("Apple Pie".to_owned()),
545                    },
546                    cake::ActiveModel {
547                        id: NotSet,
548                        name: Set("Orange Scone".to_owned()),
549                    }
550                ])
551                .build(DbBackend::Postgres)
552                .to_string(),
553            r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie'), ('Orange Scone')"#,
554        );
555    }
556
557    #[test]
558    fn insert_many_3() {
559        let apple = cake_filling::ActiveModel {
560            cake_id: ActiveValue::set(2),
561            filling_id: ActiveValue::NotSet,
562        };
563        let orange = cake_filling::ActiveModel {
564            cake_id: ActiveValue::NotSet,
565            filling_id: ActiveValue::set(3),
566        };
567        assert_eq!(
568            Insert::<cake_filling::ActiveModel>::new()
569                .add_many([apple, orange])
570                .build(DbBackend::Postgres)
571                .to_string(),
572            r#"INSERT INTO "cake_filling" ("cake_id", "filling_id") VALUES (2, NULL), (NULL, 3)"#,
573        );
574    }
575
576    #[test]
577    fn insert_6() {
578        let orange = cake::ActiveModel {
579            id: ActiveValue::set(2),
580            name: ActiveValue::set("Orange".to_owned()),
581        };
582
583        assert_eq!(
584            cake::Entity::insert(orange)
585                .on_conflict(
586                    OnConflict::column(cake::Column::Name)
587                        .do_nothing()
588                        .to_owned()
589                )
590                .build(DbBackend::Postgres)
591                .to_string(),
592            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
593        );
594    }
595
596    #[test]
597    fn insert_7() {
598        let orange = cake::ActiveModel {
599            id: ActiveValue::set(2),
600            name: ActiveValue::set("Orange".to_owned()),
601        };
602
603        assert_eq!(
604            cake::Entity::insert(orange)
605                .on_conflict(
606                    OnConflict::column(cake::Column::Name)
607                        .update_column(cake::Column::Name)
608                        .to_owned()
609                )
610                .build(DbBackend::Postgres)
611                .to_string(),
612            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
613        );
614    }
615
616    #[smol_potat::test]
617    async fn insert_8() -> Result<(), DbErr> {
618        use crate::{DbBackend, MockDatabase, Statement, Transaction};
619
620        mod post {
621            use crate as sea_orm;
622            use crate::entity::prelude::*;
623
624            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
625            #[sea_orm(table_name = "posts")]
626            pub struct Model {
627                #[sea_orm(primary_key, select_as = "INTEGER", save_as = "TEXT")]
628                pub id: i32,
629                pub title: String,
630                pub text: String,
631            }
632
633            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
634            pub enum Relation {}
635
636            impl ActiveModelBehavior for ActiveModel {}
637        }
638
639        let model = post::Model {
640            id: 1,
641            title: "News wrap up 2022".into(),
642            text: "brbrbrrrbrbrbrr...".into(),
643        };
644
645        let db = MockDatabase::new(DbBackend::Postgres)
646            .append_query_results([[model.clone()]])
647            .into_connection();
648
649        post::Entity::insert(model.into_active_model())
650            .exec(&db)
651            .await?;
652
653        assert_eq!(
654            db.into_transaction_log(),
655            [Transaction::many([Statement::from_sql_and_values(
656                DbBackend::Postgres,
657                r#"INSERT INTO "posts" ("id", "title", "text") VALUES (CAST($1 AS TEXT), $2, $3) RETURNING CAST("id" AS INTEGER)"#,
658                [
659                    1.into(),
660                    "News wrap up 2022".into(),
661                    "brbrbrrrbrbrbrr...".into(),
662                ]
663            )])]
664        );
665
666        Ok(())
667    }
668
669    #[smol_potat::test]
670    async fn insert_9() -> Result<(), DbErr> {
671        use crate::{DbBackend, MockDatabase, MockExecResult, Statement, Transaction};
672
673        mod post {
674            use crate as sea_orm;
675            use crate::entity::prelude::*;
676
677            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
678            #[sea_orm(table_name = "posts")]
679            pub struct Model {
680                #[sea_orm(
681                    primary_key,
682                    auto_increment = false,
683                    select_as = "INTEGER",
684                    save_as = "TEXT"
685                )]
686                pub id_primary: i32,
687                #[sea_orm(
688                    primary_key,
689                    auto_increment = false,
690                    select_as = "INTEGER",
691                    save_as = "TEXT"
692                )]
693                pub id_secondary: i32,
694                pub title: String,
695                pub text: String,
696            }
697
698            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
699            pub enum Relation {}
700
701            impl ActiveModelBehavior for ActiveModel {}
702        }
703
704        let model = post::Model {
705            id_primary: 1,
706            id_secondary: 1001,
707            title: "News wrap up 2022".into(),
708            text: "brbrbrrrbrbrbrr...".into(),
709        };
710
711        let db = MockDatabase::new(DbBackend::Postgres)
712            .append_exec_results([MockExecResult {
713                last_insert_id: 1,
714                rows_affected: 1,
715            }])
716            .into_connection();
717
718        post::Entity::insert(model.into_active_model())
719            .exec(&db)
720            .await?;
721
722        assert_eq!(
723            db.into_transaction_log(),
724            [Transaction::many([Statement::from_sql_and_values(
725                DbBackend::Postgres,
726                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)"#,
727                [
728                    1.into(),
729                    1001.into(),
730                    "News wrap up 2022".into(),
731                    "brbrbrrrbrbrbrr...".into(),
732                ]
733            )])]
734        );
735
736        Ok(())
737    }
738}
sea_orm/query/insert.rs

sea_orm/query/
insert.rs
