#moonlake

Status: all 22 TPC-H queries pass on official texts. The evaluator is columnar — vectorized expression kernels, a columnar hash join, and bind-time projection pruning — but APIs may still change.

#What it is

• Register CSV/Parquet files as tables, run SQL, get columnar result batches. CSV column types are inferred (int32 → int64 → float64 for integers that outgrow their type, bool, date, string) — out-of-range values widen, never wrap.
• Parquet decoding is in-house (sources/parquet) and scoped on purpose: flat schemas, v1 data pages, PLAIN, dictionary, the DELTA family and BYTE_STREAM_SPLIT page encodings (the last five through colenc), RLE boolean values, UNCOMPRESSED and SNAPPY. Nested fields, INT96 timestamps, v2 pages, DECIMAL, UINT_32/UINT_64 and the other codecs are refused with a pointed error rather than guessed at (a decimal byte array read as text, or a UINT taken as signed, would be a wrong answer rather than a refusal). Writing is the same scope and now speaks the format rather than merely the encoding: write_bytes / write_file emit one row group per batch with v1 pages, PLAIN values, STRING/DATE annotations, and per-column-chunk statistics (min_value/max_value/null_count), so moonlake's own output is prunable by its own reader; --snappy (library: write_bytes_with) stores pages compressed, and moonlake write --csv in.csv --out out.parquet is the CLI path. Every column is written OPTIONAL with RLE definition levels (about one bit per value on disk, and what pyarrow does by default): that is what lets the input stream through one batch at a time instead of being materialized, since a REQUIRED schema would have to be decided before the first page is written. harness/check_writer_interop.py reads the result with pyarrow and DuckDB and reads a DuckDB-written file back.
• Ships as a native single binary for ad-hoc command-line analysis, embeds as a MoonBit library, and compiles to WebAssembly (GC) from the same codebase.
• No external database process or database FFI binding, no storage engine — the query engine is MoonBit; filesystem access uses moonbitlang/x (including its native system bindings). moonlake reads external files and computes in memory. Queries larger than memory degrade honestly: a CLI --memory budget (library execute_with + a spill store) spills a blocking ORDER BY to sorted runs and streams the result back; large joins and GROUP BYs partition their inputs to disk the same way.

#SQL surface

• SELECT (including *) / FROM (multi-table) / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT
• INNER / LEFT / RIGHT / FULL / CROSS joins, UNION [ALL] / INTERSECT / EXCEPT (INTERSECT binds tighter; ALL keeps multisets) (hash join; join order chosen greedily by connectivity, LEFT keeps written order)
• Expressions: arithmetic, comparison, CASE WHEN, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS [NOT] NULL, NULL as a comparison/arithmetic operand, EXTRACT(year/month/day), DATE literals, date +/- INTERVAL n day/month/year (calendar-aware, day-of-month clamping), CAST, string functions (substring — both argument styles, length, upper, lower, concat), abs, round, three-valued NULL logic throughout. Division is DuckDB-style: / is always DOUBLE (x/0 is ±inf, 0/0 is nan) and % follows fmod (x % 0 is NULL). Integer literals widen int32 → int64 → float64 to stay exact. Identifiers: the DATE-part keywords year/month/day/date double as bare column/table/alias names; double-quoted identifiers ("left", with "" escape) reach every other reserved word
• Aggregates: sum / avg / min / max / count / count(*) / count(DISTINCT) / stddev (+samp/pop) / variance (+samp/pop) / median / string_agg (literal separator); aggregates over int64 that overflow raise instead of wrapping
• GROUP BY 1 ordinals resolve to select items
• Window functions in the select list: row_number(), rank(), dense_rank() and every aggregate as agg(expr) OVER (PARTITION BY ... ORDER BY ...) (NULLS FIRST/LAST per key). Aggregates use the standard default frame — RANGE UNBOUNDED PRECEDING TO CURRENT ROW (a running aggregate whose peers share the value); without ORDER BY the frame is the whole partition. Explicit frame clauses (ROWS/RANGE BETWEEN ...), window functions in WHERE/ORDER BY/HAVING/GROUP BY (order by the output alias instead) and windows inside correlated subqueries are rejected with pointed errors
• Subqueries: correlated EXISTS / NOT EXISTS / IN / NOT IN — including a HAVING inside the EXISTS (decorrelated to grouped semi/anti joins, residuals and all) — and correlated scalar aggregate subqueries in WHERE and in the select list (decorrelated to grouped LEFT joins) — nested to the depth TPC-H asks; derived tables (FROM (SELECT ...) AS t), with optional column alias lists (AS t (a, b))
• WITH name AS (SELECT ...) — non-recursive CTEs, statement level; a name is its own qualifier, later CTEs may reference earlier ones
• Scalar functions: coalesce / nullif / greatest / least, date_trunc(unit, date), date_diff(unit, start, end); SELECT works without FROM and a bare NULL select item reads as INTEGER
• Pushdown: single-table predicates from WHERE/ON are applied as build/probe filters at each hash join; equalities become hash keys even when implied by disjunctions. At joins that preserve a side (LEFT/RIGHT/FULL), WHERE predicates — spanning ones included — stay post-join so NULL-extended rows still meet them
• Larger than memory: exec --memory <n>[K|M|G] [--spill-dir <dir>] bounds the blocking operators. ORDER BY becomes sorted runs whose final merge streams into projection/DISTINCT/OFFSET/LIMIT (a LIMIT query holds only its output window); a hash join partitions both sides and splits an over-budget partition again, and a GROUP BY partitions its input and aggregates each partition one spill file at a time — so a hot group key is fine while a single join key holding most of a side is refused with that reason. --analyze under a budget reports spill runs / spill files / spill bytes written / spill bytes read, and the CLI removes its temp directory on both exit paths. A query spills into its own moonlake-spill-<n> subdirectory of --spill-dir (or TMPDIR), so two processes may share one --spill-dir without touching each other's runs, and a caller's own files there are never read or deleted. A run that is killed outright leaves its subdirectory behind: with no pid, clock or file timestamp in the filesystem API, the next run cannot tell a dead owner from a live one and does not guess (library form: execute_with with ExecOptions + a SpillStore; the engine itself never touches the filesystem)

#TPC-H cross-validation

uv run --with duckdb python harness/gen_tpch.py   # TPC-H SF0.01 data + goldens
bash harness/check_all.sh                         # 27 goldens: all PASS (22 TPC-H + parquet + 4 multi-row-group)
MOONLAKE_DATA=harness/data/sf01 bash harness/check_all.sh   # same at SF0.1 (regenerate first: gen_tpch.py 0.1)

#Quickstart

git clone https://github.com/superbigcup325/moonlake && cd moonlake
moon update
moon run cmd/main -- exec --csv harness/data/sf001/lineitem.csv \
  "SELECT l_returnflag, sum(l_extendedprice) FROM lineitem WHERE l_shipdate <= date '1998-09-02' GROUP BY l_returnflag ORDER BY l_returnflag"
# l_returnflag|sum_2
# A|532348211.6499983
# ...

moon build cmd/main --target native --release
install -m755 _build/native/release/build/cmd/main/main.exe ~/.local/bin/moonlake
moonlake exec --csv lineitem.csv --file q6.sql     # SQL from a file: no shell quoting

moon add superbigcup325/moonlake

This README and the browser playground track main. moon add gives you the latest published release from mooncakes (0.4.0), which includes the session playground, the Parquet writer and the spill/JSON fixes recorded in CHANGELOG.md.

moon update   # refresh the registry index; the graph downloads on the first check
moon check

ln -s ../colenc .colenc
printf 'members = [".", "./.colenc"]\n' > moon.work

// a @catalog.Catalog with registered table entries — @csv/@parquet
// scan into one; note the argument order: content/name, not name/content.
// registration is lazy: the schema is available at once, and a query decodes
// only the columns it projects, in 4096-row batches

///|
fn main raise {
  let cat = @catalog.Catalog::new()
  cat.register(@csv.scan_string("region,amount\nnorth,100\n", "events"))

  // read results back cell by cell: names() gives the columns,
  // row_count() the rows, get(row, col) a @types.Scalar (null via Null)
  let result = @moonlake.execute(
    "SELECT region, count(*) FROM events GROUP BY region", cat,
  )
  for r in 0..<result.row_count() {
    println(result.get(r, 0).to_string() + "|" + result.get(r, 1).to_string())
  }
}

#Docs

• Playground (open): register several CSV/Parquet files in a visible session, run cross-file joins, inspect the physical plan, and drop tables or clear the session — the whole engine as wasm-gc, all client-side.
• docs/DEMO.md: the reproducible demo, from running the engine to the DuckDB cross-check and the benchmark.
• docs/verification.md: the correctness and performance report (differential and property testing, pushdown fuzzing, memory and spill numbers).
• docs/bench-baseline.md: the release performance baseline (scan/join/gather/spill) and the machine it belongs to, with a committed JSON record and a --compare mode.
• docs/spill-survey.md: how six systems handle larger-than-memory queries, and where moonlake's design lands.
• docs/devlog.md: the build log, week by week.
• CHANGELOG.md: release notes.

#Where moonlake sits

#Development

moon check           # type-check
moon test            # run tests on the preferred target (wasm-gc)
moon test --target native  # filesystem/native backend tests
moon fmt             # format
moon run cmd/main    # run the CLI
git config core.hooksPath hooks   # once per clone: pre-commit gate

#Acknowledgements

• moonbitlang/x (Apache-2.0) — filesystem, argument parsing and encoding utilities
• moonbitlang/quickcheck (Apache-2.0) — property-based testing
• superbigcup325/colenc (Apache-2.0) — Parquet DELTA, BYTE_STREAM_SPLIT and RLE encoding/decoding primitives
• apache/parquet-format (Apache-2.0) — the Parquet format and encoding specifications that sources/parquet implements; its parquet-testing corpus supplies the files the reader is verified against

#License