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!(old_count.map(|count| count - 1), users.count().first(connection));
111/// # Ok(())
112/// # }
113/// ```
114///
115/// ### Deleting a whole table:
116///
117/// ```rust
118/// # include!("../doctest_setup.rs");
119/// #
120/// # fn main() {
121/// # delete();
122/// # }
123/// #
124/// # fn delete() -> QueryResult<()> {
125/// # use schema::users::dsl::*;
126/// # let connection = &mut establish_connection();
127/// diesel::delete(users).execute(connection)?;
128/// assert_eq!(Ok(0), users.count().first::<i64>(connection));
129/// # Ok(())
130/// # }
131/// ```
132pub fn delete<T: IntoUpdateTarget>(source: T) -> DeleteStatement<T::Table, T::WhereClause> {
133 let target = source.into_update_target();
134 DeleteStatement::new(target.table, target.where_clause)
135}
136
137/// Creates an `INSERT` statement for the target table.
138///
139/// You may add data by calling [`values()`] or [`default_values()`]
140/// as shown in the examples.
141///
142/// [`values()`]: crate::query_builder::IncompleteInsertStatement::values()
143/// [`default_values()`]: crate::query_builder::IncompleteInsertStatement::default_values()
144///
145/// Backends that support the `RETURNING` clause, such as PostgreSQL,
146/// can return the inserted rows by calling [`.get_results`] instead of [`.execute`].
147///
148/// [`.get_results`]: crate::query_dsl::RunQueryDsl::get_results()
149/// [`.execute`]: crate::query_dsl::RunQueryDsl::execute
150///
151/// # Examples
152///
153/// ```rust
154/// # include!("../doctest_setup.rs");
155/// #
156/// # fn main() {
157/// # use schema::users::dsl::*;
158/// # let connection = &mut establish_connection();
159/// let rows_inserted = diesel::insert_into(users)
160/// .values(&name.eq("Sean"))
161/// .execute(connection);
162///
163/// assert_eq!(Ok(1), rows_inserted);
164///
165/// let new_users = vec![
166/// name.eq("Tess"),
167/// name.eq("Jim"),
168/// ];
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![
195/// (id.eq(2), name.eq("Tess")),
196/// (id.eq(3), name.eq("Jim")),
197/// ];
198///
199/// let rows_inserted = diesel::insert_into(users)
200/// .values(&new_users)
201/// .execute(connection);
202///
203/// assert_eq!(Ok(2), rows_inserted);
204/// # }
205/// ```
206///
207/// ### Using struct for values
208///
209/// ```rust
210/// # include!("../doctest_setup.rs");
211/// # use schema::users;
212/// #
213/// #[derive(Insertable)]
214/// #[diesel(table_name = users)]
215/// struct NewUser<'a> {
216/// name: &'a str,
217/// }
218///
219/// # fn main() {
220/// # use schema::users::dsl::*;
221/// # let connection = &mut establish_connection();
222/// // Insert one record at a time
223///
224/// let new_user = NewUser { name: "Ruby Rhod" };
225///
226/// diesel::insert_into(users)
227/// .values(&new_user)
228/// .execute(connection)
229/// .unwrap();
230///
231/// // Insert many records
232///
233/// let new_users = vec![
234/// NewUser { name: "Leeloo Multipass" },
235/// NewUser { name: "Korben Dallas" },
236/// ];
237///
238/// let inserted_names = diesel::insert_into(users)
239/// .values(&new_users)
240/// .execute(connection)
241/// .unwrap();
242/// # }
243/// ```
244///
245/// ### Inserting default value for a column
246///
247/// You can use `Option<T>` to allow a column to be set to the default value when needed.
248///
249/// When the field is set to `None`, diesel inserts the default value on supported databases.
250/// When the field is set to `Some(..)`, diesel inserts the given value.
251///
252/// The column `color` in `brands` table is `NOT NULL DEFAULT 'Green'`.
253///
254/// ```rust
255/// # include!("../doctest_setup.rs");
256/// # #[cfg(not(feature = "sqlite"))]
257/// # use schema::brands;
258/// #
259/// # #[cfg(not(feature = "sqlite"))]
260/// #[derive(Insertable)]
261/// #[diesel(table_name = brands)]
262/// struct NewBrand {
263/// color: Option<String>,
264/// }
265///
266/// # #[cfg(not(feature = "sqlite"))]
267/// # fn main() {
268/// # use schema::brands::dsl::*;
269/// # let connection = &mut establish_connection();
270/// // Insert `Red`
271/// let new_brand = NewBrand { color: Some("Red".into()) };
272///
273/// diesel::insert_into(brands)
274/// .values(&new_brand)
275/// .execute(connection)
276/// .unwrap();
277///
278/// // Insert the default color
279/// let new_brand = NewBrand { color: None };
280///
281/// diesel::insert_into(brands)
282/// .values(&new_brand)
283/// .execute(connection)
284/// .unwrap();
285/// # }
286/// # #[cfg(feature = "sqlite")]
287/// # fn main() {}
288/// ```
289///
290/// ### Inserting default value for a nullable column
291///
292/// The column `accent` in `brands` table is `DEFAULT 'Green'`. It is a nullable column.
293///
294/// You can use `Option<Option<T>>` in this case.
295///
296/// When the field is set to `None`, diesel inserts the default value on supported databases.
297/// When the field is set to `Some(None)`, diesel inserts `NULL`.
298/// When the field is set to `Some(Some(..))` diesel inserts the given value.
299///
300/// ```rust
301/// # include!("../doctest_setup.rs");
302/// # #[cfg(not(feature = "sqlite"))]
303/// # use schema::brands;
304/// #
305/// # #[cfg(not(feature = "sqlite"))]
306/// #[derive(Insertable)]
307/// #[diesel(table_name = brands)]
308/// struct NewBrand {
309/// accent: Option<Option<String>>,
310/// }
311///
312/// # #[cfg(not(feature = "sqlite"))]
313/// # fn main() {
314/// # use schema::brands::dsl::*;
315/// # let connection = &mut establish_connection();
316/// // Insert `Red`
317/// let new_brand = NewBrand { accent: Some(Some("Red".into())) };
318///
319/// diesel::insert_into(brands)
320/// .values(&new_brand)
321/// .execute(connection)
322/// .unwrap();
323///
324/// // Insert the default accent
325/// let new_brand = NewBrand { accent: None };
326///
327/// diesel::insert_into(brands)
328/// .values(&new_brand)
329/// .execute(connection)
330/// .unwrap();
331///
332/// // Insert `NULL`
333/// let new_brand = NewBrand { accent: Some(None) };
334///
335/// diesel::insert_into(brands)
336/// .values(&new_brand)
337/// .execute(connection)
338/// .unwrap();
339/// # }
340/// # #[cfg(feature = "sqlite")]
341/// # fn main() {}
342/// ```
343///
344/// ### Insert from select
345///
346/// When inserting from a select statement,
347/// the column list can be specified with [`.into_columns`].
348/// (See also [`SelectStatement::insert_into`], which generally
349/// reads better for select statements)
350///
351/// [`SelectStatement::insert_into`]: crate::prelude::Insertable::insert_into()
352/// [`.into_columns`]: crate::query_builder::InsertStatement::into_columns()
353///
354/// ```rust
355/// # include!("../doctest_setup.rs");
356/// #
357/// # fn main() {
358/// # run_test().unwrap();
359/// # }
360/// #
361/// # fn run_test() -> QueryResult<()> {
362/// # use schema::{posts, users};
363/// # let conn = &mut establish_connection();
364/// # diesel::delete(posts::table).execute(conn)?;
365/// let new_posts = users::table
366/// .select((
367/// users::name.concat("'s First Post"),
368/// users::id,
369/// ));
370/// diesel::insert_into(posts::table)
371/// .values(new_posts)
372/// .into_columns((posts::title, posts::user_id))
373/// .execute(conn)?;
374///
375/// let inserted_posts = posts::table
376/// .select(posts::title)
377/// .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!(Ok(vec!["Diva Plavalaguna".to_string(), "Father Vito Cornelius".to_string()]), inserted_names);
401/// # }
402/// # #[cfg(not(feature = "postgres"))]
403/// # fn main() {}
404/// ```
405pub fn insert_into<T: Table>(target: T) -> IncompleteInsertStatement<T> {
406 IncompleteInsertStatement::new(target, Insert)
407}
408
409/// Creates an `INSERT [OR] IGNORE` statement.
410///
411/// If a constraint violation fails, the database will ignore the offending
412/// row and continue processing any subsequent rows. This function is only
413/// available with MySQL and SQLite.
414///
415/// With PostgreSQL, similar functionality is provided by [`on_conflict_do_nothing`].
416///
417/// [`on_conflict_do_nothing`]: crate::query_builder::InsertStatement::on_conflict_do_nothing()
418///
419/// # Example
420///
421/// ```rust
422/// # include!("../doctest_setup.rs");
423/// #
424/// # fn main() {
425/// # run_test().unwrap();
426/// # }
427/// #
428/// # #[cfg(not(feature = "postgres"))]
429/// # fn run_test() -> QueryResult<()> {
430/// # use schema::users::dsl::*;
431/// # use diesel::{delete, insert_or_ignore_into};
432/// #
433/// # let connection = &mut establish_connection();
434/// # diesel::delete(users).execute(connection)?;
435/// insert_or_ignore_into(users)
436/// .values((id.eq(1), name.eq("Jim")))
437/// .execute(connection)?;
438///
439/// insert_or_ignore_into(users)
440/// .values(&vec![
441/// (id.eq(1), name.eq("Sean")),
442/// (id.eq(2), name.eq("Tess")),
443/// ])
444/// .execute(connection)?;
445///
446/// let names = users.select(name).order(id).load::<String>(connection)?;
447/// assert_eq!(vec![String::from("Jim"), String::from("Tess")], names);
448/// # Ok(())
449/// # }
450/// #
451/// # #[cfg(feature = "postgres")]
452/// # fn run_test() -> QueryResult<()> {
453/// # Ok(())
454/// # }
455/// ```
456pub fn insert_or_ignore_into<T: Table>(target: T) -> IncompleteInsertOrIgnoreStatement<T> {
457 IncompleteInsertStatement::new(target, InsertOrIgnore)
458}
459
460/// Creates a bare select statement, with no from clause. Primarily used for
461/// testing diesel itself, but likely useful for third party crates as well. The
462/// given expressions must be selectable from anywhere.
463pub fn select<T>(expression: T) -> crate::dsl::select<T>
464where
465 T: Expression,
466 crate::dsl::select<T>: AsQuery,
467{
468 SelectStatement::new(
469 SelectClause(expression),
470 super::NoFromClause,
471 NoDistinctClause,
472 super::where_clause::NoWhereClause,
473 super::order_clause::NoOrderClause,
474 super::limit_offset_clause::LimitOffsetClause {
475 limit_clause: super::limit_clause::NoLimitClause,
476 offset_clause: super::offset_clause::NoOffsetClause,
477 },
478 super::group_by_clause::NoGroupByClause,
479 super::having_clause::NoHavingClause,
480 super::locking_clause::NoLockingClause,
481 )
482}
483
484/// Creates a `REPLACE` statement.
485///
486/// If a constraint violation fails, the database will attempt to replace the
487/// offending row instead. This function is only available with MySQL and
488/// SQLite.
489///
490/// # Example
491///
492/// ```rust
493/// # include!("../doctest_setup.rs");
494/// #
495/// # #[cfg(not(feature = "postgres"))]
496/// # fn main() {
497/// # use schema::users::dsl::*;
498/// # use diesel::{insert_into, replace_into};
499/// #
500/// # let conn = &mut establish_connection();
501/// # diesel::sql_query("DELETE FROM users").execute(conn).unwrap();
502/// replace_into(users)
503/// .values(&vec![
504/// (id.eq(1), name.eq("Sean")),
505/// (id.eq(2), name.eq("Tess")),
506/// ])
507/// .execute(conn)
508/// .unwrap();
509///
510/// replace_into(users)
511/// .values((id.eq(1), name.eq("Jim")))
512/// .execute(conn)
513/// .unwrap();
514///
515/// let names = users.select(name).order(id).load::<String>(conn);
516/// assert_eq!(Ok(vec!["Jim".into(), "Tess".into()]), names);
517/// # }
518/// # #[cfg(feature = "postgres")] fn main() {}
519pub fn replace_into<T: Table>(target: T) -> IncompleteReplaceStatement<T> {
520 IncompleteInsertStatement::new(target, Replace)
521}
522
523/// Construct a full SQL query using raw SQL.
524///
525/// This function exists for cases where a query needs to be written that is not
526/// supported by the query builder. Unlike most queries in Diesel, `sql_query`
527/// will deserialize its data by name, not by index. That means that you cannot
528/// deserialize into a tuple, and structs which you deserialize from this
529/// function will need to have `#[derive(QueryableByName)]`.
530///
531/// This function is intended for use when you want to write the entire query
532/// using raw SQL. If you only need a small bit of raw SQL in your query, use
533/// [`sql`](crate::dsl::sql()) instead.
534///
535/// Query parameters can be bound into the raw query using [`SqlQuery::bind()`].
536///
537/// # Safety
538///
539/// The implementation of `QueryableByName` will assume that columns with a
540/// given name will have a certain type. The compiler will be unable to verify
541/// that the given type is correct. If your query returns a column of an
542/// unexpected type, the result may have the wrong value, or return an error.
543///
544/// # Examples
545///
546/// ```rust
547/// # include!("../doctest_setup.rs");
548/// #
549/// # use schema::users;
550/// #
551/// # #[derive(QueryableByName, Debug, PartialEq)]
552/// # struct User {
553/// # id: i32,
554/// # name: String,
555/// # }
556/// #
557/// # fn main() {
558/// # run_test_1().unwrap();
559/// # run_test_2().unwrap();
560/// # }
561/// #
562/// # fn run_test_1() -> QueryResult<()> {
563/// # use diesel::sql_query;
564/// # use diesel::sql_types::{Integer, Text};
565/// #
566/// # let connection = &mut establish_connection();
567/// let users = sql_query("SELECT * FROM users ORDER BY id")
568/// .load(connection);
569/// let expected_users = vec![
570/// User { id: 1, name: "Sean".into() },
571/// User { id: 2, name: "Tess".into() },
572/// ];
573/// assert_eq!(Ok(expected_users), users);
574/// # Ok(())
575/// # }
576///
577/// # fn run_test_2() -> QueryResult<()> {
578/// # use diesel::sql_query;
579/// # use diesel::sql_types::{Integer, Text};
580/// #
581/// # let connection = &mut establish_connection();
582/// # diesel::insert_into(users::table)
583/// # .values(users::name.eq("Jim"))
584/// # .execute(connection).unwrap();
585/// # #[cfg(feature = "postgres")]
586/// # let users = sql_query("SELECT * FROM users WHERE id > $1 AND name != $2");
587/// # #[cfg(not(feature = "postgres"))]
588/// // Checkout the documentation of your database for the correct
589/// // bind placeholder
590/// let users = sql_query("SELECT * FROM users WHERE id > ? AND name <> ?");
591/// let users = users
592/// .bind::<Integer, _>(1)
593/// .bind::<Text, _>("Tess")
594/// .get_results(connection);
595/// let expected_users = vec![
596/// User { id: 3, name: "Jim".into() },
597/// ];
598/// assert_eq!(Ok(expected_users), users);
599/// # Ok(())
600/// # }
601/// ```
602/// [`SqlQuery::bind()`]: crate::query_builder::SqlQuery::bind()
603pub fn sql_query<T: Into<String>>(query: T) -> SqlQuery {
604 SqlQuery::from_sql(query.into())
605}
606
607#[cfg(feature = "postgres_backend")]
608pub use crate::pg::query_builder::copy::copy_from::copy_from;
609#[cfg(feature = "postgres_backend")]
610pub use crate::pg::query_builder::copy::copy_to::copy_to;