sqlc-gen-moonbit

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

sqlc plugin for generating type-safe MoonBit code from SQL.

• 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

Add to your sqlc.yaml:

version: "2"
plugins:
  - name: moonbit
    wasm:
      url: "https://github.com/mizchi/sqlc_gen_moonbit/releases/download/v0.3.2/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

Check the releases page for the latest version and sha256.

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

Use local file in sqlc.yaml:

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

db/schema.sql:

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

db/query.sql:

-- 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 (?, ?);

sqlc generate

For SQLite (native):

Add dependencies to moon.mod.json:

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

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")
  }
}

For Cloudflare D1:

Add dependencies to moon.mod.json:

{
  "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")
  }
}

For PostgreSQL (JS target with Node.js):

Add dependencies to moon.mod.json:

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

Install npm dependencies:

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)
}

Example with all 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"

Override default type mappings for specific columns or database types:

Column overrides take precedence over db_type overrides.

For each query, sqlc-gen-moonbit generates:

• 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

The d1 backend converts Int64 parameters — limit, offset, BIGINT columns, and nullable Int64 Some(v) arms — through a generated d1_bind_int64(v) helper before calling D1PreparedStatement.bind(...).

This keeps MoonBit JS-target BigInt values away from Cloudflare D1. Passing a BigInt to D1 can hang the Worker request instead of throwing, so generated D1 code emits Number(value) for Int64 bind values. Recording mocks for D1 should therefore expect plain numbers:

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

Values outside JavaScript's safe integer range may lose precision. This matches D1's practical read-path behavior, where SQLite INTEGER cells come back to JavaScript as numbers. See #22.

You can generate MoonBit code from SQL without sqlc using the CLI tool or the library API.

Clone the repository and use the 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

CLI Options:

Config file format (config.json):

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

-- @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 (?, ?);

Add dependency and use the library directly:

moon add mizchi/sqlc_gen_moonbit

Create tasks/main.mbt:

///|
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)
}

Add tasks/moon.pkg:

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

options(
  "is-main": true,
)

Run:

moon run tasks > db/gen/sqlc_queries.mbt

• 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)

Workers codebases often have extern "js" blocks with inline db.prepare(...) statements. Migrating one such statement at a time to the generated bindings is straightforward once you know the shape: the generated MoonBit function can't be called from an extern "js" block directly, but it can be called from MoonBit and the result handed back into a JS-only renderer via a JSON bridge.

The pattern:

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;
  #| }

Notes when migrating an existing inline db.prepare(...) block:

• 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).

See CONTRIBUTING.md for development workflow.

Apache-2.0