Struct QueryBuilder 

pub struct QueryBuilder<'a, Schema = (), State = (), Table = (), Marker = (), Row = (), Grouped = ()> {
    pub sql: SQL<'a, SQLiteValue<'a>>,
    /* private fields */
}

Type-safe SQL query builder for SQLite.

Start with QueryBuilder::new, then call select, insert, update, delete or with. Each method returns a builder in a new state, and each state only offers the clauses that may come next, so an out-of-order query does not compile. Call ToSQL::to_sql to get the SQL text and its bound parameters.

SelectBuilder, InsertBuilder, UpdateBuilder and DeleteBuilder are aliases of this type and document the clause order of each statement.

Type parameters

• Schema: the schema the query runs against.
• State: which clauses have been added so far (for example SelectWhereSet).
• Table: the table the last FROM or JOIN added, or the target table of an INSERT, UPDATE or DELETE.
• Marker: the selected columns and the tables in scope; used to check column references and to infer the row type.
• Row: the Rust type of one result row.
• Grouped: the GROUP BY columns, used to check which columns may be selected outside an aggregate.

Examples

use drizzle::sqlite::prelude::*;
use drizzle::sqlite::builder::QueryBuilder;

#[SQLiteTable(name = "users")]
struct User {
    #[column(primary)]
    id: i32,
    name: String,
}

#[derive(SQLiteSchema)]
struct Schema {
    user: User,
}

let builder = QueryBuilder::new::<Schema>();
let Schema { user } = Schema::new();

let query = builder.select(user.name).from(user);
assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);

SELECT:

let query = builder.select((user.id, user.name)).from(user).r#where(gt(user.id, 10));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?"#
);

INSERT:

let query = builder
    .insert(user)
    .values([InsertUser::new("Alice")]);
assert_eq!(query.to_sql().sql(), r#"INSERT INTO "users" ("name") VALUES (?)"#);

UPDATE:

let query = builder
    .update(user)
    .set(UpdateUser::default().with_name("Bob"))
    .r#where(eq(user.id, 1));
assert_eq!(query.to_sql().sql(), r#"UPDATE "users" SET "name" = ? WHERE "users"."id" = ?"#);

DELETE:

let query = builder
    .delete(user)
    .r#where(lt(user.id, 10));
assert_eq!(query.to_sql().sql(), r#"DELETE FROM "users" WHERE "users"."id" < ?"#);

Common table expressions (WITH). into_cte turns a SELECT into a CTE whose columns you can reference like a table’s:

let active_users = builder
    .select((user.id, user.name))
    .from(user)
    .into_cte::<ActiveUsersTag>();

// The CTE derefs to an aliased `User` table, so its columns are typed.
let query = builder
    .with(&active_users)
    .select(active_users.name)
    .from(&active_users);
assert_eq!(
    query.to_sql().sql(),
    r#"WITH "active_users" AS (SELECT "users"."id", "users"."name" FROM "users") SELECT "active_users"."name" FROM "active_users""#
);

Fields

sql: SQL<'a, SQLiteValue<'a>>

The SQL built so far.

Implementations

impl<'a, S, T> QueryBuilder<'a, S, DeleteInitial, T>

pub fn where<E, ScopeProof>( self, condition: E, ) -> DeleteBuilder<'a, S, DeleteWhereSet, T>

where E: ExprSources + Expr<'a, SQLiteValue<'a>>, E::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, E::SQLType: BooleanLike,

Adds a WHERE clause that picks the rows to delete.

A DELETE runs only with a WHERE clause, so a forgotten condition does not empty the table; r#where(true) deletes every row. The condition must be a boolean expression over the target table’s columns.

Examples

// Delete specific row by ID
let query = builder
    .delete(user)
    .r#where(eq(user.id, 1));
assert_eq!(query.to_sql().sql(), r#"DELETE FROM "users" WHERE "users"."id" = ?"#);

// Delete with complex conditions
let query = builder
    .delete(user)
    .r#where(and(
        gt(user.id, 100),
        or(eq(user.name, "test"), eq(user.age, 0))
    ));
assert_eq!(
    query.to_sql().sql(),
    r#"DELETE FROM "users" WHERE ("users"."id" > ? AND ("users"."name" = ? OR "users"."age" = ?))"#
);

impl<'a, S, T> QueryBuilder<'a, S, DeleteWhereSet, T>

pub fn returning<Columns, ScopeProof>( self, columns: Columns, ) -> DeleteBuilder<'a, S, DeleteReturningSet, T, Scoped<<Columns as IntoSelectTarget>::Marker, Cons<T, Nil>>, <<Columns as IntoSelectTarget>::Marker as ResolveRow<T>>::Row>

where Columns: ExprSources + ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget, Columns::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, Columns::Marker: ResolveRow<T>,

Adds a RETURNING clause after WHERE. See returning.

impl<'a, Schema, Table> QueryBuilder<'a, Schema, InsertInitial, Table>

where Table: SQLiteTable<'a>,

pub fn value<T>( self, value: Table::Insert<T>, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Table::Insert<T>: SQLModel<'a, SQLiteValue<'a>>,

Inserts one row. Same as values([value]).

value is the table’s generated insert model (for example InsertUser).

pub fn values<I, T>( self, values: I, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where I: IntoIterator<Item = Table::Insert<T>>, Table::Insert<T>: SQLModel<'a, SQLiteValue<'a>>,

Inserts one or more rows.

Each item is the table’s generated insert model (for example InsertUser). All rows must set the same columns: the insert model’s type tracks which columns are set, so rows built with different setters do not type-check together. If no column is set, this renders DEFAULT VALUES for one row (several such rows insert NULL into rowid, which a WITHOUT ROWID table rejects).

Examples

let query = builder.insert(user).values([
    InsertUser::new("Alice").with_email("alice@example.com"),
    InsertUser::new("Bob").with_email("bob@example.com"),
]);
assert_eq!(
    query.to_sql().sql(),
    r#"INSERT INTO "users" ("name", "email") VALUES (?, ?), (?, ?)"#
);

pub fn columns<Columns>( self, columns: Columns, ) -> InsertBuilder<'a, Schema, InsertColumnsSet<Columns::Columns>, Table>

where Columns: InsertTargetColumns<'a, SQLiteValue<'a>, Table>,

Lists the target columns for an INSERT ... SELECT.

Pass a tuple of the table’s columns, then call select. The column list must include every required column (one without a default), and the SELECT must produce matching types in the same order; both are checked at compile time.

Panics

Panics when the same column appears more than once.

pub fn select<Q, R, ScopeProof, AggProof>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Table: InsertSelectTable, Q: IntoSelectQuery<'a, Schema, R>, Q::Marker: InsertSelectCompatible<'a, SQLiteValue<'a>, Table, R> + MarkerScopeValidFor<ScopeProof> + MarkerAggValidFor<Q::Grouped, AggProof>,

Inserts the rows of a SELECT into every insertable column of the table.

The SELECT must produce one value per insertable column, in table order, with compatible types and nullability. Its column references and aggregates are also checked. To fill only some columns, call columns first.

Examples

let query = builder
    .insert(archived_user)
    .select(builder.select((user.id, user.name)).from(user));
assert_eq!(
    query.to_sql().sql(),
    r#"INSERT INTO "archived_users" ("id", "name") SELECT "users"."id", "users"."name" FROM "users""#
);

pub fn select_raw<Q>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Q: ToSQL<'a, SQLiteValue<'a>>,

Appends any SQL as the row source, without a column list.

Nothing is checked: not the column count, types, nullability, column scope or aggregates. Prefer select.

impl<'a, Schema, Table, Targets> QueryBuilder<'a, Schema, InsertColumnsSet<Targets>, Table>

where Table: SQLiteTable<'a> + InsertSelectTable,

pub fn select<Q, R, RequiredProof, ScopeProof, AggProof>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Targets: IncludesRequired<Table::RequiredColumns, RequiredProof>, Q: IntoSelectQuery<'a, Schema, R>, Q::Marker: PartialInsertSelectCompatible<'a, SQLiteValue<'a>, Targets> + MarkerScopeValidFor<ScopeProof> + MarkerAggValidFor<Q::Grouped, AggProof>,

Inserts the rows of a SELECT into the columns chosen with columns.

The SELECT must produce one value per chosen column, in the same order, with compatible types and nullability.

Examples

let query = builder
    .insert(archived_user)
    .columns(archived_user.name)
    .select(builder.select(user.name).from(user));
assert_eq!(
    query.to_sql().sql(),
    r#"INSERT INTO "archived_users" ("name") SELECT "users"."name" FROM "users""#
);

pub fn select_raw<Q, RequiredProof>( self, query: Q, ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>

where Targets: IncludesRequired<Table::RequiredColumns, RequiredProof>, Q: ToSQL<'a, SQLiteValue<'a>>,

Appends any SQL as the row source for the chosen columns.

Only the column list is checked (it must include every required column). The SQL itself is not checked. Prefer select.

impl<'a, S, T> QueryBuilder<'a, S, InsertValuesSet, T>

pub fn on_conflict<C: ConflictTarget<T>>( self, target: C, ) -> OnConflictBuilder<'a, S, T>

Starts an ON CONFLICT (target) clause.

The target can be a primary key or unique column, the primary key, a unique constraint, or a unique index of this table; anything else does not compile. For a partial unique index, its WHERE predicate is repeated after the target so SQLite can match the index. Finish the clause with do_nothing() or do_update(update_model). After do_update you may add a where and then returning.

When the row source is a SELECT that ends in its FROM clause, a WHERE true is added before ON CONFLICT so SQLite does not parse ON as a join condition.

Examples

fn main() {
use drizzle::sqlite::prelude::*;
use drizzle::sqlite::builder::QueryBuilder;

#[SQLiteTable(name = "users")]
struct User {
    #[column(primary)]
    id: i32,
    name: String,
    #[column(unique)]
    email: Option<String>,
}

#[derive(SQLiteSchema)]
struct Schema {
    user: User,
}

let builder = QueryBuilder::new::<Schema>();
let schema = Schema::new();
let user = schema.user;

let query = builder
    .insert(user)
    .values([InsertUser::new("Alice")])
    .on_conflict(user.id)
    .do_nothing();
assert_eq!(
    query.to_sql().sql(),
    r#"INSERT INTO "users" ("name") VALUES (?) ON CONFLICT ("id") DO NOTHING"#
);

let query = builder
    .insert(user)
    .values([InsertUser::new("Alice").with_email("a@example.com")])
    .on_conflict(user.email)
    .do_update(UpdateUser::default().with_name("Alice"));
assert_eq!(
    query.to_sql().sql(),
    r#"INSERT INTO "users" ("name", "email") VALUES (?, ?) ON CONFLICT ("email") DO UPDATE SET "name" = ?"#
);
}

pub fn on_conflict_do_nothing( self, ) -> InsertBuilder<'a, S, InsertOnConflictSet, T>

Adds ON CONFLICT DO NOTHING with no target, which skips a row that violates any unique or primary key constraint.

pub fn returning<Columns, ScopeProof>( self, columns: Columns, ) -> InsertBuilder<'a, S, InsertReturningSet, T, Scoped<<Columns as IntoSelectTarget>::Marker, Cons<T, Nil>>, <<Columns as IntoSelectTarget>::Marker as ResolveRow<T>>::Row>

where Columns: ExprSources + ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget, Columns::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, Columns::Marker: ResolveRow<T>,

Adds a RETURNING clause that reads columns of the inserted rows.

Pass one column or expression, a tuple, or () for every column (RETURNING *). Only columns of the target table may be used; other tables do not compile. The row type is inferred like a SELECT’s.

impl<'a, S, T> QueryBuilder<'a, S, InsertOnConflictSet, T>

pub fn returning<Columns, ScopeProof>( self, columns: Columns, ) -> InsertBuilder<'a, S, InsertReturningSet, T, Scoped<<Columns as IntoSelectTarget>::Marker, Cons<T, Nil>>, <<Columns as IntoSelectTarget>::Marker as ResolveRow<T>>::Row>

where Columns: ExprSources + ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget, Columns::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, Columns::Marker: ResolveRow<T>,

Adds a RETURNING clause after the conflict clause. See returning.

impl<'a, S, T> QueryBuilder<'a, S, InsertDoUpdateSet, T>

pub fn where<E, ScopeProof>( self, condition: E, ) -> InsertBuilder<'a, S, InsertOnConflictSet, T>

where E: ExprSources + Expr<'a, SQLiteValue<'a>>, E::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, E::SQLType: BooleanLike,

Adds a WHERE to DO UPDATE SET, so the update only runs for conflicting rows that match.

Renders ON CONFLICT (..) DO UPDATE SET .. WHERE condition. The condition may only reference the target table.

pub fn returning<Columns, ScopeProof>( self, columns: Columns, ) -> InsertBuilder<'a, S, InsertReturningSet, T, Scoped<<Columns as IntoSelectTarget>::Marker, Cons<T, Nil>>, <<Columns as IntoSelectTarget>::Marker as ResolveRow<T>>::Row>

where Columns: ExprSources + ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget, Columns::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, Columns::Marker: ResolveRow<T>,

Adds a RETURNING clause after DO UPDATE SET. See returning.

impl<'a, S, M> QueryBuilder<'a, S, SelectInitial, (), M>

pub fn from<T>( self, query: T, ) -> SelectBuilder<'a, S, SelectFromSet, T, FromMarker<M, T>, <M as ResolveRow<T>>::Row>

where T: ToSQL<'a, SQLiteValue<'a>> + ScopeEntry, M: ResolveRow<T>,

Sets the FROM source: a table, a CTE, or a derived table made with alias.

The result row type is inferred from the selected columns and this source. With select(()), the row is the table’s generated select model.

Examples

// Select from a table
let query = builder.select(user.name).from(user);
assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Join>,

pub fn join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>,

Adds a JOIN (an inner join).

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. A derived table (see alias) also works in the tuple form.

The other join methods (left_join, right_join, full_join, inner_join, the _outer and natural_ variants, and cross_join) take the same arguments. After a LEFT, RIGHT or FULL join, selected columns of the side that may be missing decode as Option; with select(()), that side’s whole model is an Option in the row.

The condition may only read tables already in the query; this is checked when the query is executed.

Examples

let query = builder
    .select((user.name, post.title))
    .from(user)
    .join((post, eq(user.id, post.user_id)));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "users"."name", "posts"."title" FROM "users" JOIN "posts" ON "users"."id" = "posts"."user_id""#
);

pub fn natural_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, InnerJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, InnerJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, InnerJoin>,

Adds a natural join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn natural_left_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, LeftJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, LeftJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, LeftJoin>,

Adds a natural_left join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn left_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>,

Adds a left join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn left_outer_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, LeftJoin, J::OnSources>,

Adds a left_outer join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn natural_left_outer_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, LeftJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, LeftJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, LeftJoin>,

Adds a natural_left_outer join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn natural_right_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, RightJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, RightJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, RightJoin>,

Adds a natural_right join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn right_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>,

Adds a right join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn right_outer_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, RightJoin, J::OnSources>,

Adds a right_outer join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn natural_right_outer_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, RightJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, RightJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, RightJoin>,

Adds a natural_right_outer join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn natural_full_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, FullJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, FullJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, FullJoin>,

Adds a natural_full join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn full_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, FullJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, FullJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, FullJoin, J::OnSources>,

Adds a full join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn full_outer_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, FullJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, FullJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, FullJoin, J::OnSources>,

Adds a full_outer join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn natural_full_outer_join<J: JoinSource<'a>>( self, source: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, FullJoin>>::Marker, <M as JoinStep<R, J::JoinedTable, FullJoin>>::Row, G>

where M: JoinStep<R, J::JoinedTable, FullJoin>,

Adds a natural_full_outer join (NATURAL).

A natural join matches the columns both sides share by name, so it takes a table or derived table and no ON condition.

pub fn inner_join<J: JoinArg<'a, T, Via>, Via>( self, arg: J, ) -> SelectBuilder<'a, S, SelectJoinSet, J::JoinedTable, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Marker, <M as JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>>::Row, G>

where M: JoinStep<R, J::JoinedTable, InnerJoin, J::OnSources>,

Adds a inner join.

Pass (table, condition) for an explicit ON condition, or a bare table to join on its foreign key to the previous table. See join for an example.

pub fn cross_join<Arg: CrossJoinArg<'a, T>>( self, arg: Arg, ) -> SelectBuilder<'a, S, SelectJoinSet, Arg::JoinedTable, <M as JoinStep<R, Arg::JoinedTable, InnerJoin, Arg::OnSources>>::Marker, <M as JoinStep<R, Arg::JoinedTable, InnerJoin, Arg::OnSources>>::Row, G>

where M: JoinStep<R, Arg::JoinedTable, InnerJoin, Arg::OnSources>,

Adds a CROSS JOIN, which pairs every row with every row of arg.

A bare table renders CROSS JOIN. For backwards compatibility, (table, condition) renders the equivalent INNER JOIN ... ON ....

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: SelectClause<Where>,

pub fn where<E>( self, condition: E, ) -> SelectBuilder<'a, S, SelectWhereSet, T, <M as HasScope>::With<E::Sources>, R, G>

where M: HasScope, E: Expr<'a, SQLiteValue<'a>>, E::SQLType: BooleanLike,

Adds a WHERE clause.

The condition must be a boolean expression. Combine conditions with and and or from drizzle_core::expr.

Examples

// Single condition
let query = builder
    .select(user.name)
    .from(user)
    .r#where(gt(user.id, 10));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "users"."name" FROM "users" WHERE "users"."id" > ?"#
);

// Multiple conditions
let query = builder
    .select(user.name)
    .from(user)
    .r#where(and(gt(user.id, 10), eq(user.name, "Alice")));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "users"."name" FROM "users" WHERE ("users"."id" > ? AND "users"."name" = ?)"#
);

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<GroupBy>,

pub fn group_by<Gr>( self, columns: Gr, ) -> SelectBuilder<'a, S, SelectGroupSet, T, <M as HasScope>::With<Gr::Sources>, R, Gr::Columns>

where M: HasScope, Gr: IntoGroupBy<'a, SQLiteValue<'a>>,

Adds a GROUP BY clause. Pass one expression or a tuple.

Every selected column that is not inside an aggregate must appear in the GROUP BY list; this is checked when the query is executed. One exception: grouping by a table’s single-column primary key determines the whole row, so any column of that table may be selected. Prefer .group_by(table.pk) over listing every selected column; it also lets SQLite read groups in key order instead of sorting them in a temporary B-tree.

Examples

let query = builder
    .select((post.user_id, count(post.id)))
    .from(post)
    .group_by(post.user_id)
    .having(gt(count(post.id), 5));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "posts"."user_id", COUNT ("posts"."id") FROM "posts" GROUP BY "posts"."user_id" HAVING COUNT ("posts"."id")> ?"#
);

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Having>,

pub fn having<E>( self, condition: E, ) -> SelectBuilder<'a, S, SelectGroupSet, T, <M as HasScope>::With<E::Sources>, R, G>

where M: HasScope, E: Expr<'a, SQLiteValue<'a>>, E::SQLType: BooleanLike,

Adds a HAVING clause, which filters groups.

Only available after group_by. The condition must be a boolean expression and may use aggregates. See group_by for an example.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: SelectClause<OrderBy>,

pub fn order_by<TOrderBy>( self, expressions: TOrderBy, ) -> SelectBuilder<'a, S, SelectOrderSet, T, <M as HasScope>::With<TOrderBy::Sources>, R, G>

where M: HasScope, TOrderBy: ToSQL<'a, SQLiteValue<'a>> + ExprSources,

Adds an ORDER BY clause.

Pass one ordering term or a tuple. Wrap a column in asc or desc to set the direction.

Examples

let query = builder
    .select(user.name)
    .from(user)
    .order_by((desc(user.name), asc(user.id)));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "users"."name" FROM "users" ORDER BY "users"."name" DESC, "users"."id" ASC"#
);

impl<'a, S, T, M, R, G> QueryBuilder<'a, S, SelectSetOpSet, T, M, R, G>

pub fn order_by<TOrderBy>( self, expressions: TOrderBy, ) -> SelectBuilder<'a, S, SelectOrderSet, T, M, R, G>

where TOrderBy: ToSQL<'a, SQLiteValue<'a>>,

Sorts a compound (UNION / INTERSECT / EXCEPT) result by its output columns.

Column references are written without their table name, because the combined rows no longer belong to one table (turso rejects the qualified form). See union for an example.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Limit>,

pub fn limit<P>( self, limit: P, ) -> SelectBuilder<'a, S, SelectLimitSet, T, M, R, G>

where P: PaginationArg<'a, SQLiteValue<'a>>,

Adds a LIMIT clause.

Pass a non-negative integer, which is written into the SQL, or an integer placeholder, which is bound when the query runs. See SelectBuilder for an example.

Panics

Panics when a signed numeric argument is negative or a numeric value does not fit in usize.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<StandaloneOffset>,

pub fn offset<P>( self, offset: P, ) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>

where P: PaginationArg<'a, SQLiteValue<'a>>,

Skips the first offset rows without limiting the row count.

SQLite only accepts OFFSET after a LIMIT, so this renders LIMIT -1 OFFSET n; a negative limit means no limit. Only available right after from or a set operation; elsewhere call limit first.

Examples

let query = builder.select(user.name).from(user).offset(5);
assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users" LIMIT -1 OFFSET 5"#);

Panics

Panics when a signed numeric argument is negative or a numeric value does not fit in usize.

impl<'a, S, T, M, R, G> QueryBuilder<'a, S, SelectLimitSet, T, M, R, G>

pub fn offset<P>( self, offset: P, ) -> SelectBuilder<'a, S, SelectOffsetSet, T, M, R, G>

where P: PaginationArg<'a, SQLiteValue<'a>>,

Adds an OFFSET clause after LIMIT.

Panics

Panics when a signed numeric argument is negative or a numeric value does not fit in usize.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Source>, M: DerivedSelection<'a, SQLiteValue<'a>, SQLiteSchemaType, T>,

pub fn alias<Name, AggProof>( self, _name: Name, ) -> Derived<'a, SQLiteValue<'a>, Name, <M as DerivedSelection<'a, SQLiteValue<'a>, SQLiteSchemaType, T>>::Projection, Self>

where Name: Tag, <M as DerivedSelection<'a, SQLiteValue<'a>, SQLiteSchemaType, T>>::Projection: DerivedProjection<Name>, M: MarkerAggValidFor<G, AggProof>,

Names this query so it can be used as a derived table in from or a join.

name is a value of a Tag type; its NAME becomes the SQL alias. The result exposes the selected columns, so the outer query can reference them with typed accessors.

Panics

Panics when the projection contains duplicate output names. Name a computed expression with drizzle_core::expr::AliasExt::named to make each output unique.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Simple>, T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn into_cte<Tag: Tag + 'static>( self, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased<Tag>, Self>

Turns this SELECT into a common table expression named Tag::NAME.

The result derefs to an aliased copy of the FROM table, so you can select its columns with the usual field access. Pass it to QueryBuilder::with and then use it in from. Not available on a compound query (after a set operation). See QueryBuilder::with for an example.

impl<'a, S, State, T, M, R, G> QueryBuilder<'a, S, State, T, M, R, G>

where State: ClauseAllowed<Compound>,

pub fn union<M2>( self, other: impl IntoSelect<'a, S, M2, R>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<M2>>::Combined, R, G>

where M: SetOperand<M2>,

Combines this query with other using UNION, which drops duplicate rows.

Both queries must select the same row type. After a set operation you can chain more set operations, then order_by, limit and offset for the combined result.

Examples

let query = builder
    .select(user.name)
    .from(user)
    .r#where(eq(user.id, 1))
    .union(builder.select(user.name).from(user).r#where(gt(user.id, 100)))
    .order_by(asc(user.name));
assert_eq!(
    query.to_sql().sql(),
    r#"SELECT "users"."name" FROM "users" WHERE "users"."id" = ? UNION SELECT "users"."name" FROM "users" WHERE "users"."id" > ? ORDER BY "name" ASC"#
);

pub fn union_all<M2>( self, other: impl IntoSelect<'a, S, M2, R>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<M2>>::Combined, R, G>

where M: SetOperand<M2>,

Combines this query with other using UNION ALL, which keeps duplicate rows. See union.

pub fn intersect<M2>( self, other: impl IntoSelect<'a, S, M2, R>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<M2>>::Combined, R, G>

where M: SetOperand<M2>,

Keeps only rows that other also returns (INTERSECT). See union.

pub fn except<M2>( self, other: impl IntoSelect<'a, S, M2, R>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T, <M as SetOperand<M2>>::Combined, R, G>

where M: SetOperand<M2>,

Keeps only rows that other does not return (EXCEPT). See union.

impl<'a, Schema, Table> QueryBuilder<'a, Schema, UpdateInitial, Table>

where Table: SQLiteTable<'a>,

pub fn set( self, values: Table::Update, ) -> UpdateBuilder<'a, Schema, UpdateSetClauseSet, Table>

Sets the columns to change, using the table’s generated update model.

Start from UpdateX::default() and call a with_* setter for each column to change. Columns you do not set are left as they are.

Examples

// Update single column
let query = builder
    .update(user)
    .set(UpdateUser::default().with_name("New Name"));
assert_eq!(query.to_sql().sql(), r#"UPDATE "users" SET "name" = ?"#);

// Update multiple columns
let query = builder
    .update(user)
    .set(UpdateUser::default().with_name("New Name").with_email("new@example.com"));
assert_eq!(query.to_sql().sql(), r#"UPDATE "users" SET "name" = ?, "email" = ?"#);

impl<'a, S, T> QueryBuilder<'a, S, UpdateSetClauseSet, T>

pub fn where<E, ScopeProof>( self, condition: E, ) -> UpdateBuilder<'a, S, UpdateWhereSet, T>

where E: ExprSources + Expr<'a, SQLiteValue<'a>>, E::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, E::SQLType: BooleanLike,

Adds a WHERE clause that picks the rows to update.

An UPDATE runs only with a WHERE clause, so a forgotten condition does not rewrite the whole table; r#where(true) updates every row. The condition must be a boolean expression over the updated table’s columns.

Examples

// Update specific row by ID
let query = builder
    .update(user)
    .set(UpdateUser::default().with_name("Updated Name"))
    .r#where(eq(user.id, 1));
assert_eq!(
    query.to_sql().sql(),
    r#"UPDATE "users" SET "name" = ? WHERE "users"."id" = ?"#
);

// Update multiple rows with complex condition
let query = builder
    .update(user)
    .set(UpdateUser::default().with_name("Updated"))
    .r#where(and(gt(user.id, 10), eq(user.age, 25)));

impl<'a, S, T> QueryBuilder<'a, S, UpdateWhereSet, T>

pub fn returning<Columns, ScopeProof>( self, columns: Columns, ) -> UpdateBuilder<'a, S, UpdateReturningSet, T, Scoped<<Columns as IntoSelectTarget>::Marker, Cons<T, Nil>>, <<Columns as IntoSelectTarget>::Marker as ResolveRow<T>>::Row>

where Columns: ExprSources + ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget, Columns::Sources: SourcesIn<Cons<T, Nil>, ScopeProof>, Columns::Marker: ResolveRow<T>,

Adds a RETURNING clause after WHERE. See returning.

impl<'a, Schema, State, Table, Marker, Row, Grouped> QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

where State: ExecutableState,

pub fn comment(self, text: impl AsRef<str>) -> Self

Prepends a sqlcommenter comment (/*...*/) to the query.

/* and */ inside text are broken up (/ *, * /) so the text cannot end the comment early. An empty text leaves the query unchanged.

Examples

let query = builder.select(user.id).from(user).comment("list users");
assert_eq!(query.to_sql().sql(), r#"/*list users*/ SELECT "users"."id" FROM "users""#);

pub fn comment_tags<I, K, V>(self, pairs: I) -> Self

where I: IntoIterator<Item = (K, V)>, K: AsRef<str>, V: AsRef<str>,

Prepends a tag-style sqlcommenter comment to the query.

Each (key, value) pair is URL-encoded and written as key='value'. Pairs are sorted and joined with ,. Pairs with an empty value are skipped; if none remain, the query is unchanged.

Examples

let query = builder
    .select(user.id)
    .from(user)
    .comment_tags([("route", "/users"), ("action", "list")]);
assert_eq!(
    query.to_sql().sql(),
    r#"/*action='list',route='%2Fusers'*/ SELECT "users"."id" FROM "users""#
);

impl<'a> QueryBuilder<'a>

pub const fn new<S>() -> QueryBuilder<'a, S, BuilderInit>

Creates a query builder for the schema S.

Examples

use drizzle::sqlite::prelude::*;
use drizzle::sqlite::builder::QueryBuilder;

#[SQLiteTable(name = "users")]
struct User {
    #[column(primary)]
    id: i32,
    name: String,
}

#[derive(SQLiteSchema)]
struct MySchema {
    user: User,
}

let builder = QueryBuilder::new::<MySchema>();

impl<'a, Schema> QueryBuilder<'a, Schema, BuilderInit>

pub fn select<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget,

Starts a SELECT with the given columns.

Pass one column or expression, a tuple of them, or () to select every column of the FROM table (and of joined tables). Call from next.

Examples

// Select a single column
let query = builder.select(user.name).from(user);
assert_eq!(query.to_sql().sql(), r#"SELECT "users"."name" FROM "users""#);

// Select multiple columns
let query = builder.select((user.id, user.name)).from(user);
assert_eq!(query.to_sql().sql(), r#"SELECT "users"."id", "users"."name" FROM "users""#);

// Select every column
let query = builder.select(()).from(user);
assert_eq!(query.to_sql().sql(), r#"SELECT "users"."id", "users"."name" FROM "users""#);

pub fn select_distinct<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget,

Starts a SELECT DISTINCT, which drops duplicate rows from the result.

Examples

let query = builder.select_distinct(user.name).from(user);
assert_eq!(query.to_sql().sql(), r#"SELECT DISTINCT "users"."name" FROM "users""#);

impl<'a, Schema> QueryBuilder<'a, Schema, CTEInit>

pub fn select<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget,

Starts a SELECT after the WITH clause.

See QueryBuilder::with for an example.

pub fn select_distinct<T>( &self, columns: T, ) -> SelectBuilder<'a, Schema, SelectInitial, (), T::Marker>

where T: ToSQL<'a, SQLiteValue<'a>> + IntoSelectTarget,

Starts a SELECT DISTINCT after the WITH clause.

pub fn insert<Table>( &self, table: Table, ) -> InsertBuilder<'a, Schema, InsertInitial, Table>

where Table: SQLiteTable<'a>,

Starts an INSERT after the WITH clause.

pub fn update<Table>( &self, table: Table, ) -> UpdateBuilder<'a, Schema, UpdateInitial, Table>

where Table: SQLiteTable<'a>,

Starts an UPDATE after the WITH clause.

pub fn delete<Table>( &self, table: Table, ) -> DeleteBuilder<'a, Schema, DeleteInitial, Table>

where Table: SQLiteTable<'a>,

Starts a DELETE after the WITH clause.

pub fn with<C>(&self, cte: &C) -> Self

where C: CTEDefinition<'a>,

Adds another common table expression to the WITH clause.

impl<'a, Schema> QueryBuilder<'a, Schema, BuilderInit>

pub fn insert<Table>( &self, table: Table, ) -> InsertBuilder<'a, Schema, InsertInitial, Table>

where Table: SQLiteTable<'a>,

Starts an INSERT into table.

Call values, value, select or columns next.

Examples

let query = builder
    .insert(user)
    .values([InsertUser::new("Alice")]);
assert_eq!(query.to_sql().sql(), r#"INSERT INTO "users" ("name") VALUES (?)"#);

pub fn update<Table>( &self, table: Table, ) -> UpdateBuilder<'a, Schema, UpdateInitial, Table>

where Table: SQLiteTable<'a>,

Starts an UPDATE of table. Call set next.

Examples

let query = builder
    .update(user)
    .set(UpdateUser::default().with_name("Bob"))
    .r#where(eq(user.id, 1));
assert_eq!(query.to_sql().sql(), r#"UPDATE "users" SET "name" = ? WHERE "users"."id" = ?"#);

pub fn delete<Table>( &self, table: Table, ) -> DeleteBuilder<'a, Schema, DeleteInitial, Table>

where Table: SQLiteTable<'a>,

Starts a DELETE from table.

Without where the statement deletes every row.

Examples

let query = builder
    .delete(user)
    .r#where(lt(user.id, 10));
assert_eq!(query.to_sql().sql(), r#"DELETE FROM "users" WHERE "users"."id" < ?"#);

pub fn with<C>(&self, cte: &C) -> QueryBuilder<'a, Schema, CTEInit>

where C: CTEDefinition<'a>,

Starts a WITH clause with one common table expression.

Build the CTE with into_cte. Add more CTEs with another .with(..), then start the main statement.

Examples

struct Recent;
impl drizzle::core::Tag for Recent {
    const NAME: &'static str = "recent";
}

let recent = builder
    .select((user.id, user.name))
    .from(user)
    .r#where(gt(user.id, 100))
    .into_cte::<Recent>();

let query = builder.with(&recent).select(recent.name).from(&recent);
assert_eq!(
    query.to_sql().sql(),
    r#"WITH "recent" AS (SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."id" > ?) SELECT "recent"."name" FROM "recent""#
);

Trait Implementations

impl<'a, Schema: Clone, State: Clone, Table: Clone, Marker: Clone, Row: Clone, Grouped: Clone> Clone for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

fn clone(&self) -> Self

Returns a duplicate of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl<'a, Schema: Debug, State: Debug, Table: Debug, Marker: Debug, Row: Debug, Grouped: Debug> Debug for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl<'a, Schema: Default, State: Default, Table: Default, Marker: Default, Row: Default, Grouped: Default> Default for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

fn default() -> Self

Returns the “default value” for a type.

impl<'a, Schema, State, Table, Marker, Row, Grouped> ToSQL<'a, SQLiteValue<'a>> for QueryBuilder<'a, Schema, State, Table, Marker, Row, Grouped>

fn to_sql(&self) -> SQL<'a, SQLiteValue<'a>>

Renders self as a SQL fragment.

fn into_sql(self) -> SQL<'a, V>

where Self: Sized,

Renders self as a SQL fragment, consuming it.