fmodata-client

name	fmodata-client
description	fmodata OData FMServerConnection fmTableOccurrence field builders textField numberField dateField timestampField containerField calcField listField query builder execute() filter operators eq ne gt gte lt lte contains startsWith endsWith matchesPattern inArray notInArray isNull isNotNull and or not tolower toupper trim CRUD insert update delete byId where navigate expand relationships batch Result error handling Effect.ts pattern FMODataError HTTPError ODataError ValidationError BatchTruncatedError entity IDs FMTID FMFID defaultSelect readValidator writeValidator orderBy asc desc top skip single maybeSingle count getSingleField FileMaker OData API schema management webhooks getTableColumns select("all")
metadata	{"type":"core","library":"proofkit","library_version":"0.1.1"}
requires	["typegen-fmodata"]
sources	["proofsh/proofkit:packages/fmodata/src/**/*.ts","proofsh/proofkit:apps/docs/content/docs/fmodata/*.mdx"]

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: "[e-mail]", 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, "[e-mail]"))
  .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: "[e-mail]", active: true })
  .execute();

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

// Update by ROWID
const updatedByRowId = await db
  .from(contacts)
  .update({ phone: "[phone]" })
  .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: "[e-mail]" }),
    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.