🥮 mooncakes.io

foxql

Compile-time type-safe PostgreSQL SQL builder with fluent API

sql
postgresql
query-builder
type-safe
moon add jaredzhou/foxql@0.1.4
Download zip
Author
Version
0.1.4
License
Apache-2.0
Repository
Last updated
20 days ago
Downloads
17

Dependencies

README

#FoxQL

Compile-time type-safe SQL builder for PostgreSQL in MoonBit.

#Setup

moon add jaredzhou/foxql@0.1.4

Then import in your moon.pkg:

import {
  "jaredzhou/foxql",
}

#Quick start

Define table proxies with typed columns, then build queries fluently.

///|
struct UserTable {
  table : Table
  id : Column[Int]
  name : Column[String]
  age : Column[Int]
}

///|
let users : UserTable = {
  table: Table::new("users"),
  id: Column::new("id", "users", SqlType::Integer),
  name: Column::new("name", "users", SqlType::Text),
  age: Column::new("age", "users", SqlType::Integer),
}

///|
struct OrdersTable {
  table : Table
  id : Column[Int]
  user_id : Column[Int]
  amount : Column[Int]
}

///|
let orders : OrdersTable = {
  table: Table::new("orders"),
  id: Column::new("id", "orders", SqlType::Integer),
  user_id: Column::new("user_id", "orders", SqlType::Integer),
  amount: Column::new("amount", "orders", SqlType::Integer),
}

#SELECT

let (sql, args) = users.table.select().to_sql()
// sql  = "SELECT * FROM users"
// args = []

let (sql, args) = users.table.select(columns=[users.name, users.age]).to_sql()
// sql  = "SELECT users.name, users.age FROM users"
// args = []

let (sql, args) = users.table.select().where_(users.age.eq(18)).to_sql()
// sql  = "SELECT * FROM users WHERE users.age = $1"
// args = [18]

#WHERE operators

All operators are type-safe at compile time — Column[Int] gets comparison operators, Column[String] gets string operators, Column[Bool] gets only equality.

CategoryOperatorsAvailable on
Equalityeq, neqAll types
Comparisongt, gte, lt, lteInt, Double
Rangebetween(lo, hi)Int, Double
Setin_([vals])All types
Stringlike, not_likeString
Nullis_null, is_not_nullAll types

users.age.gt(18)
// → users.age > $1  [18]

users.age.between(18, 65)
// → users.age BETWEEN $1 AND $2  [18, 65]

users.name.like("Al%")
// → users.name LIKE $1  ["Al%"]

users.name.is_null()
// → users.name IS NULL  []

users.age.in_([1, 2, 3])
// → users.age IN ($1, $2, $3)  [1, 2, 3]

#Condition composition

Combine conditions with .and_(), .or_(), and not_(). All conditions are values of type Filter (an alias for Expr — the underlying AST type).

let (sql, args) = users.table.select()
  .where_(users.age.gte(18).and_(users.age.lte(65)))
  .to_sql()
// sql  = "SELECT * FROM users WHERE (users.age >= $1 AND users.age <= $2)"
// args = [18, 65]

let (sql, args) = users.table.select()
  .where_(users.name.eq("Alice").or_(users.name.eq("Bob")))
  .to_sql()
// sql  = "SELECT * FROM users WHERE (users.name = $1 OR users.name = $2)"
// args = ["Alice", "Bob"]

let (sql, args) = users.table.select()
  .where_(not_(users.age.eq(0)))
  .to_sql()
// sql  = "SELECT * FROM users WHERE NOT (users.age = $1)"
// args = [0]

// Complex nesting
let (sql, args) = users.table.select()
  .where_(users.age.lt(18).or_(users.age.gt(65)).and_(users.name.is_not_null()))
  .to_sql()
// sql  = "SELECT * FROM users WHERE ((users.age < $1 OR users.age > $2) AND users.name IS NOT NULL)"
// args = [18, 65]

#Building filters from optional conditions

Use empty() as a starting point, then .and_() on each optional condition. Empty is the identity element — it absorbs into AND/OR and renders as nothing.

// Build a filter from optional request params + mandatory ACL
fn build_filter(
  name : String?,
  min_age : Int?,
  tenant_id : Int,
) -> Filter {
  let f = empty()
  // optional — only applied when Some
  match name { Some(n) => f = f.and_(users.name.eq(n)), None => () }
  match min_age { Some(a) => f = f.and_(users.age.gte(a)), None => () }
  // mandatory — always applied
  f.and_(users.tenant_id.eq(tenant_id))
}

let (sql, args) = users.table.select().where_(build_filter(Some("A%"), None, 42)).to_sql()
// sql  = "SELECT * FROM users WHERE (users.name = $1 AND users.tenant_id = $2)"
// args = ["A%", 42]

#Multiple where_() calls

Call .where_() multiple times to build WHERE clauses across different layers (e.g. business logic + repository filters). Each call is AND-ed with the previous conditions.

// Layer 1: user-facing filters
let query = users.table.select().where_(users.age.gte(18))

// Layer 2: repo-internal soft-delete filter
let (sql, args) = query.where_(users.deleted_at.is_null()).to_sql()
// sql  = "SELECT * FROM users WHERE (users.age >= $1 AND users.deleted_at IS NULL)"
// args = [18]

This also works on UPDATE and DELETE via their *Ready types:

let (sql, args) = users.table.delete()
  .where_(users.age.lt(18))
  .where_(users.status.eq("inactive"))
  .to_sql()
// sql  = "DELETE FROM users WHERE (users.age < $1 AND users.status = $2)"
// args = [18, "inactive"]

#Raw SQL

Use raw() to embed hand-written SQL fragments. Placeholders ($1, $2, …) are auto-renumbered to fit the surrounding query — you always start from $1 in your fragment.

// Standalone
let (sql, args) = users.table.select()
  .where_(raw("users.age > $1 AND users.age < $2", [18, 65]))
  .to_sql()
// sql  = "SELECT * FROM users WHERE users.age > $1 AND users.age < $2"
// args = [18, 65]

// Combined with typed filters (multiple where_ = AND)
let (sql, args) = users.table.select()
  .where_(users.name.is_not_null())
  .where_(raw("users.age BETWEEN $1 AND $2", [18, 65]))
  .to_sql()
// sql  = "SELECT * FROM users WHERE (users.name IS NOT NULL AND users.age BETWEEN $1 AND $2)"
// args = [18, 65]

// Renumbering: SET params push raw placeholders up
let (sql, args) = users.table.update()
  .set(users.name, "Dave")
  .set(users.age, 99)
  .where_(raw("users.id = $1 OR users.status = $2", [1, "active"]))
  .to_sql()
// sql  = "UPDATE users SET users.name = $1, users.age = $2 WHERE users.id = $3 OR users.status = $4"
// args = ["Dave", 99, 1, "active"]

#ORDER BY, LIMIT, OFFSET, DISTINCT

let (sql, args) = users.table.select()
  .order_by(users.name.asc())
  .order_by(users.age.desc())
  .limit(10)
  .offset(20)
  .to_sql()
// sql  = "SELECT * FROM users ORDER BY users.name ASC, users.age DESC LIMIT $1 OFFSET $2"
// args = [10, 20]

let (sql, args) = users.table.select(columns=[users.name]).distinct().to_sql()
// sql  = "SELECT DISTINCT users.name FROM users"
// args = []

let (sql, args) = users.table.select(columns=[users.name, users.age])
  .distinct_on([users.name]).to_sql()
// sql  = "SELECT DISTINCT ON (users.name) users.name, users.age FROM users"
// args = []

#INSERT / UPDATE / DELETE

#INSERT

let (sql, args) = users.table.insert([users.name, users.age])
  .values(["Alice", 30])
  .to_sql()
// sql  = "INSERT INTO users (name, age) VALUES ($1, $2)"
// args = ["Alice", 30]

let (sql, args) = users.table.insert([users.name, users.age])
  .rows([["Alice", 30], ["Bob", 25]])
  .to_sql()
// sql  = "INSERT INTO users (name, age) VALUES ($1, $2), ($3, $4)"
// args = ["Alice", 30, "Bob", 25]

let (sql, args) = users.table.insert([users.name, users.age])
  .values(["Alice", 30])
  .returning([users.id])
  .to_sql()
// sql  = "INSERT INTO users (name, age) VALUES ($1, $2) RETURNING users.id"
// args = ["Alice", 30]

#UPDATE

Type-state enforced: .where_() is required before .to_sql().

let (sql, args) = users.table.update()
  .set(users.name, "Dave")
  .set(users.age, 99)
  .where_(users.id.eq(1))
  .returning([users.id, users.name])
  .to_sql()
// sql  = "UPDATE users SET users.name = $1, users.age = $2 WHERE users.id = $3 RETURNING users.id, users.name"
// args = ["Dave", 99, 1]

#DELETE

Type-state enforced: .where_() is required before .to_sql().

let (sql, args) = users.table.delete()
  .where_(users.age.lt(18))
  .returning([users.id])
  .to_sql()
// sql  = "DELETE FROM users WHERE users.age < $1 RETURNING users.id"
// args = [18]

#ON CONFLICT (upsert)

let (sql, args) = users.table.insert([users.name, users.age])
  .values(["Alice", 30])
  .on_conflict([users.name])
  .do_nothing()
  .to_sql()
// sql  = "INSERT INTO users (name, age) VALUES ($1, $2) ON CONFLICT (name) DO NOTHING"
// args = ["Alice", 30]

let (sql, args) = users.table.insert([users.name, users.age])
  .values(["Alice", 30])
  .on_conflict([users.name])
  .do_update(users.age, 30)
  .to_sql()
// sql  = "INSERT INTO users (name, age) VALUES ($1, $2) ON CONFLICT (name) DO UPDATE SET users.age = $3"
// args = ["Alice", 30, 30]

#JOIN

let (sql, args) = users.table
  .select(columns=[users.name, orders.amount])
  .join(orders.table, users.id.eq_col(orders.user_id))
  .where_(orders.amount.gt(100))
  .order_by(orders.amount.desc())
  .to_sql()
// sql  = "SELECT users.name, orders.amount FROM users"
//        " INNER JOIN orders ON users.id = orders.user_id"
//        " WHERE orders.amount > $1"
//        " ORDER BY orders.amount DESC"
// args = [100]

#Aggregation & GROUP BY

let (sql, args) = orders.table
  .select(columns=[orders.user_id])
  .agg(sum(orders.amount))
  .agg(count_star())
  .group_by([orders.user_id])
  .having(sum(orders.amount).gt(1000))
  .order_by(orders.user_id.asc())
  .to_sql()
// sql  = "SELECT orders.user_id, SUM(orders.amount), COUNT(*)"
//        " FROM orders"
//        " GROUP BY orders.user_id"
//        " HAVING SUM(orders.amount) > $1"
//        " ORDER BY orders.user_id ASC"
// args = [1000]

#Subqueries

// WHERE … IN (SELECT …)
let sub = orders.table.select(columns=[orders.user_id])
  .where_(orders.amount.gt(100)).build()
let (sql, args) = users.table.select()
  .where_(users.id.in_select(sub)).to_sql()
// sql  = "SELECT * FROM users WHERE users.id IN (SELECT orders.user_id FROM orders WHERE orders.amount > $1)"
// args = [100]

// Scalar subquery
let max_age = users.table.select(columns=[users.age])
  .order_by(users.age.desc()).limit(1).build()
let (sql, args) = users.table.select()
  .where_(users.age.eq_select(max_age)).to_sql()
// sql  = "SELECT * FROM users WHERE users.age = (SELECT users.age FROM users ORDER BY users.age DESC LIMIT $1)"
// args = [1]

#Schema-qualified tables

///|
let table = Table::new("profiles", schema="public")
// → "public"."profiles"

#Building AST nodes

Use .build() instead of .to_sql() to get the AST node for subqueries:

///|
let stmt : SelectStmt = users.table.select().where_(users.age.gt(18)).build()

#ToSql trait

All types that can be rendered implement the ToSql trait — call .to_sql() on any of them to get (sql, args).

using foxql.ToSql

// Filter (standalone WHERE condition)
let (sql, args) = users.age.gte(18).and_(users.name.like("A%")).to_sql()
// sql  = "(users.age >= $1 AND users.name LIKE $2)"
// args = [18, "A%"]

// AST nodes (.build()) — useful for subqueries
let stmt : SelectStmt = orders.select(columns=[orders.user_id])
  .where_(orders.amount.gt(100)).build()
let (sql, args) = stmt.to_sql()

TypeImplements ToSql
Filterbare WHERE conditions
SelectStmt, InsertStmt, UpdateStmt, DeleteStmtAST nodes from .build()
SelectBuilder, InsertBuilder, DeleteReady, UpdateReadyfluent builders

#Type-safe operators

Operator availability is checked at compile time:

Column typegt gte lt lte betweenlike not_likeeq neq is_null
Column[Int]✅❌✅
Column[Double]✅❌✅
Column[String]❌✅✅
Column[Bool]❌❌✅

// These compile:
users.age.gt(18)
users.name.like("A%")

// These fail at compile time:
// users.age.like("A%")   // Int does not implement StringMatchable
// users.name.gt(18)      // String does not implement Comparable

#Package structure

foxql/
├── ast/          # AST types (SelectStmt, Expr, Value, ...)
├── builder/      # Fluent builders (Table, Column, SelectBuilder, ...)
├── tosql/        # SQL rendering
├── schema.mbt    # Dynamic schema (Schema, SchemaTable)
├── foxql.mbt     # Re-exports (Table, Column, SqlType, count, sum, ...)
└── foxql_test.mbt

Import the root package for the common surface:

import { "jaredzhou/foxql" }

Or sub-packages for fine-grained control:

import {
  "jaredzhou/foxql/ast",
  "jaredzhou/foxql/builder",
}

#Dynamic schema (SchemaTable)

When table structures are not known at compile time, use Schema::load to introspect at runtime. All the same builders — select(), insert(), update(), delete() — work via .col("name") instead of typed Column[T] fields. Use only when you need runtime introspection; prefer static proxies otherwise.

// 1. Introspect on startup (query information_schema in your app)
let schema = Schema::load([
  { table_name: "users", column_name: "id",   sql_type: SqlType::Integer },
  { table_name: "users", column_name: "name", sql_type: SqlType::Text },
  { table_name: "users", column_name: "age",  sql_type: SqlType::Integer },
])

let users = schema.table("users")

// 2. SELECT with dynamic columns
let (sql, args) = users
  .select(columns=[users.col("name"), users.col("age")])
  .where_(users.col("age").gt(18).and_(users.col("name").like("A%")))
  .order_by(users.col("name").asc())
  .limit(10)
  .to_sql()

// 3. INSERT / UPDATE / DELETE work the same way
users.insert([users.col("name"), users.col("age")])
  .values(["Alice", 30])
  .to_sql()

users.update()
  .set(users.col("name"), "Bob")
  .where_(users.col("id").eq(1))
  .to_sql()

#
Aggregation

source

An aggregation function call.

#
Column

source

Typed column reference.

#
ColumnRef

source

Type-erased column reference.

#
DeleteBuilder

source

delete builder (pending — no WHERE yet)

#
DeleteReady

source

delete builder (ready — WHERE attached)

#
DeleteStmt

source

A DELETE statement AST node.

#
Expr

source
using @jaredzhou/foxql/ast { type Expr }

A WHERE-clause expression tree.

#
Filter

source
using @jaredzhou/foxql/ast { type Expr as Filter }

A WHERE/HAVING/JOIN filter — same as Expr, with a business-layer-friendly name.

#
InsertBuilder

source

insert builder

#
InsertStmt

source

An INSERT statement AST node.

#
OrderByClause

source

ORDER BY clause.

#
OrderDirection

source

ORDER BY direction.

#
SelectBuilder

source

select builder

#
SelectStmt

source

A SELECT statement AST node.

#
SqlType

source
type SqlType = String

PostgreSQL column types.

#
Table

source

Table identity.

#
UpdateBuilder

source

update builder (pending — no WHERE yet)

#
UpdateReady

source

update builder (ready — WHERE attached)

#
UpdateStmt

source

An UPDATE statement AST node.

#
FieldResolver

source
pub(open) trait FieldResolver {
  fn resolve_column(Self, field : String) -> 
ColumnRef?
  fn table_name(Self) -> String
}

Trait for resolving string field names to ColumnRefs. Used by queryx and other dynamic query builders.

#
ToSql

source
pub trait ToSql {
  fn to_sql(Self) -> (String, Array[&
ToValue])
}

Trait for types that can be rendered to parameterised SQL.
impl ToSql for DeleteStmt
impl ToSql for Expr
impl ToSql for InsertStmt
impl ToSql for SelectStmt
impl ToSql for UpdateStmt

#
Schema

source
pub(all) struct Schema {
  tables : Map[String, SchemaTable]
}

Introspected schema. Build with Schema::load.

#
Schema::load

source
fn Schema::load(columns : Array[SchemaColumn]) -> Schema

Load schema from a flat list of column descriptors. Columns must be grouped by table; primary_key is derived from the first column with is_primary_key == true in each table group.

#
Schema::table

source
fn Schema::table(self : Schema, name : String) -> SchemaTable

Get a dynamic table reference. Panics if the table is not in the schema.

#
SchemaColumn

source
pub(all) struct SchemaColumn {
  table_name : String
  column_name : String
  sql_type : String
  is_primary_key : Bool
} derive(Eq, 
Debug)

One column's metadata, typically from information_schema.columns.

#
SchemaTable

source
pub(all) struct SchemaTable {
  table_name : String
  columns : Map[String, SchemaColumn]
  column_names : Array[String]
  primary_key : String?
}

A dynamic table reference. Exposes .select() / .insert() / .update() / .delete().

#
SchemaTable::col

source

Get a ColumnRef for a column. Panics if the column is not in the schema.

#
SchemaTable::delete

source

Start building a DELETE query. Requires .where_() before .to_sql().

#
SchemaTable::has_column

source
fn SchemaTable::has_column(self : SchemaTable, name : String) -> Bool

Check whether a column exists.

#
SchemaTable::insert

source

Start building an INSERT query.

#
SchemaTable::select

source

Start building a SELECT query. Omit columns for SELECT *.

#
SchemaTable::update

source

Start building an UPDATE query. Requires .where_() before .to_sql().

#
avg

source

AVG(column) aggregation.

#
count

source

COUNT(column) aggregation.

#
count_star

source

COUNT(*) — count all rows.

#
empty

source

The empty filter — does nothing. Use as a starting point when building filters from many optional conditions with .and_().

#
max

source

MAX(column) aggregation.

#
min

source

MIN(column) aggregation.

#
not_

source

Wrap a filter with NOT.

#
raw

source

Create a raw SQL filter with parameterised arguments. Placeholders ($1, $2, ...) are automatically renumbered to fit the query.

#
sum

source

SUM(column) aggregation.

Source Files

Powered by MoonBit

Site sourceReport issuePackagesBuild queueSkillsStatistics

© 2026 mooncakes.io