Repository navigation
Support SQLite and Cloudflare D1 through the existing dialect and Effect SQL APIs #48
Description
Our project and why this matters
We are building MyRemotely, a multi-tenant SaaS for rental property managers. It coordinates lock access, tour scheduling and resident communication across external systems.
We are moving the application toward what we call the CAFE stack:
- C — Cloudflare: Workers, Durable Objects and D1.
- A — Alchemy: infrastructure as code and the Domain model for commands, events and projections.
- F — Foldkit: the user interface.
- E — Effect: services, dependency injection, schemas, typed failures and runtime behavior.
Our goal is an application that is Effect-native across its layers. The ORM is one part of that larger design. We want database access to compose with the same services, schemas and error model as the rest of the system, with less custom glue and fewer separate definitions to keep in sync.
Drizzle already gives us native Effect drivers, SQLite/D1 support and migration tooling. We are interested in effect-orm because its derived runtime row schemas and tenant analysis could give us a stronger ORM that fits our application more closely. Better fit and efficiency for our project mean clearer guarantees, less integration code and a simpler maintenance path; any query-speed advantage still needs measurement on our workloads.
We are ready to use effect-orm in MyRemotely and help improve it through real use. We will provide usage feedback, report issues with reproducible examples, and send pull requests for fixes and missing capabilities. This prototype and the follow-up SQLite/D1 PR are our first contributions toward that goal.
Problem
effect-orm can compile and decode queries for ClickHouse and Postgres, but it has no SQLite dialect. This blocks an Effect application that runs a multi-tenant acknowledgment projection on Cloudflare D1 and uses SQLite for local tests.
The existing dialect design already describes positional ? bindings with reuse: false. A small prototype confirms that the shared builder, row decoding and tenant analysis work on a real local D1 binding without changes to the query core.
Prototype and comparison
Runnable prototype at commit a9073a7.
Run mise exec -- bun run prototype:whats-new-ack on that branch. It uses Miniflare/workerd and @effect/sql-d1, with synthetic rows and no cloud credentials.
The same fixtures match Drizzle 1.0.0-rc.5 for copy, duplicate writes that keep the first row, reads by user, and keyset paging. Both paths use native Effect SQL drivers. The additional checks confirm derived runtime row decoding and the opt-in requireTenantScope execution guard. D1 interactive transactions are refused before their body runs.
This is a behavior comparison, not a speed benchmark. It does not change the application ORM or its database.
Requested first step
- A SQLite entry with column codecs, positional bindings, safe literals and a small SQLite function catalog, using the existing builder.
- A distinct D1 capability profile: 100 bound parameters per statement and no interactive transactions or savepoints.
- Execution through the existing
Database.fromSqlClientAPI, using@effect/sql-sqlite-nodelocally and@effect/sql-d1for D1. - Real SQLite and local workerd/D1 tests, including row decoding, tenant enforcement, unsupported syntax and transaction behavior.
SQLite syntax needs explicit treatment beyond replacing $n: offset without a limit, union branches with their own ordering/limit, per-cell DEFAULT, ON CONSTRAINT targets and DISTINCT ON must be rendered correctly or refused.
Separate follow-up design
SQLite schema snapshots, migration diff/introspection and kit support can follow the query/execution dialect. The first step should document their absence. Existing external migrations remain usable.
D1 has atomic batch() for a fixed list of statements. It cannot implement an arbitrary Effect transaction body with dependent reads, so the first step must not emulate interactive transactions. A future ORM batch API should preserve decoding and tenant checks for each statement.
References: D1 limits, D1 batch API, existing dialect design.
The tested query/execution implementation is now in PR #49. SQLite migration tooling and an ORM batch API remain separate follow-up work.
Activity
artile commentedon Oct 25, 2026
Roadmap: effect-orm as the full SQLite/D1 data layer
Our goal: effect-orm replaces Drizzle for all D1 and Durable Object SQLite tables in MyRemotely. Today we run Drizzle
1.0.0-rc.5on@effect/sql-d1there. Each step below is one PR. Each PR stacks on the one before it.table: columns, primary keys, defaults, indexes, unique indexeseffect-orm generatefor SQLite: diff to<ts>_<name>/migration.sql+snapshot.json; column changes by table rebuild;--from-drizzlebaseline for SQLiteSqlClient(@effect/sql-sqlite-bun,@effect/sql-sqlite-do); drift check throughPRAGMAtransactionSync, no await inside); tests on a real workerd DObatch(): atomic fixed list of statements, with decoding and tenant checks per statementNotes:
Cloudflare.D1.Database({ migrations })reads this layout, so U3 needs no change in Alchemy.--from-drizzlelets a project with live D1 databases change to effect-orm with no DDL run.We will keep each PR small and tested on real SQLite and local workerd. Tell us if you want a different order or split.
Makisuo commentedon Oct 25, 2026
Done on main, contributed by @tao-io in #49/#50/#51 and merged with review fixes as a stack:
/sqlitequery entry,sqliteDialectandd1DialectSQLite.tableDDL,sqliteEntitiesOf,renderSqliteSchemaeffect-orm generatefordialect: "sqlite", including drizzle-kit 1.0 adoptionSQLite and D1 run the shared core builder suite and every fuzz property (#118, #122): queries, unions, writes, literals and migrations. See docs/sqlite.md and docs/migrations.md#sqlite. Follow-ups are tracked in #54–#61, #85 and #90.