proofgeist/odata-query-optimization

Setup

This skill assumes a working fmodata client (see fmodata-client). Optimization starts at the schema level with defaultSelect and continues per-query with select().

defaultSelect on schema

import { fmTableOccurrence, textField, numberField } from "@proofkit/fmodata";

// "schema" (default) — only fetches fields defined in the schema via $select
const users = fmTableOccurrence("users", {
  id: textField().primaryKey().entityId("FMFID:12039485"),
  name: textField().notNull().entityId("FMFID:34323433"),
  email: textField().entityId("FMFID:12232424"),
  age: numberField().entityId("FMFID:43234355"),
}, {
  defaultSelect: "schema", // default — adds $select with all schema fields
  entityId: "FMTID:12432533",
});

// "all" — no $select applied, FileMaker returns every field on the layout
const usersAll = fmTableOccurrence("users", {
  id: textField().primaryKey(),
  name: textField().notNull(),
}, {
  defaultSelect: "all", // slower: returns all layout fields, not just schema fields
});

Per-query select override

// Override defaultSelect for a single query — fetch only what you need
const result = await db.from(users).list()
  .select({ name: users.name, email: users.email })
  .execute();

// Override defaultSelect:"schema" to fetch all fields for this query
const result = await db.from(users).list()
  .select("all")
  .execute();

Core Patterns

1. Selective fetching with select()

Fewer fields = smaller response = faster queries. Always prefer explicit select() when you need a subset of fields.

// Fetch only 2 fields instead of all schema fields
const result = await db.from(users).list()
  .select({ name: users.name, email: users.email })
  .execute();

// Rename fields in the result object
const result = await db.from(users).list()
  .select({ userName: users.name, userEmail: users.email })
  .execute();
// result.data[0].userName, result.data[0].userEmail

// Exclude specific fields using destructuring + getTableColumns
import { getTableColumns } from "@proofkit/fmodata";
const { age, ...cols } = getTableColumns(users);
const result = await db.from(users).list().select(cols).execute();

2. Pagination with top/skip/count

list() applies .top(1000) by default. Override for smaller pages or to fetch more.

// Page 1: first 50 records
const page1 = await db.from(users).list().top(50).skip(0).execute();

// Page 2: next 50
const page2 = await db.from(users).list().top(50).skip(50).execute();

// Get total count (separate request, returns number)
const countResult = await db.from(users).list().count().execute();
// countResult.data === 1523

// Fetch more than 1000 (override default limit)
const allRecords = await db.from(users).list().top(5000).execute();

3. Batch operations for reducing round trips

Combine multiple queries into a single HTTP request. Write operations are transactional.

const contactsQuery = db.from(contacts).list().top(5);
const usersQuery = db.from(users).list().top(5);

// Single HTTP request for both queries
const result = await db.batch([contactsQuery, usersQuery]).execute();

const [contactsResult, usersResult] = result.results;
if (contactsResult.data) { /* ... */ }
if (usersResult.data) { /* ... */ }

// Mix reads and writes — writes are atomic
const result = await db.batch([
  db.from(contacts).list().top(10),
  db.from(contacts).insert({ name: "Alice", email: "[email protected]" }),
  db.from(users).update({ active: true }).byId("user-123"),
]).execute();

4. Entity IDs for rename resilience

Entity IDs (FMTID/FMFID) prevent breakage when FileMaker fields or table occurrences are renamed.

IMPORTANT: Entity IDs must come from FileMaker metadata via @proofkit/typegen. Do NOT invent FMFID/FMTID values — the IDs shown below are illustrative only. Guessed IDs cause silent query failures.

// Entity IDs are generated by @proofkit/typegen — do not manually add or change
const users = fmTableOccurrence("users", {
  id: textField().primaryKey().entityId("FMFID:12039485"),
  name: textField().notNull().entityId("FMFID:34323433"),
}, {
  entityId: "FMTID:12432533",
});

// Enable at database level
const db = connection.database("MyDatabase", { useEntityIds: true });

// Or per-request
const result = await db.from(users).list().execute({ useEntityIds: true });

5. Debugging with getQueryString()

Inspect the generated OData URL without executing the request.

import { eq, asc } from "@proofkit/fmodata";

const queryString = db.from(users).list()
  .select({ name: users.name, email: users.email })
  .where(eq(users.active, true))
  .orderBy(asc(users.name))
  .top(10)
  .getQueryString();

console.log(queryString);
// "/users?$select=name,email&$filter=active eq true&$orderby=name&$top=10"

// View with entity IDs resolved to see what's actually sent
const entityIdQuery = db.from(users).list()
  .getQueryString({ useEntityIds: false });

Common Mistakes

[HIGH] Not accounting for default 1000 record limit

Wrong:

// Expects all 5000 records but only gets 1000
const result = await db.from(users).list().execute();

Correct:

// Explicitly set top() to fetch more than 1000
const result = await db.from(users).list().top(5000).execute();

// Or paginate through results
let allRecords = [];
let skip = 0;
const pageSize = 1000;
while (true) {
  const page = await db.from(users).list().top(pageSize).skip(skip).execute();
  if (!page.data || page.data.length === 0) break;
  allRecords.push(...page.data);
  if (page.data.length < pageSize) break;
  skip += pageSize;
}

list() internally calls .top(1000) as a safety limit. If you need more records, override with an explicit .top() or paginate.

Source: packages/fmodata/src/client/entity-set.ts (line 185), packages/fmodata/src/client/query/query-builder.ts (line 50)

[MEDIUM] includeSpecialColumns silently dropped with explicit select()

Wrong:

const db = connection.database("MyDatabase", { includeSpecialColumns: true });

// Expects ROWID and ROWMODID but they are silently excluded
const result = await db.from(users).list()
  .select({ name: users.name })
  .execute();

Correct:

// Pass system columns as second argument to select()
const result = await db.from(users).list()
  .select(
    { name: users.name },
    { ROWID: true, ROWMODID: true }
  )
  .execute();

// Or don't use select() — special columns appear when no $select is applied
const result = await db.from(users).list().execute();

Per OData spec, special columns are only included when no $select query parameter is applied. Using .select() generates a $select, so special columns must be explicitly requested via the second argument.

Source: apps/docs/content/docs/fmodata/extra-properties.mdx

[MEDIUM] Entity IDs on tables without configured IDs

Wrong:

// Table has no entityId or fmfIds configured
const users = fmTableOccurrence("users", {
  id: textField().primaryKey(),
  name: textField(),
});

// Throws immediately — no FMTID/FMFID available
const result = await db.from(users).list().execute({ useEntityIds: true });

Correct:

// Configure entity IDs on the schema
const users = fmTableOccurrence("users", {
  id: textField().primaryKey().entityId("FMFID:12039485"),
  name: textField().entityId("FMFID:34323433"),
}, {
  entityId: "FMTID:12432533",
});

// Now useEntityIds works
const result = await db.from(users).list().execute({ useEntityIds: true });

Entity IDs require both a table-level entityId (FMTID) and per-field .entityId() (FMFID) to be set. These must be generated by @proofkit/typegen from FileMaker metadata — do not manually add or invent entity IDs.

Source: apps/docs/content/docs/fmodata/entity-ids.mdx, packages/fmodata/src/orm/table.ts

[HIGH] Filtering on null fields causes severe performance degradation

Wrong:

import { isNull } from "@proofkit/fmodata";

// Extremely slow on large datasets — FileMaker OData handles null checks poorly
const result = await db.from(users).list()
  .where(isNull(users.deletedAt))
  .execute();

Correct:

import { eq } from "@proofkit/fmodata";

// Use a calc field in FileMaker that returns 1 when deletedAt is empty
// e.g., IsEmpty(deletedAt) calc field → isActive
const result = await db.from(users).list()
  .where(eq(users.isActive, 1))
  .execute();

FileMaker's OData implementation has severe performance issues when filtering on null/empty fields. Create a calculation field in FileMaker that evaluates the null condition and filter on that instead.

Source: FileMaker OData performance testing (known limitation)

[HIGH] Overwhelming OData service during testing

Wrong:

// Rapid-fire queries in a loop without any throttling
for (const id of userIds) {
  const result = await db.from(users).get(id).execute();
  // ... process
}

Correct:

// Use batch operations to reduce round trips
const queries = userIds.map(id => db.from(users).get(id));
const result = await db.batch(queries).execute();

// Or paginate with reasonable page sizes and delays between pages
for (let skip = 0; skip < total; skip += 100) {
  const result = await db.from(users).list().top(100).skip(skip).execute();
  // ... process page
  await new Promise(resolve => setTimeout(resolve, 100)); // throttle
}

Rapid sequential queries can degrade the FileMaker OData service. Use batch operations to combine multiple requests, and add throttling when iterating through large datasets during testing or migration.

Source: FileMaker OData service behavior (known limitation)

[MEDIUM] Not testing relationship query performance

Wrong:

// Assumes expand performance is predictable
const result = await db.from(invoices).list()
  .expand(lineItems)
  .top(500)
  .execute();

Correct:

// Test with small datasets first and measure performance
const test = await db.from(invoices).list()
  .expand(lineItems)
  .top(10)
  .execute();
// Measure timing, then scale up cautiously

// Debug the generated query
const queryString = db.from(invoices).list()
  .expand(lineItems)
  .top(10)
  .getQueryString();
console.log(queryString);

// Consider fetching related records separately if expand is too slow
const invoiceResult = await db.from(invoices).list().top(100).execute();
const lineItemResult = await db.from(lineItems).list()
  .where(inArray(lineItems.invoiceId, invoiceIds))
  .execute();

Relationship query performance via expand() is unpredictable in FileMaker's OData implementation. Always test with production-sized data. If performance is poor, fetch related records in separate queries or use batch operations.

Source: FileMaker OData relationship performance (must be tested case-by-case)

References

• fmodata-client — Client setup, connection configuration, database initialization. Understanding the client is prerequisite to applying these optimization patterns.