d1-eloquent/Nuxt workspace example

Beta.3 feature showcase

Fourteen features shipped in @orphnet/d1-eloquent beta.3, each run live against D1 by a dedicated server route and rendered below. Every card shows what it proves, the copyable d1-eloquent call, and the real result computed this request.

Backing endpoint: GET /api/features (all at once) or GET /api/features/:key (one in isolation). Each loader reseeds its own dedicated feat_* tables, so results are deterministic and independent of the acme seed. Full source is on the /source page.

1 increment() / decrement() GET /api/features/increment-decrement ↗

Atomic counter bumps at the query-builder AND instance level, plus a NULL column coalesced to 0 - no read-modify-write race.

await FeatCounter.query().whereEq("id", id).increment("views", 5)
await FeatCounter.query().whereEq("id", id).decrement("views", 4)
const c = await FeatCounter.query().whereEq("id", id).first()
await c.increment("views", 3)      // syncs in-memory attr, clears dirty
await FeatCounter.query().whereEq("id", id).increment("hits", 1) // NULL → COALESCE(0)+1

Error: D1_ERROR: no such table: feat_counters: SQLITE_ERROR

2 whereRelation() / orWhereRelation() + firstWhere() GET /api/features/where-relation-first-where ↗

Filter parents by a condition on a related table (2-arg = and 3-arg operator forms), OR it with a column filter, and fetch the first match (or null) in one call.

await FeatTeam.query().whereRelation("members", "role", "lead").get()
await FeatTeam.query().whereRelation("members", "seniority", ">", 5).get()
await FeatTeam.query().whereEq("name", "Beta").orWhereRelation("members", "role", "lead").get()
await FeatTeam.query().firstWhere("name", "Alpha")   // → team | null
await FeatMember.query().firstWhere("seniority", ">", 5)

Error: D1_ERROR: no such table: feat_members: SQLITE_ERROR

3 replicate() + wasRecentlyCreated GET /api/features/replicate-was-recently-created ↗

Clone a row (PK stripped, marked dirty & unpersisted, deleted_at dropped from a soft-deleted original) and tell a freshly-INSERTed model from a loaded one.

const created = await FeatArticle.create({ ... })
created.wasRecentlyCreated                     // true
const loaded = await FeatArticle.query().whereEq("id", id).first()
loaded.wasRecentlyCreated                      // false
const clone = original.replicate()             // pk stripped, dirty, unpersisted
await clone.save()                             // persists as a NEW row

Error: D1_ERROR: no such table: feat_articles: SQLITE_ERROR

4 constrained eager loading - .with({ rel: q => q... }) GET /api/features/constrained-eager-loading ↗

Filter / order an eager-loaded relation with a callback (object-map form), or load it unconstrained with `true`.

await FeatTeam.query().with({ members: (q) => q.where("role", "=", "lead") }).get()
await FeatTeam.query().with({ members: (q) => q.orderBy("seniority", "desc") }).get()
await FeatTeam.query().with({ members: true }).get()   // unconstrained

Error: D1_ERROR: no such table: feat_members: SQLITE_ERROR

5 enumCast() (incl. onInvalidRead opt-in) GET /api/features/enum-cast ↗

Whitelist a column's values: writes outside the set throw; reads coerce; a `onInvalidRead: 'null'` model reads a drifted value as null instead of throwing.

static casts = { status: enumCast(["draft", "published", "archived"]), level: enumCast([1, 2, 3]) }
await FeatEnumDoc.create({ id, status: "published", level: 2 })   // ok
await FeatEnumDoc.create({ id, status: "spam" })                  // throws Invalid enum value
// lenient model: enumCast([...], { onInvalidRead: 'null' }) → reads a bad row as null

Error: D1_ERROR: no such table: feat_enum_docs: SQLITE_ERROR

6 withMin() / withMax() / withExists() GET /api/features/with-min-max-exists ↗

Attach MIN/MAX of a related column and a concrete 0/1 existence flag (never null, even for parents with zero children) as correlated subqueries.

await FeatTeam.query().withMin("members", "seniority").withMax("members", "seniority").withExists("members").get()
await FeatTeam.query().withMax("members", "seniority", "top").get()   // custom alias

Error: D1_ERROR: no such table: feat_members: SQLITE_ERROR

7 intersect() / except() set operators GET /api/features/intersect-except ↗

Combine two queries with INTERSECT / EXCEPT - and the soft-delete scope applies to BOTH operands, so a trashed row that matches both predicates is still excluded.

await FeatArticle.query().whereEq("status", "published")
           .intersect(FeatArticle.query().whereEq("views", 100)).get()
await FeatArticle.query().whereEq("status", "published")
           .except(FeatArticle.query().whereEq("views", 100)).get()

Error: D1_ERROR: no such table: feat_articles: SQLITE_ERROR

8 date-part wheres - whereDate/Time/Year/Month/Day GET /api/features/date-part-where ↗

Filter a datetime column by an extracted part: calendar day, time-of-day (incl. operator form), year, month, or day-of-month.

await FeatArticle.query().whereDate("published_at", "2026-01-15").get()
await FeatArticle.query().whereMonth("published_at", 1).get()
await FeatArticle.query().whereYear("published_at", 2026).get()
await FeatArticle.query().whereDay("published_at", 15).get()
await FeatArticle.query().whereTime("published_at", "09:30:00").get()
await FeatArticle.query().whereTime("published_at", ">=", "10:00:00").get()

Error: D1_ERROR: no such table: feat_articles: SQLITE_ERROR

9 global scopes + withoutGlobalScope(s) GET /api/features/global-scopes ↗

A `static globalScopes` entry auto-applies to every query (and count), composes AND with user clauses, tracks external state, and is removable per-query.

static globalScopes = { tenant: (q) => q.whereEq('tenant', currentTenant) }
await FeatScopedDoc.query().get()                          // tenant-scoped
await FeatScopedDoc.query().withoutGlobalScope('tenant').get()  // all tenants
await FeatScopedDoc.query().withoutGlobalScopes().count()       // unscoped count

Error: D1_ERROR: no such table: feat_scoped_docs: SQLITE_ERROR

10 hasManyThrough / hasOneThrough GET /api/features/has-many-through ↗

Reach a distant relation across an intermediate table: a Country's Stories THROUGH its Citizens - eager, lazy, and the single-row hasOneThrough (null when empty).

stories:     { type: 'hasManyThrough', model: () => FeatStory, through: () => FeatCitizen, firstKey: 'country_id', secondKey: 'citizen_id' }
latestStory: { type: 'hasOneThrough',  model: () => FeatStory, through: () => FeatCitizen, firstKey: 'country_id', secondKey: 'citizen_id' }
await FeatCountry.query().with(["stories"]).get()
await country.related("stories").get()          // lazy

Error: D1_ERROR: no such table: feat_stories: SQLITE_ERROR

11 prepared queries - prepare() + placeholder() GET /api/features/prepared-queries ↗

Compile a query ONCE, then execute it repeatedly with different bound params via named placeholders; a missing param throws.

const byRole = FeatMember.query().whereEq("role", placeholder("role")).prepare(env.DB)
await byRole.get({ role: "lead" })     // reuse
await byRole.get({ role: "member" })   // reuse, different bind
await byRole.first({})                 // throws: missing placeholder 'role'

Error: D1_ERROR: no such table: feat_members: SQLITE_ERROR

12 schema-diff generate CLI (d1-eloquent generate) GET /api/features/schema-diff-generate ↗

A build-time command (not an HTTP route): diffs model column declarations against the migration history and emits an ADD/DROP COLUMN migration. This endpoint reports the drift resolved in THIS repo.

# 1. FeatArticle declares an `archived` column the create-table migration lacked.
bun run db:generate        # d1-eloquent generate → writes an ADD COLUMN migration
bun run db:migrate         # apply it
# See database/migrations/20260517101800_alter_feat_articles_add_archived.ts

Error: D1_ERROR: no such table: feat_articles: SQLITE_ERROR

13 transaction() - atomic unit-of-work GET /api/features/transactions ↗

Multiple writes commit together or not at all: a parent+child insert commits atomically; a failing statement rolls back EVERYTHING, including the earlier valid write.

await transaction(env.DB, async (tx) => {
  await tx.create(FeatAccount, { id: "a", name: "Alice", balance: 100 })
  await tx.create(FeatAccount, { id: "b", name: "Bob",   balance: 0 })
})
// a NOT NULL violation inside the closure rejects and rolls back both inserts

Error: D1_ERROR: no such table: feat_accounts: SQLITE_ERROR

14 tx.increment() / tx.decrement() - atomic counters in a tx GET /api/features/tx-increment-decrement ↗

Balance-transfer inside a transaction: a decrement on one row and an increment on another commit together, preserving the invariant; a throw rolls the bump back.

await transaction(env.DB, async (tx) => {
  tx.decrement(FeatAccount.query().whereEq("id", "a"), "balance", 30)
  tx.increment(FeatAccount.query().whereEq("id", "b"), "balance", 30)
})   // both apply atomically → total balance preserved

Error: D1_ERROR: no such table: feat_accounts: SQLITE_ERROR