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, OnConflict, 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 column value has discrepancy across rows
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                continue;
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
153    pub fn add_many<M, I>(mut self, models: I) -> Self
154    where
155        M: IntoActiveModel<A>,
156        I: IntoIterator<Item = M>,
157    {
158        for model in models.into_iter() {
159            self = self.add(model);
160        }
161        self
162    }
163
164    /// On conflict
165    ///
166    /// on conflict do nothing
167    /// ```
168    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
169    ///
170    /// let orange = cake::ActiveModel {
171    ///     id: ActiveValue::set(2),
172    ///     name: ActiveValue::set("Orange".to_owned()),
173    /// };
174    /// assert_eq!(
175    ///     cake::Entity::insert(orange)
176    ///         .on_conflict(
177    ///             OnConflict::column(cake::Column::Name)
178    ///                 .do_nothing()
179    ///                 .to_owned()
180    ///         )
181    ///         .build(DbBackend::Postgres)
182    ///         .to_string(),
183    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
184    /// );
185    /// ```
186    ///
187    /// on conflict do update
188    /// ```
189    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
190    ///
191    /// let orange = cake::ActiveModel {
192    ///     id: ActiveValue::set(2),
193    ///     name: ActiveValue::set("Orange".to_owned()),
194    /// };
195    /// assert_eq!(
196    ///     cake::Entity::insert(orange)
197    ///         .on_conflict(
198    ///             OnConflict::column(cake::Column::Name)
199    ///                 .update_column(cake::Column::Name)
200    ///                 .to_owned()
201    ///         )
202    ///         .build(DbBackend::Postgres)
203    ///         .to_string(),
204    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
205    /// );
206    /// ```
207    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Self {
208        self.query.on_conflict(on_conflict);
209        self
210    }
211
212    /// Allow insert statement return safely if inserting nothing.
213    /// The database will not be affected.
214    pub fn do_nothing(self) -> TryInsert<A>
215    where
216        A: ActiveModelTrait,
217    {
218        TryInsert::from_insert(self)
219    }
220
221    /// alias to do_nothing
222    pub fn on_empty_do_nothing(self) -> TryInsert<A>
223    where
224        A: ActiveModelTrait,
225    {
226        TryInsert::from_insert(self)
227    }
228
229    /// Set ON CONFLICT on primary key do nothing, but with MySQL specific polyfill.
230    ///
231    /// ```
232    /// use sea_orm::{entity::*, query::*, sea_query::OnConflict, tests_cfg::cake, DbBackend};
233    ///
234    /// let orange = cake::ActiveModel {
235    ///     id: ActiveValue::set(2),
236    ///     name: ActiveValue::set("Orange".to_owned()),
237    /// };
238    ///
239    /// assert_eq!(
240    ///     cake::Entity::insert(orange.clone())
241    ///         .on_conflict_do_nothing()
242    ///         .build(DbBackend::MySql)
243    ///         .to_string(),
244    ///     r#"INSERT INTO `cake` (`id`, `name`) VALUES (2, 'Orange') ON DUPLICATE KEY UPDATE `id` = `id`"#,
245    /// );
246    /// assert_eq!(
247    ///     cake::Entity::insert(orange.clone())
248    ///         .on_conflict_do_nothing()
249    ///         .build(DbBackend::Postgres)
250    ///         .to_string(),
251    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("id") DO NOTHING"#,
252    /// );
253    /// assert_eq!(
254    ///     cake::Entity::insert(orange)
255    ///         .on_conflict_do_nothing()
256    ///         .build(DbBackend::Sqlite)
257    ///         .to_string(),
258    ///     r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("id") DO NOTHING"#,
259    /// );
260    /// ```
261    pub fn on_conflict_do_nothing(mut self) -> TryInsert<A>
262    where
263        A: ActiveModelTrait,
264    {
265        let primary_keys = <A::Entity as EntityTrait>::PrimaryKey::iter();
266        self.query.on_conflict(
267            OnConflict::columns(primary_keys.clone())
268                .do_nothing_on(primary_keys)
269                .to_owned(),
270        );
271
272        TryInsert::from_insert(self)
273    }
274}
275
276impl<A> QueryTrait for Insert<A>
277where
278    A: ActiveModelTrait,
279{
280    type QueryStatement = InsertStatement;
281
282    fn query(&mut self) -> &mut InsertStatement {
283        &mut self.query
284    }
285
286    fn as_query(&self) -> &InsertStatement {
287        &self.query
288    }
289
290    fn into_query(self) -> InsertStatement {
291        self.query
292    }
293}
294
295/// Performs INSERT operations on a ActiveModel, will do nothing if input is empty.
296///
297/// All functions works the same as if it is Insert<A>. Please refer to Insert<A> page for more information
298#[derive(Debug)]
299pub struct TryInsert<A>
300where
301    A: ActiveModelTrait,
302{
303    pub(crate) insert_struct: Insert<A>,
304}
305
306impl<A> Default for TryInsert<A>
307where
308    A: ActiveModelTrait,
309{
310    fn default() -> Self {
311        Self::new()
312    }
313}
314
315#[allow(missing_docs)]
316impl<A> TryInsert<A>
317where
318    A: ActiveModelTrait,
319{
320    pub(crate) fn new() -> Self {
321        Self {
322            insert_struct: Insert::new(),
323        }
324    }
325
326    pub fn one<M>(m: M) -> Self
327    where
328        M: IntoActiveModel<A>,
329    {
330        Self::new().add(m)
331    }
332
333    pub fn many<M, I>(models: I) -> Self
334    where
335        M: IntoActiveModel<A>,
336        I: IntoIterator<Item = M>,
337    {
338        Self::new().add_many(models)
339    }
340
341    #[allow(clippy::should_implement_trait)]
342    pub fn add<M>(mut self, m: M) -> Self
343    where
344        M: IntoActiveModel<A>,
345    {
346        self.insert_struct = self.insert_struct.add(m);
347        self
348    }
349
350    pub fn add_many<M, I>(mut self, models: I) -> Self
351    where
352        M: IntoActiveModel<A>,
353        I: IntoIterator<Item = M>,
354    {
355        for model in models.into_iter() {
356            self.insert_struct = self.insert_struct.add(model);
357        }
358        self
359    }
360
361    pub fn on_conflict(mut self, on_conflict: OnConflict) -> Self {
362        self.insert_struct.query.on_conflict(on_conflict);
363        self
364    }
365
366    // helper function for do_nothing in Insert<A>
367    pub fn from_insert(insert: Insert<A>) -> Self {
368        Self {
369            insert_struct: insert,
370        }
371    }
372}
373
374impl<A> QueryTrait for TryInsert<A>
375where
376    A: ActiveModelTrait,
377{
378    type QueryStatement = InsertStatement;
379
380    fn query(&mut self) -> &mut InsertStatement {
381        &mut self.insert_struct.query
382    }
383
384    fn as_query(&self) -> &InsertStatement {
385        &self.insert_struct.query
386    }
387
388    fn into_query(self) -> InsertStatement {
389        self.insert_struct.query
390    }
391}
392#[cfg(test)]
393mod tests {
394    use sea_query::OnConflict;
395
396    use crate::tests_cfg::cake::{self};
397    use crate::{ActiveValue, DbBackend, DbErr, EntityTrait, Insert, IntoActiveModel, QueryTrait};
398
399    #[test]
400    fn insert_1() {
401        assert_eq!(
402            Insert::<cake::ActiveModel>::new()
403                .add(cake::ActiveModel {
404                    id: ActiveValue::not_set(),
405                    name: ActiveValue::set("Apple Pie".to_owned()),
406                })
407                .build(DbBackend::Postgres)
408                .to_string(),
409            r#"INSERT INTO "cake" ("name") VALUES ('Apple Pie')"#,
410        );
411    }
412
413    #[test]
414    fn insert_2() {
415        assert_eq!(
416            Insert::<cake::ActiveModel>::new()
417                .add(cake::ActiveModel {
418                    id: ActiveValue::set(1),
419                    name: ActiveValue::set("Apple Pie".to_owned()),
420                })
421                .build(DbBackend::Postgres)
422                .to_string(),
423            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
424        );
425    }
426
427    #[test]
428    fn insert_3() {
429        assert_eq!(
430            Insert::<cake::ActiveModel>::new()
431                .add(cake::Model {
432                    id: 1,
433                    name: "Apple Pie".to_owned(),
434                })
435                .build(DbBackend::Postgres)
436                .to_string(),
437            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie')"#,
438        );
439    }
440
441    #[test]
442    fn insert_4() {
443        assert_eq!(
444            Insert::<cake::ActiveModel>::new()
445                .add_many([
446                    cake::Model {
447                        id: 1,
448                        name: "Apple Pie".to_owned(),
449                    },
450                    cake::Model {
451                        id: 2,
452                        name: "Orange Scone".to_owned(),
453                    }
454                ])
455                .build(DbBackend::Postgres)
456                .to_string(),
457            r#"INSERT INTO "cake" ("id", "name") VALUES (1, 'Apple Pie'), (2, 'Orange Scone')"#,
458        );
459    }
460
461    #[test]
462    #[should_panic(expected = "columns mismatch")]
463    fn insert_5() {
464        let apple = cake::ActiveModel {
465            name: ActiveValue::set("Apple".to_owned()),
466            ..Default::default()
467        };
468        let orange = cake::ActiveModel {
469            id: ActiveValue::set(2),
470            name: ActiveValue::set("Orange".to_owned()),
471        };
472        assert_eq!(
473            Insert::<cake::ActiveModel>::new()
474                .add_many([apple, orange])
475                .build(DbBackend::Postgres)
476                .to_string(),
477            r#"INSERT INTO "cake" ("id", "name") VALUES (NULL, 'Apple'), (2, 'Orange')"#,
478        );
479    }
480
481    #[test]
482    fn insert_6() {
483        let orange = cake::ActiveModel {
484            id: ActiveValue::set(2),
485            name: ActiveValue::set("Orange".to_owned()),
486        };
487
488        assert_eq!(
489            cake::Entity::insert(orange)
490                .on_conflict(
491                    OnConflict::column(cake::Column::Name)
492                        .do_nothing()
493                        .to_owned()
494                )
495                .build(DbBackend::Postgres)
496                .to_string(),
497            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO NOTHING"#,
498        );
499    }
500
501    #[test]
502    fn insert_7() {
503        let orange = cake::ActiveModel {
504            id: ActiveValue::set(2),
505            name: ActiveValue::set("Orange".to_owned()),
506        };
507
508        assert_eq!(
509            cake::Entity::insert(orange)
510                .on_conflict(
511                    OnConflict::column(cake::Column::Name)
512                        .update_column(cake::Column::Name)
513                        .to_owned()
514                )
515                .build(DbBackend::Postgres)
516                .to_string(),
517            r#"INSERT INTO "cake" ("id", "name") VALUES (2, 'Orange') ON CONFLICT ("name") DO UPDATE SET "name" = "excluded"."name""#,
518        );
519    }
520
521    #[smol_potat::test]
522    async fn insert_8() -> Result<(), DbErr> {
523        use crate::{DbBackend, MockDatabase, Statement, Transaction};
524
525        mod post {
526            use crate as sea_orm;
527            use crate::entity::prelude::*;
528
529            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
530            #[sea_orm(table_name = "posts")]
531            pub struct Model {
532                #[sea_orm(primary_key, select_as = "INTEGER", save_as = "TEXT")]
533                pub id: i32,
534                pub title: String,
535                pub text: String,
536            }
537
538            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
539            pub enum Relation {}
540
541            impl ActiveModelBehavior for ActiveModel {}
542        }
543
544        let model = post::Model {
545            id: 1,
546            title: "News wrap up 2022".into(),
547            text: "brbrbrrrbrbrbrr...".into(),
548        };
549
550        let db = MockDatabase::new(DbBackend::Postgres)
551            .append_query_results([[model.clone()]])
552            .into_connection();
553
554        post::Entity::insert(model.into_active_model())
555            .exec(&db)
556            .await?;
557
558        assert_eq!(
559            db.into_transaction_log(),
560            [Transaction::many([Statement::from_sql_and_values(
561                DbBackend::Postgres,
562                r#"INSERT INTO "posts" ("id", "title", "text") VALUES (CAST($1 AS TEXT), $2, $3) RETURNING CAST("id" AS INTEGER)"#,
563                [
564                    1.into(),
565                    "News wrap up 2022".into(),
566                    "brbrbrrrbrbrbrr...".into(),
567                ]
568            )])]
569        );
570
571        Ok(())
572    }
573
574    #[smol_potat::test]
575    async fn insert_9() -> Result<(), DbErr> {
576        use crate::{DbBackend, MockDatabase, MockExecResult, Statement, Transaction};
577
578        mod post {
579            use crate as sea_orm;
580            use crate::entity::prelude::*;
581
582            #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
583            #[sea_orm(table_name = "posts")]
584            pub struct Model {
585                #[sea_orm(
586                    primary_key,
587                    auto_increment = false,
588                    select_as = "INTEGER",
589                    save_as = "TEXT"
590                )]
591                pub id_primary: i32,
592                #[sea_orm(
593                    primary_key,
594                    auto_increment = false,
595                    select_as = "INTEGER",
596                    save_as = "TEXT"
597                )]
598                pub id_secondary: i32,
599                pub title: String,
600                pub text: String,
601            }
602
603            #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
604            pub enum Relation {}
605
606            impl ActiveModelBehavior for ActiveModel {}
607        }
608
609        let model = post::Model {
610            id_primary: 1,
611            id_secondary: 1001,
612            title: "News wrap up 2022".into(),
613            text: "brbrbrrrbrbrbrr...".into(),
614        };
615
616        let db = MockDatabase::new(DbBackend::Postgres)
617            .append_exec_results([MockExecResult {
618                last_insert_id: 1,
619                rows_affected: 1,
620            }])
621            .into_connection();
622
623        post::Entity::insert(model.into_active_model())
624            .exec(&db)
625            .await?;
626
627        assert_eq!(
628            db.into_transaction_log(),
629            [Transaction::many([Statement::from_sql_and_values(
630                DbBackend::Postgres,
631                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)"#,
632                [
633                    1.into(),
634                    1001.into(),
635                    "News wrap up 2022".into(),
636                    "brbrbrrrbrbrbrr...".into(),
637                ]
638            )])]
639        );
640
641        Ok(())
642    }
643}
sea_orm/query/insert.rs

sea_orm/query/
insert.rs
