Queries

This guide covers the Query API: how to construct queries, apply sort key conditions, filter results, paginate, and collect.

BoundQuery — A Fluent Chain

Queries in effect-dynamodb are fluent chains with pre-resolved services. A collection query or entity scan returns a BoundQuery that you chain combinators onto, then terminate with .collect(), .fetch(), or .paginate().

import { DynamoClient } from "effect-dynamodb"

const db = yield* DynamoClient.make({
  entities: { TaskEntity },
  tables: { MainTable },
})

// Construct and execute a query in one chain
const results = yield* db.entities.TaskEntity
  .byProject({ projectId: "proj-alpha" })
  .filter({ status: "active" })
  .limit(25)
  .reverse()
  .collect()

Entity Queries

Query accessors live on db.entities (for entity index queries, primary key lookups, and scans) and db.collections (for auto-discovered cross-entity queries). Entity index queries return typed arrays; collection queries return grouped results.

// Primary key lookup (not a query — returns single item)
const task = yield* tasks.get({ taskId: "t-001" })

// Named index queries — via entity index accessors
const projectTasks = yield* db.entities.TaskEntity.byProject({
  projectId: "proj-alpha",
}).collect()
const assigneeTasks = yield* db.entities.TaskEntity.byAssignee({
  assigneeId: "emp-alice",
}).collect()

Every entity also exposes a .primary(...) accessor for the primary index — same contract as GSI accessors (required PK composites, optional SK composites with begins_with prefix matching):

// Primary-index query — list every item under a shared primary partition.
// Used when multiple items share the primary PK and are distinguished by SK
// (the join-table single-table pattern).
const allMembers = yield* db.entities.Memberships.primary({
  orgId: "org-acme",
}).collect()

Generated Accessor Mapping

Every index — including primary — gets a query accessor. Accessors accept required PK composites and optional SK composites (partial SK composites apply begins_with prefix matching). .get(fullKey) remains the dedicated GetItem path for single-item fetches by full primary key.

Definition	Accessor	Argument Type
primaryKey: { pk: { composite: ["taskId"] }, ... }	db.entities.TaskEntity.get(...)	{ taskId: string }
primaryKey: { pk: { composite: ["orgId"] }, sk: { composite: ["userId"] } }	db.entities.Memberships.primary(...)	{ orgId: string; userId?: string }
indexes: { byProject: { name: "gsi1", pk: { composite: ["projectId"] }, ... } }	db.entities.TaskEntity.byProject(...)	{ projectId: string }
indexes: { byAssignee: { name: "gsi2", pk: { composite: ["assigneeId"] }, ... } }	db.entities.TaskEntity.byAssignee(...)	{ assigneeId: string }

.primary() vs .get()

Use .get(fullKey) when you know the full primary composite key and want a single item — it’s a strongly-consistent GetItem, cheaper than a Query. Use .primary(partialKey) when you want to list items that share a primary partition key, for example a join-table where one partition holds many items distinguished by the sort key:

// `Memberships` primary key: pk = orgId, sk = userId
//
// List every membership in an organization — PK only, SK composites omitted
const allMembers = yield* db.entities.Memberships.primary({
  orgId: "org-acme",
}).collect()

// Narrow by sort-key prefix — the full SK composite is provided, so this
// returns an array (possibly empty) rather than failing with `ItemNotFound`.
const bobs = yield* db.entities.Memberships.primary({
  orgId: "org-acme",
  userId: "u-bob",
}).collect()

Behavior is symmetric with GSI accessors: .where(), .filter(), .select(), .limit(), .pageSize(), .maxPages(), .reverse(), .startFrom(), .consistentRead(), .collect(), .fetch(), .paginate(), and .count() all chain off the returned BoundQuery.

How much of the sort key an accessor pins

Supplying some of the sort key composites narrows to exactly those composite values. The generated begins_with stops on a segment boundary, so a value is never confused with a longer sibling — byTenant({ tenantId, status: "done" }) on sk: ["status", "taskId"] matches done only, not done_archived or doneish.

Supplying all of them is still a prefix match, not an exact one: the stored key ends there, so there is no boundary left to stop on and a longer sibling value can match. On sk: ["label"], byBoard({ boardId, label: "ship" }) also returns shipped and ship_it. Use .get() when you want exactly one item, or leave the composite off the accessor and pass it to .where() instead — byBoard({ boardId }).where((t, { eq }) => eq(t.label, "ship")) compiles to sk = …#label_ship, an exact match.

Sort Key Conditions

.where() adds a KeyConditionExpression against a remaining sort key composite — DynamoDB evaluates it on the index server-side, so it does reduce read capacity. Use it whenever the condition is on a sort-key composite the accessor hasn’t already pinned. Operators: eq, lt, lte, gt, gte, between, beginsWith.

“Hasn’t already pinned” is enforced, not advice. DynamoDB allows exactly one sort key condition, and .where() replaces the begins_with the accessor installed for its pinned prefix — so a condition on a composite the accessor already fixed would discard that prefix and return rows outside it, not narrow within it. byBoard({ boardId, label: "ship" }).where((t, { eq }) => eq(t.label, "shine")) would have matched shine rows. It is now refused with EDD-9053, and on an accessor that pins every composite .where() is not offered at all.

The operand you pass is a composite attribute value, not a raw sort key. The library runs it through the same pipeline the write path used — the composite’s own codec, then serialization, then casing — and drops the result into the same $schema#v1#entity#<name>_<value> shape, so gte(t.createdAt, "2025-02-10") compares like-for-like against the stored key.

That includes the composite’s type. A composite typed number, bigint, boolean, Date or DateTime takes a value of that type, not a string, because the write path zero-pads numbers (16 digits) and bigints (38 digits) and formats dates as ISO strings. Passing "42" for a numeric composite is a compile error rather than a query that silently matches nothing.

It also covers composites whose schema transforms between its domain type and what DynamoDB stores. Schema.BigIntFromString has a bigint domain type and a string wire form, so a 420n composite is stored as txn_420; .where((t, { eq }) => eq(t.txn, 420n)) encodes the operand the same way and finds it. A value that cannot be encoded at all — a prefix string against a bigint composite, say — is refused with EDD-9050 rather than silently compared against nothing.

Keys and composites always take the domain type

.get(), .update(), .delete() and every query accessor take the composite’s domain value — the same one the model holds and .where() accepts — never its stored wire form. On the Ledger above that is get({ bookId, txn: 420n }), not get({ bookId, txn: "420" }); the wire form is rejected with a ValidationError naming the attribute. One convention across the whole API.

// byProject SK composites: ["status", "createdAt"].
// `.where()` constrains the first composite the accessor did not pin.
const activeTasks = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .where((t, { eq }) => eq(t.status, "active"))
  .collect()

// Pin `status` on the accessor, then range over `createdAt`.
const activeSinceFeb = yield* db.entities.TaskEntity.byProject({
  projectId: "proj-alpha",
  status: "active",
})
  .where((t, { gte }) => gte(t.createdAt, "2025-02-10"))
  .collect()

// `between` is inclusive at both ends.
const activeInFeb = yield* db.entities.TaskEntity.byProject({
  projectId: "proj-alpha",
  status: "active",
})
  .where((t, { between }) => between(t.createdAt, "2025-02-01", "2025-02-28"))
  .collect()

// `estimateHours` is a number, so `.where()` takes a number — not a string.
// The library zero-pads it exactly as the stored key was padded.
const bigTasks = yield* db.entities.TaskEntity.byEstimate({ projectId: "proj-alpha" })
  .where((t, { gte }) => gte(t.estimateHours, 8))
  .collect()

.where() consumes the remaining SK composites — it can be called once per query. Constrain a later composite by pinning the earlier ones on the accessor itself, as activeSinceFeb does above. Targeting a composite while an earlier one is still unpinned is rejected with EDD-9004, and calling .where() on an index whose sort key has no composites is rejected with EDD-9045.

A condition on a non-terminal composite (status, with createdAt still to follow) covers that value’s whole subtree: eq(t.status, "active") matches every active task regardless of createdAt, and lte/between include the far edge of the subtree.

One case DynamoDB cannot express

A strict lt on the last SK composite when the accessor has pinned an earlier one — byProject({ projectId, status: "active" }).where((t, { lt }) => lt(t.createdAt, "2025-03-01")) — needs begins_with(prefix) AND sk < value. DynamoDB allows only one sort key condition, BETWEEN is inclusive at both ends, and a FilterExpression may not reference a key attribute. Rather than silently returning the boundary item, this throws EDD-9046; use between or lte instead.

Post-Query Filtering

.filter() applies a FilterExpression after items are read from the index — it does not reduce read capacity, only network transfer. Use it for conditions on non-key attributes.

// Shorthand — AND-equality on multiple fields
const highPriActive = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .filter({ status: "active", priority: "high" })
  .collect()

// Shorthand — simple AND-equality
const activeShorthand = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .filter({ status: "active" })
  .collect()

Predicates DynamoDB cannot express

.filterBy() takes an ordinary TypeScript predicate, evaluated on the decoded item — for conditions a FilterExpression has no operator for. Case-insensitive matching is the standard case: DynamoDB has no lower(), so a FilterExpression compares the stored attribute byte-for-byte.

// Stored: name = "Melbourne Cricket Ground"
.filter((t, { beginsWith }) => beginsWith(t.name, "melbourne"))   // no match
.filterBy((v) => v.name.toLowerCase().startsWith("melbourne"))    // matches

Composite keys do not have this problem — casing is folded on both the stored key and the operand — so take as much as the key prefix can carry and match the rest with .filterBy().

The predicate runs inside the same accumulate loop .limit() uses, which is the whole point of it being a combinator rather than something you do to the result. Filtering page.items yourself breaks pagination twice over: the page comes back short, and its cursor resumes after the last item returned rather than the last one kept, so the next page skips rows.

Two consequences worth knowing:

• It runs after the read. Every examined row still crosses the wire and is still paid for. Prefer .filter() whenever DynamoDB can express the condition.
• .count() reads rows. Select: "COUNT" returns no items to test, so under .filterBy() the rows are read and the accepted ones counted. Correct, but it costs what a .collect() costs.

.filterBy() cannot be combined with .select() — a projection returns only the attributes it names, and a predicate is a closure whose reads the library cannot see, so it would be handed items missing the fields it tests. That combination raises EDD-9054.

Aggregate.list takes the same thing as filterBy in its options, applied to the root item before assembly — so a rejected aggregate never pays for its partition read.

When to use SK composites vs filter vs filterBy:

Approach	DynamoDB Mapping	Reduces Read Capacity?	Use For
SK composites in accessor	KeyConditionExpression	Yes	Narrowing by sort key prefix
.filter()	FilterExpression	No	Any attribute (post-read)
.filterBy()	none — runs in the client	No	Conditions DynamoDB has no operator for

See the Expressions Guide for the complete operator reference and DynamoDB mapping tables.

Collection Queries

Collection queries return all member entity types, grouped by member name.

const db = yield* DynamoClient.make({
  entities: { ClusteredEmployees, ClusteredTasks },
  tables: { MainTable },
})

// All entities in the collection (auto-discovered from entity indexes with collection: "tenantMembers")
const { ClusteredEmployees, ClusteredTasks } = yield* db.collections
  .tenantMembers({ tenantId: "t-acme" })
  .collect()
// ClusteredEmployees: Employee[], ClusteredTasks: Task[]

Hierarchical Sub-Collections

Use the array form collection: ["parent", "child"] together with type: "clustered" to nest entities in a hierarchy. A query at the parent level returns the parent’s items and every descendant; a query at a child level returns only items at that level or deeper.

// Parent — returns Employee + Task + ProjectMember (everything in the partition)
const contributions = yield* db.collections
  .contributions({ employeeId: "emp-alice" })
  .collect()
// { SubEmployee: Employee[], SubTasks: Task[], SubProjectMembers: ProjectMember[] }

// Child — returns only the deeper-level entities
const assignments = yield* db.collections
  .assignments({ employeeId: "emp-alice" })
  .collect()
// { SubTasks: Task[], SubProjectMembers: ProjectMember[] }

For independent collections that just happen to share an index (no parent/child relationship), use a single string instead of an array (collection: "name"). See the Indexes & Collections guide for the full pattern, SK shape, and trade-offs.

Pagination

Collect All Items

.collect() fetches all pages and flattens into a single array (or grouped result for collections):

// Collect all items across all pages
const allProjectTasks = yield* db.entities.TaskEntity.byProject({
  projectId: "proj-alpha",
}).collect()

limit vs pageSize

These are two different ideas, and they do not share a word:

Combinator	Contract	Sets DynamoDB Limit?
.limit(n)	Return at most n items — a contract on results	No
.pageSize(n)	Fetch in batches of n rows — a contract on round trips	Yes
.maxPages(n)	Stop after n requests, however many items that yielded	No

// `limit` bounds the RESULT — at most 3 tasks come back
const firstThree = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .limit(3)
  .collect()

// `pageSize` bounds each ROUND TRIP — every match comes back, fetched in
// requests of 2
const inBatches = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .pageSize(2)
  .collect()

// Both compose — requests of 2, accumulating until 5 items
const batchedAndCapped = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .pageSize(2)
  .limit(5)
  .collect()

.limit(n) works under a .filter() (and under a .filterBy()), and this is the reason the two are separate. DynamoDB’s Limit bounds the rows examined, and a FilterExpression is applied after that — so Limit can never express “give me 3 matching items”. .limit() is satisfied by accumulating across requests until n items are accepted or the key range is exhausted; .pageSize() is what each of those requests asks for. On a large partition with a very selective filter, pair .limit() with .maxPages() to bound the work.

Changed in 1.16

.limit(n) used to set DynamoDB’s per-request Limit, which meant .limit(3).collect() returned every matching item, fetched in pages of 3. It now returns 3. If you were using .limit() as a page-size hint, move to .pageSize() — .limit(2).paginate() streaming everything in pages of 2 becomes .pageSize(2).paginate().

Single Page

.fetch() returns one page of items plus an optional cursor. Without a .limit(), a page is one DynamoDB request; with .limit(n), a page is n items and the request loop fills it:

// Single page with limit — returns page with items and cursor
const page = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" }).limit(3).fetch()
// page.items: Task[] (up to 3 items)
// page.cursor: string | null — resumes after the 3rd item, or null when done

The cursor always resumes after the last item you were handed, even when the underlying request read past it and the surplus was discarded. cursor: null means the key range is genuinely exhausted.

Cursor-Based Pagination

Use .startFrom() to resume from a previous page’s cursor:

// Cursor-based pagination
const page1 = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .limit(3)
  .fetch()

if (page1.cursor) {
  const page2 = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
    .limit(3)
    .startFrom(page1.cursor)
    .fetch()
}

Streaming

.paginate() returns a Stream<A>, automatically handling DynamoDB pagination:

// Streaming — automatic pagination via Stream
const stream = tasks.scan().paginate()
const allFromStream = yield* Stream.runCollect(stream)

Scan

Entity scans read the entire table and return items matching the entity type. db.entities.Entity.scan() returns a BoundQuery, so all combinators and terminals work with scans.

// Basic scan — all items of this entity type
const allTasks = yield* tasks.scan().collect()

// Scan with filter
const activeScan = yield* tasks.scan().filter({ status: "active" }).collect()

// Scan with limit — at most 3 items come back
const firstPage = yield* tasks.scan().limit(3).collect()

// Scan in batches — every item comes back, read 3 rows per request
const batchedScan = yield* tasks.scan().pageSize(3).collect()

// Scan with consistent read
const consistent = yield* tasks.scan().consistentRead().collect()

// Stream-based scan
const scanStream = tasks.scan().paginate()
yield* Stream.runForEach(scanStream, (t) => Console.log(`  Scanned: ${t.taskId} — "${t.title}"`))

When to use Scan vs Query:

	Query	Scan
Targets	Specific partition	Entire table
Efficiency	Reads only matching partition	Reads every item
Cost	Low (proportional to results)	High (proportional to table size)
Use cases	Normal application queries	Admin tools, migrations, data exports, analytics

Scan automatically filters by __edd_e__ — even in a single-table design, db.entities.Tasks.scan() only returns Task items.

Consistent Reads

By default, DynamoDB reads are eventually consistent. For strong consistency, use consistentRead:

// Consistent read on get — use entity definition's get + pipe
const consistentTask = yield* TaskEntity.get({ taskId: "t-001" }).pipe(Entity.consistentRead())

// Consistent read on scan (applies to any BoundQuery against the base table).
// Note: DynamoDB GSIs do not support consistent reads — only the base table
// and local secondary indexes do.
const consistentScan = yield* tasks.scan().consistentRead().collect()

Consistent reads cost 2x the read capacity of eventually-consistent reads. Use them when you need read-after-write consistency (e.g., immediately after a put or update). GSIs cannot serve consistent reads — consistentRead() is only valid against the primary table or a local secondary index.

Ordering

By default, results are in ascending sort key order. Use .reverse() for descending:

// The 3 most recent tasks (descending sort key order)
const recent = yield* db.entities.TaskEntity.byProject({ projectId: "proj-alpha" })
  .reverse()
  .limit(3)
  .collect()

Complete Example

import { Effect, Layer, Stream } from "effect"
import { DynamoClient } from "effect-dynamodb"

const program = Effect.gen(function* () {
  const db = yield* DynamoClient.make({
    entities: { TaskEntity, ClusteredEmployees, ClusteredTasks },
    tables: { MainTable },
  })

  // --- Single entity scan with filter ---
  const activeTasks = yield* db.entities.TaskEntity.scan()
    .filter({ status: "active" })
    .limit(50)
    .collect()

  // --- Entity index query, reversed, with filter ---
  const recentHighPriority = yield* db.entities.TaskEntity
    .byAssignee({ assigneeId: "emp-alice" })
    .filter({ priority: "high" })
    .reverse()
    .limit(10)
    .collect()

  // --- Auto-discovered collection query: all tenant members ---
  const { ClusteredEmployees, ClusteredTasks } = yield* db.collections
    .tenantMembers({ tenantId: "t-acme" })
    .collect()
  // ClusteredEmployees: Employee[], ClusteredTasks: Task[]

  // --- Scan with streaming ---
  const scanStream = db.entities.TaskEntity.scan()
    .filter({ status: "active" })
    .paginate()
  yield* Stream.runForEach(scanStream, (t) =>
    Effect.log(`Task: ${t.title}`)
  )
})

const main = program.pipe(
  Effect.provide(
    Layer.mergeAll(
      DynamoClient.layer({ region: "us-east-1" }),
      MainTable.layer({ name: "Main" }),
    )
  )
)

What’s Next?

• Expressions — Complete reference for condition, filter, update, and projection expressions with DynamoDB mapping tables
• Data Integrity — Unique constraints, versioning, and optimistic concurrency
• Lifecycle — Soft delete, restore, purge, and version retention
• Advanced — Rich updates, batch operations, conditional writes
• DynamoDB Streams — Decode stream records into typed domain objects