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::builder::QueryBuilder;
use drizzle_macros::{SQLiteTable, SQLiteSchema};
use drizzle_core::ToSQL;

#[SQLiteTable(name = "users")]
struct User {
    #[integer(primary)]
    id: i32,
    #[text]
    name: String,
    #[text]
    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::expressions::conditions::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

JOIN Operations

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

Ordering and Limiting

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: ToSQLiteSQL<'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>

where T: SQLiteTable<'a>,

pub fn join<U: SQLiteTable<'a>>( self, table: U, condition: impl ToSQLiteSQL<'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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

pub fn where( self, condition: impl ToSQLiteSQL<'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::expressions::conditions.

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

pub fn group_by( self, expressions: Vec<SQL<'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>

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: SQL<'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 ToSQLiteSQL<'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 ToSQLiteSQL<'a>, ) -> SelectBuilder<'a, S, SelectJoinSet, T>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

pub fn group_by( self, expressions: Vec<SQL<'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: SQLiteTable<'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: SQL<'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, 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, SelectLimitSet, T>

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

Adds an OFFSET clause after LIMIT