diesel/query_builder/
functions.rs

1use super::delete_statement::DeleteStatement;
2use super::distinct_clause::NoDistinctClause;
3use super::insert_statement::{Insert, InsertOrIgnore, Replace};
4use super::select_clause::SelectClause;
5use super::{
6    AsQuery, IncompleteInsertOrIgnoreStatement, IncompleteInsertStatement,
7    IncompleteReplaceStatement, IntoUpdateTarget, SelectStatement, SqlQuery, UpdateStatement,
8};
9use crate::expression::Expression;
10use crate::Table;
11
12/// Creates an `UPDATE` statement.
13///
14/// When a table is passed to `update`, every row in the table will be updated.
15/// You can narrow this scope by calling [`filter`] on the table before passing it in,
16/// which will result in `UPDATE your_table SET ... WHERE args_to_filter`.
17///
18/// Passing a type which implements `Identifiable` is the same as passing
19/// `some_table.find(some_struct.id())`.
20///
21/// [`filter`]: crate::query_builder::UpdateStatement::filter()
22///
23/// # Examples
24///
25/// ```rust
26/// # include!("../doctest_setup.rs");
27/// #
28/// # #[cfg(feature = "postgres")]
29/// # fn main() {
30/// #     use schema::users::dsl::*;
31/// #     let connection = &mut establish_connection();
32/// let updated_row = diesel::update(users.filter(id.eq(1)))
33///     .set(name.eq("James"))
34///     .get_result(connection);
35/// // On backends that support it, you can call `get_result` instead of `execute`
36/// // to have `RETURNING *` automatically appended to the query. Alternatively, you
37/// // can explicitly return an expression by using the `returning` method before
38/// // getting the result.
39/// assert_eq!(Ok((1, "James".to_string())), updated_row);
40/// # }
41/// # #[cfg(not(feature = "postgres"))]
42/// # fn main() {}
43/// ```
44///
45/// To update multiple columns, give [`set`] a tuple argument:
46///
47/// [`set`]: crate::query_builder::UpdateStatement::set()
48///
49/// ```rust
50/// # include!("../doctest_setup.rs");
51/// #
52/// # table! {
53/// #     users {
54/// #         id -> Integer,
55/// #         name -> VarChar,
56/// #         surname -> VarChar,
57/// #     }
58/// # }
59/// #
60/// # #[cfg(feature = "postgres")]
61/// # fn main() {
62/// # use self::users::dsl::*;
63/// # let connection = &mut establish_connection();
64/// # diesel::sql_query("DROP TABLE users").execute(connection).unwrap();
65/// # diesel::sql_query("CREATE TABLE users (
66/// #     id SERIAL PRIMARY KEY,
67/// #     name VARCHAR,
68/// #     surname VARCHAR)").execute(connection).unwrap();
69/// # diesel::sql_query("INSERT INTO users(name, surname) VALUES('Sage', 'Griffin')").execute(connection).unwrap();
70///
71/// let updated_row = diesel::update(users.filter(id.eq(1)))
72///     .set((name.eq("James"), surname.eq("Bond")))
73///     .get_result(connection);
74///
75/// assert_eq!(Ok((1, "James".to_string(), "Bond".to_string())), updated_row);
76/// # }
77/// # #[cfg(not(feature = "postgres"))]
78/// # fn main() {}
79/// ```
80pub fn update<T: IntoUpdateTarget>(source: T) -> UpdateStatement<T::Table, T::WhereClause> {
81    UpdateStatement::new(source.into_update_target())
82}
83
84/// Creates a `DELETE` statement.
85///
86/// When a table is passed to `delete`,
87/// every row in the table will be deleted.
88/// This scope can be narrowed by calling [`filter`]
89/// on the table before it is passed in.
90///
91/// [`filter`]: crate::query_builder::DeleteStatement::filter()
92///
93/// # Examples
94///
95/// ### Deleting a single record:
96///
97/// ```rust
98/// # include!("../doctest_setup.rs");
99/// #
100/// # fn main() {
101/// #     delete();
102/// # }
103/// #
104/// #
105/// # fn delete() -> QueryResult<()> {
106/// #     use schema::users::dsl::*;
107/// #     let connection = &mut establish_connection();
108/// let old_count = users.count().first::<i64>(connection);
109/// diesel::delete(users.filter(id.eq(1))).execute(connection)?;
110/// assert_eq!(
111///     old_count.map(|count| count - 1),
112///     users.count().first(connection)
113/// );
114/// # Ok(())
115/// # }
116/// ```
117///
118/// ### Deleting a whole table:
119///
120/// ```rust
121/// # include!("../doctest_setup.rs");
122/// #
123/// # fn main() {
124/// #     delete();
125/// # }
126/// #
127/// # fn delete() -> QueryResult<()> {
128/// #     use schema::users::dsl::*;
129/// #     let connection = &mut establish_connection();
130/// diesel::delete(users).execute(connection)?;
131/// assert_eq!(Ok(0), users.count().first::<i64>(connection));
132/// # Ok(())
133/// # }
134/// ```
135pub fn delete<T: IntoUpdateTarget>(source: T) -> DeleteStatement<T::Table, T::WhereClause> {
136    let target = source.into_update_target();
137    DeleteStatement::new(target.table, target.where_clause)
138}
139
140/// Creates an `INSERT` statement for the target table.
141///
142/// You may add data by calling [`values()`] or [`default_values()`]
143/// as shown in the examples.
144///
145/// [`values()`]: crate::query_builder::IncompleteInsertStatement::values()
146/// [`default_values()`]: crate::query_builder::IncompleteInsertStatement::default_values()
147///
148/// Backends that support the `RETURNING` clause, such as PostgreSQL,
149/// can return the inserted rows by calling [`.get_results`] instead of [`.execute`].
150///
151/// [`.get_results`]: crate::query_dsl::RunQueryDsl::get_results()
152/// [`.execute`]: crate::query_dsl::RunQueryDsl::execute
153///
154/// # Examples
155///
156/// ```rust
157/// # include!("../doctest_setup.rs");
158/// #
159/// # fn main() {
160/// #     use schema::users::dsl::*;
161/// #     let connection = &mut establish_connection();
162/// let rows_inserted = diesel::insert_into(users)
163///     .values(&name.eq("Sean"))
164///     .execute(connection);
165///
166/// assert_eq!(Ok(1), rows_inserted);
167///
168/// let new_users = vec![name.eq("Tess"), name.eq("Jim")];
169///
170/// let rows_inserted = diesel::insert_into(users)
171///     .values(&new_users)
172///     .execute(connection);
173///
174/// assert_eq!(Ok(2), rows_inserted);
175/// # }
176/// ```
177///
178/// ### Using a tuple for values
179///
180/// ```rust
181/// # include!("../doctest_setup.rs");
182/// #
183/// # fn main() {
184/// #     use schema::users::dsl::*;
185/// #     let connection = &mut establish_connection();
186/// #     diesel::delete(users).execute(connection).unwrap();
187/// let new_user = (id.eq(1), name.eq("Sean"));
188/// let rows_inserted = diesel::insert_into(users)
189///     .values(&new_user)
190///     .execute(connection);
191///
192/// assert_eq!(Ok(1), rows_inserted);
193///
194/// let new_users = vec![(id.eq(2), name.eq("Tess")), (id.eq(3), name.eq("Jim"))];
195///
196/// let rows_inserted = diesel::insert_into(users)
197///     .values(&new_users)
198///     .execute(connection);
199///
200/// assert_eq!(Ok(2), rows_inserted);
201/// # }
202/// ```
203///
204/// ### Using struct for values
205///
206/// ```rust
207/// # include!("../doctest_setup.rs");
208/// # use schema::users;
209/// #
210/// #[derive(Insertable)]
211/// #[diesel(table_name = users)]
212/// struct NewUser<'a> {
213///     name: &'a str,
214/// }
215///
216/// # fn main() {
217/// #     use schema::users::dsl::*;
218/// #     let connection = &mut establish_connection();
219/// // Insert one record at a time
220///
221/// let new_user = NewUser { name: "Ruby Rhod" };
222///
223/// diesel::insert_into(users)
224///     .values(&new_user)
225///     .execute(connection)
226///     .unwrap();
227///
228/// // Insert many records
229///
230/// let new_users = vec![
231///     NewUser {
232///         name: "Leeloo Multipass",
233///     },
234///     NewUser {
235///         name: "Korben Dallas",
236///     },
237/// ];
238///
239/// let inserted_names = diesel::insert_into(users)
240///     .values(&new_users)
241///     .execute(connection)
242///     .unwrap();
243/// # }
244/// ```
245///
246/// ### Inserting default value for a column
247///
248/// You can use `Option<T>` to allow a column to be set to the default value when needed.
249///
250/// When the field is set to `None`, diesel inserts the default value on supported databases.
251/// When the field is set to `Some(..)`, diesel inserts the given value.
252///
253/// The column `color` in `brands` table is `NOT NULL DEFAULT 'Green'`.
254///
255/// ```rust
256/// # include!("../doctest_setup.rs");
257/// # #[cfg(not(feature = "sqlite"))]
258/// # use schema::brands;
259/// #
260/// # #[cfg(not(feature = "sqlite"))]
261/// #[derive(Insertable)]
262/// #[diesel(table_name = brands)]
263/// struct NewBrand {
264///     color: Option<String>,
265/// }
266///
267/// # #[cfg(not(feature = "sqlite"))]
268/// # fn main() {
269/// #     use schema::brands::dsl::*;
270/// #     let connection = &mut establish_connection();
271/// // Insert `Red`
272/// let new_brand = NewBrand {
273///     color: Some("Red".into()),
274/// };
275///
276/// diesel::insert_into(brands)
277///     .values(&new_brand)
278///     .execute(connection)
279///     .unwrap();
280///
281/// // Insert the default color
282/// let new_brand = NewBrand { color: None };
283///
284/// diesel::insert_into(brands)
285///     .values(&new_brand)
286///     .execute(connection)
287///     .unwrap();
288/// # }
289/// # #[cfg(feature = "sqlite")]
290/// # fn main() {}
291/// ```
292///
293/// ### Inserting default value for a nullable column
294///
295/// The column `accent` in `brands` table is `DEFAULT 'Green'`. It is a nullable column.
296///
297/// You can use `Option<Option<T>>` in this case.
298///
299/// When the field is set to `None`, diesel inserts the default value on supported databases.
300/// When the field is set to `Some(None)`, diesel inserts `NULL`.
301/// When the field is set to `Some(Some(..))` diesel inserts the given value.
302///
303/// ```rust
304/// # include!("../doctest_setup.rs");
305/// # #[cfg(not(feature = "sqlite"))]
306/// # use schema::brands;
307/// #
308/// # #[cfg(not(feature = "sqlite"))]
309/// #[derive(Insertable)]
310/// #[diesel(table_name = brands)]
311/// struct NewBrand {
312///     accent: Option<Option<String>>,
313/// }
314///
315/// # #[cfg(not(feature = "sqlite"))]
316/// # fn main() {
317/// #     use schema::brands::dsl::*;
318/// #     let connection = &mut establish_connection();
319/// // Insert `Red`
320/// let new_brand = NewBrand {
321///     accent: Some(Some("Red".into())),
322/// };
323///
324/// diesel::insert_into(brands)
325///     .values(&new_brand)
326///     .execute(connection)
327///     .unwrap();
328///
329/// // Insert the default accent
330/// let new_brand = NewBrand { accent: None };
331///
332/// diesel::insert_into(brands)
333///     .values(&new_brand)
334///     .execute(connection)
335///     .unwrap();
336///
337/// // Insert `NULL`
338/// let new_brand = NewBrand { accent: Some(None) };
339///
340/// diesel::insert_into(brands)
341///     .values(&new_brand)
342///     .execute(connection)
343///     .unwrap();
344/// # }
345/// # #[cfg(feature = "sqlite")]
346/// # fn main() {}
347/// ```
348///
349/// ### Insert from select
350///
351/// When inserting from a select statement,
352/// the column list can be specified with [`.into_columns`].
353/// (See also [`SelectStatement::insert_into`], which generally
354/// reads better for select statements)
355///
356/// [`SelectStatement::insert_into`]: crate::prelude::Insertable::insert_into()
357/// [`.into_columns`]: crate::query_builder::InsertStatement::into_columns()
358///
359/// ```rust
360/// # include!("../doctest_setup.rs");
361/// #
362/// # fn main() {
363/// #     run_test().unwrap();
364/// # }
365/// #
366/// # fn run_test() -> QueryResult<()> {
367/// #     use schema::{posts, users};
368/// #     let conn = &mut establish_connection();
369/// #     diesel::delete(posts::table).execute(conn)?;
370/// let new_posts = users::table.select((users::name.concat("'s First Post"), users::id));
371/// diesel::insert_into(posts::table)
372///     .values(new_posts)
373///     .into_columns((posts::title, posts::user_id))
374///     .execute(conn)?;
375///
376/// let inserted_posts = posts::table.select(posts::title).load::<String>(conn)?;
377/// let expected = vec!["Sean's First Post", "Tess's First Post"];
378/// assert_eq!(expected, inserted_posts);
379/// #     Ok(())
380/// # }
381/// ```
382///
383/// ### With return value
384///
385/// ```rust
386/// # include!("../doctest_setup.rs");
387/// #
388/// # #[cfg(feature = "postgres")]
389/// # fn main() {
390/// #     use schema::users::dsl::*;
391/// #     let connection = &mut establish_connection();
392/// let inserted_names = diesel::insert_into(users)
393///     .values(&vec![
394///         name.eq("Diva Plavalaguna"),
395///         name.eq("Father Vito Cornelius"),
396///     ])
397///     .returning(name)
398///     .get_results(connection);
399/// assert_eq!(
400///     Ok(vec![
401///         "Diva Plavalaguna".to_string(),
402///         "Father Vito Cornelius".to_string()
403///     ]),
404///     inserted_names
405/// );
406/// # }
407/// # #[cfg(not(feature = "postgres"))]
408/// # fn main() {}
409/// ```
410pub fn insert_into<T: Table>(target: T) -> IncompleteInsertStatement<T> {
411    IncompleteInsertStatement::new(target, Insert)
412}
413
414/// Creates an `INSERT [OR] IGNORE` statement.
415///
416/// If a constraint violation fails, the database will ignore the offending
417/// row and continue processing any subsequent rows. This function is only
418/// available with MySQL and SQLite.
419///
420/// With PostgreSQL, similar functionality is provided by [`on_conflict_do_nothing`].
421///
422/// [`on_conflict_do_nothing`]: crate::query_builder::InsertStatement::on_conflict_do_nothing()
423///
424/// # Example
425///
426/// ```rust
427/// # include!("../doctest_setup.rs");
428/// #
429/// # fn main() {
430/// #     run_test().unwrap();
431/// # }
432/// #
433/// # #[cfg(not(feature = "postgres"))]
434/// # fn run_test() -> QueryResult<()> {
435/// #     use schema::users::dsl::*;
436/// #     use diesel::{delete, insert_or_ignore_into};
437/// #
438/// #     let connection = &mut establish_connection();
439/// #     diesel::delete(users).execute(connection)?;
440/// insert_or_ignore_into(users)
441///     .values((id.eq(1), name.eq("Jim")))
442///     .execute(connection)?;
443///
444/// insert_or_ignore_into(users)
445///     .values(&vec![
446///         (id.eq(1), name.eq("Sean")),
447///         (id.eq(2), name.eq("Tess")),
448///     ])
449///     .execute(connection)?;
450///
451/// let names = users.select(name).order(id).load::<String>(connection)?;
452/// assert_eq!(vec![String::from("Jim"), String::from("Tess")], names);
453/// #     Ok(())
454/// # }
455/// #
456/// # #[cfg(feature = "postgres")]
457/// # fn run_test() -> QueryResult<()> {
458/// #     Ok(())
459/// # }
460/// ```
461pub fn insert_or_ignore_into<T: Table>(target: T) -> IncompleteInsertOrIgnoreStatement<T> {
462    IncompleteInsertStatement::new(target, InsertOrIgnore)
463}
464
465/// Creates a bare select statement, with no from clause. Primarily used for
466/// testing diesel itself, but likely useful for third party crates as well. The
467/// given expressions must be selectable from anywhere.
468pub fn select<T>(expression: T) -> crate::dsl::select<T>
469where
470    T: Expression,
471    crate::dsl::select<T>: AsQuery,
472{
473    SelectStatement::new(
474        SelectClause(expression),
475        super::NoFromClause,
476        NoDistinctClause,
477        super::where_clause::NoWhereClause,
478        super::order_clause::NoOrderClause,
479        super::limit_offset_clause::LimitOffsetClause {
480            limit_clause: super::limit_clause::NoLimitClause,
481            offset_clause: super::offset_clause::NoOffsetClause,
482        },
483        super::group_by_clause::NoGroupByClause,
484        super::having_clause::NoHavingClause,
485        super::locking_clause::NoLockingClause,
486    )
487}
488
489/// Creates a `REPLACE` statement.
490///
491/// If a constraint violation fails, the database will attempt to replace the
492/// offending row instead. This function is only available with MySQL and
493/// SQLite.
494///
495/// # Example
496///
497/// ```rust
498/// # include!("../doctest_setup.rs");
499/// #
500/// # #[cfg(not(feature = "postgres"))]
501/// # fn main() {
502/// #     use schema::users::dsl::*;
503/// #     use diesel::{insert_into, replace_into};
504/// #
505/// #     let conn = &mut establish_connection();
506/// #     diesel::sql_query("DELETE FROM users").execute(conn).unwrap();
507/// replace_into(users)
508///     .values(&vec![
509///         (id.eq(1), name.eq("Sean")),
510///         (id.eq(2), name.eq("Tess")),
511///     ])
512///     .execute(conn)
513///     .unwrap();
514///
515/// replace_into(users)
516///     .values((id.eq(1), name.eq("Jim")))
517///     .execute(conn)
518///     .unwrap();
519///
520/// let names = users.select(name).order(id).load::<String>(conn);
521/// assert_eq!(Ok(vec!["Jim".into(), "Tess".into()]), names);
522/// # }
523/// # #[cfg(feature = "postgres")] fn main() {}
524pub fn replace_into<T: Table>(target: T) -> IncompleteReplaceStatement<T> {
525    IncompleteInsertStatement::new(target, Replace)
526}
527
528/// Construct a full SQL query using raw SQL.
529///
530/// This function exists for cases where a query needs to be written that is not
531/// supported by the query builder. Unlike most queries in Diesel, `sql_query`
532/// will deserialize its data by name, not by index. That means that you cannot
533/// deserialize into a tuple, and structs which you deserialize from this
534/// function will need to have `#[derive(QueryableByName)]`.
535///
536/// This function is intended for use when you want to write the entire query
537/// using raw SQL. If you only need a small bit of raw SQL in your query, use
538/// [`sql`](crate::dsl::sql()) instead.
539///
540/// Query parameters can be bound into the raw query using [`SqlQuery::bind()`].
541///
542/// # Safety
543///
544/// The implementation of `QueryableByName` will assume that columns with a
545/// given name will have a certain type. The compiler will be unable to verify
546/// that the given type is correct. If your query returns a column of an
547/// unexpected type, the result may have the wrong value, or return an error.
548///
549/// # Examples
550///
551/// ```rust
552/// # include!("../doctest_setup.rs");
553/// #
554/// # use schema::users;
555/// #
556/// # #[derive(QueryableByName, Debug, PartialEq)]
557/// # struct User {
558/// #     id: i32,
559/// #     name: String,
560/// # }
561/// #
562/// # fn main() {
563/// #     run_test_1().unwrap();
564/// #     run_test_2().unwrap();
565/// # }
566/// #
567/// # fn run_test_1() -> QueryResult<()> {
568/// #     use diesel::sql_query;
569/// #     use diesel::sql_types::{Integer, Text};
570/// #
571/// #     let connection = &mut establish_connection();
572/// let users = sql_query("SELECT * FROM users ORDER BY id").load(connection);
573/// let expected_users = vec![
574///     User {
575///         id: 1,
576///         name: "Sean".into(),
577///     },
578///     User {
579///         id: 2,
580///         name: "Tess".into(),
581///     },
582/// ];
583/// assert_eq!(Ok(expected_users), users);
584/// #     Ok(())
585/// # }
586///
587/// # fn run_test_2() -> QueryResult<()> {
588/// #     use diesel::sql_query;
589/// #     use diesel::sql_types::{Integer, Text};
590/// #
591/// #     let connection = &mut establish_connection();
592/// #     diesel::insert_into(users::table)
593/// #         .values(users::name.eq("Jim"))
594/// #         .execute(connection).unwrap();
595/// #     #[cfg(feature = "postgres")]
596/// #     let users = sql_query("SELECT * FROM users WHERE id > $1 AND name != $2");
597/// #     #[cfg(not(feature = "postgres"))]
598/// // Checkout the documentation of your database for the correct
599/// // bind placeholder
600/// let users = sql_query("SELECT * FROM users WHERE id > ? AND name <> ?");
601/// let users = users
602///     .bind::<Integer, _>(1)
603///     .bind::<Text, _>("Tess")
604///     .get_results(connection);
605/// let expected_users = vec![User {
606///     id: 3,
607///     name: "Jim".into(),
608/// }];
609/// assert_eq!(Ok(expected_users), users);
610/// #     Ok(())
611/// # }
612/// ```
613/// [`SqlQuery::bind()`]: crate::query_builder::SqlQuery::bind()
614pub fn sql_query<T: Into<String>>(query: T) -> SqlQuery {
615    SqlQuery::from_sql(query.into())
616}
617
618#[cfg(feature = "postgres_backend")]
619pub use crate::pg::query_builder::copy::copy_from::copy_from;
620#[cfg(feature = "postgres_backend")]
621pub use crate::pg::query_builder::copy::copy_to::copy_to;
diesel/query_builder/functions.rs

diesel/query_builder/
functions.rs
