Indexing and Queries

Indexing & Queries

What this page covers: declaring secondary indexes — with @Indexed on fields or manual IndexHint factories — and reading through them with findBy and the composable Query API (eq / range / in / and). Indexes are declared, not implicit: a field is queryable only after you say so, and querying an undeclared field throws on every backend. Plain key/CRUD reads are on CRUD Operations.

---

The 30-second version

import br.com.finalcraft.everydatabase.*;
import br.com.finalcraft.everydatabase.codec.JacksonJsonCodec;
import br.com.finalcraft.everydatabase.query.*;

// Declare two indexes on the builder, then query them.
EntityDescriptor<UUID, TestPlayer> PLAYERS = EntityDescriptor.builder(UUID.class, TestPlayer.class)
        .collection("players")
        .keyExtractor(TestPlayer::getUuid)
        .codec(new JacksonJsonCodec<>(TestPlayer.class))
        .index(IndexHint.string("name"))
        .index(IndexHint.integer("score"))
        .build();

Repository<UUID, TestPlayer> repo = storage.repository(PLAYERS);

// Shorthand equality on an indexed field:
List<TestPlayer> alices = repo.findBy("name", "Alice").join();

// Composable query — conditions are AND-joined:
List<TestPlayer> top = repo.query(
        Query.eq("name", "Alice").and(Query.range("score", 1000, null))).join();

📌 Note — findBy and query return CompletableFuture<List<V>>; the examples use .join() for brevity. There are no blocking variants — compose with thenApply / thenCompose in real code. See The Async API.

TestPlayer mirrors the real test entity (core/src/test/java/br/com/finalcraft/everydatabase/data/TestPlayer.java): uuid / name / score / world / active / createdAt.

---

Two ways to declare an index

A field becomes queryable in exactly one of two ways. They can be combined on the same entity.

1. @Indexed annotations (auto-detected)

Annotate fields; EntityDescriptor.build() scans the class hierarchy and creates the matching IndexHint for each. Mirrors AnnotatedTestPlayer (core/src/test/java/.../data/AnnotatedTestPlayer.java):

public class AnnotatedTestPlayer {
    private UUID uuid;

    @Indexed
    private String name;                       // FieldType auto-detected: STRING

    @Indexed
    private int score;                         // INT, ascending (default)

    @Indexed(path = "rank.title", type = String.class)      // nested dot-path
    private Rank rank;

    @Indexed(path = "location.world", type = String.class)  // nested dot-path
    private Location location;

    private List<Badge> badges;                // not annotated — stored, not indexed
}

@Indexed has three members:

Member	Default	Meaning
path()	"" → the field's name	the index field path; use dot-notation for nested fields
type()	void.class → auto-detect	override the Java type the FieldType is resolved from
order()	ASCENDING	sort order of the backing index

The FieldType is inferred from the field's declared Java type: String → STRING, int/Integer → INT, long/Long → LONG, float/double (+ boxed) → DOUBLE, boolean/Boolean → BOOLEAN, Instant/LocalDateTime → TIMESTAMP.

⚠️ Gotcha — for a nested path (e.g. "location.world"), the annotated field's own Java type (Location) doesn't match the indexed value's type (String), so the scanner can't infer it. You must set type explicitly. An annotated field whose type can't be mapped and has no explicit type throws IllegalArgumentException at build().

2. Manual IndexHint factories (on the builder)

When you can't (or don't want to) annotate the class, declare hints on the builder. Mirrors TestPlayer, which declares its hints manually:

EntityDescriptor<UUID, TestPlayer> PLAYERS = EntityDescriptor.builder(UUID.class, TestPlayer.class)
        .collection("players")
        .keyExtractor(TestPlayer::getUuid)
        .codec(new JacksonJsonCodec<>(TestPlayer.class))
        .index(IndexHint.string("name"))
        .index(IndexHint.integer("score"))
        .index(IndexHint.bool("active"))
        .index(IndexHint.timestamp("createdAt"))
        .build();

The factory table

Factory	FieldType	SQL column type	Java field type
IndexHint.string(path)	STRING	TEXT	String
IndexHint.integer(path)	INT	INT	int / Integer
IndexHint.bigInt(path)	LONG	BIGINT	long / Long
IndexHint.decimal(path)	DOUBLE	DOUBLE	double / float
IndexHint.bool(path)	BOOLEAN	BOOLEAN	boolean / Boolean
IndexHint.timestamp(path)	TIMESTAMP	DATETIME(3) / TIMESTAMPTZ	long / Instant / LocalDateTime

Modifiers return a new immutable hint: IndexHint.string("name").asDescending() (or .asAscending()).

⚠️ Gotcha — the factory names don't match the type names. bigInt(...) produces a LONG index (for Java long), and decimal(...) produces a DOUBLE index (for Java double). There is no IndexHint.long(...) (long is a reserved word) and no IndexHint.double(...). Reach for bigInt whenever the field is a long, and decimal whenever it's a double/float.

Combining the two — and the duplicate rule

You can mix manual .index(...) calls with @Indexed annotations on the same entity. But if the same field path appears in both, build() throws (IllegalStateException, duplicate index hint) — declare each path exactly once.

📌 Note — indexes are reconciled automatically. Add a new hint to an existing collection and the backend ALTERs the column/index in and backfills it from existing rows the next time the repository is opened; remove a hint and the backing index is dropped. No manual migration needed (that's separate from migrate() — see Schema Migrations).

---

Querying

findBy — equality shorthand

CompletableFuture<List<V>> findBy(String fieldPath, Object value);

repo.findBy("name", "Alice") is exactly repo.query(Query.eq("name", "Alice")), just shorter for the common single-equality case.

query — the composable API

CompletableFuture<List<V>> query(Query query);

Query is built from static factories and composed with .and(...):

// Equality
repo.query(Query.eq("world", "world_nether")).join();

// Range — inclusive on both ends; null = open end
repo.query(Query.range("score", 100, 500)).join();          // 100 <= score <= 500
repo.query(Query.range("score", 100, null)).join();         // score >= 100  (open upper)
repo.query(Query.range("score", null, 500)).join();         // score <= 500  (open lower)

// IN-list (Collection or varargs)
repo.query(Query.in("name", Arrays.asList("Alice", "Bob"))).join();
repo.query(Query.in("name", "Alice", "Bob")).join();        // varargs convenience

// AND of several conditions
repo.query(Query.eq("world", "world")
        .and(Query.range("score", 1000, null))).join();      // world == "world" AND score >= 1000

The operators:

Factory	Operator	Semantics
Query.eq(path, value)	EQ	path = value
Query.range(path, from, to)	RANGE	from <= path <= to; inclusive; null = open end
Query.in(path, values) / (path, varargs)	IN	path is one of values
q.and(other)	—	concatenates conditions, all joined by AND

⚠️ Gotcha — AND only, no native OR. Query.and(...) is the only combinator, and conditions are always intersected. For OR, run two queries and union the results client-side:

var a = repo.query(Query.eq("world", "world")).join();
var b = repo.query(Query.eq("world", "world_nether")).join();
// union by key (dedupe) on your side

---

TIMESTAMP fields are epoch-millis everywhere

A timestamp index accepts Instant or LocalDateTime in queries, and the entity field itself may be a Java long (epoch millis), an Instant, or a LocalDateTime. Every backend stores and compares the value as epoch-milliseconds — SQL just uses a native date column type so values stay human-readable in DB tools; InMemory and LocalFile keep a Long internally.

// Declared with .index(IndexHint.timestamp("createdAt"))
repo.query(Query.range("createdAt",
        Instant.now().minus(7, ChronoUnit.DAYS), Instant.now())).join();   // last 7 days

repo.query(Query.range("createdAt", someInstant, null)).join();   // after someInstant
repo.query(Query.range("createdAt", null, someInstant)).join();   // before someInstant

💡 Tip — because comparison is epoch-millis under the hood, a long-typed createdAt field and an Instant-typed one are interchangeable for querying; pick whatever your entity already uses.

---

Undeclared fields are rejected — on every backend

A query (via findBy or query) against a field that was not declared as an index throws IllegalArgumentException at execution time. This holds on every backend, including LocalFile — which has no real index and answers declared-field queries with a correct-but-slow full scan, yet still validates the declaration first and rejects undeclared fields.

// 'mood' was never declared as an index:
repo.findBy("mood", "happy");   // future completes exceptionally with IllegalArgumentException

🧭 Decision — why LocalFile rejects fields it could scan. It would be technically possible for LocalFile to brute-force any field. It deliberately doesn't, so that a query which works on LocalFile keeps working unchanged when you swap the backend for SQL or Mongo. Validate-then-scan keeps behavior identical across Choosing a Backend; you never write a query that silently only works on one of them.

How each backend executes a valid query:

• SQL (MySQL/MariaDB/PostgreSQL/H2) — a stored _idx_<field> column populated at save time, with a real B-tree index; conditions become WHERE … = ? / BETWEEN / IN joined by AND.
• MongoDB — value mirrored into _idx_<field> with createIndex; Filters.and(...).
• InMemory — a Map<value, Set<key>> per indexed field; intersection of key sets.
• LocalFile — no real index: full scan + tree-walk filter (O(n) per call), after validating the field is declared.

---

See also

• CRUD Operations — key-based reads/writes (find, save, delete, …).
• Defining Entities — the EntityDescriptor.builder where indexes are declared.
• The Async API — findBy/query return futures; composition and error model.
• Choosing a Backend — how each backend materializes an index (and the LocalFile full-scan).
• Schema Migrations — index reconciliation vs. explicit migrate().
• Gotchas & Pitfalls — the bigInt→LONG / decimal→DOUBLE naming trap, AND-only queries, the undeclared-field rejection, in one place.