#sqlc-gen-moonbit

⚠️ Experimental: This project is experimental and under active development. APIs may change without notice.

#Supported Backends

#Features

• Generates type-safe MoonBit structs from SQL schemas
• Generates query functions with proper parameter binding
• Custom type overrides via overrides option
• Supports :one, :many, :exec query types
• Optional validators and JSON Schema generation

#Installation

#WASM Plugin (Recommended)

version: "2"
plugins:
  - name: moonbit
    wasm:
      url: "https://github.com/mizchi/sqlc_gen_moonbit/releases/download/v0.4.0/sqlc-gen-moonbit.wasm"
      sha256: "8639024dc5ee7271e1b4a8dfec853e85a72814577d66f30810b64270f7fead05"
sql:
  - engine: sqlite
    schema: "schema.sql"
    queries: "query.sql"
    codegen:
      - plugin: moonbit
        out: "gen"
        options:
          backend: "sqlite"  # or "d1" for Cloudflare D1

#Build from Source

moon update
moon build --target wasm ./cmd/wasm
# Output: target/wasm/release/build/cmd/wasm/wasm.wasm

plugins:
  - name: moonbit
    wasm:
      url: "file://./path/to/wasm.wasm"
      sha256: ""  # Optional for local files

#Usage

#1. Define your schema

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

#2. Write queries with sqlc annotations

-- name: GetUser :one
SELECT * FROM users WHERE id = ?;

-- name: ListUsers :many
SELECT * FROM users ORDER BY name;

-- name: CreateUser :exec
INSERT INTO users (name, email) VALUES (?, ?);

#3. Generate code

sqlc generate

#4. Use generated code

{
  "deps": {
    "mizchi/sqlite": "0.1.3",
    "moonbitlang/x": "0.4.40"
  }
}

fn main {
  let db = @sqlite.sqlite_open_v2(...)

  // Create user
  @gen.create_user(db, @gen.CreateUserParams::new("Alice", "alice@example.com"))

  // List users
  let users = @gen.list_users(db)

  // Get user by ID
  match @gen.get_user(db, @gen.GetUserParams::new(1L)) {
    Some(user) => println("Found: " + user.name)
    None => println("Not found")
  }
}

{
  "deps": {
    "mizchi/cloudflare": "0.1.6",
    "mizchi/js": "0.10.10",
    "moonbitlang/async": "0.16.0"
  }
}

///|
pub async fn handler(
  db : @cloudflare.D1Database,
) -> Unit raise @cloudflare.D1Error {
  // Create user
  @gen.create_user(db, @gen.CreateUserParams::new("Alice", "alice@example.com"))

  // List users (async)
  let users = @gen.list_users(db)

  // Get user by ID (async)
  match @gen.get_user(db, @gen.GetUserParams::new(1L)) {
    Some(user) => println("Found: " + user.name)
    None => println("Not found")
  }
}

{
  "deps": {
    "mizchi/npm_typed": "0.1.2",
    "mizchi/js": "0.10.10",
    "moonbitlang/x": "0.4.40"
  }
}

npm install pg

///|
async fn run_tests_inner() -> Unit {
  let pool = @pg.Pool::new(
    host="localhost",
    port=5432,
    user="postgres",
    password="postgres",
    database="mydb",
  )

  // Create user (async, returns inserted id)
  let user_id = @gen.create_user(
    pool,
    @gen.CreateUserParams::new("Alice", "alice@example.com"),
  )

  // List users (async)
  let users = @gen.list_users(pool)

  // Get user by ID (async)
  match @gen.get_user(pool, @gen.GetUserParams::new(user_id)) {
    Some(user) => println("Found: \{user.name}")
    None => println("Not found")
  }
  pool.end()
}

///|
async fn run_tests() -> Unit noraise {
  run_tests_inner() catch {
    e => println("Error: \{e}")
  }
}

///|
fn main {
  @core.run_async(run_tests)
}

#Plugin Options

codegen:
  - plugin: moonbit
    out: "db/gen"
    options:
      backend: "d1"
      validators: true
      json_schema: true
      overrides:
        - column: "users.id"
          moonbit_type: "UserId"
        - db_type: "uuid"
          moonbit_type: "@mypackage.UUID"

#Type Overrides

#Generated Code

• Param structs: GetUserParams, CreateUserParams with ::new() constructor
• Row structs: GetUserRow, ListUsersRow with fields matching SELECT columns
• Query functions: get_user(), list_users(), create_user() with type-safe parameters
• Validators (optional): GetUserParams::validate() returning Result[Unit, String]
• JSON Schema (optional): sqlc_schema.json with type definitions

#Query Types

#D1 Int64 parameters bind as JavaScript Number

assert.deepEqual(call.params, ["mizchi", 100, 0]);

#Standalone Code Generation

#CLI Tool

# Clone and build
git clone https://github.com/mizchi/sqlc_gen_moonbit
cd sqlc_gen_moonbit

# Show help
moon run tools/codegen --target native -- --help

# Generate code (stdout)
moon run tools/codegen --target native -- queries.sql

# Generate to file with D1 backend
moon run tools/codegen --target native -- -b d1 -o db/gen/sqlc_queries.mbt queries.sql

# Check dependencies
moon run tools/codegen --target native -- --check-deps -b d1 queries.sql

{
  "backend": "sqlite",
  "validators": true,
  "json_schema": false,
  "overrides": [
    { "column": "users.id", "moonbit_type": "UserId" },
    { "db_type": "uuid", "moonbit_type": "@uuid.UUID" }
  ]
}

#SQL File Format

-- @query GetUser :one
-- @param id INTEGER
-- @returns id INTEGER, name TEXT, email TEXT
SELECT * FROM users WHERE id = ?;

-- @query ListUsers :many
-- @returns id INTEGER, name TEXT, email TEXT
SELECT * FROM users ORDER BY name;

-- @query CreateUser :exec
-- @param name TEXT
-- @param email TEXT
INSERT INTO users (name, email) VALUES (?, ?);

#Library API

moon add mizchi/sqlc_gen_moonbit

///|
fn main {
  let sql_content = @fs.read_file_to_string("queries.sql") catch {
    e => panic()
  }
  let code = @codegen.generate_from_sql(sql_content)
  println(code)
}

import {
  "moonbitlang/x/fs",
  "mizchi/sqlc_gen_moonbit/lib/codegen",
}

options(
  "is-main": true,
)

moon run tasks > db/gen/sqlc_queries.mbt

#Examples

• examples/sqlite_native - SQLite with native binding
• examples/sqlite_js - SQLite with JS target (Node.js / Browser)
• examples/d1 - Cloudflare Worker with D1
• examples/postgres_native - PostgreSQL with native binding
• examples/postgres_js - PostgreSQL with JS target (Node.js)
• examples/mysql_js - MySQL with JS target (Node.js)

#Coexisting with extern "js"

1. The MoonBit async fn calls the sqlc-generated query and serialises the rows into a JSON array string.
2. An extern "js" function (no SQL, no DB access) takes that JSON plus any rendering context and returns the final string / HTML / markdown.

// 1. Sqlc-generated query lives in the `@db` package.

///|
async fn list_pages_markdown(
  binding : String,
  handle : String,
  limit : Int,
  offset : Int,
) -> String raise Error {
  let db = d1_binding(binding)
  let params = @db.ListPagesParams::new(
    handle,
    limit.to_int64(),
    offset.to_int64(),
  )
  let rows = @db.list_pages(db, params)
  let rows_json = serialise_rows(rows) // builds Json::array(...).stringify()
  // 2. JS renderer takes the JSON, no DB access here.
  render_pages_markdown(rows_json, handle)
}

///|
extern "js" fn render_pages_markdown(
  rows_json : String,
  handle : String,
) -> String =
  #| (rowsJson, handle) => {
  #|   const rows = JSON.parse(rowsJson);
  #|   // ...pure string building (escape, sort, format)...
  #|   return out;
  #| }

• Drop .wait() in callers that previously expected a @js_async.Promise[String] — a MoonBit async fn returns String directly.
• D1 Int64 parameters (limit/offset etc.) bind as JavaScript Number, not BigInt, to avoid Cloudflare D1 request hangs. See issue #22.
• Keep escaping / URL building / DOM-shaped output in the JS half; only the SQL round-trip moves to MoonBit.
• Wrap row fields in a small safe_str / fallback_str helper while sqlc casts cell values without a runtime null check (see issue #5).

#Development

#License