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