Struct Query 

pub struct Query<T> { /* private fields */ }

A SELECT on one table, built step by step, whose values are always bound parameters.

Generated models start every query with query() (e.g. post::query(), which is Query::table("posts")), so the row type is known: all returns Vec<Post>. Scopes are plain functions taking and returning a Query (see scope).

• Values (eq, is_in, contains…) are always bound as parameters, never written into the SQL.
• Column names and SQL fragments are &'static str: they come from your code, so a request cannot inject SQL through them. To sort by a column the user picks, match their input onto a fixed column name.
• Conditions combine with AND; any groups conditions with OR, not negates a group.
• Raw fragments (where_sql, having) use bare ? placeholders; the builder numbers every placeholder ?1, ?2... in the final SQL, in order.

Building costs nothing on the free plan. Running it reads the rows D1 scans (see Db): filter and order on indexed columns, and bound lists with limit or page.

Examples

use ocre::{Direction, Page, Query, params};

#[derive(serde::Deserialize)]
struct Post {
    id: i64,
    title: String,
}

let query: Query<Post> = Query::table("posts")
    .eq("published", true)
    .any(|q| q.contains("title", "rust").contains("body", "rust"))
    .order_by("created_at", Direction::Desc)
    .page(Page { limit: 20, offset: 40 });
let stmt = query.to_statement();
assert_eq!(
    stmt.sql,
    "SELECT * FROM posts WHERE published = ?1 AND (title LIKE ?2 ESCAPE '\\' OR body LIKE ?3 ESCAPE '\\') \
     ORDER BY created_at DESC LIMIT ?4 OFFSET ?5"
);
assert_eq!(stmt.params, params![true, "%rust%", "%rust%", 20, 40]);

Running it, in a handler or a model function:

async fn published(ctx: &Ctx) -> Result<Vec<Post>> {
    Query::table("posts").eq("published", true).order_desc("id").limit(20).all(&ctx.db()?).await
}

Implementations

impl<T> Query<T>

pub fn table(table: &'static str) -> Self

Starts a query on table: SELECT * FROM <table>.

Examples

let query: ocre::Query<serde_json::Value> = ocre::Query::table("posts");
assert_eq!(query.to_statement().sql, "SELECT * FROM posts");

pub fn scope(self, f: impl FnOnce(Self) -> Self) -> Self

Applies a scope: f(self). Scopes are plain functions, so they chain like Rails scopes and take arguments like any function.

Examples

use ocre::Query;

fn published(query: Query<Post>) -> Query<Post> {
    query.eq("published", true)
}
fn by_author(query: Query<Post>, author_id: i64) -> Query<Post> {
    query.eq("author_id", author_id)
}

let query = Query::<Post>::table("posts").scope(published).scope(|q| by_author(q, 7));
assert_eq!(query.to_statement().sql, "SELECT * FROM posts WHERE published = ?1 AND author_id = ?2");

pub fn select<U>(self, columns: &'static str) -> Query<U>

Selects columns (an SQL select list) instead of *; the rows become U.

The row type changes because the columns do: read them into a struct with matching field names (use AS for expressions).

Examples

use ocre::Query;

#[derive(serde::Deserialize)]
struct Title {
    title: String,
}

let query: Query<Title> = Query::<()>::table("posts").select("title");
assert_eq!(query.to_statement().sql, "SELECT title FROM posts");

pub fn distinct(self) -> Self

SELECT DISTINCT: drops duplicate rows.

Examples

use ocre::Query;

let query: Query<()> = Query::<()>::table("posts").select("author_id").distinct();
assert_eq!(query.to_statement().sql, "SELECT DISTINCT author_id FROM posts");

pub fn join(self, clause: &'static str) -> Self

Adds a join clause, written in full: JOIN ... or LEFT JOIN ....

Select the columns you need with select (SELECT * would mix both tables’ columns) and qualify ambiguous names.

Examples

use ocre::Query;

let query: Query<()> = Query::<()>::table("posts")
    .select("posts.*")
    .join("JOIN taggings ON taggings.post_id = posts.id")
    .eq("taggings.tag_id", 3);
assert_eq!(
    query.to_statement().sql,
    "SELECT posts.* FROM posts JOIN taggings ON taggings.post_id = posts.id WHERE taggings.tag_id = ?1"
);

pub fn eq(self, column: &'static str, value: impl IntoParam) -> Self

column = value. A None value matches nothing (SQL = NULL); use is_null for missing values.

Examples

use ocre::{Query, params};

let stmt = Query::<()>::table("users").eq("email", "ada@example.com").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM users WHERE email = ?1");
assert_eq!(stmt.params, params!["ada@example.com"]);

pub fn ne(self, column: &'static str, value: impl IntoParam) -> Self

column != value.

Examples

let stmt = ocre::Query::<()>::table("posts").ne("status", "archived").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE status != ?1");

pub fn gt(self, column: &'static str, value: impl IntoParam) -> Self

column > value.

Examples

let stmt = ocre::Query::<()>::table("posts").gt("id", 100).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE id > ?1");

pub fn gte(self, column: &'static str, value: impl IntoParam) -> Self

column >= value.

Examples

let stmt = ocre::Query::<()>::table("posts").gte("created_at", "2026-01-01").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE created_at >= ?1");

pub fn lt(self, column: &'static str, value: impl IntoParam) -> Self

column < value.

Examples

let stmt = ocre::Query::<()>::table("posts").lt("views", 10).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE views < ?1");

pub fn lte(self, column: &'static str, value: impl IntoParam) -> Self

column <= value.

Examples

let stmt = ocre::Query::<()>::table("posts").lte("views", 10).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE views <= ?1");

pub fn between( self, column: &'static str, low: impl IntoParam, high: impl IntoParam, ) -> Self

column BETWEEN low AND high, both bounds included. For a date range with an optional bound, use gte/lt under if let Some(..).

Examples

let stmt = ocre::Query::<()>::table("events").between("starts_on", "2026-01-01", "2026-12-31").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM events WHERE starts_on BETWEEN ?1 AND ?2");

pub fn is_in<V: IntoParam>( self, column: &'static str, values: impl IntoIterator<Item = V>, ) -> Self

column IN (?, ?, ...). An empty list matches no row (0), like Rails’ where(id: []). D1 binds at most 100 parameters per query.

Examples

use ocre::Query;

let stmt = Query::<()>::table("posts").is_in("id", [1, 2, 3]).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE id IN (?1, ?2, ?3)");
let none = Query::<()>::table("posts").is_in("id", Vec::<i64>::new()).to_statement();
assert_eq!(none.sql, "SELECT * FROM posts WHERE 0");

pub fn not_in<V: IntoParam>( self, column: &'static str, values: impl IntoIterator<Item = V>, ) -> Self

column NOT IN (?, ?, ...). An empty list excludes nothing.

Examples

let stmt = ocre::Query::<()>::table("posts").not_in("status", ["draft", "archived"]).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE status NOT IN (?1, ?2)");

pub fn is_null(self, column: &'static str) -> Self

column IS NULL.

Examples

let stmt = ocre::Query::<()>::table("posts").is_null("deleted_at").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE deleted_at IS NULL");

pub fn is_not_null(self, column: &'static str) -> Self

column IS NOT NULL.

Examples

let stmt = ocre::Query::<()>::table("posts").is_not_null("published_at").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE published_at IS NOT NULL");

pub fn like(self, column: &'static str, pattern: impl IntoParam) -> Self

column LIKE pattern, with the pattern as given: % and _ are wildcards. For user input, use contains, starts_with or ends_with, which escape them. SQLite’s LIKE ignores ASCII case.

Examples

let stmt = ocre::Query::<()>::table("posts").like("slug", "2026-%").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE slug LIKE ?1");

pub fn not_like(self, column: &'static str, pattern: impl IntoParam) -> Self

column NOT LIKE pattern (wildcards as given, see like).

Examples

let stmt = ocre::Query::<()>::table("users").not_like("email", "%@example.com").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM users WHERE email NOT LIKE ?1");

pub fn contains(self, column: &'static str, text: &str) -> Self

column contains text (case-insensitive for ASCII), with %, _ and \ in text matched literally (see escape_like).

Examples

use ocre::{Query, params};

let stmt = Query::<()>::table("posts").contains("title", "100%").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE title LIKE ?1 ESCAPE '\\'");
assert_eq!(stmt.params, params!["%100\\%%"]);

pub fn starts_with(self, column: &'static str, text: &str) -> Self

column starts with text (wildcards escaped, see contains).

Examples

let stmt = ocre::Query::<()>::table("users").starts_with("name", "Ad").to_statement();
assert_eq!(stmt.params, ocre::params!["Ad%"]);

pub fn ends_with(self, column: &'static str, text: &str) -> Self

column ends with text (wildcards escaped, see contains).

Examples

let stmt = ocre::Query::<()>::table("users").ends_with("email", "@example.com").to_statement();
assert_eq!(stmt.params, ocre::params!["%@example.com"]);

pub fn where_sql(self, fragment: &'static str, params: Vec<Param>) -> Self

Adds a raw SQL condition with bare ? placeholders, bound to params in order.

The fragment is wrapped in parentheses, so an OR inside stays grouped. Use it for anything the other methods do not cover: SQL functions, subqueries (EXISTS (...) for “has at least one comment”), date arithmetic.

Examples

use ocre::{Query, params};

let stmt = Query::<()>::table("posts")
    .eq("published", true)
    .where_sql("created_at > datetime('now', ?)", params!["-7 days"])
    .where_sql("EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id)", params![])
    .to_statement();
assert_eq!(
    stmt.sql,
    "SELECT * FROM posts WHERE published = ?1 AND (created_at > datetime('now', ?2)) \
     AND (EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id))"
);

pub fn where_associated( self, table: &'static str, foreign_key: &'static str, ) -> Self

Keeps rows with at least one row in table pointing to them through foreign_key (Rails’ where.associated on a has-many side).

Writes EXISTS (SELECT 1 FROM <table> WHERE <table>.<foreign_key> = <this table>.id), which stops at the first child: with an index on the foreign key (generated for every references field) it reads one child row per parent. For the belongs-to side, test the column itself: is_not_null("author_id").

Examples

let stmt = ocre::Query::<()>::table("posts").where_associated("comments", "post_id").to_statement();
assert_eq!(
    stmt.sql,
    "SELECT * FROM posts WHERE EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id)"
);

pub fn where_missing( self, table: &'static str, foreign_key: &'static str, ) -> Self

Keeps rows that no row of table points to through foreign_key (Rails’ where.missing on a has-many side): posts without comments.

The belongs-to side is is_null("author_id").

Examples

let stmt = ocre::Query::<()>::table("posts").where_missing("comments", "post_id").to_statement();
assert_eq!(
    stmt.sql,
    "SELECT * FROM posts WHERE NOT EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id)"
);

pub fn date_range<V: IntoParam>( self, column: &'static str, from: Option<V>, to: Option<V>, ) -> Self

Filters column between two optional bounds, like Loco’s DateRangeBuilder.

Both bounds: BETWEEN from AND to (inclusive). One bound: strictly after from (>) or strictly before to (<). No bound: no condition. Made for ?from=&to= filters, whose values arrive as Options; works on any comparable column (dates stored as ISO text compare correctly).

Examples

use ocre::{Query, params};

let both = Query::<()>::table("posts").date_range("created_at", Some("2026-01-01"), Some("2026-01-31"));
assert_eq!(both.to_statement().sql, "SELECT * FROM posts WHERE created_at BETWEEN ?1 AND ?2");
let from = Query::<()>::table("posts").date_range("created_at", Some("2026-01-01"), None);
assert_eq!(from.to_statement().sql, "SELECT * FROM posts WHERE created_at > ?1");
let to = Query::<()>::table("posts").date_range("created_at", None, Some("2026-01-31"));
assert_eq!(to.to_statement().sql, "SELECT * FROM posts WHERE created_at < ?1");
let none = Query::<()>::table("posts").date_range::<&str>("created_at", None, None);
assert_eq!(none.to_statement().sql, "SELECT * FROM posts");

pub fn unscope_where(self) -> Self

Removes every condition added so far (Rails’ unscope(:where)); chain new ones after it for Rails’ rewhere.

Useful to reuse a scoped query (a model’s query() that hides soft-deleted rows, say) without its conditions. Joins, order and limits stay.

Examples

let visible = ocre::Query::<()>::table("posts").is_null("deleted_at").order_desc("id");
let stmt = visible.unscope_where().eq("author_id", 3).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE author_id = ?1 ORDER BY id DESC");

pub fn unscope_limit(self) -> Self

Removes the limit and offset set so far (Rails’ unscope(:limit, :offset)).

Examples

let stmt = ocre::Query::<()>::table("posts").limit(10).offset(20).unscope_limit().to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts");

pub fn reverse_order(self) -> Self

Reverses the order (Rails’ reverse_order): ASC terms become DESC and back; terms without a direction (order_in, a bare order_sql) get DESC. Without any order, sorts by <table>.id DESC.

Write raw terms with NULLS FIRST/LAST in full instead: they cannot be flipped by appending a direction.

Examples

use ocre::Query;

let stmt = Query::<()>::table("posts").order_asc("title").order_desc("id").reverse_order().to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title DESC, id ASC");
assert_eq!(Query::<()>::table("posts").reverse_order().to_statement().sql, "SELECT * FROM posts ORDER BY posts.id DESC");

pub fn any(self, f: impl FnOnce(Self) -> Self) -> Self

Matches rows meeting at least one of the conditions f adds: (a OR b ...).

f receives an empty query to add conditions to; its table, order and limits are ignored. No condition added: nothing changes.

Examples

let stmt = ocre::Query::<()>::table("posts")
    .eq("published", true)
    .any(|q| q.eq("author_id", 1).is_null("author_id"))
    .to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE published = ?1 AND (author_id = ?2 OR author_id IS NULL)");

pub fn not(self, f: impl FnOnce(Self) -> Self) -> Self

Matches rows that do not meet all the conditions f adds: NOT (a AND b ...).

Examples

let stmt = ocre::Query::<()>::table("posts").not(|q| q.eq("status", "draft").eq("author_id", 3)).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE NOT (status = ?1 AND author_id = ?2)");

pub fn none(self) -> Self

Matches no row (WHERE 0), like Rails’ none: a scope can return it when a filter makes the result empty. D1 still runs the query, but reads no row.

Examples

let stmt = ocre::Query::<()>::table("posts").none().to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE 0");

pub fn group_by(self, columns: &'static str) -> Self

GROUP BY columns; combine with select for the aggregates and having to filter groups.

Examples

use ocre::{Query, params};

#[derive(serde::Deserialize)]
struct PerAuthor {
    author_id: i64,
    count: i64,
}

let query: Query<PerAuthor> = Query::<()>::table("posts")
    .select("author_id, COUNT(*) AS count")
    .group_by("author_id")
    .having("COUNT(*) >= ?", params![5])
    .order_desc("count");
assert_eq!(
    query.to_statement().sql,
    "SELECT author_id, COUNT(*) AS count FROM posts GROUP BY author_id HAVING (COUNT(*) >= ?1) ORDER BY count DESC"
);

pub fn having(self, fragment: &'static str, params: Vec<Param>) -> Self

Adds a HAVING condition (raw SQL with bare ? placeholders) on the groups.

See group_by.

Examples

let stmt = ocre::Query::<()>::table("posts").group_by("author_id").having("COUNT(*) > ?", ocre::params![1]).to_statement();
assert!(stmt.sql.ends_with("HAVING (COUNT(*) > ?1)"));

pub fn order_asc(self, column: &'static str) -> Self

ORDER BY column ASC, after any order already set.

Examples

let stmt = ocre::Query::<()>::table("posts").order_asc("title").order_desc("id").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title ASC, id DESC");

pub fn order_desc(self, column: &'static str) -> Self

ORDER BY column DESC, after any order already set.

Examples

let stmt = ocre::Query::<()>::table("posts").order_desc("id").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY id DESC");

pub fn order_by(self, column: &'static str, direction: Direction) -> Self

ORDER BY column <direction>, after any order already set.

The direction may come from the request; the column may not, so map the user’s choice onto a fixed name.

Examples

use ocre::{Direction, Query};

// `sort` and `direction` from `?sort=title&direction=desc`.
let (sort, direction) = ("title", Direction::Desc);
let column = match sort {
    "title" => "title",
    _ => "created_at",
};
let stmt = Query::<()>::table("posts").order_by(column, direction).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title DESC");

pub fn order_in<V: IntoParam>( self, column: &'static str, values: impl IntoIterator<Item = V>, ) -> Self

Orders column by an explicit list of values (Rails’ in_order_of): rows whose value is not listed come last.

Examples

let stmt = ocre::Query::<()>::table("tasks").order_in("status", ["urgent", "open"]).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM tasks ORDER BY CASE status WHEN ?1 THEN 0 WHEN ?2 THEN 1 ELSE 2 END");

pub fn order_sql(self, term: &'static str) -> Self

Adds a raw ORDER BY term, e.g. lower(title) or published_at DESC NULLS LAST.

Examples

let stmt = ocre::Query::<()>::table("posts").order_sql("lower(title) ASC").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY lower(title) ASC");

pub fn reorder(self) -> Self

Removes the order set so far (Rails’ reorder when followed by a new order).

Examples

let stmt = ocre::Query::<()>::table("posts").order_desc("id").reorder().order_asc("title").to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title ASC");

pub fn limit(self, limit: i64) -> Self

Returns at most limit rows.

Examples

let stmt = ocre::Query::<()>::table("posts").limit(5).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts LIMIT ?1");

pub fn offset(self, offset: i64) -> Self

Skips the first offset rows.

Examples

let stmt = ocre::Query::<()>::table("posts").offset(10).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts LIMIT -1 OFFSET ?1");

pub fn page(self, page: Page) -> Self

Limit and offset from a Page (?limit=&offset=).

Examples

use ocre::{Page, Query, params};

let stmt = Query::<()>::table("posts").page(Page { limit: 20, offset: 40 }).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts LIMIT ?1 OFFSET ?2");
assert_eq!(stmt.params, params![20, 40]);

pub fn explain_statement(&self) -> Statement

EXPLAIN QUERY PLAN of the SELECT (Rails’ explain): how SQLite finds the rows, e.g. SEARCH posts USING INDEX index_posts_on_author_id (author_id=?) or a full SCAN posts.

Run it with explain, or print the SQL and run it with ocre sql "EXPLAIN QUERY PLAN ..." on the local database.

Examples

let stmt = ocre::Query::<()>::table("posts").eq("author_id", 3).explain_statement();
assert_eq!(stmt.sql, "EXPLAIN QUERY PLAN SELECT * FROM posts WHERE author_id = ?1");

pub fn batches(self, size: i64, id: fn(&T) -> i64) -> Batches<T>

Walks the matching rows in batches of size, ordered by id (Rails’ find_in_batches / in_batches, and find_each with a loop over each batch).

id reads a row’s primary key (|post| post.id): each batch starts after the last id of the previous one (keyset pagination: WHERE id > last ORDER BY id LIMIT size), so batches stay cheap however far they go, unlike OFFSET. The query’s own order and limits are replaced; for Rails’ start: / finish: add gte("id", start) / lte("id", finish). Batches::next runs one batch.

Free plan

Each batch is one query reading size rows. A Worker invocation may run 50 D1 queries on the free plan and has 10 ms of CPU: walk large tables from a job or scheduled task, a few batches per invocation (keep Batches::after to resume in the next one).

Examples

use ocre::Query;

#[derive(serde::Deserialize)]
struct Post {
    id: i64,
}

let mut batches = Query::<Post>::table("posts").eq("published", true).batches(500, |post| post.id);
assert_eq!(
    batches.statement().sql,
    "SELECT * FROM posts WHERE published = ?1 ORDER BY posts.id ASC LIMIT ?2"
);
batches.advance(&[Post { id: 7 }, Post { id: 9 }]);
assert_eq!(batches.after(), Some(9));
assert_eq!(
    batches.statement().sql,
    "SELECT * FROM posts WHERE published = ?1 AND posts.id > ?2 ORDER BY posts.id ASC LIMIT ?3"
);

pub fn to_statement(&self) -> Statement

The SELECT statement, with placeholders numbered ?1, ?2....

Terminal methods (all, first…) run it; use it directly for Db::batch or logging.

Examples

let stmt = ocre::Query::<()>::table("posts").eq("id", 1).to_statement();
assert_eq!(stmt.sql, "SELECT * FROM posts WHERE id = ?1");

pub fn count_statement(&self) -> Statement

SELECT COUNT(*) AS count of the matching rows, ignoring order, limit and offset. With group_by or distinct, counts the groups or distinct rows.

Examples

use ocre::Query;

let query = Query::<()>::table("posts").eq("published", true).order_desc("id").limit(10);
assert_eq!(query.count_statement().sql, "SELECT COUNT(*) AS count FROM posts WHERE published = ?1");
let authors = Query::<()>::table("posts").select::<()>("author_id").distinct();
assert_eq!(
    authors.count_statement().sql,
    "SELECT COUNT(*) AS count FROM (SELECT DISTINCT author_id FROM posts)"
);

pub fn exists_statement(&self) -> Statement

SELECT 1 ... LIMIT 1: whether any row matches, stopping at the first.

Examples

let stmt = ocre::Query::<()>::table("users").eq("email", "a@b.co").exists_statement();
assert_eq!(stmt.sql, "SELECT 1 FROM users WHERE email = ?1 LIMIT 1");

pub fn value_statement(&self, expression: &'static str) -> Statement

SELECT <expression> AS value, keeping conditions, order and limits: one column of every matching row (Rails’ pluck), or with an aggregate expression (SUM(price)) the calculation over the matching rows (order and limits are dropped then, as they do not apply).

Examples

use ocre::Query;

let query = Query::<()>::table("posts").eq("published", true).order_desc("id").limit(3);
assert_eq!(
    query.value_statement("title").sql,
    "SELECT title AS value FROM posts WHERE published = ?1 ORDER BY id DESC LIMIT ?2"
);
assert_eq!(query.aggregate_statement("SUM(views)").sql, "SELECT SUM(views) AS value FROM posts WHERE published = ?1");

pub fn aggregate_statement(&self, expression: &'static str) -> Statement

SELECT <aggregate> AS value over the matching rows, without order or limits.

See value_statement.

Examples

let stmt = ocre::Query::<()>::table("products").aggregate_statement("MAX(price)");
assert_eq!(stmt.sql, "SELECT MAX(price) AS value FROM products");

pub fn update_statement(&self, sets: Vec<(&'static str, Param)>) -> Statement

UPDATE <table> SET ... WHERE ... on the matching rows (Rails’ update_all): no validation, no updated_at change unless listed.

Joins, order and limits are not part of the statement.

Examples

use ocre::{IntoParam, Query, params};

let stmt = Query::<()>::table("posts")
    .eq("author_id", 3)
    .update_statement(vec![("published", false.into_param()), ("updated_at", "2026-09-29 10:00:00".into_param())]);
assert_eq!(stmt.sql, "UPDATE posts SET published = ?1, updated_at = ?2 WHERE author_id = ?3");
assert_eq!(stmt.params, params![false, "2026-09-29 10:00:00", 3]);

pub fn delete_statement(&self) -> Statement

DELETE FROM <table> WHERE ... on the matching rows (Rails’ delete_all).

Joins, order and limits are not part of the statement. Without any condition, it deletes every row of the table.

Examples

let stmt = ocre::Query::<()>::table("sessions").lt("expires_at", 1_790_000_000).delete_statement();
assert_eq!(stmt.sql, "DELETE FROM sessions WHERE expires_at < ?1");

impl<T: DeserializeOwned> Query<T>

pub async fn all(&self, db: &Db) -> Result<Vec<T>>

Runs the query and returns every matching row.

All rows are buffered in memory: bound the query with limit or page.

Errors

Error::Internal (500, logged with the SQL) when D1 rejects the statement or a row does not deserialize into T.

Free plan

Counts every row D1 scans as a row read.

Examples

async fn drafts(ctx: &Ctx) -> Result<Vec<Post>> {
    Query::table("posts").eq("published", false).order_desc("id").limit(50).all(&ctx.db()?).await
}

pub async fn first(&self, db: &Db) -> Result<Option<T>>

Runs the query with LIMIT 1 and returns the first row, if any (Rails’ first, take and find_by).

Rows come in the query’s order; add one (order_asc) for a predictable row.

Errors

Error::Internal (500) when D1 rejects the statement or the row does not deserialize into T.

Free plan

Reads the rows D1 scans before the first match: one with an index on the filtered column.

Examples

async fn by_slug(ctx: &Ctx, slug: &str) -> Result<Post> {
    Query::table("posts").eq("slug", slug).first(&ctx.db()?).await?.or_404()
}

pub async fn paginate(&self, db: &Db, page: Page) -> Result<Paginated<T>>

Runs the query and its count_statement for page: the rows plus the total for pagination links.

Errors

Error::Internal (500) when D1 rejects either statement or a row does not deserialize.

Free plan

Two queries: the count reads every matching row (see Paginated).

Examples

use axum::extract::State;
use ocre::{ApiResult, Ctx, Json, Page, Paginated, Query};

#[derive(serde::Deserialize, serde::Serialize)]
struct Post {
    id: i64,
    title: String,
}

async fn index(State(ctx): State<Ctx>, page: Page) -> ApiResult<Json<Paginated<Post>>> {
    let posts = Query::table("posts").order_desc("id").paginate(&ctx.db()?, page).await?;
    Ok(Json(posts))
}

pub async fn first_or_create<F, Fut>(&self, db: &Db, create: F) -> Result<T>

where F: FnOnce() -> Fut, Fut: Future<Output = Result<T>>,

Returns the first matching row, or runs create when there is none (Rails’ find_or_create_by; with a New... value built in memory instead of saved, find_or_initialize_by).

Two requests can both miss and both create: back the lookup with a UNIQUE index and use create_or_first when duplicates must not happen.

Errors

The lookup’s errors, or those of create (e.g. Error::Invalid).

Free plan

One query when the row exists, plus create’s otherwise.

Examples

use ocre::{Ctx, Error, Query, Result, params};

#[derive(serde::Deserialize)]
struct Tag {
    id: i64,
    name: String,
}

async fn tag_named(ctx: &Ctx, name: &str) -> Result<Tag> {
    let db = ctx.db()?;
    Query::table("tags")
        .eq("name", name)
        .first_or_create(&db, || async {
            let sql = "INSERT INTO tags (name) VALUES (?1) RETURNING *";
            db.first(sql, params![name]).await?.ok_or_else(|| Error::internal("no row returned"))
        })
        .await
}

pub async fn create_or_first<F, Fut>(&self, db: &Db, create: F) -> Result<T>

where F: FnOnce() -> Fut, Fut: Future<Output = Result<T>>,

Runs create first and, when it fails because the value is already taken, returns the existing row instead (Rails’ create_or_find_by).

Safe against two requests racing: the table’s UNIQUE index decides and the loser reads the winner’s row. “Taken” means Error::is_taken: a generated model’s “has already been taken” validation, or D1’s UNIQUE constraint failed. The query must match the conflicting row (usually eq on the unique column).

Errors

Any other error of create; Error::NotFound when the conflicting row cannot be found by this query.

Free plan

create’s queries, plus one lookup after a conflict.

Examples

use ocre::{Ctx, Error, Query, Result, params};

#[derive(serde::Deserialize)]
struct Subscriber {
    id: i64,
    email: String,
}

// `email` has a UNIQUE index (`email:string^`).
async fn subscribe(ctx: &Ctx, email: &str) -> Result<Subscriber> {
    let db = ctx.db()?;
    Query::table("subscribers")
        .eq("email", email)
        .create_or_first(&db, || async {
            let sql = "INSERT INTO subscribers (email) VALUES (?1) RETURNING *";
            db.first(sql, params![email]).await?.ok_or_else(|| Error::internal("no row returned"))
        })
        .await
}

impl<T> Query<T>

pub async fn explain(&self, db: &Db) -> Result<Vec<String>>

The query plan, one line per step (Rails’ explain): SEARCH means an index is used, SCAN a full table read.

Runs explain_statement. Check a query while developing (log it, or return it from a debug route), not on every request.

Errors

Error::Internal (500) when D1 rejects the statement.

Free plan

One query that reads no table row.

Examples

async fn plan(ctx: &Ctx) -> Result<String> {
    let steps = Query::<()>::table("posts").eq("author_id", 3).explain(&ctx.db()?).await?;
    Ok(steps.join("\n")) // "SEARCH posts USING INDEX index_posts_on_author_id (author_id=?)"
}

pub async fn count(&self, db: &Db) -> Result<i64>

Number of matching rows (COUNT(*)), ignoring order and limits.

Errors

Error::Internal (500) when D1 rejects the statement.

Free plan

Reads every matching row (an index on the filtered columns keeps it to those).

Examples

async fn published_count(ctx: &Ctx) -> Result<i64> {
    Query::<()>::table("posts").eq("published", true).count(&ctx.db()?).await
}

pub async fn exists(&self, db: &Db) -> Result<bool>

Whether any row matches (SELECT 1 ... LIMIT 1; Rails’ exists?/any?).

Errors

Error::Internal (500) when D1 rejects the statement.

Free plan

Stops at the first match: one row read with an index.

Examples

async fn email_taken(ctx: &Ctx, email: &str) -> Result<bool> {
    Query::<()>::table("users").eq("email", email).exists(&ctx.db()?).await
}

pub async fn pluck<V: DeserializeOwned>( &self, db: &Db, expression: &'static str, ) -> Result<Vec<V>>

Values of one column (or expression) of the matching rows (Rails’ pluck/ids).

Keeps the query’s order and limits; with first-like use, add .limit(1) (Rails’ pick).

Errors

Error::Internal (500) when D1 rejects the statement or a value does not deserialize into V.

Free plan

Same rows read as all; less memory and CPU than full rows.

Examples

async fn published_ids(ctx: &Ctx) -> Result<Vec<i64>> {
    Query::<()>::table("posts").eq("published", true).limit(100).pluck(&ctx.db()?, "id").await
}

pub async fn aggregate<V: DeserializeOwned>( &self, db: &Db, expression: &'static str, ) -> Result<Option<V>>

An aggregate over the matching rows: SUM(price), AVG(rating), MIN(created_at), MAX(views), COUNT(DISTINCT author_id)…

None when SQL returns NULL (e.g. SUM or MAX of no row), so read it as Option<V>.

Errors

Error::Internal (500) when D1 rejects the statement or the value does not deserialize into V.

Free plan

Reads every matching row.

Examples

async fn average_rating(ctx: &Ctx, book_id: i64) -> Result<Option<f64>> {
    Query::<()>::table("reviews").eq("book_id", book_id).aggregate(&ctx.db()?, "AVG(stars)").await
}

pub async fn update_all( &self, db: &Db, sets: Vec<(&'static str, Param)>, ) -> Result<usize>

Updates every matching row without validation (Rails’ update_all) and returns how many changed.

Errors

Error::Internal (500) when D1 rejects the statement (e.g. a constraint).

Free plan

Counts the rows written, plus the rows scanned to find them.

Examples

async fn unpublish_author(ctx: &Ctx, author_id: i64) -> Result<usize> {
    Query::<()>::table("posts")
        .eq("author_id", author_id)
        .update_all(&ctx.db()?, vec![("published", false.into_param())])
        .await
}

pub async fn delete_all(&self, db: &Db) -> Result<usize>

Deletes every matching row (Rails’ delete_all) and returns how many were deleted.

Runs no model code: attachments in R2 stay (see storage::delete_attachments).

Errors

Error::Internal (500) when D1 rejects the statement (e.g. a foreign key).

Free plan

Counts the rows written (deleted, plus cascaded ones), plus the rows scanned.

Examples

async fn purge_expired(ctx: &Ctx) -> Result<usize> {
    Query::<()>::table("sessions").lt("expires_at", ocre::now()).delete_all(&ctx.db()?).await
}

Trait Implementations

impl<T> Clone for Query<T>

fn clone(&self) -> Self

Returns a duplicate of the value.

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

Performs copy-assignment from source.

impl<T> Debug for Query<T>

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

Formats the value using the given formatter.