# Local-first SQL that syncs, streams and queries

Dux is an open source sync stack. Apps read and write SQLite on the device, changes merge into PostgreSQL, and every committed row version lands in [DuckLake](https://ducklake.select) for `dux sql`. Built in Rust by [linesofcode](https://x.com/linesofcode).

```sh
npm create dux-app my-app
```

Currently v0.1.0 · pre-release, M1 in progress

- **1.88×** Ingest against Timescale's loader, TSBS devops
- **27 of 27** TSBS query types with identical rows
- **560,000** Accepted rows/s over HTTP, p99 ack 11.2 ms
- **24** Rust crates in the workspace

**Deterministic by default.** `dux-core` has no I/O, clock or randomness. A seed replays the same trace and the same state hash.

**SQL all the way down.** SQLite on the device, PostgreSQL on the server, DuckDB over the lake. No query language to learn.

**Measured in public.** Benchmarks ship with the command that produced them, including the queries where Dux is slower.

## Offline-first sync

Run `dux dev` in a project and it compiles `dux.schema`, starts PostgreSQL on a free port and serves sync. Devices write SQLite offline; buckets and write rules decide what each user sees and may change. [M1 commands](https://github.com/TimMikeladze/dux/blob/main/docs/m1-status.md).

```sh
cargo run -- init /tmp/dux-app
cd /tmp/dux-app
/path/to/dux/target/debug/dux dev
# Compiles dux.schema, starts PostgreSQL in .dux/postgres on a free port (or uses
# DUX_DATABASE_URL), serves sync on 127.0.0.1:8080 (or a free port if taken) and
# writes .dux-dev.json (endpoint, token, inspector URL, database URL).
# Inspector: /dev/inspector. Extra users: GET /dev/token?user=NAME.
# Attachments: set DUX_S3_ENDPOINT, DUX_S3_ACCESS_KEY, DUX_S3_SECRET_KEY
# (optional DUX_S3_BUCKET, DUX_S3_REGION, DUX_BLOB_DIR) to mount /blob.
# Analytics lake (docs/p1.md): dux dev runs one in .dux/lake (SQLite catalog,
# 2 s flush); dux serve opts in with --lake sqlite|postgres|files.
# Stream tables (docs/p2.md): POST /ingest appends; rollups run after each
# flush (1 s in dev); retention every --retention-secs (60 s in dev), which also
# expires DuckLake snapshots older than --lake-snapshot-days. `dux sql` includes
# rows still in the stream log.
# Streams need `write <stream> insert: <predicate>`. `dux dev --no-postgres` serves streams, rollups and
# /query over the lake alone (no sync).
# Production: configure issuer, audience and HTTPS JWKS instead.
/path/to/dux/target/debug/dux sql "SELECT lane_id, count(*) FROM cards GROUP BY 1"
/path/to/dux/target/debug/dux lake ingest visits visits.csv
/path/to/dux/target/debug/dux serve --issuer https://issuer.example --audience dux --jwks-url https://issuer.example/jwks.json --cors-origin https://app.example
# Without --database-url, serve streams and /query only: add --lake files|sqlite.
```

## Replay any failure

The simulator drives three clients through dropped acks, partitions, crashes and clock jumps. Pass `--seed` and `--steps`; the same pair reproduces the same trace and state hash.

```console
$ cargo run -- simulate --seed 7 --steps 1000
seed=7 steps=1000 accepted=23 rejected=159 events=1326 state_hash=4fbadd1bf0e4a38f converged=true
```

## Streams without the wait

Append-only rows go to `POST /ingest` or the client outbox and are acknowledged after fsync. Watermarked rollups can sync back to devices. [P2 evidence](https://github.com/TimMikeladze/dux/blob/main/docs/p2.md).

| Producers × rows per batch | Accepted rows/s | Ack p50 | Ack p99 | Notes |
|---|---|---|---|---|
| 2 × 500 | 148,000 | 5.8 ms | 12.6 ms | flush keeps up (86k rows left at the end) |
| 4 × 500 | 286,000 | 5.8 ms | 12.9 ms | flush keeps up |
| 16 × 1,000 | 560,000 | 7.0 ms | 11.2 ms | flush-bound: 4,176 busy (503) replies, backlog held at 2M rows |

## Speaks Postgres

`--pg-listen` serves the Postgres wire protocol, so `psql` and Grafana connect without a plugin. Timescale-style hyperfunctions like `time_bucket_gapfill` work as-is. [Grafana walkthrough](https://github.com/TimMikeladze/dux/blob/main/docs/grafana.md).

```sql
SELECT time_bucket_gapfill('2 minutes', ts) AS time,
       locf(approx_percentile(0.95, percentile_agg(value))) AS p95
FROM metrics WHERE $__timeFilter(ts) GROUP BY 1 ORDER BY 1
```

## Run code next to the data

Put DAGs and workflows in `compute/` and ship them with `dux deploy`. Outputs land exactly once in stream, lake or synced tables. [P9 status](https://github.com/TimMikeladze/dux/blob/main/docs/p9.md).

```bash
dux dev --no-postgres --lake sqlite        # compute is on; python3 / bun run steps locally
mkdir compute && $EDITOR compute/sensors.yaml
dux deploy                                  # bundles compute/, validates, activates
dux runs ls                                 # the ledger
```

## Against Timescale

TSBS's own generator, queries and runner on both, in containers with equal limits, via `cargo run --release -p dux-tsbs -- run`. p99 in ms with Dux's result cache off. Selective point lookups are still slower, and the table says so. [Full results](https://github.com/TimMikeladze/dux/blob/main/docs/benchmarks/tsbs.md).

| devops query | Timescale best | dux | ratio (first run) |
|---|---:|---:|---:|
| double-groupby-1 / -5 / -all | 118.7 / 210.9 / 213.6 | 26.4 / 31.7 / 47.0 | **0.22 / 0.15 / 0.22** (0.97 / 0.76 / 0.65) |
| cpu-max-all-8 | 18.2 | 11.0 | **0.61** (9.89) |
| cpu-max-all-1 | 8.7 | 6.9 | **0.79** (20.79) |
| single-groupby-1-8-1 | 3.9 | 7.2 | 1.86 (13.66) |
| single-groupby-5-1-12 / -1-1-12 | 3.0 / 3.3 | 6.5 / 9.3 | 2.15 / 2.79 (16.44 / 18.15) |
| high-cpu-all | 102.5 | 240.1 | 2.34 (17.80) |
| high-cpu-1 | 2.9 | 7.6 | 2.64 (27.31) |
| lastpoint | 3.0 | 8.7 | 2.95 (88.37) |
| single-groupby-5-8-1 | 3.2 | 10.0 | 3.15 (4.95) |
| single-groupby-5-1-1 / -1-1-1 | 1.7 / 1.5 | 10.5 / 11.5 | 6.20 / 7.72 (24.74 / 24.26) |
| groupby-orderby-limit | 1.9 | 28.8 | 15.08 (102.70) |

## Where it stands

### What holds

- Same rows as Timescale on 27 of 27 TSBS query types
- Acknowledged stream rows survive `kill -9` exactly once
- Seeded simulations replay byte for byte

### What is a judgement

- Ingest numbers are from one laptop, scale 250
- Point lookups are 2–8× slower than Timescale's indexes
- The sync performance targets are not measured yet

### What is not here yet

- Loro text and RPC features
- Torn-write and power-loss durability tests
- A published release and the LICENSE file

## Links

- [Reference](https://dux.sh/reference)
- [Repository](https://github.com/TimMikeladze/dux)