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

jaredzhou

Version

0.1.4

License

Apache-2.0

Repository

github.com/jaredzhou/foxql

Last updated

20 days ago

Downloads

Dependencies

jaredzhou/moonpg0.8.9

README

#FoxQL

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

#Setup

Add to your moon.mod:

import {
  "jaredzhou/foxql@0.1.0",
}

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.

Category	Operators	Available on
Equality	eq, neq	All types
Comparison	gt, gte, lt, lte	Int, Double
Range	between(lo, hi)	Int, Double
Set	in_([vals])	All types
String	like, not_like	String
Null	is_null, is_not_null	All 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

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]

#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()

#Type-safe operators

Operator availability is checked at compile time:

Column type	gt gte lt lte between	like not_like	eq 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)

Use only when you need runtime introspection. For tables with a known, stable structure, prefer the static Column[T] proxies — they give you compile-time type safety, operator traits, and zero abort risk. SchemaTable is for building PostgREST-style dynamic APIs where table schemas are discovered at startup.

// 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. Dynamic column access via .col("name")
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()
// sql  = "SELECT users.name, users.age FROM users"
//        " WHERE (users.age > $1 AND users.name LIKE $2)"
//        " ORDER BY users.name ASC LIMIT $3"
// args = [18, "A%", 10]

// 3. Mutations work the same way
users.insert([users.col("name"), users.col("age")])
  .values(["Alice", 30])
  .returning([users.col("id")])
  .to_sql()
// sql  = "INSERT INTO users (name, age) VALUES ($1, $2) RETURNING users.id"
// args = ["Alice", 30]

users.update()
  .set(users.col("name"), "Bob")
  .where_(users.col("id").eq(1))
  .to_sql()
// sql  = "UPDATE users SET users.name = $1 WHERE users.id = $2"
// args = ["Bob", 1]

SchemaTable also implements FieldResolver, so queryx to_select works with a single argument:

let query : Query = from_json!(...)
let (sql, args) = to_select(query, users).to_sql()

#
Aggregation

source

using @jaredzhou/foxql/ast { type Aggregation }

An aggregation function call.

#
Column

source

using @jaredzhou/foxql/builder { type Column }

Typed column reference.

#
ColumnRef

source

using @jaredzhou/foxql/ast { type ColumnRef }

Type-erased column reference.

#
Expr

source

using @jaredzhou/foxql/ast { type Expr }

A WHERE-clause expression tree.

#
OrderByClause

source

using @jaredzhou/foxql/ast { type OrderByClause }

ORDER BY clause.

#
OrderDirection

source

using @jaredzhou/foxql/ast { type OrderDirection }

ORDER BY direction.

#
SelectStmt

source

using @jaredzhou/foxql/ast { type SelectStmt }

A SELECT statement AST node.

#
SqlType

source

using @jaredzhou/foxql/ast { type SqlType }

PostgreSQL column types.

#
Table

source

using @jaredzhou/foxql/builder { type Table }

Table identity.

#
Value

source

using @jaredzhou/foxql/ast { type Value }

A parameterised value.

#
FieldResolver

source

pub(open) trait FieldResolver {
  fn resolve_column(Self, field : String) -> @jaredzhou/foxql/ast.ColumnRef
ColumnRef?
  fn table_name(Self) -> String
}

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

#
Schema

source

pub(all) struct Schema {
  tables : Map[String, Map[String, @jaredzhou/foxql/ast.SqlType
SqlType]]
}

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.

#
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 : @jaredzhou/foxql/ast.SqlType
SqlType
} derive(Eq, @moonbitlang/core/debug.Debug
Debug)

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

#
SchemaTable

source

pub(all) struct SchemaTable {
  table_name : String
  columns : Map[String, @jaredzhou/foxql/ast.SqlType
SqlType]
}

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

impl FieldResolver for SchemaTable

#
SchemaTable::col

source

fn SchemaTable::col(self : SchemaTable, name : String) -> @jaredzhou/foxql/ast.ColumnRef
ColumnRef

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

#
SchemaTable::delete

source

fn SchemaTable::delete(self : SchemaTable) -> @jaredzhou/foxql/builder.DeleteBuilder
DeleteBuilder

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

fn SchemaTable::insert(self : SchemaTable, columns : Array[&@jaredzhou/foxql/builder.AnyColumn
AnyColumn]) -> @jaredzhou/foxql/builder.InsertBuilder
InsertBuilder

Start building an INSERT query.

#
SchemaTable::select

source

fn SchemaTable::select(self : SchemaTable, columns? : Array[&@jaredzhou/foxql/builder.AnyColumn
AnyColumn]) -> @jaredzhou/foxql/builder.SelectBuilder
SelectBuilder

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

#
SchemaTable::update

source

fn SchemaTable::update(self : SchemaTable) -> @jaredzhou/foxql/builder.UpdateBuilder
UpdateBuilder

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

#
avg

source

fn[T] avg(col : @jaredzhou/foxql/builder.Column
Column[T]) -> @jaredzhou/foxql/ast.Aggregation
Aggregation

AVG(column) aggregation.

#
count

source

fn[T] count(col : @jaredzhou/foxql/builder.Column
Column[T]) -> @jaredzhou/foxql/ast.Aggregation
Aggregation

COUNT(column) aggregation.

#
count_star

source

fn count_star() -> @jaredzhou/foxql/ast.Aggregation
Aggregation

COUNT(*) — count all rows.

#
max

source

fn[T] max(col : @jaredzhou/foxql/builder.Column
Column[T]) -> @jaredzhou/foxql/ast.Aggregation
Aggregation

MAX(column) aggregation.

#
min

source

fn[T] min(col : @jaredzhou/foxql/builder.Column
Column[T]) -> @jaredzhou/foxql/ast.Aggregation
Aggregation

MIN(column) aggregation.

#
not_

source

fn not_(expr : @jaredzhou/foxql/ast.Expr
Expr) -> @jaredzhou/foxql/ast.Expr
Expr

Wrap an expression with NOT.

#
sum

source

fn[T] sum(col : @jaredzhou/foxql/builder.Column
Column[T]) -> @jaredzhou/foxql/ast.Aggregation
Aggregation

SUM(column) aggregation.

Source Files

Site source Report issue Packages Build queue Skills Statistics

foxql

#FoxQL

#Setup

#Quick start

#SELECT

#WHERE operators

#Condition composition

#ORDER BY, LIMIT, OFFSET, DISTINCT

#INSERT / UPDATE / DELETE

#INSERT

#UPDATE

#DELETE

#ON CONFLICT (upsert)

#JOIN

#Aggregation & GROUP BY

#Subqueries

#Schema-qualified tables

#Building AST nodes

#Type-safe operators

#Package structure

#Dynamic schema (SchemaTable)

#Aggregation

#Column

#ColumnRef

#Expr

#OrderByClause

#OrderDirection

#SelectStmt

#SqlType

#Table

#Value

#FieldResolver

#Schema

#Schema::load

#Schema::table

#SchemaColumn

#SchemaTable

#SchemaTable::col

#SchemaTable::delete

#SchemaTable::has_column

#SchemaTable::insert

#SchemaTable::select

#SchemaTable::update

#avg

#count

#count_star

#max

#min

#not_

#sum

Source Files

#
Aggregation

#
Column

#
ColumnRef

#
Expr

#
OrderByClause

#
OrderDirection

#
SelectStmt

#
SqlType

#
Table

#
Value

#
FieldResolver

#
Schema

#
Schema::load

#
Schema::table

#
SchemaColumn

#
SchemaTable

#
SchemaTable::col

#
SchemaTable::delete

#
SchemaTable::has_column

#
SchemaTable::insert

#
SchemaTable::select

#
SchemaTable::update

#
avg

#
count

#
count_star

#
max

#
min

#
not_

#
sum