Roadmap signal: effect-orm as the ORM of the CAFE stack #52

What this is

This issue groups a set of signal issues from the MyRemotely team. Each one says where we want effect-orm to go. They are not demands, and they are not PRs yet. We open them to show direction and ask for your view. We can send PRs for the items you accept. #48 and PRs #49–#51 (SQLite / D1) are our first steps.

Our context

MyRemotely is a multi-tenant SaaS for rental property managers: locks, tours and resident messages. We build on the CAFE stack: Cloudflare (Workers, D1, Durable Objects), Alchemy, Foldkit, Effect.

We plan to move our database from MariaDB to Cloudflare: D1 for shared and per-org data, and Durable Object SQLite for stateful entities. We want to do the move with effect-orm.

Today's schema, from schema.prisma, and our production code:

Why effect-orm and not Drizzle

Drizzle 1.0 has Effect drivers for MySQL, D1 and DO. But in Drizzle, Effect is a wrapper: a query is yieldable, all errors share one tag, and rows are not decoded by a Schema. effect-orm already has what Drizzle cannot add on top: row Schemas derived from each query, a tenant-scope proof, and transactions as a requirement in R. We want to make it the best Drizzle alternative in the CAFE stack.

The issues come from three sources:

1. Cloudflare needs. What the MariaDB → D1 / DO move requires.
2. Effect-native. Ideas from effect-qb and from Effect core (SqlModel, SqlResolver, BeginTransaction).
3. Drizzle / Prisma parity. The features we use every day.

Issues

Cloudflare: the MariaDB → D1 / DO move

• MySQL / MariaDB dialect #53 MySQL / MariaDB dialect
• Durable Object SQLite dialect profile #54 Durable Object SQLite dialect profile
• D1 batch() as a typed atomic unit #55 D1 batch() as a typed atomic unit
• D1 read replication: Sessions API and bookmarks #56 D1 read replication: Sessions API and bookmarks
• Bound-parameter limits: chunk IN lists and multi-row inserts #57 Bound-parameter limits: chunk IN lists and multi-row inserts
• Database-per-tenant routing from the tenant scope #58 Database-per-tenant routing from the tenant scope
• Migrations that Alchemy can apply to D1 and Durable Objects #59 Migrations that Alchemy can apply to D1 and Durable Objects
• A Database layer from an Alchemy binding #60 A Database layer from an Alchemy binding
• Move data between dialects: MariaDB → D1 copy and verify #61 Move data between dialects: MariaDB → D1 copy and verify
• effect-orm pull --from-prisma: import a Prisma schema #62 effect-orm pull --from-prisma: import a Prisma schema
• One table definition for several dialects #63 One table definition for several dialects

Effect-native

• Install the dialect through Effect context, not module state #64 Install the dialect through Effect context, not module state
• A pipeable, data-last query API next to the fluent builder #65 A pipeable, data-last query API next to the fluent builder
• Dialect mismatch and missing sources as type errors #66 Dialect mismatch and missing sources as type errors
• Predicate facts: narrow row types from joins and WHERE #67 Predicate facts: narrow row types from joins and WHERE
• Table Schemas: select, insert, update and json variants #68 Table Schemas: select, insert, update and json variants
• Cardinality helpers: runOne, runOption, runNonEmpty #69 Cardinality helpers: runOne, runOption, runNonEmpty
• Db.stream: stream rows with decoding #70 Db.stream: stream rows with decoding
• Typed constraint errors across dialects #71 Typed constraint errors across dialects
• Use SqlClient.BeginTransaction for transaction settings #72 Use SqlClient.BeginTransaction for transaction settings
• afterCommit: post-commit work bound to the transaction #73 afterCommit: post-commit work bound to the transaction
• Request batching with SqlResolver (N+1 safety) #74 Request batching with SqlResolver (N+1 safety)
• makeRepository(table): typed, tenant-scoped CRUD #75 makeRepository(table): typed, tenant-scoped CRUD
• Test layers: in-memory and SQLite-backed Database #76 Test layers: in-memory and SQLite-backed Database
• Observability: observe hook, span attributes, parameter redaction #77 Observability: observe hook, span attributes, parameter redaction
• Decode errors that are safe to log #78 Decode errors that are safe to log

Drizzle / Prisma parity

• Relational queries: nested with loading, decoded by Schema #79 Relational queries: nested with loading, decoded by Schema
• Relation counts and filters: _count, some, every, none #80 Relation counts and filters: _count, some, every, none
• Portable upsert on SQLite and MySQL, plus an excluded helper #81 Portable upsert on SQLite and MySQL, plus an excluded helper
• Client-side defaults as Effects: defaultFn and onUpdate #82 Client-side defaults as Effects: defaultFn and onUpdate
• Portable text search: case-insensitive contains and LIKE escaping #83 Portable text search: case-insensitive contains and LIKE escaping
• Typed JSON paths and JSON updates on SQLite and MySQL #84 Typed JSON paths and JSON updates on SQLite and MySQL
• Row locks on SQLite / D1: refuse in the type, document the replacement #85 Row locks on SQLite / D1: refuse in the type, document the replacement
• Keyset (cursor) pagination helper #86 Keyset (cursor) pagination helper
• Name mapping: TS keys and DB names (@map / @@map, casing) #87 Name mapping: TS keys and DB names (@map / @@map, casing)
• Seeding from Schema arbitraries #88 Seeding from Schema arbitraries
• Drift check: table definitions against a live database #89 Drift check: table definitions against a live database
• Temporal and decimal codecs for SQLite and MySQL #90 Temporal and decimal codecs for SQLite and MySQL
• Docs: "Coming from Drizzle / Prisma" and a CAFE example #91 Docs: "Coming from Drizzle / Prisma" and a CAFE example