#moonbit-sql

#Changes in 0.2.0

• Breaking: remove pg and decode from the module and drop their dependencies. Use ast + builder with an application-owned driver adapter; see migration. Old source and tests remain in legacy/.
• Preserve nested WITH clauses, set-operation grouping, and operand-local clauses.
• Preserve GROUP BY parameter identities across SELECT, HAVING, and ORDER BY without merging unrelated literal parameters.
• Capture independent AST snapshots when composing queries.
• Add a node-postgres example, execution tests, and CI for the pinned toolchain.

#Scope

• SELECT / INSERT / UPDATE / DELETE
• Quoted identifiers and separate $1, $2, ... parameters
• Subqueries, CTEs, set operations, upsert, CASE, and window functions
• Pure MoonBit core, tested on JS, Wasm, Wasm GC, and native

#Installation

moon add ash1day/sql

import {
  "ash1day/sql/ast",
  "ash1day/sql/builder",
}

#Quick Start

let q = @builder.select()
  .columns(["id", "name", "email"])
  .from("users")
  .where_(@builder.col("active").eq(@builder.val_bool(true)))
  .order_by("name", @ast.Asc)
  .limit(20)
  .build()

// q.sql    => SELECT "id", "name", "email" FROM "users" WHERE ("active" = $1) ORDER BY "name" ASC LIMIT $2
// q.params => [Bool(true), Int(20)]
assert_eq(q.params, [@ast.Bool(true), @ast.Int(20)])

#SELECT

// Basic
let _ = @builder.select()
  .columns(["id", "name"])      // specific columns
  .from("users")
  .build()

// All columns (*)
let _ = @builder.select().all().from("users").build()

// DISTINCT
let _ = @builder.select().distinct().columns(["country"]).from("users").build()

// FROM with alias
let _ = @builder.select().all().from_as("users", "u").build()

// FROM with schema
let _ = @builder.select().all().from_schema("public", "users").build()

// Single column
let _ = @builder.select().column("id").from("users").build()

// Expression with alias
let _ = @builder.select()
  .expr_as(@builder.col("price").mul(@builder.val_int(2)), "double_price")
  .from("products")
  .build()

// table.*
let _ = @builder.select().table_all_columns("users").from("users").build()

#WHERE

let _ = @builder.select()
  .all()
  .from("users")
  .where_(@builder.col("age").gte(@builder.val_int(18)))
  .where_(@builder.col("active").eq(@builder.val_bool(true)))
  .build()
// WHERE (("age" >= $1) AND ("active" = $2))

#JOIN

let _ = @builder.select()
  .expr(@builder.table_col("orders", "id"))
  .expr(@builder.table_col("users", "name"))
  .from("orders")
  .left_join("users", @builder.table_col("orders", "user_id").eq(@builder.table_col("users", "id")))
  .build()

// With alias
let _ = @builder.select()
  .all()
  .from("orders")
  .left_join_as("users", "u", @builder.table_col("orders", "user_id").eq(@builder.table_col("u", "id")))
  .build()

#GROUP BY / HAVING

let _ = @builder.select()
  .columns(["department"])
  .expr_as(@builder.func("COUNT", [@builder.asterisk()]), "cnt")
  .from("employees")
  .group_by("department")
  .having(@builder.func("COUNT", [@builder.asterisk()]).gt(@builder.val_int(5)))
  .build()

#ORDER BY / LIMIT / OFFSET

let _ = @builder.select()
  .all()
  .from("products")
  .order_by("price", @ast.Desc)
  .limit(10)
  .offset(20)
  .build()

#INSERT

// Single row
let _ = @builder.insert_into("users")
  .columns(["name", "email"])
  .values([@builder.val_str("Alice"), @builder.val_str("alice@example.com")])
  .build()

// Multi-row
let _ = @builder.insert_into("users")
  .columns(["name", "email"])
  .values([@builder.val_str("Alice"), @builder.val_str("alice@example.com")])
  .values([@builder.val_str("Bob"),   @builder.val_str("bob@example.com")])
  .build()

// RETURNING
let _ = @builder.insert_into("users")
  .columns(["name"])
  .values([@builder.val_str("Carol")])
  .returning(["id", "created_at"])
  .build()

#UPDATE

let _ = @builder.update("users")
  .set("name", @builder.val_str("Bob"))
  .set("active", @builder.val_bool(false))
  .where_(@builder.col("id").eq(@builder.val_int(42)))
  .returning(["id", "name"])
  .build()

#DELETE

let _ = @builder.delete_from("users")
  .where_(@builder.col("id").in_list([@builder.val_int(1), @builder.val_int(2), @builder.val_int(3)]))
  .returning(["id"])
  .build()

#Expression Helpers

#Expression Operators

#Advanced Features

#Subqueries

// FROM subquery
let sub = @builder.select()
  .columns(["id", "name"])
  .from("users")
  .where_(@builder.col("active").eq(@builder.val_bool(true)))

let _ = @builder.select().all().from_sub(sub, "active_users").build()
// SELECT * FROM (SELECT "id", "name" FROM "users" WHERE ("active" = $1)) AS "active_users"

// NOT IN subquery
let banned = @builder.select().columns(["user_id"]).from("bans")
let _ = @builder.select()
  .all()
  .from("users")
  .where_(@builder.not_in_sub(@builder.col("id"), banned))
  .build()

#CTEs (WITH)

let active = @builder.select()
  .columns(["id", "name"])
  .from("users")
  .where_(@builder.col("active").eq(@builder.val_bool(true)))

let _ = @builder.select()
  .with_("active_users", active)
  .all()
  .from("active_users")
  .build()
// WITH "active_users" AS (SELECT "id", "name" FROM "users" WHERE ("active" = $1))
// SELECT * FROM "active_users"

#Set Operations

let _ = @builder.select().columns(["id"]).from("admins")
  .union(@builder.select().columns(["id"]).from("moderators"))
  .build()
// SELECT "id" FROM "admins" UNION SELECT "id" FROM "moderators"

#Upsert (INSERT ON CONFLICT)

let _ = @builder.insert_into("users")
  .columns(["id", "name", "email"])
  .values([@builder.val_int(1), @builder.val_str("Alice"), @builder.val_str("alice@example.com")])
  .on_conflict_do_update(["id"], [("name", @builder.table_col("excluded", "name"))])
  .build()
// INSERT INTO "users" ("id", "name", "email") VALUES ($1, $2, $3)
// ON CONFLICT ("id") DO UPDATE SET "name" = "excluded"."name"

// Do nothing on conflict
let _ = @builder.insert_into("users")
  .columns(["id", "name"])
  .values([@builder.val_int(1), @builder.val_str("Alice")])
  .on_conflict_do_nothing(["id"])
  .build()

#Window Functions

let _ = @builder.select()
  .expr_as(
    @builder.window_func(
      "ROW_NUMBER",
      [],
      partition_by=["department"],
      order_by=[("salary", @ast.Desc)]
    ),
    "rank"
  )
  .from("employees")
  .build()
// SELECT ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
// FROM "employees"

#Executing with a driver

const result = await client.query({ text: q.sql, values: driverValues });

npm ci
DATABASE_URL=postgresql://localhost/mydb node examples/node-postgres.mjs

#Composition semantics

• Builders are mutable. Chained calls modify the same builder. Construct a fresh builder when branching a query; assigning it to another variable does not copy it.
• build_statement() takes a deep snapshot. Subqueries, CTEs and set operands also capture snapshots. Later changes to the source builder or expression arrays do not change a captured statement.
• Set operations chain left-to-right. a.union(b).intersect(c) means (a UNION b) INTERSECT c. Clauses on the outer builder apply to the combined result. Clauses inside b apply only to that operand.
• Nested WITH clauses are retained, including in INSERT ... SELECT.
• GROUP BY expressions share parameter identities within one SELECT. Structurally equal grouping expressions reuse placeholders in SELECT (including subexpressions), GROUP BY, HAVING, and ORDER BY, even if absent from the SELECT list. Bare literal values are excluded: equal values in unrelated type contexts remain separate parameters. This reuse does not cross subquery boundaries; correlated subqueries referencing parameterized outer grouping expressions are not supported by this reuse mechanism.
• Identifiers are literal names. col("users.id") names a single column containing a dot. Use table_col("users", "id") for qualification and .expr(...) to select it. String-based columns, group_by, order_by and returning follow the same literal-name rule.

#Migrating from pg and decode

• Native parameter substitution also rewrites $N inside quoted SQL. It is not protocol-level binding, and its escaping must not be treated as a general injection-safety guarantee.
• The native dependency loses NULL versus empty-string information and mishandles non-ASCII text. Its value heuristics also turn text such as "00123" and "t" into integers or booleans.
• The JS adapter sends Int64 through JSON numbers, losing precision above 2^53. Neither adapter reliably produces the ColumnValue::Int64 variant.

#Development

curl -fsSL https://cli.moonbitlang.com/install/unix.sh -o /tmp/install-moon.sh
bash /tmp/install-moon.sh "$(cat .moonbit-version)"
moon update
moon check --target all
moon test --target all
python3 scripts/check-readme.py

npm ci
DATABASE_URL=postgresql://localhost/postgres npm run test:postgres

#Inspiration

• sea-query (Rust) — ADT-based AST and expression design
• squirrel (Go) — Builder API design

#License