drizzle_sqlite/builder/mod.rs
1//! The typed `SQLite` query builder.
2//!
3//! [`QueryBuilder`] builds SELECT, INSERT, UPDATE and DELETE statements (with
4//! optional common table expressions) and renders them to [`SQL`]. The
5//! submodules hold each statement's builder states. The `drizzle` crate's
6//! drivers wrap this builder to run the queries.
7
8use drizzle_core::Token;
9pub use drizzle_core::builder::{BuilderInit, ExecutableState};
10pub use drizzle_core::{
11 OrderBy, SQL, ToSQL,
12 traits::{SQLSchema, SQLTable},
13};
14
15// Local imports
16use crate::{common::SQLiteSchemaType, traits::SQLiteTable, values::SQLiteValue};
17use core::{fmt::Debug, marker::PhantomData};
18
19// Import modules - these provide specific builder types
20pub mod cte;
21pub mod delete;
22pub mod insert;
23pub mod prepared;
24pub mod select;
25pub mod update;
26
27// Re-export CTE types
28pub use cte::{CTEDefinition, CTEView};
29
30// Export state markers for easier use
31pub use delete::{DeleteInitial, DeleteReturningSet, DeleteWhereSet};
32pub use insert::{
33 InsertColumnsSet, InsertDoUpdateSet, InsertInitial, InsertOnConflictSet, InsertReturningSet,
34 InsertValuesSet, OnConflictBuilder,
35};
36pub use select::{
37 SelectFromSet, SelectGroupSet, SelectInitial, SelectJoinSet, SelectLimitSet, SelectOffsetSet,
38 SelectOrderSet, SelectSetOpSet, SelectWhereSet,
39};
40pub use update::{UpdateInitial, UpdateReturningSet, UpdateSetClauseSet, UpdateWhereSet};
41
42/// Builder state after [`QueryBuilder::with`]: the next call starts the
43/// statement that uses the common table expressions.
44#[derive(Debug, Clone)]
45pub struct CTEInit;
46
47impl ExecutableState for CTEInit {}
48
49/// Type-safe SQL query builder for `SQLite`.
50///
51/// Start with [`QueryBuilder::new`], then call [`select`](Self::select),
52/// [`insert`](Self::insert), [`update`](Self::update),
53/// [`delete`](Self::delete) or [`with`](Self::with). Each method returns a
54/// builder in a new state, and each state only offers the clauses that may
55/// come next, so an out-of-order query does not compile. Call
56/// [`ToSQL::to_sql`] to get the SQL text and its bound parameters.
57///
58/// [`SelectBuilder`](select::SelectBuilder),
59/// [`InsertBuilder`](insert::InsertBuilder),
60/// [`UpdateBuilder`](update::UpdateBuilder) and
61/// [`DeleteBuilder`](delete::DeleteBuilder) are aliases of this type and
62/// document the clause order of each statement.
63///
64/// # Type parameters
65///
66/// - `Schema`: the schema the query runs against.
67/// - `State`: which clauses have been added so far (for example
68/// [`SelectWhereSet`]).
69/// - `Table`: the table the last FROM or JOIN added, or the target table of
70/// an INSERT, UPDATE or DELETE.
71/// - `Marker`: the selected columns and the tables in scope; used to check
72/// column references and to infer the row type.
73/// - `Row`: the Rust type of one result row.
74/// - `Grouped`: the GROUP BY columns, used to check which columns may be
75/// selected outside an aggregate.
76///
77/// # Examples
78///
79/// ```
80/// # mod drizzle {
81/// # pub mod core { pub use drizzle_core::*; }
82/// # pub mod error { pub use drizzle_core::error::*; }
83/// # pub mod types { pub use drizzle_types::*; }
84/// # pub mod migrations { pub use drizzle_migrations::*; }
85/// # pub use drizzle_types::Dialect;
86/// # pub use drizzle_types as ddl;
87/// # pub mod sqlite {
88/// # pub use drizzle_sqlite::*;
89/// # #[cfg(feature = "rusqlite")]
90/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
91/// # #[cfg(feature = "libsql")]
92/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
93/// # #[cfg(feature = "turso")]
94/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
95/// # pub mod prelude {
96/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
97/// # pub use drizzle_sqlite::{*, attrs::*};
98/// # pub use drizzle_core::*;
99/// # }
100/// # }
101/// # }
102/// use drizzle::sqlite::prelude::*;
103/// use drizzle::sqlite::builder::QueryBuilder;
104///
105/// #[SQLiteTable(name = "users")]
106/// struct User {
107/// #[column(primary)]
108/// id: i32,
109/// name: String,
110/// }
111///
112/// #[derive(SQLiteSchema)]
113/// struct Schema {
114/// user: User,
115/// }
116///
117/// let builder = QueryBuilder::new::<Schema>();
118/// let Schema { user } = Schema::new();
119///
120/// let query = builder.select(user.name).from(user);
121/// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
122/// ```
123///
124/// SELECT:
125/// ```rust
126/// # mod drizzle {
127/// # pub mod core { pub use drizzle_core::*; }
128/// # pub mod error { pub use drizzle_core::error::*; }
129/// # pub mod types { pub use drizzle_types::*; }
130/// # pub mod migrations { pub use drizzle_migrations::*; }
131/// # pub use drizzle_types::Dialect;
132/// # pub use drizzle_types as ddl;
133/// # pub mod sqlite {
134/// # pub use drizzle_sqlite::*;
135/// # #[cfg(feature = "rusqlite")]
136/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
137/// # #[cfg(feature = "libsql")]
138/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
139/// # #[cfg(feature = "turso")]
140/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
141/// # pub mod prelude {
142/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
143/// # pub use drizzle_sqlite::{*, attrs::*};
144/// # pub use drizzle_core::*;
145/// # }
146/// # }
147/// # }
148/// # use drizzle::sqlite::prelude::*;
149/// # use drizzle::core::expr::gt;
150/// # use drizzle::sqlite::builder::QueryBuilder;
151/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
152/// # #[derive(SQLiteSchema)] struct Schema { user: User }
153/// # let builder = QueryBuilder::new::<Schema>();
154/// # let Schema { user } = Schema::new();
155/// let query = builder.select((user.id, user.name)).from(user).r#where(gt(user.id, 10));
156/// assert_eq!(
157/// query.to_sql().sql(),
158/// r#"SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?"#
159/// );
160/// ```
161///
162/// INSERT:
163/// ```rust
164/// # mod drizzle {
165/// # pub mod core { pub use drizzle_core::*; }
166/// # pub mod error { pub use drizzle_core::error::*; }
167/// # pub mod types { pub use drizzle_types::*; }
168/// # pub mod migrations { pub use drizzle_migrations::*; }
169/// # pub use drizzle_types::Dialect;
170/// # pub use drizzle_types as ddl;
171/// # pub mod sqlite {
172/// # pub use drizzle_sqlite::*;
173/// # #[cfg(feature = "rusqlite")]
174/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
175/// # #[cfg(feature = "libsql")]
176/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
177/// # #[cfg(feature = "turso")]
178/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
179/// # pub mod prelude {
180/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
181/// # pub use drizzle_sqlite::{*, attrs::*};
182/// # pub use drizzle_core::*;
183/// # }
184/// # }
185/// # }
186/// # use drizzle::sqlite::prelude::*;
187/// # use drizzle::sqlite::builder::QueryBuilder;
188/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
189/// # #[derive(SQLiteSchema)] struct Schema { user: User }
190/// # let builder = QueryBuilder::new::<Schema>();
191/// # let Schema { user } = Schema::new();
192/// let query = builder
193/// .insert(user)
194/// .values([InsertUser::new("Alice")]);
195/// assert_eq!(query.to_sql().sql(), r#"INSERT INTO "users" ("name") VALUES (?)"#);
196/// ```
197///
198/// UPDATE:
199/// ```rust
200/// # mod drizzle {
201/// # pub mod core { pub use drizzle_core::*; }
202/// # pub mod error { pub use drizzle_core::error::*; }
203/// # pub mod types { pub use drizzle_types::*; }
204/// # pub mod migrations { pub use drizzle_migrations::*; }
205/// # pub use drizzle_types::Dialect;
206/// # pub use drizzle_types as ddl;
207/// # pub mod sqlite {
208/// # pub use drizzle_sqlite::*;
209/// # #[cfg(feature = "rusqlite")]
210/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
211/// # #[cfg(feature = "libsql")]
212/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
213/// # #[cfg(feature = "turso")]
214/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
215/// # pub mod prelude {
216/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
217/// # pub use drizzle_sqlite::{*, attrs::*};
218/// # pub use drizzle_core::*;
219/// # }
220/// # }
221/// # }
222/// # use drizzle::sqlite::prelude::*;
223/// # use drizzle::core::expr::eq;
224/// # use drizzle::sqlite::builder::QueryBuilder;
225/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
226/// # #[derive(SQLiteSchema)] struct Schema { user: User }
227/// # let builder = QueryBuilder::new::<Schema>();
228/// # let Schema { user } = Schema::new();
229/// let query = builder
230/// .update(user)
231/// .set(UpdateUser::default().with_name("Bob"))
232/// .r#where(eq(user.id, 1));
233/// assert_eq!(query.to_sql().sql(), r#"UPDATE "users" SET "name" = ? WHERE "users"."id" = ?"#);
234/// ```
235///
236/// DELETE:
237/// ```rust
238/// # mod drizzle {
239/// # pub mod core { pub use drizzle_core::*; }
240/// # pub mod error { pub use drizzle_core::error::*; }
241/// # pub mod types { pub use drizzle_types::*; }
242/// # pub mod migrations { pub use drizzle_migrations::*; }
243/// # pub use drizzle_types::Dialect;
244/// # pub use drizzle_types as ddl;
245/// # pub mod sqlite {
246/// # pub use drizzle_sqlite::*;
247/// # #[cfg(feature = "rusqlite")]
248/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
249/// # #[cfg(feature = "libsql")]
250/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
251/// # #[cfg(feature = "turso")]
252/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
253/// # pub mod prelude {
254/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
255/// # pub use drizzle_sqlite::{*, attrs::*};
256/// # pub use drizzle_core::*;
257/// # }
258/// # }
259/// # }
260/// # use drizzle::sqlite::prelude::*;
261/// # use drizzle::core::expr::lt;
262/// # use drizzle::sqlite::builder::QueryBuilder;
263/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
264/// # #[derive(SQLiteSchema)] struct Schema { user: User }
265/// # let builder = QueryBuilder::new::<Schema>();
266/// # let Schema { user } = Schema::new();
267/// let query = builder
268/// .delete(user)
269/// .r#where(lt(user.id, 10));
270/// assert_eq!(query.to_sql().sql(), r#"DELETE FROM "users" WHERE "users"."id" < ?"#);
271/// ```
272///
273/// Common table expressions (WITH). [`into_cte`](Self::into_cte) turns a
274/// SELECT into a CTE whose columns you can reference like a table's:
275///
276/// ```rust
277/// # mod drizzle {
278/// # pub mod core { pub use drizzle_core::*; }
279/// # pub mod error { pub use drizzle_core::error::*; }
280/// # pub mod types { pub use drizzle_types::*; }
281/// # pub mod migrations { pub use drizzle_migrations::*; }
282/// # pub use drizzle_types::Dialect;
283/// # pub use drizzle_types as ddl;
284/// # pub mod sqlite {
285/// # pub use drizzle_sqlite::*;
286/// # #[cfg(feature = "rusqlite")]
287/// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
288/// # #[cfg(feature = "libsql")]
289/// # pub mod libsql { pub use ::libsql::{Row, Value}; }
290/// # #[cfg(feature = "turso")]
291/// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
292/// # pub mod prelude {
293/// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
294/// # pub use drizzle_sqlite::{*, attrs::*};
295/// # pub use drizzle_core::*;
296/// # }
297/// # }
298/// # }
299/// # use drizzle::sqlite::prelude::*;
300/// # use drizzle::sqlite::builder::QueryBuilder;
301/// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
302/// # #[derive(SQLiteSchema)] struct Schema { user: User }
303/// # let builder = QueryBuilder::new::<Schema>();
304/// # let Schema { user } = Schema::new();
305/// # struct ActiveUsersTag;
306/// # impl drizzle::core::Tag for ActiveUsersTag {
307/// # const NAME: &'static str = "active_users";
308/// # }
309/// let active_users = builder
310/// .select((user.id, user.name))
311/// .from(user)
312/// .into_cte::<ActiveUsersTag>();
313///
314/// // The CTE derefs to an aliased `User` table, so its columns are typed.
315/// let query = builder
316/// .with(&active_users)
317/// .select(active_users.name)
318/// .from(&active_users);
319/// assert_eq!(
320/// query.to_sql().sql(),
321/// r#"WITH "active_users" AS (SELECT "users"."id", "users"."name" FROM "users") SELECT "active_users"."name" FROM "active_users""#
322/// );
323/// ```
324#[derive(Debug, Clone, Default)]
325pub struct QueryBuilder<
326 'a,
327 Schema = (),
328 State = (),
329 Table = (),
330 Marker = (),
331 Row = (),
332 Grouped = (),
333> {
334 /// The SQL built so far.
335 pub sql: SQL<'a, SQLiteValue<'a>>,
336 schema: PhantomData<Schema>,
337 state: PhantomData<State>,
338 table: PhantomData<Table>,
339 marker: PhantomData<Marker>,
340 row: PhantomData<Row>,
341 grouped: PhantomData<Grouped>,
342}
343
344//------------------------------------------------------------------------------
345// QueryBuilder Implementation
346//------------------------------------------------------------------------------
347
348impl<'a, Schema, State, Table, Marker, Row, Grouped> ToSQL<'a, SQLiteValue<'a>>
349 for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>
350{
351 fn to_sql(&self) -> SQL<'a, SQLiteValue<'a>> {
352 self.sql.clone()
353 }
354}
355
356impl<'a, Schema, State, Table, Marker, Row, Grouped>
357 QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>
358where
359 State: ExecutableState,
360{
361 /// Prepends a [sqlcommenter](https://google.github.io/sqlcommenter/)
362 /// comment (`/*...*/`) to the query.
363 ///
364 /// `/*` and `*/` inside `text` are broken up (`/ *`, `* /`) so the text
365 /// cannot end the comment early. An empty `text` leaves the query
366 /// unchanged.
367 ///
368 /// # Examples
369 ///
370 /// ```rust
371 /// # mod drizzle {
372 /// # pub mod core { pub use drizzle_core::*; }
373 /// # pub mod error { pub use drizzle_core::error::*; }
374 /// # pub mod types { pub use drizzle_types::*; }
375 /// # pub mod migrations { pub use drizzle_migrations::*; }
376 /// # pub use drizzle_types::Dialect;
377 /// # pub use drizzle_types as ddl;
378 /// # pub mod sqlite {
379 /// # pub use drizzle_sqlite::*;
380 /// # #[cfg(feature = "rusqlite")]
381 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
382 /// # #[cfg(feature = "libsql")]
383 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
384 /// # #[cfg(feature = "turso")]
385 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
386 /// # pub mod prelude {
387 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
388 /// # pub use drizzle_sqlite::{*, attrs::*};
389 /// # pub use drizzle_core::*;
390 /// # }
391 /// # }
392 /// # }
393 /// # use drizzle::sqlite::prelude::*;
394 /// # use drizzle::sqlite::builder::QueryBuilder;
395 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
396 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
397 /// # let builder = QueryBuilder::new::<Schema>();
398 /// # let Schema { user } = Schema::new();
399 /// let query = builder.select(user.id).from(user).comment("list users");
400 /// assert_eq!(query.to_sql().sql(), r#"/*list users*/ SELECT "users"."id" FROM "users""#);
401 /// ```
402 #[must_use]
403 pub fn comment(mut self, text: impl AsRef<str>) -> Self {
404 let fragment = drizzle_core::sql::comment::<SQLiteValue<'a>>(text);
405 if fragment.chunks.is_empty() {
406 return self;
407 }
408 let existing = core::mem::replace(&mut self.sql, fragment);
409 self.sql.append_mut(existing);
410 self
411 }
412
413 /// Prepends a tag-style [sqlcommenter](https://google.github.io/sqlcommenter/)
414 /// comment to the query.
415 ///
416 /// Each `(key, value)` pair is URL-encoded and written as `key='value'`.
417 /// Pairs are sorted and joined with `,`. Pairs with an empty value are
418 /// skipped; if none remain, the query is unchanged.
419 ///
420 /// # Examples
421 ///
422 /// ```rust
423 /// # mod drizzle {
424 /// # pub mod core { pub use drizzle_core::*; }
425 /// # pub mod error { pub use drizzle_core::error::*; }
426 /// # pub mod types { pub use drizzle_types::*; }
427 /// # pub mod migrations { pub use drizzle_migrations::*; }
428 /// # pub use drizzle_types::Dialect;
429 /// # pub use drizzle_types as ddl;
430 /// # pub mod sqlite {
431 /// # pub use drizzle_sqlite::*;
432 /// # #[cfg(feature = "rusqlite")]
433 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
434 /// # #[cfg(feature = "libsql")]
435 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
436 /// # #[cfg(feature = "turso")]
437 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
438 /// # pub mod prelude {
439 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
440 /// # pub use drizzle_sqlite::{*, attrs::*};
441 /// # pub use drizzle_core::*;
442 /// # }
443 /// # }
444 /// # }
445 /// # use drizzle::sqlite::prelude::*;
446 /// # use drizzle::sqlite::builder::QueryBuilder;
447 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
448 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
449 /// # let builder = QueryBuilder::new::<Schema>();
450 /// # let Schema { user } = Schema::new();
451 /// let query = builder
452 /// .select(user.id)
453 /// .from(user)
454 /// .comment_tags([("route", "/users"), ("action", "list")]);
455 /// assert_eq!(
456 /// query.to_sql().sql(),
457 /// r#"/*action='list',route='%2Fusers'*/ SELECT "users"."id" FROM "users""#
458 /// );
459 /// ```
460 #[must_use]
461 pub fn comment_tags<I, K, V>(mut self, pairs: I) -> Self
462 where
463 I: IntoIterator<Item = (K, V)>,
464 K: AsRef<str>,
465 V: AsRef<str>,
466 {
467 let fragment = drizzle_core::sql::comment_tags::<SQLiteValue<'a>, _, _, _>(pairs);
468 if fragment.chunks.is_empty() {
469 return self;
470 }
471 let existing = core::mem::replace(&mut self.sql, fragment);
472 self.sql.append_mut(existing);
473 self
474 }
475}
476
477impl<'a> QueryBuilder<'a> {
478 /// Creates a query builder for the schema `S`.
479 ///
480 /// # Examples
481 ///
482 /// ```rust
483 /// # mod drizzle {
484 /// # pub mod core { pub use drizzle_core::*; }
485 /// # pub mod error { pub use drizzle_core::error::*; }
486 /// # pub mod types { pub use drizzle_types::*; }
487 /// # pub mod migrations { pub use drizzle_migrations::*; }
488 /// # pub use drizzle_types::Dialect;
489 /// # pub use drizzle_types as ddl;
490 /// # pub mod sqlite {
491 /// # pub use drizzle_sqlite::*;
492 /// # #[cfg(feature = "rusqlite")]
493 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
494 /// # #[cfg(feature = "libsql")]
495 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
496 /// # #[cfg(feature = "turso")]
497 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
498 /// # pub mod prelude {
499 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
500 /// # pub use drizzle_sqlite::{*, attrs::*};
501 /// # pub use drizzle_core::*;
502 /// # }
503 /// # }
504 /// # }
505 /// use drizzle::sqlite::prelude::*;
506 /// use drizzle::sqlite::builder::QueryBuilder;
507 ///
508 /// #[SQLiteTable(name = "users")]
509 /// struct User {
510 /// #[column(primary)]
511 /// id: i32,
512 /// name: String,
513 /// }
514 ///
515 /// #[derive(SQLiteSchema)]
516 /// struct MySchema {
517 /// user: User,
518 /// }
519 ///
520 /// let builder = QueryBuilder::new::<MySchema>();
521 /// ```
522 #[must_use]
523 pub const fn new<S>() -> QueryBuilder<'a, S, BuilderInit> {
524 QueryBuilder {
525 sql: SQL::empty(),
526 schema: PhantomData,
527 state: PhantomData,
528 table: PhantomData,
529 marker: PhantomData,
530 row: PhantomData,
531 grouped: PhantomData,
532 }
533 }
534}
535
536impl<'a, Schema> QueryBuilder<'a, Schema, BuilderInit> {
537 /// Starts a SELECT with the given columns.
538 ///
539 /// Pass one column or expression, a tuple of them, or `()` to select
540 /// every column of the FROM table (and of joined tables). Call
541 /// [`from`](select::SelectBuilder::from) next.
542 ///
543 /// # Examples
544 ///
545 /// ```rust
546 /// # mod drizzle {
547 /// # pub mod core { pub use drizzle_core::*; }
548 /// # pub mod error { pub use drizzle_core::error::*; }
549 /// # pub mod types { pub use drizzle_types::*; }
550 /// # pub mod migrations { pub use drizzle_migrations::*; }
551 /// # pub use drizzle_types::Dialect;
552 /// # pub use drizzle_types as ddl;
553 /// # pub mod sqlite {
554 /// # pub use drizzle_sqlite::*;
555 /// # #[cfg(feature = "rusqlite")]
556 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
557 /// # #[cfg(feature = "libsql")]
558 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
559 /// # #[cfg(feature = "turso")]
560 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
561 /// # pub mod prelude {
562 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
563 /// # pub use drizzle_sqlite::{*, attrs::*};
564 /// # pub use drizzle_core::*;
565 /// # }
566 /// # }
567 /// # }
568 /// # use drizzle::sqlite::prelude::*;
569 /// # use drizzle::sqlite::builder::QueryBuilder;
570 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
571 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
572 /// # let builder = QueryBuilder::new::<Schema>();
573 /// # let Schema { user } = Schema::new();
574 /// // Select a single column
575 /// let query = builder.select(user.name).from(user);
576 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);
577 ///
578 /// // Select multiple columns
579 /// let query = builder.select((user.id, user.name)).from(user);
580 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."id", "users"."name" FROM "users""#);
581 ///
582 /// // Select every column
583 /// let query = builder.select(()).from(user);
584 /// assert_eq!(query.to_sql().sql(), r#"SELECT "users"."id", "users"."name" FROM "users""#);
585 /// ```
586 pub fn select<T>(
587 &self,
588 columns: T,
589 ) -> select::SelectBuilder<'a, Schema, select::SelectInitial, (), T::Marker>
590 where
591 T: ToSQL<'a, SQLiteValue<'a>> + drizzle_core::IntoSelectTarget,
592 {
593 let sql = crate::helpers::select(columns);
594 select::SelectBuilder {
595 sql,
596 schema: PhantomData,
597 state: PhantomData,
598 table: PhantomData,
599 marker: PhantomData,
600 row: PhantomData,
601 grouped: PhantomData,
602 }
603 }
604
605 /// Starts a SELECT DISTINCT, which drops duplicate rows from the result.
606 ///
607 /// # Examples
608 ///
609 /// ```rust
610 /// # mod drizzle {
611 /// # pub mod core { pub use drizzle_core::*; }
612 /// # pub mod error { pub use drizzle_core::error::*; }
613 /// # pub mod types { pub use drizzle_types::*; }
614 /// # pub mod migrations { pub use drizzle_migrations::*; }
615 /// # pub use drizzle_types::Dialect;
616 /// # pub use drizzle_types as ddl;
617 /// # pub mod sqlite {
618 /// # pub use drizzle_sqlite::*;
619 /// # #[cfg(feature = "rusqlite")]
620 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
621 /// # #[cfg(feature = "libsql")]
622 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
623 /// # #[cfg(feature = "turso")]
624 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
625 /// # pub mod prelude {
626 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
627 /// # pub use drizzle_sqlite::{*, attrs::*};
628 /// # pub use drizzle_core::*;
629 /// # }
630 /// # }
631 /// # }
632 /// # use drizzle::sqlite::prelude::*;
633 /// # use drizzle::sqlite::builder::QueryBuilder;
634 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
635 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
636 /// # let builder = QueryBuilder::new::<Schema>();
637 /// # let Schema { user } = Schema::new();
638 /// let query = builder.select_distinct(user.name).from(user);
639 /// assert_eq!(query.to_sql().sql(), r#"SELECT DISTINCT "users"."name" FROM "users""#);
640 /// ```
641 pub fn select_distinct<T>(
642 &self,
643 columns: T,
644 ) -> select::SelectBuilder<'a, Schema, select::SelectInitial, (), T::Marker>
645 where
646 T: ToSQL<'a, SQLiteValue<'a>> + drizzle_core::IntoSelectTarget,
647 {
648 let sql = crate::helpers::select_distinct(columns);
649 select::SelectBuilder {
650 sql,
651 schema: PhantomData,
652 state: PhantomData,
653 table: PhantomData,
654 marker: PhantomData,
655 row: PhantomData,
656 grouped: PhantomData,
657 }
658 }
659}
660
661impl<'a, Schema> QueryBuilder<'a, Schema, CTEInit> {
662 /// Starts a SELECT after the WITH clause.
663 ///
664 /// See [`QueryBuilder::with`] for an example.
665 pub fn select<T>(
666 &self,
667 columns: T,
668 ) -> select::SelectBuilder<'a, Schema, select::SelectInitial, (), T::Marker>
669 where
670 T: ToSQL<'a, SQLiteValue<'a>> + drizzle_core::IntoSelectTarget,
671 {
672 let sql = self.sql.clone().append(crate::helpers::select(columns));
673 select::SelectBuilder {
674 sql,
675 schema: PhantomData,
676 state: PhantomData,
677 table: PhantomData,
678 marker: PhantomData,
679 row: PhantomData,
680 grouped: PhantomData,
681 }
682 }
683
684 /// Starts a SELECT DISTINCT after the WITH clause.
685 pub fn select_distinct<T>(
686 &self,
687 columns: T,
688 ) -> select::SelectBuilder<'a, Schema, select::SelectInitial, (), T::Marker>
689 where
690 T: ToSQL<'a, SQLiteValue<'a>> + drizzle_core::IntoSelectTarget,
691 {
692 let sql = self
693 .sql
694 .clone()
695 .append(crate::helpers::select_distinct(columns));
696 select::SelectBuilder {
697 sql,
698 schema: PhantomData,
699 state: PhantomData,
700 table: PhantomData,
701 marker: PhantomData,
702 row: PhantomData,
703 grouped: PhantomData,
704 }
705 }
706
707 /// Starts an INSERT after the WITH clause.
708 pub fn insert<Table>(
709 &self,
710 table: Table,
711 ) -> insert::InsertBuilder<'a, Schema, insert::InsertInitial, Table>
712 where
713 Table: SQLiteTable<'a>,
714 {
715 let sql = self.sql.clone().append(crate::helpers::insert::<
716 Table,
717 SQLiteSchemaType,
718 SQLiteValue<'a>,
719 >(&table));
720
721 insert::InsertBuilder {
722 sql,
723 schema: PhantomData,
724 state: PhantomData,
725 table: PhantomData,
726 marker: PhantomData,
727 row: PhantomData,
728 grouped: PhantomData,
729 }
730 }
731
732 /// Starts an UPDATE after the WITH clause.
733 pub fn update<Table>(
734 &self,
735 table: Table,
736 ) -> update::UpdateBuilder<'a, Schema, update::UpdateInitial, Table>
737 where
738 Table: SQLiteTable<'a>,
739 {
740 let sql = self.sql.clone().append(crate::helpers::update::<
741 Table,
742 SQLiteSchemaType,
743 SQLiteValue<'a>,
744 >(&table));
745
746 update::UpdateBuilder {
747 sql,
748 schema: PhantomData,
749 state: PhantomData,
750 table: PhantomData,
751 marker: PhantomData,
752 row: PhantomData,
753 grouped: PhantomData,
754 }
755 }
756
757 /// Starts a DELETE after the WITH clause.
758 pub fn delete<Table>(
759 &self,
760 table: Table,
761 ) -> delete::DeleteBuilder<'a, Schema, delete::DeleteInitial, Table>
762 where
763 Table: SQLiteTable<'a>,
764 {
765 let sql = self.sql.clone().append(crate::helpers::delete::<
766 Table,
767 SQLiteSchemaType,
768 SQLiteValue<'a>,
769 >(&table));
770
771 delete::DeleteBuilder {
772 sql,
773 schema: PhantomData,
774 state: PhantomData,
775 table: PhantomData,
776 marker: PhantomData,
777 row: PhantomData,
778 grouped: PhantomData,
779 }
780 }
781
782 /// Adds another common table expression to the WITH clause.
783 #[must_use]
784 pub fn with<C>(&self, cte: &C) -> Self
785 where
786 C: CTEDefinition<'a>,
787 {
788 let sql = self
789 .sql
790 .clone()
791 .push(Token::COMMA)
792 .append(cte.cte_definition());
793 QueryBuilder {
794 sql,
795 schema: PhantomData,
796 state: PhantomData,
797 table: PhantomData,
798 marker: PhantomData,
799 row: PhantomData,
800 grouped: PhantomData,
801 }
802 }
803}
804
805impl<'a, Schema> QueryBuilder<'a, Schema, BuilderInit> {
806 /// Starts an INSERT into `table`.
807 ///
808 /// Call [`values`](insert::InsertBuilder::values),
809 /// [`value`](insert::InsertBuilder::value),
810 /// [`select`](insert::InsertBuilder::select) or
811 /// [`columns`](insert::InsertBuilder::columns) next.
812 ///
813 /// # Examples
814 ///
815 /// ```rust
816 /// # mod drizzle {
817 /// # pub mod core { pub use drizzle_core::*; }
818 /// # pub mod error { pub use drizzle_core::error::*; }
819 /// # pub mod types { pub use drizzle_types::*; }
820 /// # pub mod migrations { pub use drizzle_migrations::*; }
821 /// # pub use drizzle_types::Dialect;
822 /// # pub use drizzle_types as ddl;
823 /// # pub mod sqlite {
824 /// # pub use drizzle_sqlite::*;
825 /// # #[cfg(feature = "rusqlite")]
826 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
827 /// # #[cfg(feature = "libsql")]
828 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
829 /// # #[cfg(feature = "turso")]
830 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
831 /// # pub mod prelude {
832 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
833 /// # pub use drizzle_sqlite::{*, attrs::*};
834 /// # pub use drizzle_core::*;
835 /// # }
836 /// # }
837 /// # }
838 /// # use drizzle::sqlite::prelude::*;
839 /// # use drizzle::sqlite::builder::QueryBuilder;
840 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
841 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
842 /// # let builder = QueryBuilder::new::<Schema>();
843 /// # let Schema { user } = Schema::new();
844 /// let query = builder
845 /// .insert(user)
846 /// .values([InsertUser::new("Alice")]);
847 /// assert_eq!(query.to_sql().sql(), r#"INSERT INTO "users" ("name") VALUES (?)"#);
848 /// ```
849 pub fn insert<Table>(
850 &self,
851 table: Table,
852 ) -> insert::InsertBuilder<'a, Schema, insert::InsertInitial, Table>
853 where
854 Table: SQLiteTable<'a>,
855 {
856 let sql = crate::helpers::insert::<Table, SQLiteSchemaType, SQLiteValue<'a>>(&table);
857
858 insert::InsertBuilder {
859 sql,
860 schema: PhantomData,
861 state: PhantomData,
862 table: PhantomData,
863 marker: PhantomData,
864 row: PhantomData,
865 grouped: PhantomData,
866 }
867 }
868
869 /// Starts an UPDATE of `table`. Call [`set`](update::UpdateBuilder::set) next.
870 ///
871 /// # Examples
872 ///
873 /// ```rust
874 /// # mod drizzle {
875 /// # pub mod core { pub use drizzle_core::*; }
876 /// # pub mod error { pub use drizzle_core::error::*; }
877 /// # pub mod types { pub use drizzle_types::*; }
878 /// # pub mod migrations { pub use drizzle_migrations::*; }
879 /// # pub use drizzle_types::Dialect;
880 /// # pub use drizzle_types as ddl;
881 /// # pub mod sqlite {
882 /// # pub use drizzle_sqlite::*;
883 /// # #[cfg(feature = "rusqlite")]
884 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
885 /// # #[cfg(feature = "libsql")]
886 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
887 /// # #[cfg(feature = "turso")]
888 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
889 /// # pub mod prelude {
890 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
891 /// # pub use drizzle_sqlite::{*, attrs::*};
892 /// # pub use drizzle_core::*;
893 /// # }
894 /// # }
895 /// # }
896 /// # use drizzle::sqlite::prelude::*;
897 /// # use drizzle::core::expr::eq;
898 /// # use drizzle::sqlite::builder::QueryBuilder;
899 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
900 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
901 /// # let builder = QueryBuilder::new::<Schema>();
902 /// # let Schema { user } = Schema::new();
903 /// let query = builder
904 /// .update(user)
905 /// .set(UpdateUser::default().with_name("Bob"))
906 /// .r#where(eq(user.id, 1));
907 /// assert_eq!(query.to_sql().sql(), r#"UPDATE "users" SET "name" = ? WHERE "users"."id" = ?"#);
908 /// ```
909 pub fn update<Table>(
910 &self,
911 table: Table,
912 ) -> update::UpdateBuilder<'a, Schema, update::UpdateInitial, Table>
913 where
914 Table: SQLiteTable<'a>,
915 {
916 let sql = crate::helpers::update::<Table, SQLiteSchemaType, SQLiteValue<'a>>(&table);
917
918 update::UpdateBuilder {
919 sql,
920 schema: PhantomData,
921 state: PhantomData,
922 table: PhantomData,
923 marker: PhantomData,
924 row: PhantomData,
925 grouped: PhantomData,
926 }
927 }
928
929 /// Starts a DELETE from `table`.
930 ///
931 /// Without `where` the statement
932 /// deletes every row.
933 ///
934 /// # Examples
935 ///
936 /// ```rust
937 /// # mod drizzle {
938 /// # pub mod core { pub use drizzle_core::*; }
939 /// # pub mod error { pub use drizzle_core::error::*; }
940 /// # pub mod types { pub use drizzle_types::*; }
941 /// # pub mod migrations { pub use drizzle_migrations::*; }
942 /// # pub use drizzle_types::Dialect;
943 /// # pub use drizzle_types as ddl;
944 /// # pub mod sqlite {
945 /// # pub use drizzle_sqlite::*;
946 /// # #[cfg(feature = "rusqlite")]
947 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
948 /// # #[cfg(feature = "libsql")]
949 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
950 /// # #[cfg(feature = "turso")]
951 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
952 /// # pub mod prelude {
953 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
954 /// # pub use drizzle_sqlite::{*, attrs::*};
955 /// # pub use drizzle_core::*;
956 /// # }
957 /// # }
958 /// # }
959 /// # use drizzle::sqlite::prelude::*;
960 /// # use drizzle::core::expr::lt;
961 /// # use drizzle::sqlite::builder::QueryBuilder;
962 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
963 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
964 /// # let builder = QueryBuilder::new::<Schema>();
965 /// # let Schema { user } = Schema::new();
966 /// let query = builder
967 /// .delete(user)
968 /// .r#where(lt(user.id, 10));
969 /// assert_eq!(query.to_sql().sql(), r#"DELETE FROM "users" WHERE "users"."id" < ?"#);
970 /// ```
971 pub fn delete<Table>(
972 &self,
973 table: Table,
974 ) -> delete::DeleteBuilder<'a, Schema, delete::DeleteInitial, Table>
975 where
976 Table: SQLiteTable<'a>,
977 {
978 let sql = crate::helpers::delete::<Table, SQLiteSchemaType, SQLiteValue<'a>>(&table);
979
980 delete::DeleteBuilder {
981 sql,
982 schema: PhantomData,
983 state: PhantomData,
984 table: PhantomData,
985 marker: PhantomData,
986 row: PhantomData,
987 grouped: PhantomData,
988 }
989 }
990
991 /// Starts a WITH clause with one common table expression.
992 ///
993 /// Build the CTE with [`into_cte`](select::SelectBuilder::into_cte). Add
994 /// more CTEs with another `.with(..)`, then start the main statement.
995 ///
996 /// # Examples
997 ///
998 /// ```rust
999 /// # mod drizzle {
1000 /// # pub mod core { pub use drizzle_core::*; }
1001 /// # pub mod error { pub use drizzle_core::error::*; }
1002 /// # pub mod types { pub use drizzle_types::*; }
1003 /// # pub mod migrations { pub use drizzle_migrations::*; }
1004 /// # pub use drizzle_types::Dialect;
1005 /// # pub use drizzle_types as ddl;
1006 /// # pub mod sqlite {
1007 /// # pub use drizzle_sqlite::*;
1008 /// # #[cfg(feature = "rusqlite")]
1009 /// # pub mod rusqlite { pub use ::rusqlite::{Error, Result, Row, types}; }
1010 /// # #[cfg(feature = "libsql")]
1011 /// # pub mod libsql { pub use ::libsql::{Row, Value}; }
1012 /// # #[cfg(feature = "turso")]
1013 /// # pub mod turso { pub use ::turso::{Error, IntoValue, Result, Row, Value}; }
1014 /// # pub mod prelude {
1015 /// # pub use drizzle_macros::{SQLiteTable, SQLiteSchema};
1016 /// # pub use drizzle_sqlite::{*, attrs::*};
1017 /// # pub use drizzle_core::*;
1018 /// # }
1019 /// # }
1020 /// # }
1021 /// # use drizzle::sqlite::prelude::*;
1022 /// # use drizzle::core::expr::gt;
1023 /// # use drizzle::sqlite::builder::QueryBuilder;
1024 /// # #[SQLiteTable(name = "users")] struct User { #[column(primary)] id: i32, name: String }
1025 /// # #[derive(SQLiteSchema)] struct Schema { user: User }
1026 /// # let builder = QueryBuilder::new::<Schema>();
1027 /// # let Schema { user } = Schema::new();
1028 /// struct Recent;
1029 /// impl drizzle::core::Tag for Recent {
1030 /// const NAME: &'static str = "recent";
1031 /// }
1032 ///
1033 /// let recent = builder
1034 /// .select((user.id, user.name))
1035 /// .from(user)
1036 /// .r#where(gt(user.id, 100))
1037 /// .into_cte::<Recent>();
1038 ///
1039 /// let query = builder.with(&recent).select(recent.name).from(&recent);
1040 /// assert_eq!(
1041 /// query.to_sql().sql(),
1042 /// r#"WITH "recent" AS (SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?) SELECT "recent"."name" FROM "recent""#
1043 /// );
1044 /// ```
1045 pub fn with<C>(&self, cte: &C) -> QueryBuilder<'a, Schema, CTEInit>
1046 where
1047 C: CTEDefinition<'a>,
1048 {
1049 let sql = SQL::from(Token::WITH).append(cte.cte_definition());
1050 QueryBuilder {
1051 sql,
1052 schema: PhantomData,
1053 state: PhantomData,
1054 table: PhantomData,
1055 marker: PhantomData,
1056 row: PhantomData,
1057 grouped: PhantomData,
1058 }
1059 }
1060}
1061
1062#[cfg(test)]
1063mod tests {
1064 use super::*;
1065
1066 #[test]
1067 fn test_query_builder_new() {
1068 let qb = QueryBuilder::new::<()>();
1069 let sql = qb.to_sql();
1070 assert_eq!(sql.sql(), "");
1071 assert_eq!(sql.params().count(), 0);
1072 }
1073
1074 #[test]
1075 fn test_builder_init_type() {
1076 let _state = BuilderInit;
1077 }
1078}