proofgeist/fmodata-client

Setup

Start with typegen. Before writing any fmodata code, use npx @proofkit/typegen@beta to generate table schemas with correct entity IDs from FileMaker metadata. See the typegen-fmodata skill. Schema files (fields, entity IDs) MUST come from typegen — do NOT manually add fields or change entity IDs (FMFID/FMTID), as guessing them causes silent failures. You MAY add/edit readValidator, writeValidator, defaultSelect, navigationPaths, and other options on existing fields.

1. Create a server connection

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

const connection = new FMServerConnection({
  serverUrl: "https://your-server.com",
  auth: { username: "admin", password: "secret" },
  // OR with OttoFMS API key:
  // auth: { apiKey: "your-otto-api-key" },
  // OR with Claris ID for FileMaker Cloud:
  // auth: { clarisId: { username: "[email protected]", password: "secret" } },
  fetchClientOptions: {
    retries: 2,
    timeout: 30000,
  },
});

For FileMaker Cloud, use a Claris ID account, not an external IdP account. MFA-backed Claris ID accounts are not supported.

2. Create a database reference

const db = connection.database("MyDatabase.fmp12", {
  useEntityIds: true,           // use FMTID/FMFID instead of names
  includeSpecialColumns: false,  // include ROWID/ROWMODID
});

3. Define table schemas with fmTableOccurrence

IMPORTANT: Schema files — including field definitions and entity IDs — are generated by @proofkit/typegen. The entity IDs shown below are illustrative only. Do NOT invent FMFID/FMTID values; they must come from FileMaker metadata via typegen. You may customize generated schemas by adding readValidator, writeValidator, defaultSelect, navigationPaths, and other options.

import {
  fmTableOccurrence,
  textField,
  numberField,
  timestampField,
  containerField,
  calcField,
  listField,
} from "@proofkit/fmodata";
import { z } from "zod/v4";

// Generated by @proofkit/typegen — do not manually add fields or change entity IDs
const contacts = fmTableOccurrence(
  "contacts",
  {
    id: textField().primaryKey().entityId("FMFID:1"),
    name: textField().notNull().entityId("FMFID:2"),
    email: textField().notNull().entityId("FMFID:3"),
    phone: textField().entityId("FMFID:4"),
    age: numberField().entityId("FMFID:5"),
    // readValidator/writeValidator are safe to add or edit manually
    active: numberField()
      .readValidator(z.coerce.boolean())
      .writeValidator(z.boolean().transform((v) => (v ? 1 : 0)))
      .entityId("FMFID:6"),
    tags: listField({ itemValidator: z.string() }),
    photo: containerField(),
    fullName: calcField(),
    createdAt: timestampField().readOnly().entityId("FMFID:10"),
  },
  {
    entityId: "FMTID:100",
    // defaultSelect and navigationPaths are safe to add or edit manually
    defaultSelect: "schema",
    navigationPaths: ["invoices", "notes"],
  },
);

Field builders: textField(), numberField(), dateField(), timeField(), timestampField(), containerField(), calcField(), listField().

Chainable methods: .primaryKey(), .notNull(), .readOnly(), .entityId("FMFID:..."), .readValidator(schema), .writeValidator(schema), .comment("...").

defaultSelect options:

• "schema" (default) -- always sends $select with only schema-defined fields
• "all" -- no $select, returns all non-container fields from FM
• (cols) => ({ ... }) -- custom subset of columns

Core Patterns

Querying (list / get / select / where / execute)

import { eq, gt, and, contains, asc, desc, getTableColumns } from "@proofkit/fmodata";

// List with filter, sort, pagination
const result = await db
  .from(contacts)
  .list()
  .select({ name: contacts.name, email: contacts.email })
  .where(and(eq(contacts.active, true), gt(contacts.age, 18)))
  .orderBy(asc(contacts.name), desc(contacts.age))
  .top(50)
  .skip(0)
  .execute();

if (result.data) {
  for (const row of result.data) {
    console.log(row.name, row.email);
  }
}

// Get single record by ID
const one = await db.from(contacts).get("abc-123").execute();

// Get single record by FileMaker ROWID
const byRowId = await db.from(contacts).get({ ROWID: 2 }).execute();

// single() -- error if != 1 result; maybeSingle() -- null if 0, error if > 1
const exact = await db
  .from(contacts)
  .list()
  .where(eq(contacts.email, "[email protected]"))
  .single()
  .execute();

// Count
const count = await db.from(contacts).list().count().execute();

// Override defaultSelect for one query
const all = await db.from(contacts).list().select("all").execute();

// Get single field (required for container fields)
const photo = await db
  .from(contacts)
  .get("abc-123")
  .getSingleField(contacts.photo)
  .execute();

// Select all columns except some
const { photo: _, ...cols } = getTableColumns(contacts);
const withoutPhoto = await db.from(contacts).list().select(cols).execute();

CRUD (insert / update / delete)

// Insert -- notNull fields are required, readOnly/primaryKey excluded
const inserted = await db
  .from(contacts)
  .insert({ name: "Alice", email: "[email protected]", active: true })
  .execute();

// Update by ID (default: returns { updatedCount })
const updated = await db
  .from(contacts)
  .update({ phone: "+1-555-0100" })
  .byId("abc-123")
  .execute();

// Update by ROWID
const updatedByRowId = await db
  .from(contacts)
  .update({ phone: "+1-555-0100" })
  .byRowId(2)
  .execute();

// Update by filter
const bulk = await db
  .from(contacts)
  .update({ active: false })
  .where((q) => q.where(eq(contacts.active, true)))
  .execute();

// Delete by ID
const deleted = await db.from(contacts).delete().byId("abc-123").execute();

// Delete by ROWID
const deletedByRowId = await db.from(contacts).delete().byRowId(2).execute();

// Delete by filter
const bulkDel = await db
  .from(contacts)
  .delete()
  .where((q) => q.where(eq(contacts.active, false)))
  .execute();

Error handling (Result pattern)

Every .execute() returns Result<T>:

type Result<T> = { data: T; error: undefined } | { data: undefined; error: FMODataErrorType };

Always check result.error before accessing result.data. Use type guards or instanceof:

import { isHTTPError, isODataError, isValidationError, isBatchTruncatedError } from "@proofkit/fmodata";

const result = await db.from(contacts).list().execute();

if (result.error) {
  if (isHTTPError(result.error)) {
    console.log(result.error.status, result.error.statusText);
  } else if (isODataError(result.error)) {
    console.log(result.error.code, result.error.details);
  } else if (isValidationError(result.error)) {
    console.log(result.error.field, result.error.issues);
  }
  return;
}

// result.data is guaranteed non-undefined here
console.log(result.data);

Relationships (navigate / expand)

Define navigationPaths on table occurrences, then use navigate() or expand().

// navigate -- changes query context to related table
const orders = await db
  .from(contacts)
  .get("abc-123")
  .navigate(invoices)
  .execute();

// Navigate starting from a ROWID-located record
const ordersByRowId = await db
  .from(contacts)
  .get({ ROWID: 2 })
  .navigate(invoices)
  .execute();

// expand -- includes related records inline
const withInvoices = await db
  .from(contacts)
  .list()
  .expand(invoices, (b) =>
    b
      .select({ total: invoices.total, date: invoices.date })
      .where(gt(invoices.total, 100))
      .top(5),
  )
  .execute();

// Nested expand
const nested = await db
  .from(contacts)
  .list()
  .expand(invoices, (ib) =>
    ib.expand(lineItems, (lb) => lb.select({ desc: lineItems.description })),
  )
  .execute();

Batch operations

const result = await db
  .batch([
    db.from(contacts).list().top(5),
    db.from(contacts).insert({ name: "New", email: "[email protected]" }),
    db.from(contacts).update({ active: false }).byId("old-id"),
  ])
  .execute();

const [r1, r2, r3] = result.results;
console.log(result.successCount, result.errorCount, result.truncated);

Common Mistakes

[CRITICAL] Forgetting .execute() on query builders

Wrong:

const data = await db.from(contacts).list().where(eq(contacts.active, true));

Correct:

const result = await db.from(contacts).list().where(eq(contacts.active, true)).execute();

Query builders are lazy; they return a builder object, not data. .execute() triggers the HTTP request and returns Result<T>.

Source: packages/fmodata/src/client/query/query-builder.ts

[CRITICAL] Ignoring Result error -- data is null when error present

Wrong:

const result = await db.from(contacts).list().execute();
console.log(result.data.length);

Correct:

const result = await db.from(contacts).list().execute();
if (result.error) {
  console.error(result.error.message);
  return;
}
console.log(result.data.length);

Result is a discriminated union. When error is defined, data is undefined. Accessing .data without checking error causes runtime TypeError.

Source: packages/fmodata/src/types.ts

[CRITICAL] Using Drizzle ORM patterns instead of fmodata patterns

Wrong:

import { eq } from "drizzle-orm";
const rows = await db.select().from(contacts).where(eq(contacts.name, "Alice"));

Correct:

import { eq } from "@proofkit/fmodata";
const result = await db.from(contacts).list().where(eq(contacts.name, "Alice")).select({ name: contacts.name }).execute();

fmodata has a different chain order: db.from(table).list().where().select().execute(). Operators must be imported from @proofkit/fmodata, not drizzle-orm. fmodata uses list() not select() to start a query, and always ends with .execute().

Source: packages/fmodata/src/client/entity-set.ts

[CRITICAL] Using raw JS operators instead of OData filter functions

Wrong:

.where(contacts.name === "Alice")
.where(contacts.age > 18)

Correct:

import { eq, gt } from "@proofkit/fmodata";
.where(eq(contacts.name, "Alice"))
.where(gt(contacts.age, 18))

JavaScript comparison operators return booleans at build time, not filter expressions. Use the imported operator functions which produce OData $filter query strings.

Source: packages/fmodata/src/orm/operators.ts

[HIGH] Container fields in select() calls

Wrong:

const result = await db
  .from(contacts)
  .list()
  .select({ photo: contacts.photo, name: contacts.name })
  .execute();

Correct:

// Get container field separately
const photo = await db
  .from(contacts)
  .get("abc-123")
  .getSingleField(contacts.photo)
  .execute();

Container fields (Edm.Stream) cannot be included in $select. The FileMaker OData API requires fetching them individually via .getSingleField(). TypeScript will show a compile error if you try to select a container field.

Source: packages/fmodata/src/orm/table.ts (ValidateNoContainerFields type)

[HIGH] Expecting batch to continue after first error

Wrong:

const result = await db.batch([op1, op2, op3]).execute();
// Assuming all three ran regardless of errors
const allData = result.results.map((r) => r.data);

Correct:

const result = await db.batch([op1, op2, op3]).execute();
if (result.truncated) {
  console.warn(`Stopped at index ${result.firstErrorIndex}`);
}
for (const r of result.results) {
  if (isBatchTruncatedError(r.error)) {
    console.log(`Op ${r.error.operationIndex} never ran`);
  }
}

FileMaker stops batch processing on first error. Subsequent operations get BatchTruncatedError with status: 0. Check result.truncated and handle each result individually.

Source: packages/fmodata/src/errors.ts (BatchTruncatedError)

[MEDIUM] Script names with special characters via OData

Wrong:

await db.runScript("My Script (v2)");

Correct:

await db.runScript("MyScript_v2");

OData script endpoint uses Script.{name} URL pattern. Spaces, parentheses, and special characters can cause URL encoding issues. Prefer alphanumeric + underscore names for scripts called via OData.

Source: packages/fmodata/src/client/database.ts (runScript)

[MEDIUM] Using defaultSelect "all" without understanding performance

Wrong:

const contacts = fmTableOccurrence("contacts", { /* 50 fields */ }, {
  defaultSelect: "all",
});
// Every query fetches all 50+ fields from FM

Correct:

const contacts = fmTableOccurrence("contacts", { /* 50 fields */ }, {
  defaultSelect: "schema",  // default -- only fetches defined fields
});
// Or override per-query:
const result = await db.from(contacts).list().select("all").execute();

defaultSelect: "all" removes $select from every query, causing FileMaker to return all non-container fields. This is slower for tables with many fields. Use "schema" (default) and override with .select("all") per-query when needed.

Source: packages/fmodata/src/client/entity-set.ts

[CRITICAL] Manually adding fields or entity IDs to schema files

Wrong:

// Agent adds a field it thinks exists in FileMaker
const contacts = fmTableOccurrence("contacts", {
  ...existingFields,
  newField: textField().entityId("FMFID:99"), // guessed ID — will silently fail
});

Correct:

# Re-run typegen to pick up new fields from FileMaker
npx @proofkit/typegen

Field definitions and entity IDs (FMFID/FMTID) must come from FileMaker metadata via @proofkit/typegen. Guessed entity IDs cause silent query failures (wrong data or empty results). You may safely add readValidator, writeValidator, defaultSelect, navigationPaths, and other options to existing fields.

Source: packages/typegen/src/fmodata/typegen.ts

[CRITICAL] Manually redefining types instead of using generated/inferred types

fmodata infers all types from fmTableOccurrence definitions. Use InferTableSchema<typeof table> if you need an explicit type alias. See typegen-fmodata skill for details.

[HIGH] Mixing Zod v3 and v4 in the same project

Use one Zod version consistently (v4 recommended). See typegen-fmodata skill for details.

References

• Filter Operators Reference
• Error Types Reference
• Cross-ref: typegen-fmodata -- generate schemas from FileMaker layouts. This is the recommended entry point for new projects.