Type Alias SelectBuilder 

pub type SelectBuilder<'a, Schema, State, Table = ()> = QueryBuilder<'a, Schema, State, Table>;

Builds a SELECT query specifically for SQLite.

SelectBuilder provides a type-safe, fluent API for constructing SELECT statements with compile-time verification of query structure and table relationships.

Type Parameters

• Schema: The database schema type, ensuring only valid tables can be referenced
• State: The current builder state, enforcing proper query construction order
• Table: The primary table being queried (when applicable)

Query Building Flow

1. Start with QueryBuilder::select() to specify columns
2. Add from() to specify the source table
3. Optionally add joins, conditions, grouping, ordering, and limits

Basic Usage

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

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

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

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

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

// SELECT with WHERE clause
use drizzle::core::expr::gt;
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" > ?"#
);

Advanced Queries

let query = builder
    .select((user.name, post.title))
    .from(user)
    .join(post, eq(user.id, post.user_id));

let query = builder
    .select(user.name)
    .from(user)
    .order_by(OrderBy::asc(user.name))
    .limit(10);

Aliased Type

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

Fields

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

Implementations

impl<'a, S> SelectBuilder<'a, S, SelectInitial>

pub fn from<T>(self, query: T) -> SelectBuilder<'a, S, SelectFromSet, T>

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

Specifies the table or subquery to select FROM.

This method transitions the builder from the initial state to the FROM state, enabling subsequent WHERE, JOIN, ORDER BY, and other clauses.

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, T> SelectBuilder<'a, S, SelectFromSet, T>

pub fn join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

Adds an INNER JOIN clause to the query.

Joins another table to the current query using the specified condition. The joined table must be part of the schema and the condition should relate columns from both tables.

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<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_left_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn left_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn left_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_left_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_right_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn right_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn right_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_right_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_full_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn full_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn full_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_full_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn inner_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn cross_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn where( self, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectWhereSet, T>

Adds a WHERE clause to filter query results.

This method applies conditions to filter the rows returned by the query. You can use various condition functions from drizzle::core::expr::conditions.

// 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")]));

pub fn group_by( self, expressions: impl IntoIterator<Item = impl ToSQL<'a, SQLiteValue<'a>>>, ) -> SelectBuilder<'a, S, SelectGroupSet, T>

Adds a GROUP BY clause to the query

pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T>

Limits the number of rows returned

pub fn offset(self, offset: usize) -> SelectBuilder<'a, S, SelectOffsetSet, T>

Sets the offset for the query results

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

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

Sorts the query results

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

The returned CTEView provides typed access to the table’s columns through an aliased table instance, allowing you to reference CTE columns in subsequent queries.

Type Parameters

The T (Table) type parameter from .from(table) determines the aliased type, enabling type-safe field access on the returned CTE.

Examples

// Create a CTE from a select query
let active_users = builder
    .select((user.id, user.name))
    .from(user)
    .as_cte("active_users");

// Use the CTE with typed field access
let query = builder
    .with(&active_users)
    .select(active_users.name)  // Deref gives access to aliased table fields
    .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""#
);

impl<'a, S, T> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn where( self, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectWhereSet, T>

Adds a WHERE condition after a JOIN

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

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

Sorts the query results

pub fn join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

Adds a JOIN clause to the query

pub fn natural_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_left_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn left_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn left_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_left_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_right_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn right_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn right_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_right_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_full_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn full_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn full_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn natural_full_outer_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn inner_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

pub fn cross_join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

impl<'a, S, T> SelectBuilder<'a, S, SelectJoinSet, T>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

impl<'a, S, T> SelectBuilder<'a, S, SelectWhereSet, T>

pub fn group_by( self, expressions: impl IntoIterator<Item = impl ToSQL<'a, SQLiteValue<'a>>>, ) -> SelectBuilder<'a, S, SelectGroupSet, T>

Adds a GROUP BY clause after a WHERE

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

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

Adds an ORDER BY clause after a WHERE

pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T>

Adds a LIMIT clause after a WHERE

impl<'a, S, T> SelectBuilder<'a, S, SelectWhereSet, T>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

impl<'a, S, T> SelectBuilder<'a, S, SelectGroupSet, T>

pub fn having( self, condition: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectGroupSet, T>

Adds a HAVING clause after GROUP BY

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

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

Adds an ORDER BY clause after GROUP BY

impl<'a, S, T> SelectBuilder<'a, S, SelectGroupSet, T>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

impl<'a, S, T> SelectBuilder<'a, S, SelectOrderSet, T>

pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T>

Adds a LIMIT clause after ORDER BY

impl<'a, S, T> SelectBuilder<'a, S, SelectOrderSet, T>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

impl<'a, S, T> SelectBuilder<'a, S, SelectLimitSet, T>

pub fn offset(self, offset: usize) -> SelectBuilder<'a, S, SelectOffsetSet, T>

Adds an OFFSET clause after LIMIT

impl<'a, S, T> SelectBuilder<'a, S, SelectLimitSet, T>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

impl<'a, S, T> SelectBuilder<'a, S, SelectOffsetSet, T>

where T: SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>,

pub fn as_cte( self, name: &'static str, ) -> CTEView<'a, <T as SQLTable<'a, SQLiteSchemaType, SQLiteValue<'a>>>::Aliased, Self>

Converts this SELECT query into a CTE (Common Table Expression) with the given name.

impl<'a, S, State, T> SelectBuilder<'a, S, State, T>

where State: ExecutableState,

pub fn union( self, other: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T>

Combines this query with another using UNION.

pub fn union_all( self, other: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T>

Combines this query with another using UNION ALL.

pub fn intersect( self, other: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T>

Combines this query with another using INTERSECT.

pub fn intersect_all( self, other: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T>

Combines this query with another using INTERSECT ALL.

pub fn except( self, other: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T>

Combines this query with another using EXCEPT.

pub fn except_all( self, other: impl ToSQL<'a, SQLiteValue<'a>>, ) -> SelectBuilder<'a, S, SelectSetOpSet, T>

Combines this query with another using EXCEPT ALL.

impl<'a, S, T> SelectBuilder<'a, S, SelectSetOpSet, T>

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

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

Sorts the results of a set operation.

pub fn limit(self, limit: usize) -> SelectBuilder<'a, S, SelectLimitSet, T>

Limits the results of a set operation.

pub fn offset(self, offset: usize) -> SelectBuilder<'a, S, SelectOffsetSet, T>

Offsets the results of a set operation.