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