# ProofKit: fmodata

> FileMaker OData API client with Drizzle-like ORM

Documentation for the @proofkit/fmodata package.

---

# Batch Operations
URL: https://proofkit.dev/docs/fmodata/batch



import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";

Batch operations allow you to execute multiple queries and operations together in a single request. All operations in a batch are executed atomically - they all succeed or all fail together. This is both more efficient (fewer network round-trips) and ensures data consistency across related operations.

## Batch Result Structure

Batch operations return a `BatchResult` object that contains individual results for each operation. Each result has its own `data`, `error`, and `status` properties, allowing you to handle success and failure on a per-operation basis:

```typescript
type BatchItemResult<T> = {
  data: T | undefined;
  error: FMODataErrorType | undefined;
  status: number; // HTTP status code (0 for truncated operations)
};

type BatchResult<T extends readonly any[]> = {
  results: { [K in keyof T]: BatchItemResult<T[K]> };
  successCount: number;
  errorCount: number;
  truncated: boolean; // true if FileMaker stopped processing due to an error
  firstErrorIndex: number | null; // Index of the first operation that failed
};
```

## Basic Batch with Multiple Queries

Execute multiple read operations in a single batch:

```typescript
// Create query builders
const contactsQuery = db.from(contacts).list().top(5);
const usersQuery = db.from(users).list().top(5);

// Execute both queries in a single batch
const result = await db.batch([contactsQuery, usersQuery]).execute();

// Access individual results
const [r1, r2] = result.results;

if (r1.error) {
  console.error("Contacts query failed:", r1.error);
} else {
  console.log("Contacts:", r1.data);
}

if (r2.error) {
  console.error("Users query failed:", r2.error);
} else {
  console.log("Users:", r2.data);
}

// Check summary statistics
console.log(`Success: ${result.successCount}, Errors: ${result.errorCount}`);
```

## Mixed Operations (Reads and Writes)

Combine queries, inserts, updates, and deletes in a single batch:

```typescript
// Mix different operation types
const listQuery = db.from(contacts).list().top(10);
const insertOp = db.from(contacts).insert({
  name: "John Doe",
  email: "john@example.com",
});
const updateOp = db.from(users).update({ active: true }).byId("user-123");

// All operations execute atomically
const result = await db.batch([listQuery, insertOp, updateOp]).execute();

// Access individual results
const [r1, r2, r3] = result.results;

if (r1.error) {
  console.error("List query failed:", r1.error);
} else {
  console.log("Fetched contacts:", r1.data);
}

if (r2.error) {
  console.error("Insert failed:", r2.error);
} else {
  console.log("Inserted contact:", r2.data);
}

if (r3.error) {
  console.error("Update failed:", r3.error);
} else {
  console.log("Updated user:", r3.data);
}
```

## Handling Errors in Batches

When FileMaker encounters an error in a batch operation, it **stops processing** subsequent operations. Operations that were never executed due to an earlier error will have a `BatchTruncatedError`:

```typescript
import { BatchTruncatedError, isBatchTruncatedError } from "@proofkit/fmodata";

const result = await db.batch([query1, query2, query3]).execute();

const [r1, r2, r3] = result.results;

// First operation succeeded
if (r1.error) {
  console.error("First query failed:", r1.error);
} else {
  console.log("First query succeeded:", r1.data);
}

// Second operation failed
if (r2.error) {
  console.error("Second query failed:", r2.error);
  console.log("HTTP Status:", r2.status); // e.g., 404
}

// Third operation was never executed (truncated)
if (r3.error && isBatchTruncatedError(r3.error)) {
  console.log("Third operation was not executed");
  console.log(`Failed at operation ${r3.error.failedAtIndex}`);
  console.log(`This operation index: ${r3.error.operationIndex}`);
  console.log("Status:", r3.status); // 0 (never executed)
}

// Check if batch was truncated
if (result.truncated) {
  console.log(`Batch stopped early at index ${result.firstErrorIndex}`);
}
```

## Transactional Behavior

Batch operations are transactional for write operations (inserts, updates, deletes). If any operation in the batch fails, all write operations are rolled back:

```typescript
const result = await db
  .batch([
    db.from(users).insert({ username: "alice", email: "alice@example.com" }),
    db.from(users).insert({ username: "bob", email: "bob@example.com" }),
    db.from(users).insert({ username: "charlie", email: "invalid" }), // This fails
  ])
  .execute();

// Check individual results
const [r1, r2, r3] = result.results;

if (r1.error || r2.error || r3.error) {
  // All three inserts are rolled back - no users were created
  console.error("Batch had errors:");
  if (r1.error) console.error("Operation 1:", r1.error);
  if (r2.error) console.error("Operation 2:", r2.error);
  if (r3.error) console.error("Operation 3:", r3.error);
}
```

## Important Notes

- **FileMaker stops on first error**: When an error occurs, FileMaker stops processing subsequent operations in the batch. Truncated operations will have `BatchTruncatedError` with `status: 0`.
- **Insert operations in batches**: FileMaker ignores `Prefer: return=representation` in batch requests. Insert operations return `{}` or `{ ROWID?: number }` instead of the full created record.
- **All results are always defined**: Every operation in the batch will have a corresponding result in `result.results`, even if it was never executed (truncated operations).
- **Summary statistics**: Use `result.successCount`, `result.errorCount`, `result.truncated`, and `result.firstErrorIndex` for quick batch status checks.

<Callout type="info">
Batch operations automatically group write operations (POST, PATCH, DELETE) into changesets for transactional behavior, while read operations (GET) are executed individually within the batch.
</Callout>

---

# CLI
URL: https://proofkit.dev/docs/fmodata/cli

Run fmodata operations from the command line — queries, scripts, webhooks, metadata, and schema changes

import { Callout } from "fumadocs-ui/components/callout";
import { Tab, Tabs } from "fumadocs-ui/components/tabs";

The `@proofkit/fmodata` package ships a built-in CLI binary called **`fmodata`**. It exposes every library operation — querying records, running scripts, managing webhooks, inspecting metadata, and modifying schema — as a non-interactive command suitable for scripting, CI pipelines, and quick one-off database operations.

## Installation

The binary is included automatically when you install the package:

```bash
pnpm add @proofkit/fmodata
# or
npm install @proofkit/fmodata
```

If you want it available globally:

```bash
pnpm add -g @proofkit/fmodata
```

## Connection Configuration

All commands share the same global connection options. Each flag has an environment variable fallback so you can set credentials once and run many commands.

| Flag | Env var | Description |
|---|---|---|
| `--server <url>` | `FM_SERVER` | FileMaker Server URL (e.g. `https://fm.example.com`) |
| `--database <name>` | `FM_DATABASE` | Database filename (e.g. `MyApp.fmp12`) |
| `--username <user>` | `FM_USERNAME` | FileMaker account username |
| `--password <pass>` | `FM_PASSWORD` | FileMaker account password |
| `--api-key <key>` | `OTTO_API_KEY` | OttoFMS API key (preferred over username/password) |

<Callout type="info" title="Auth priority">
When both `--api-key` and `--username` are present, the API key is used. If an API key is present, missing `FM_PASSWORD` does not block authentication.
</Callout>

**Example — using environment variables:**

```bash
export FM_SERVER=https://fm.example.com
export FM_DATABASE=MyApp.fmp12
export OTTO_API_KEY=otto_...

fmodata metadata tables
```

**Example — passing flags directly:**

```bash
fmodata --server https://fm.example.com \
        --database MyApp.fmp12 \
        --api-key otto_... \
        metadata tables
```

## Output Formats

By default all commands print **JSON** to stdout. Add `--pretty` to render results as a human-readable ASCII table instead.

```bash
# JSON (default)
fmodata metadata tables

# ASCII table
fmodata metadata tables --pretty
```

Errors are written to **stderr** and the process exits with code `1`.

---

## Commands

### `records`

CRUD operations against any table.

#### `records list`

Fetch records from a table.

```bash
fmodata records list --table contacts
fmodata records list --table contacts --top 10 --skip 20
fmodata records list --table contacts --select "name,email"
fmodata records list --table contacts --where "name eq 'Alice'"
fmodata records list --table contacts --order-by "name:asc"
fmodata records list --table contacts --order-by "createdAt:desc,name:asc"
```

| Option | Description |
|---|---|
| `--table <name>` | **Required.** Table to query |
| `--top <n>` | Maximum records to return |
| `--skip <n>` | Records to skip (for pagination) |
| `--select <fields>` | Comma-separated field names |
| `--where <expr>` | OData `$filter` expression |
| `--order-by <field>` | `field:asc` or `field:desc`, comma-separated for multi-sort |

<Callout type="info" title="Query argument encoding">
Values passed to `--select`, `--where`, and `--order-by` are URL-encoded by the CLI before sending the request.
</Callout>

#### `records insert`

Insert a single record.

```bash
fmodata records insert --table contacts --data '{"name":"Alice","email":"alice@example.com"}'
```

| Option | Description |
|---|---|
| `--table <name>` | **Required.** Target table |
| `--data <json>` | **Required.** Record fields as a JSON object |

#### `records update`

Update records matching a filter (or all records if `--where` is omitted).

```bash
fmodata records update \
  --table contacts \
  --data '{"status":"inactive"}' \
  --where "lastLogin lt 2024-01-01"
```

| Option | Description |
|---|---|
| `--table <name>` | **Required.** Target table |
| `--data <json>` | **Required.** Fields to update as a JSON object |
| `--where <expr>` | OData `$filter` expression (omit to update all rows) |

#### `records delete`

Delete records matching a filter.

```bash
fmodata records delete --table contacts --where "status eq 'archived'"
```

| Option | Description |
|---|---|
| `--table <name>` | **Required.** Target table |
| `--where <expr>` | OData `$filter` expression |

<Callout type="warning">
Omitting `--where` from `records delete` will delete **all records** in the table.
</Callout>

---

### `script`

#### `script run`

Execute a FileMaker script and print the result code and return value.

```bash
# No parameter
fmodata script run MyScriptName

# String parameter
fmodata script run SendEmail --param '"hello@example.com"'

# JSON object parameter
fmodata script run ProcessOrder --param '{"orderId":"123","action":"approve"}'
```

| Option | Description |
|---|---|
| `--param <json>` | Script parameter — parsed as JSON, falls back to plain string |

The output is a JSON object:

```json
{
  "resultCode": 0,
  "result": "optional-return-value"
}
```

<Callout type="info">
OData does not support script names with special characters (`@`, `&`, `/`) or names beginning with a number.
</Callout>

---

### `webhook`

#### `webhook list`

List all webhooks registered on the database.

```bash
fmodata webhook list
fmodata webhook list --pretty
```

#### `webhook get`

Get details for a specific webhook by its numeric ID.

```bash
fmodata webhook get 42
```

#### `webhook add`

Register a new webhook on a table.

```bash
fmodata webhook add \
  --table contacts \
  --url https://example.com/hooks/contacts

# With field selection and custom headers
fmodata webhook add \
  --table contacts \
  --url https://example.com/hooks/contacts \
  --select "name,email,modifiedAt" \
  --header "Authorization=Bearer token123" \
  --header "X-App-ID=my-app"
```

| Option | Description |
|---|---|
| `--table <name>` | **Required.** Table to monitor |
| `--url <url>` | **Required.** Webhook endpoint URL |
| `--select <fields>` | Comma-separated field names to include in the payload |
| `--header <k=v>` | Custom request header in `key=value` format (repeatable) |

#### `webhook remove`

Delete a webhook by ID.

```bash
fmodata webhook remove 42
```

---

### `metadata`

#### `metadata get`

Retrieve OData metadata for the database.

```bash
# JSON (default)
fmodata metadata get

# XML
fmodata metadata get --format xml
```

| Option | Description |
|---|---|
| `--format <format>` | `json` (default) or `xml` |

#### `metadata tables`

List all table names in the database. This is the quickest way to inspect what's available.

```bash
fmodata metadata tables
fmodata metadata tables --pretty
```

---

### `schema`

Schema modification commands are **safe by default**: without `--confirm` they perform a **dry run** and print what _would_ happen without making any changes.

#### `schema list-tables`

List all tables (alias for `metadata tables`).

```bash
fmodata schema list-tables
```

#### `schema create-table`

Create a new table. The `--fields` option accepts the same JSON field definition used by the TypeScript API.

```bash
# Dry run (no changes)
fmodata schema create-table \
  --name NewTable \
  --fields '[{"name":"id","type":"string","primary":true},{"name":"label","type":"string"}]'

# Execute for real
fmodata schema create-table \
  --name NewTable \
  --fields '[{"name":"id","type":"string","primary":true},{"name":"label","type":"string"}]' \
  --confirm
```

| Option | Description |
|---|---|
| `--name <name>` | **Required.** New table name |
| `--fields <json>` | **Required.** Array of field definitions (see [Schema Management](/docs/fmodata/schema-management)) |
| `--confirm` | Execute the operation (without this flag it's a dry run) |

#### `schema add-fields`

Add fields to an existing table.

```bash
# Dry run
fmodata schema add-fields \
  --table contacts \
  --fields '[{"name":"phone","type":"string","nullable":true}]'

# Execute
fmodata schema add-fields \
  --table contacts \
  --fields '[{"name":"phone","type":"string","nullable":true}]' \
  --confirm
```

| Option | Description |
|---|---|
| `--table <name>` | **Required.** Existing table name |
| `--fields <json>` | **Required.** Array of field definitions |
| `--confirm` | Execute the operation (without this flag it's a dry run) |

<Callout type="info" title="Schema operations require elevated permissions">
Creating tables and adding fields require a FileMaker account with DDL (Data Definition Language) privileges. Operations will throw an error if the account lacks sufficient permissions.
</Callout>

---

## Using in CI / Scripts

Because all connection options accept environment variables, the CLI integrates cleanly into CI pipelines:

```bash
# GitHub Actions example
- name: Run post-deploy script
  env:
    FM_SERVER: ${{ secrets.FM_SERVER }}
    FM_DATABASE: ${{ secrets.FM_DATABASE }}
    OTTO_API_KEY: ${{ secrets.OTTO_API_KEY }}
  run: |
    npx fmodata script run PostDeploy --param '"${{ github.sha }}"'
```

```bash
# Quick schema check in a shell script
#!/usr/bin/env bash
set -euo pipefail

TABLES=$(fmodata metadata tables)
echo "Tables in $FM_DATABASE: $TABLES"
```

---

## Using with an AI Agent

Because `fmodata` is a standard shell tool that reads from environment variables and writes JSON to stdout, it works as a natural tool for AI coding agents (Claude Code, Claude Desktop with MCP, custom agents built on the Claude API, etc.).

### Claude Code

If your project has `@proofkit/fmodata` installed, Claude Code can run `fmodata` commands directly in the terminal during a conversation. Set your credentials in the environment first:

```bash
export FM_SERVER=https://fm.example.com
export FM_DATABASE=MyApp.fmp12
export OTTO_API_KEY=otto_...
```

Then just describe what you want in plain language:

> "List the first 5 records from the `contacts` table and show me which fields are available."

Claude will run something like:

```bash
fmodata records list --table contacts --top 5
```

…and use the JSON output to reason about the schema and answer your question.

### MCP Tool Server

You can expose `fmodata` as a set of MCP tools so any MCP-compatible host (Claude Desktop, IDEs, custom agents) can call FileMaker operations directly. Create an MCP server that shells out to the CLI:

```typescript
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { execFileSync } from "node:child_process";
import { z } from "zod";

const server = new McpServer({ name: "fmodata", version: "1.0.0" });

server.tool(
  "query_list",
  "List records from a FileMaker table",
  {
    table: z.string().describe("Table name"),
    where: z.string().optional().describe("OData $filter expression"),
    top: z.number().optional().describe("Max records to return"),
  },
  async ({ table, where, top }) => {
    const args = ["records", "list", "--table", table];
    if (where) args.push("--where", where);
    if (top) args.push("--top", String(top));

    const output = execFileSync("fmodata", args, { encoding: "utf8" });
    return { content: [{ type: "text", text: output }] };
  },
);

server.tool(
  "run_script",
  "Execute a FileMaker script",
  {
    scriptName: z.string(),
    param: z.string().optional().describe("Script parameter as JSON"),
  },
  async ({ scriptName, param }) => {
    const args = ["script", "run", scriptName];
    if (param) args.push("--param", param);

    const output = execFileSync("fmodata", args, { encoding: "utf8" });
    return { content: [{ type: "text", text: output }] };
  },
);
```

<Callout type="info" title="Why the CLI is a good MCP tool boundary">
Each `fmodata` command is atomic, stateless, and returns clean JSON — exactly the shape MCP tools expect. The agent never needs to understand OData internals; it just passes field names and filter strings as arguments.
</Callout>

### Giving an Agent Context

The more context an agent has about your database, the more useful it can be. Provide a brief schema description alongside the CLI:

```
You have access to the `fmodata` CLI connected to our production FileMaker database.

Key tables:
- contacts  (name, email, phone, status, id_company)
- companies (name, industry, arr, id_owner)
- deals     (title, stage, amount, close_date, id_contact)

Use `fmodata metadata tables` to list all tables.
Use `fmodata records list --table <name> --top 1` to inspect a table's fields.
OData filter syntax: eq, ne, lt, gt, contains(), startswith()
```

### Safety Considerations

<Callout type="warning" title="Scope what the agent can do">
Grant agents only the permissions they need:

- **Read-only tasks**: restrict to `records list`, `metadata get/tables`
- **Automation tasks**: allow `records insert/update` and `script run`
- **Schema changes**: keep `schema create-table` and `schema add-fields` behind a human approval step — the `--confirm` flag exists specifically to make this easy to enforce
</Callout>

---

# Modifying Data
URL: https://proofkit.dev/docs/fmodata/crud



import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";

## Insert

Insert new records with type-safe data:

```typescript
// Insert a new user
const result = await db
  .from(users)
  .insert({
    username: "johndoe",
    email: "john@example.com",
    active: true,
  })
  .execute();

if (result.data) {
  console.log("Created user:", result.data);
}
```

Fields are automatically required for insert if they use `.notNull()`. Read-only fields (including primary keys) are automatically excluded:

```typescript
const users = fmTableOccurrence("users", {
  id: textField().primaryKey(), // Auto-required, but excluded from insert (primaryKey)
  username: textField().notNull(), // Auto-required (notNull)
  email: textField().notNull(), // Auto-required (notNull)
  phone: textField(), // Optional by default (nullable)
  createdAt: timestampField().readOnly(), // Excluded from insert/update
});

// TypeScript enforces: username and email are required
// TypeScript excludes: id and createdAt cannot be provided
const result = await db
  .from(users)
  .insert({
    username: "johndoe",
    email: "john@example.com",
    phone: "+1234567890", // Optional
  })
  .execute();
```

## Update

Update records by ID or filter:

```typescript
// Update by ID
const result = await db
  .from(users)
  .update({ username: "newname" })
  .byId("user-123")
  .execute();

if (result.data) {
  console.log(`Updated ${result.data.updatedCount} record(s)`);
}

// Update by filter (using ORM API)
import { lt, and, eq } from "@proofkit/fmodata";

const result = await db
  .from(users)
  .update({ active: false })
  .where(lt(users.lastLogin, "2023-01-01"))
  .execute();

// Complex filter example
const result = await db
  .from(users)
  .update({ active: false })
  .where(and(eq(users.active, true), lt(users.count, 5)))
  .execute();
```

<Callout type="info">
All fields are optional for updates (except read-only fields which are automatically excluded). TypeScript will enforce that you can only update fields that aren't marked as read-only.
</Callout>

## Delete

Delete records by ID or filter:

```typescript
// Delete by ID
const result = await db.from(users).delete().byId("user-123").execute();

if (result.data) {
  console.log(`Deleted ${result.data.deletedCount} record(s)`);
}

// Delete by filter (using ORM API)
import { eq, and, lt } from "@proofkit/fmodata";

const result = await db
  .from(users)
  .delete()
  .where(eq(users.active, false))
  .execute();

// Delete with complex filters
const result = await db
  .from(users)
  .delete()
  .where(and(eq(users.active, false), lt(users.lastLogin, "2023-01-01")))
  .execute();
```

## Required and Read-Only Fields

The library automatically infers which fields are required based on field builder configuration:

- **Auto-inference:** Fields with `.notNull()` are automatically required for insert
- **Primary keys:** Fields with `.primaryKey()` are automatically read-only
- **Read-only fields:** Use `.readOnly()` to exclude fields from insert/update (e.g., timestamps, calculated fields)
- **Update flexibility:** All fields are optional for updates (except read-only fields)

```typescript
const users = fmTableOccurrence("users", {
  id: textField().primaryKey(), // Auto-required, auto-readOnly (primaryKey)
  username: textField().notNull(), // Auto-required (notNull)
  email: textField().notNull(), // Auto-required (notNull)
  status: textField(), // Optional (nullable by default)
  createdAt: timestampField().readOnly(), // Read-only system field
  updatedAt: timestampField(), // Optional (nullable)
});

// Insert: username and email are required
// Insert: id and createdAt are excluded (cannot be provided - read-only)
db.from(users).insert({
  username: "john",
  email: "john@example.com",
  status: "active", // Optional
  updatedAt: new Date().toISOString(), // Optional
});

// Update: all fields are optional except id and createdAt are excluded
db.from(users)
  .update({
    status: "active", // Optional
    // id and createdAt cannot be modified (read-only)
  })
  .byId("user-123");
```

---

# Custom Fetch Handlers
URL: https://proofkit.dev/docs/fmodata/custom-fetch-handlers

You can provide custom fetch handlers for testing or custom networking

```typescript
const customFetch = async (url, options) => {
  console.log("Fetching:", url);
  return fetch(url, options);
};

const result = await db.from("users").list().execute({
  fetchHandler: customFetch,
});
```

---

# Entity IDs vs. Field Names
URL: https://proofkit.dev/docs/fmodata/entity-ids



import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";


This library supports using FileMaker's internal field identifiers (FMFID) and table occurrence identifiers (FMTID) instead of names. This protects your integration from both field and table occurrence name changes.

## Why use Entity IDs?

There are some pros and cons of this, so it's important to understand how this works behind the scenes so you can make the best decision for your use case.

**Pros**:
- ✅ Entity IDs are a persistent internal identifier that doesn't change when a field's name does, which can make your API calls more resilient.
- ✅ Since each OData request is logged and query requests include your full search criteria, using entity IDs help obscure the logs to make it less obvious what your actual table and fields names are.

**Cons**:
- ⚠️ Harder to debug queries with obfuscated field/table names
- ⚠️ IDs are scoped to the FileMaker file and therefore won't work if you want to make the same query on a different file.
  - 📝 Note: If you are using OttoFMS to deploy files to multiple servers, entity IDs can be relied upon as long as you have strict practice of only deploying from a central development copy and never re-creating schema in each file.


## How it works

There are 2 steps to enable this feature:
1. Define the entity IDs in your schema
2. Enable `useEntityIds` in for your request. This can be set at the database, level, schema definition, or request level.

Once enabled, this feature will feel transparent to you. Behind the scenes the library will transform the names to entity IDs in your request, and the response back to the names you specify in your schema.

### Step 1: Define Entity IDs in your schema
Define your schema with entity IDs using the `.entityId()` method on field builders and the `entityId` option in `fmTableOccurrence()`.

<Callout type="info">
This step is done for you automatically when you use the `@proofkit/typegen` tool to generate your schema.

Otherwise, you can find them manually in the XML version of the `$metadata` endpoint for your database, or you can calculate them using these [custom functions](https://github.com/rwu2359/CFforID) from John Renfrew.
</Callout>

```typescript title="Example schema with entity IDs"
import {
  fmTableOccurrence,
  textField,
  timestampField,
} from "@proofkit/fmodata";

// Define a table with FileMaker field IDs and table occurrence ID
const users = fmTableOccurrence(
  "users",
  {
    id: textField().primaryKey().entityId("FMFID:12039485"),
    username: textField().notNull().entityId("FMFID:34323433"),
    email: textField().entityId("FMFID:12232424"),
    createdAt: timestampField().readOnly().entityId("FMFID:43234355"),
  },
  {
    entityId: "FMTID:12432533", // FileMaker table occurrence ID
  },
);
```

### Step 2: Enable `useEntityIds` for your request(s)
Enable `useEntityIds` in for your request. This can be set at the database, level, schema definition, or request level.

```typescript tab="Database Level"
// Enable for all requests to this database
const db = connection.database("MyDatabase", {
  useEntityIds: true,
});
```

```typescript tab="Schema Level"
// Enable for all requests to this schema
const users = fmTableOccurrence(
  "users",
  {
    id: textField().primaryKey().entityId("FMFID:12039485"),
    username: textField().notNull().entityId("FMFID:34323433"),
    email: textField().entityId("FMFID:12232424"),
    createdAt: timestampField().readOnly().entityId("FMFID:43234355"),
  },
  {
    entityId: "FMTID:12432533", // FileMaker table occurrence ID
    useEntityIds: true,
  },
);
```

```typescript tab="Request Level"
// Enable for this request only
const { data, error } = await db.from(users).list().execute({
  useEntityIds: true,
});
```

The heirarchy is Database > Schema > Request. This means that if you enable at the database level, you can turn it off at the schema level, or request level.

To help you with debugging, you can also set `useEntityIds` in the `getQueryString()` method to inspect the query string with or without entity IDs.
```typescript
const queryString = db.from(users).list().getQueryString({ useEntityIds: false });
console.log(queryString); // e.g. "/users?$select=id,username,email,createdAt"
```

---

# Error Handling
URL: https://proofkit.dev/docs/fmodata/errors



import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";

All operations return a `Result` type with either `data` or `error`. The library provides rich error types that help you handle different error scenarios appropriately.

## Basic Error Checking

```typescript
const result = await db.from(users).list().execute();

if (result.error) {
  console.error("Query failed:", result.error.message);
  return;
}

if (result.data) {
  console.log("Query succeeded:", result.data);
}
```

## HTTP Errors

Handle HTTP status codes (4xx, 5xx) with the `HTTPError` class:

```typescript
import { HTTPError, isHTTPError } from "@proofkit/fmodata";

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

if (result.error) {
  if (isHTTPError(result.error)) {
    // TypeScript knows this is HTTPError
    console.log("HTTP Status:", result.error.status);

    if (result.error.isNotFound()) {
      console.log("Resource not found");
    } else if (result.error.isUnauthorized()) {
      console.log("Authentication required");
    } else if (result.error.is5xx()) {
      console.log("Server error - try again later");
    } else if (result.error.is4xx()) {
      console.log("Client error:", result.error.statusText);
    }

    // Access the response body if available
    if (result.error.response) {
      console.log("Error details:", result.error.response);
    }
  }
}
```

## Network Errors

Handle network-level errors (timeouts, connection issues, etc.):

```typescript
import {
  TimeoutError,
  NetworkError,
  RetryLimitError,
  CircuitOpenError,
} from "@proofkit/fmodata";

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

if (result.error) {
  if (result.error instanceof TimeoutError) {
    console.log("Request timed out");
    // Show user-friendly timeout message
  } else if (result.error instanceof NetworkError) {
    console.log("Network connectivity issue");
    // Show offline message
  } else if (result.error instanceof RetryLimitError) {
    console.log("Request failed after retries");
    // Log the underlying error: result.error.cause
  } else if (result.error instanceof CircuitOpenError) {
    console.log("Service is currently unavailable");
    // Show maintenance message
  }
}
```

## Validation Errors

When schema validation fails, you get a `ValidationError` with rich context:

```typescript
import { ValidationError, isValidationError } from "@proofkit/fmodata";

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

if (result.error) {
  if (isValidationError(result.error)) {
    // Access validation issues (Standard Schema format)
    console.log("Validation failed for field:", result.error.field);
    console.log("Issues:", result.error.issues);
    console.log("Failed value:", result.error.value);
  }
}
```

### Validator-Agnostic Error Handling

The library uses [Standard Schema](https://github.com/standard-schema/standard-schema) to support any validation library (Zod, Valibot, ArkType, etc.). The `ValidationError.cause` property contains the normalized Standard Schema issues array:

```typescript
import { ValidationError } from "@proofkit/fmodata";

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

if (result.error instanceof ValidationError) {
  // The cause property (ES2022 Error.cause) contains the Standard Schema issues array
  // This is validator-agnostic and works with Zod, Valibot, ArkType, etc.
  console.log("Validation issues:", result.error.cause);
  console.log("Issues are also available directly:", result.error.issues);

  // Both point to the same array
  console.log(result.error.cause === result.error.issues); // true

  // Access additional context
  console.log("Failed field:", result.error.field);
  console.log("Failed value:", result.error.value);

  // Standard Schema issues have a normalized format
  result.error.issues.forEach((issue) => {
    console.log("Path:", issue.path);
    console.log("Message:", issue.message);
  });
}
```

## OData Errors

Handle OData-specific protocol errors:

```typescript
import { ODataError, isODataError } from "@proofkit/fmodata";

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

if (result.error) {
  if (isODataError(result.error)) {
    console.log("OData Error Code:", result.error.code);
    console.log("OData Error Message:", result.error.message);
    console.log("OData Error Details:", result.error.details);
  }
}
```

## Error Handling Patterns

### Pattern 1: Using instanceof

```typescript
import {
  HTTPError,
  ValidationError,
  TimeoutError,
  NetworkError,
} from "@proofkit/fmodata";

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

if (result.error) {
  if (result.error instanceof TimeoutError) {
    showTimeoutMessage();
  } else if (result.error instanceof HTTPError) {
    if (result.error.isNotFound()) {
      showNotFoundMessage();
    } else if (result.error.is5xx()) {
      showServerErrorMessage();
    }
  } else if (result.error instanceof ValidationError) {
    showValidationError(result.error.field, result.error.issues);
  } else if (result.error instanceof NetworkError) {
    showOfflineMessage();
  }
}
```

### Pattern 2: Using kind property (for exhaustive matching)

```typescript
const result = await db.from(users).list().execute();

if (result.error) {
  switch (result.error.kind) {
    case "TimeoutError":
      showTimeoutMessage();
      break;
    case "HTTPError":
      handleHTTPError(result.error.status);
      break;
    case "ValidationError":
      showValidationError(result.error.field, result.error.issues);
      break;
    case "NetworkError":
      showOfflineMessage();
      break;
    case "ODataError":
      handleODataError(result.error.code);
      break;
    // TypeScript ensures exhaustive matching!
  }
}
```

### Pattern 3: Using type guards

```typescript
import {
  isHTTPError,
  isValidationError,
  isODataError,
  isNetworkError,
} from "@proofkit/fmodata";

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

if (result.error) {
  if (isHTTPError(result.error)) {
    // TypeScript knows this is HTTPError
    console.log("Status:", result.error.status);
  } else if (isValidationError(result.error)) {
    // TypeScript knows this is ValidationError
    console.log("Field:", result.error.field);
    console.log("Issues:", result.error.issues);
  } else if (isODataError(result.error)) {
    // TypeScript knows this is ODataError
    console.log("Code:", result.error.code);
  } else if (isNetworkError(result.error)) {
    // TypeScript knows this is NetworkError
    console.log("Network issue:", result.error.cause);
  }
}
```

## Error Properties

All errors include helpful metadata:

```typescript
if (result.error) {
  // All errors have a timestamp
  console.log("Error occurred at:", result.error.timestamp);

  // All errors have a kind property for discriminated unions
  console.log("Error kind:", result.error.kind);

  // All errors have a message
  console.log("Error message:", result.error.message);
}
```

## Available Error Types

- **`HTTPError`** - HTTP status errors (4xx, 5xx) with helper methods (`is4xx()`, `is5xx()`, `isNotFound()`, etc.)
- **`ODataError`** - OData protocol errors with code and details
- **`ValidationError`** - Schema validation failures with issues, schema reference, and failed value
- **`ResponseStructureError`** - Malformed API responses
- **`RecordCountMismatchError`** - When `single()` or `maybeSingle()` expectations aren't met
- **`TimeoutError`** - Request timeout (from ffetch)
- **`NetworkError`** - Network connectivity issues (from ffetch)
- **`RetryLimitError`** - Request failed after retries (from ffetch)
- **`CircuitOpenError`** - Circuit breaker is open (from ffetch)
- **`AbortError`** - Request was aborted (from ffetch)
- **`BatchTruncatedError`** - Batch operation was truncated due to an earlier error

---

# Extra Properties
URL: https://proofkit.dev/docs/fmodata/extra-properties

Control which extra properties are included in the response

import { Callout } from "fumadocs-ui/components/callout";

## Include Special Columns by Default (ROWID and ROWMODID)

FileMaker provides special columns `ROWID` and `ROWMODID` that uniquely identify records and track modifications. These can be included in query responses when enabled.

Enable special columns at the database level:

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

const result = await db.from(users).list().execute();
// result.data[0] will have ROWID and ROWMODID properties
```

Override at the request level:

```typescript
// Enable for this request only
const result = await db.from(users).list().execute({
  includeSpecialColumns: true,
});

// Disable for this request
const result = await db.from(users).list().execute({
  includeSpecialColumns: false,
});
```

<Callout type="warning">
Special columns are only included when no `$select` query is applied (per OData specification). When using `.select()`, special columns are excluded even if `includeSpecialColumns` is enabled.
</Callout>




## OData Annotations

By default, the library automatically strips OData annotations fields (`@id` and `@editLink`) from responses. If you need these fields, you can include them by passing `includeODataAnnotations: true`:

```typescript
const result = await db.from("users").list().execute({
  includeODataAnnotations: true,
});
```

---

# Overview
URL: https://proofkit.dev/docs/fmodata

@proofkit/fmodata

import { Callout } from "fumadocs-ui/components/callout";
import { Card, Cards } from "fumadocs-ui/components/card";
import { CliCommand } from "@/components/CliCommand";

<Callout type="warning">
This library is in beta status. We don't expect the method names or arguments to change further, but please submit feedback or report issues on the [community forum](https://community.ottomatic.cloud/c/proofkit/13) or on [GitHub](https://github.com/proofgeist/proofkit/issues).
</Callout>

A strongly-typed FileMaker OData API client that provides full TypeScript type inference, runtime validation, and a fluent query builder API.

## Key Features

- **Type-safe queries** - Full TypeScript inference for field names and types
- **ORM-style API** - Fluent builder pattern with column references
- **Runtime validation** - Optional standard schema validation for data transformation
- **Relationship navigation** - Type-safe navigation and expansion of related records
- **Batch operations** - Execute multiple operations atomically
- **Schema management** - Create and modify tables and fields programmatically
- **Entity IDs** - Protect against field/table name changes using FileMaker IDs
- **Webhooks** - Easily manage webhooks on your FileMaker Server to monitor table changes

## Prerequisites

To use this library you need:

- OData service enabled on your FileMaker server
- A FileMaker account with `fmodata` privilege enabled
- (if using OttoFMS) a Data API key setup for your FileMaker account with OData enabled


## AI Agent Integration

If you use an AI coding agent, install ProofKit skills for better code generation after installing the package:

<CliCommand command="@tanstack/intent@latest install" exec />

## When to Use OData vs Data API

Claris has given signals that OData is the future of data access for FileMaker. It's much faster and more powerful than the Data API since it does not require the server to spin up a server-side client or maintain a session for each request. However, it's a new paradigm for thinking about how we can interact with the FileMaker server and may take some adjustment. You should not worry about continuing to use the Data API if needed, but we suggest trying OData for new projects.

<Cards>
  <Card title="Quick Start" href="/docs/fmodata/quick-start">Get up and running in minutes</Card>
  <Card title="Schema Definitions" href="/docs/fmodata/schema">Learn how to define your table schemas</Card>
  <Card title="Querying Data" href="/docs/fmodata/queries">Filter, sort, and paginate your queries</Card>
  <Card title="CRUD Operations" href="/docs/fmodata/crud">Create, read, update, and delete records</Card>
</Cards>

---

# Inspecting Query Strings
URL: https://proofkit.dev/docs/fmodata/inspecting-queries

View the full query string without executing the request

To help you with debugging, you can inspect the full query string without executing the request using the `getQueryString()` method.

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

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

---

# API Reference
URL: https://proofkit.dev/docs/fmodata/methods



import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";

Quick reference for all available methods and operators in `@proofkit/fmodata`.

## Query Methods

| Method | Description | Example |
|--------|-------------|---------|
| `list()` | Retrieve multiple records | `db.from(users).list().execute()` |
| `get(id)` | Get a single record by ID | `db.from(users).get("user-123").execute()` |
| `getSingleField(column)` | Get a single field value | `db.from(users).get("user-123").getSingleField(users.email).execute()` |
| `single()` | Ensure exactly one record | `db.from(users).list().where(eq(...)).single().execute()` |
| `maybeSingle()` | Get at most one record (returns null if none) | `db.from(users).list().where(eq(...)).maybeSingle().execute()` |

## CRUD Methods

| Method | Description | Example |
|--------|-------------|---------|
| `insert(data)` | Insert a new record | `db.from(users).insert({ username: "john" }).execute()` |
| `update(data)` | Update records | `db.from(users).update({ active: true }).byId("user-123").execute()` |
| `delete()` | Delete records | `db.from(users).delete().byId("user-123").execute()` |

## Query Modifiers

| Method | Description | Example |
|--------|-------------|---------|
| `where(filter)` | Filter records | `db.from(users).list().where(eq(users.active, true)).execute()` |
| `select(fields)` | Select specific fields | `db.from(users).list().select({ username: users.username }).execute()` |
| `orderBy(...columns)` | Sort results | `db.from(users).list().orderBy(asc(users.name)).execute()` |
| `top(n)` | Limit results | `db.from(users).list().top(10).execute()` |
| `skip(n)` | Skip records (pagination) | `db.from(users).list().top(10).skip(20).execute()` |
| `count()` | Get total count | `db.from(users).list().count().execute()` |
| `expand(table, builder?)` | Expand related records | `db.from(contacts).list().expand(users).execute()` |
| `navigate(table)` | Navigate to related table | `db.from(contacts).get("id").navigate(users).execute()` |

## Filter Operators

### Comparison Operators

| Operator | Description | Example |
|----------|-------------|---------|
| `eq(column, value)` | Equal to | `eq(users.active, true)` |
| `ne(column, value)` | Not equal to | `ne(users.role, "admin")` |
| `gt(column, value)` | Greater than | `gt(users.age, 18)` |
| `gte(column, value)` | Greater than or equal | `gte(users.score, 100)` |
| `lt(column, value)` | Less than | `lt(users.age, 65)` |
| `lte(column, value)` | Less than or equal | `lte(users.score, 0)` |

### String Operators

| Operator | Description | Example |
|----------|-------------|---------|
| `contains(column, value)` | Contains substring | `contains(users.name, "John")` |
| `startsWith(column, value)` | Starts with | `startsWith(users.email, "admin")` |
| `endsWith(column, value)` | Ends with | `endsWith(users.email, ".com")` |
| `matchesPattern(column, pattern)` | Matches regex pattern | `matchesPattern(users.name, "^A.*e$")` |

### Array Operators

| Operator | Description | Example |
|----------|-------------|---------|
| `inArray(column, values)` | Value in array | `inArray(users.role, ["admin", "moderator"])` |
| `notInArray(column, values)` | Value not in array | `notInArray(users.status, ["banned", "deleted"])` |

### Null Operators

| Operator | Description | Example |
|----------|-------------|---------|
| `isNull(column)` | Is null | `isNull(users.deletedAt)` |
| `isNotNull(column)` | Is not null | `isNotNull(users.email)` |

### Logical Operators

| Operator | Description | Example |
|----------|-------------|---------|
| `and(...filters)` | Logical AND | `and(eq(users.active, true), gt(users.age, 18))` |
| `or(...filters)` | Logical OR | `or(eq(users.role, "admin"), eq(users.role, "moderator"))` |
| `not(filter)` | Logical NOT | `not(eq(users.active, false))` |

### String Transform Functions

| Function | Description | Example |
|----------|-------------|---------|
| `tolower(column)` | Convert to lowercase | `eq(tolower(users.name), "john")` |
| `toupper(column)` | Convert to uppercase | `eq(toupper(users.name), "JOHN")` |
| `trim(column)` | Remove leading/trailing whitespace | `eq(trim(users.name), "John")` |

String transforms can be nested and used with any operator:

```typescript
// Nested transforms
eq(tolower(trim(users.name)), "john")

// With other operators
contains(tolower(users.name), "john")
startsWith(toupper(users.email), "ADMIN")
```

## Sort Helpers

| Helper | Description | Example |
|--------|-------------|---------|
| `asc(column)` | Ascending order | `orderBy(asc(users.name))` |
| `desc(column)` | Descending order | `orderBy(desc(users.age))` |

## Webhook Methods

| Method | Description | Example |
|--------|-------------|---------|
| `webhook.add(config)` | Add a webhook | `db.webhook.add({ webhook: "https://...", tableName: users })` |
| `webhook.list()` | List all webhooks | `db.webhook.list()` |
| `webhook.get(id)` | Get a webhook by ID | `db.webhook.get(1)` |
| `webhook.remove(id)` | Remove a webhook | `db.webhook.remove(1)` |
| `webhook.invoke(id, options?)` | Manually invoke a webhook | `db.webhook.invoke(1, { rowIDs: [1, 2, 3] })` |

## Schema Methods

| Method | Description | Example |
|--------|-------------|---------|
| `schema.createTable(name, fields)` | Create a new table | `db.schema.createTable("users", fields)` |
| `schema.addFields(table, fields)` | Add fields to a table | `db.schema.addFields("users", newFields)` |
| `schema.deleteTable(name)` | Delete a table | `db.schema.deleteTable("old_table")` |
| `schema.deleteField(table, field)` | Delete a field | `db.schema.deleteField("users", "old_field")` |
| `schema.createIndex(table, field)` | Create an index | `db.schema.createIndex("users", "email")` |
| `schema.deleteIndex(table, field)` | Delete an index | `db.schema.deleteIndex("users", "email")` |

## Script Methods

| Method | Description | Example |
|--------|-------------|---------|
| `runScript(name, options?)` | Execute a FileMaker script | `db.runScript("MyScript", { scriptParam: "value" })` |

## Batch Methods

| Method | Description | Example |
|--------|-------------|---------|
| `batch(operations)` | Execute multiple operations | `db.batch([query1, query2]).execute()` |

## Field Builders

| Builder | FileMaker Type | Chainable Methods |
|---------|---------------|-------------------|
| `textField()` | Text | `.primaryKey()`, `.notNull()`, `.readOnly()`, `.entityId()`, `.readValidator()`, `.writeValidator()` |
| `numberField()` | Number | Same as above |
| `dateField()` | Date | Same as above |
| `timeField()` | Time | Same as above |
| `timestampField()` | Timestamp | Same as above |
| `containerField()` | Container | Same as above |
| `calcField()` | Calculation | Same as above |

## Error Types

| Error Type | Description | Type Guard |
|------------|-------------|------------|
| `HTTPError` | HTTP status errors (4xx, 5xx) | `isHTTPError()` |
| `ODataError` | OData protocol errors | `isODataError()` |
| `ValidationError` | Schema validation failures | `isValidationError()` |
| `TimeoutError` | Request timeout | `instanceof TimeoutError` |
| `NetworkError` | Network connectivity issues | `instanceof NetworkError` |
| `RetryLimitError` | Request failed after retries | `instanceof RetryLimitError` |
| `CircuitOpenError` | Circuit breaker is open | `instanceof CircuitOpenError` |
| `BatchTruncatedError` | Batch operation truncated | `isBatchTruncatedError()` |

---

# Querying Data
URL: https://proofkit.dev/docs/fmodata/queries



import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";

## Basic Queries

Use `list()` to retrieve multiple records:

```typescript
// Get all users
const result = await db.from(users).list().execute();

if (result.data) {
  result.data.forEach((user) => {
    console.log(user.username);
  });
}
```

Get a specific record by ID:

```typescript
const result = await db.from(users).get("user-123").execute();

if (result.data) {
  console.log(result.data.username);
}
```

Get a single field value:

```typescript
const result = await db
  .from(users)
  .get("user-123")
  .getSingleField(users.email)
  .execute();

if (result.data) {
  console.log(result.data); // "user@example.com"
}
```

## Filtering

fmodata provides type-safe filter operations that prevent common errors at compile time. Use the ORM-style API with operators and column references:

```typescript
import { eq, gt, and, or, contains } from "@proofkit/fmodata";

// Simple equality
const result = await db
  .from(users)
  .list()
  .where(eq(users.active, true))
  .execute();

// Comparison operators
const result = await db.from(users).list().where(gt(users.age, 18)).execute();

// String operators
const result = await db
  .from(users)
  .list()
  .where(contains(users.name, "John"))
  .execute();

// Combine with AND
const result = await db
  .from(users)
  .list()
  .where(and(eq(users.active, true), gt(users.age, 18)))
  .execute();

// Combine with OR
const result = await db
  .from(users)
  .list()
  .where(or(eq(users.role, "admin"), eq(users.role, "moderator")))
  .execute();
```

### Available Operators

**Comparison:**
- `eq()` - Equal to
- `ne()` - Not equal to
- `gt()` - Greater than
- `gte()` - Greater than or equal to
- `lt()` - Less than
- `lte()` - Less than or equal to

**String:**
- `contains()` - Contains substring
- `startsWith()` - Starts with
- `endsWith()` - Ends with
- `matchesPattern()` - Matches regex pattern

**Array:**
- `inArray()` - Value in array
- `notInArray()` - Value not in array

**Null:**
- `isNull()` - Is null
- `isNotNull()` - Is not null

**Logical:**
- `and()` - Logical AND
- `or()` - Logical OR
- `not()` - Logical NOT

**String Transforms:**
- `tolower()` - Convert to lowercase for comparison
- `toupper()` - Convert to uppercase for comparison
- `trim()` - Remove leading/trailing whitespace

## Sorting

Sort results using `orderBy()` with column references:

```typescript
import { asc, desc } from "@proofkit/fmodata";

// Single field (ascending by default)
const result = await db.from(users).list().orderBy(users.name).execute();

// Single field with explicit direction
const result = await db.from(users).list().orderBy(asc(users.name)).execute();
const result = await db.from(users).list().orderBy(desc(users.age)).execute();

// Multiple fields (variadic)
const result = await db
  .from(users)
  .list()
  .orderBy(asc(users.lastName), desc(users.firstName))
  .execute();

// Multiple fields (array syntax)
const result = await db
  .from(users)
  .list()
  .orderBy([
    [users.lastName, "asc"],
    [users.firstName, "desc"],
  ])
  .execute();
```

## Pagination

Control the number of records returned and pagination:

```typescript
// Limit results
const result = await db.from(users).list().top(10).execute();

// Skip records (pagination)
const result = await db.from(users).list().top(10).skip(20).execute();

// Count total records
const result = await db.from(users).list().count().execute();
```

## Selecting Fields

Select specific fields to return using column references:

```typescript
// Using column references (type-safe, supports renaming)
const result = await db
  .from(users)
  .list()
  .select({
    username: users.username,
    email: users.email,
    userId: users.id, // Renamed from "id" to "userId"
  })
  .execute();

// result.data[0] will only have username, email, and userId fields
```

## Single Records

Use `single()` to ensure exactly one record is returned (returns an error if zero or multiple records are found):

```typescript
const result = await db
  .from(users)
  .list()
  .where(eq(users.email, "user@example.com"))
  .single()
  .execute();

if (result.data) {
  // result.data is a single record, not an array
  console.log(result.data.username);
}
```

Use `maybeSingle()` when you want at most one record (returns `null` if no record is found, returns an error if multiple records are found):

```typescript
const result = await db
  .from(users)
  .list()
  .where(eq(users.email, "user@example.com"))
  .maybeSingle()
  .execute();

if (result.data) {
  // result.data is a single record or null
  console.log(result.data?.username);
} else {
  // No record found - result.data would be null
  console.log("User not found");
}
```

**Difference between `single()` and `maybeSingle()`:**

- `single()` - Requires exactly one record. Returns an error if zero or multiple records are found.
- `maybeSingle()` - Allows zero or one record. Returns `null` if no record is found, returns an error only if multiple records are found.

## Chaining Methods

All query methods can be chained together:

```typescript
const result = await db
  .from(users)
  .list()
  .select({
    username: users.username,
    email: users.email,
    age: users.age,
  })
  .where(gt(users.age, 18))
  .orderBy(asc(users.username))
  .top(10)
  .skip(0)
  .execute();
```

---

# Quick Start
URL: https://proofkit.dev/docs/fmodata/quick-start



import { Steps, Step } from "fumadocs-ui/components/steps";
import {
  Tabs,
  TabsList,
  TabsTrigger,
  TabsContent,
} from "fumadocs-ui/components/tabs";
import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";
import { CliCommand } from "@/components/CliCommand";
import { PackageInstall } from "@/components/PackageInstall";
import { Badge } from "@/components/ui/badge";

Here's a minimal example to get you started with `@proofkit/fmodata`:

<Steps>
  <Step>
    ### Install the package

    <PackageInstall packages="@proofkit/fmodata" />

    #### AI Agent Integration

    If you use an AI coding agent, install ProofKit skills for better code generation:

    <CliCommand command="@tanstack/intent@latest install" exec />

  </Step>

  <Step>
    ### Create a server connection

    Create a connection to your FileMaker server using either username/password or API key authentication (requires OttoFMS 4.11+):

    ```typescript title="connection.ts" tab="Username/Password"
    import { FMServerConnection } from "@proofkit/fmodata";

    export const connection = new FMServerConnection({
      serverUrl: process.env.FM_SERVER,
      auth: {
        username: process.env.FM_USERNAME,
        password: process.env.FM_PASSWORD,
      },
    });
    ```

    ```typescript title="connection.ts" tab="API key"
    import { FMServerConnection } from "@proofkit/fmodata";

    export const connection = new FMServerConnection({
      serverUrl: process.env.FM_SERVER,
      auth: {
        apiKey: process.env.OTTO_API_KEY,
      },
    });
    ```

  </Step>

  <Step>
    ### Define your table schema

    <Tabs defaultValue="automatic">

  <TabsList>
    <TabsTrigger value="automatic">Automatically <Badge variant="success" appearance="light">Recommended</Badge></TabsTrigger>
    <TabsTrigger value="manual">Manually</TabsTrigger>
  </TabsList>
  <TabsContent value="automatic">
  Run this command in your project to launch a browser-based UI for configuring your schema definitions. You will need environment variables set for your FileMaker server and database.
  <CliCommand command="ui" exec packageName="@proofkit/typegen" />

Learn more about the [@proofkit/typegen](/docs/typegen) tool.

  </TabsContent>
<TabsContent value="manual">
 Use field builders to create type-safe table schemas:

    ```typescript title="schema.ts"
    import {
      fmTableOccurrence,
      textField,
      numberField,
    } from "@proofkit/fmodata";
    import { z } from "zod/v4";

    const users = fmTableOccurrence("users", {
      id: textField().primaryKey(),
      username: textField().notNull(),
      email: textField().notNull(),
      active: numberField()
        .readValidator(z.coerce.boolean())
        .writeValidator(z.boolean().transform((v) => (v ? 1 : 0))),
    });
    ```

</TabsContent>
</Tabs>

  </Step>

  <Step>
    ### Create a database instance and query data

    Connect to your database and start querying:

    ```typescript title="query.ts"
    import { eq } from "@proofkit/fmodata";
    import { connection } from "./connection";
    import { users } from "./schema";

    const db = connection.database(process.env.FM_DATABASE);

    // Query all users
    const { data, error } = await db.from(users).list().execute();

    if (error) {
      console.error(error);
      return;
    }

    if (data) {
      console.log(data); // Array of users, properly typed
    }

    // Filter active users
    const activeUsers = await db
      .from(users)
      .list()
      .where(eq(users.active, true))
      .execute();
    ```

  </Step>
</Steps>

---

# Related Data
URL: https://proofkit.dev/docs/fmodata/relationships

How to retrieve related data from your FileMaker database

import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";


## FileMaker Relationships vs OData Navigation

When FileMaker server parses your relationship graph for relationships, it can only create basic navigation paths between table occurences where the fields are equal to each other. Sorting via a relationship is not supported and other comparison operators (if used) will be ignored and everything will be treated as a simple equality match between the fields. For complex queries or sorting, you should use the other methods of OData.

### Navigating vs Expanding

When you navigate to a related table, are essentially changing the context of your query to the related table, but with a filtered subset of records related to the parent record(s). This is most often done if you know the primary key ID of the parent record and only need the children records.

For example the query: `/users('123')/orders` will return all the orders for the user with the ID `123`. In @proofkit/fmodata, you can do this with the `navigate()` method.

```typescript tab="Query"
const result = await db.from(users).get("123").navigate(orders).execute();
```

```jsonc tab="Result"
// data is from the orders table; no fields from the users table will be included.
{
  "data": [
    { "id": "456", "amount": 100, "date": "2021-01-01" },
    { "id": "789", "amount": 200, "date": "2021-01-02" },
  ]
}
```

When you use the `expand()` method, you are essentially adding the related records to the current query. This is most often done if you need to get the parent records along with the children records.

```typescript tab="Query"
const result = await db.from(users).get("123").expand(orders).execute();
```

```jsonc tab="Result"
// data is from the users table, along with the related orders records.
{
  "data": {
    "id": "123",
    "name": "John Doe",
    "email": "john.doe@example.com",
    "orders": [
      {
        "id": "456",
        "amount": 100,
        "date": "2021-01-01"
      },
      {
        "id": "789",
        "amount": 200,
        "date": "2021-01-02"
      }
    ]
  }
}
```

## Defining Navigation Paths

Define navigation paths using the `navigationPaths` option when creating table occurrences. The same navigation paths are used whether you use the `navigate()` or `expand()` methods.

<Callout type="info" title="Navigation Paths">
These are created for you automatically when you use the `@proofkit/typegen` tool to generate your schema files.
</Callout>

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

const contacts = fmTableOccurrence(
  "contacts",
  {
    id: textField().primaryKey(),
    name: textField().notNull(),
    userId: textField().notNull(),
  },
  {
    navigationPaths: ["users"], // Valid navigation targets
  },
);

const users = fmTableOccurrence(
  "users",
  {
    id: textField().primaryKey(),
    username: textField().notNull(),
    email: textField().notNull(),
  },
  {
    navigationPaths: ["contacts"], // Valid navigation targets
  },
);
```

The `navigationPaths` option:

- Specifies which table occurrences can be navigated to from this table
- Enables runtime validation when using `expand()` or `navigate()`
- Throws descriptive errors if you try to navigate to an invalid path

## Navigating Between Tables

Navigate to related records:

```typescript
// Navigate from a specific record (using column references)
const result = await db
  .from(contacts)
  .get("contact-123")
  .navigate(users)
  .select({
    username: users.username,
    email: users.email,
  })
  .execute();

// Navigate without specifying a record first
const result = await db.from(contacts).navigate(users).list().execute();
```

## Expanding Related Records

Use `expand()` to include related records in your query results. The library validates that the target table is in the source table's `navigationPaths`:

```typescript
// Simple expand
const result = await db.from(contacts).list().expand(users).execute();

// Expand with field selection (using column references)
const result = await db
  .from(contacts)
  .list()
  .expand(users, (b) =>
    b.select({
      username: users.username,
      email: users.email,
    }),
  )
  .execute();

// Expand with filtering (using ORM API)
import { eq } from "@proofkit/fmodata";

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

// Multiple expands
const result = await db
  .from(contacts)
  .list()
  .expand(users, (b) => b.select({ username: users.username }))
  .expand(orders, (b) => b.select({ total: orders.total }).top(5))
  .execute();
```

## Nested Expands

You can nest expands to include related records of related records:

```typescript
// Nested expands
const result = await db
  .from(contacts)
  .list()
  .expand(users, (usersBuilder) =>
    usersBuilder
      .select({
        username: users.username,
        email: users.email,
      })
      .expand(customers, (customerBuilder) =>
        customerBuilder.select({
          name: customers.name,
          tier: customers.tier,
        }),
      ),
  )
  .execute();
```

## Complex Expand Examples

Combine multiple options in a single expand:

```typescript
// Complex expand with multiple options
const result = await db
  .from(contacts)
  .list()
  .expand(users, (b) =>
    b
      .select({
        username: users.username,
        email: users.email,
      })
      .where(eq(users.active, true))
      .orderBy(asc(users.username))
      .top(10)
      .expand(customers, (nested) => nested.select({ name: customers.name })),
  )
  .execute();
```

---

# Schema Management
URL: https://proofkit.dev/docs/fmodata/schema-management



import { Callout } from "fumadocs-ui/components/callout";

## Schema Management

The library provides methods for managing database schema through the `db.schema` property. You can create and delete tables, add and remove fields, and manage indexes.

### Creating Tables

```typescript
import type { Field } from "@proofkit/fmodata";

const fields: Field[] = [
  {
    name: "id",
    type: "string",
    primary: true,
    maxLength: 36,
  },
  {
    name: "username",
    type: "string",
    nullable: false,
    unique: true,
    maxLength: 50,
  },
  {
    name: "email",
    type: "string",
    nullable: false,
    maxLength: 255,
  },
];

const tableDefinition = await db.schema.createTable("users", fields);
console.log(tableDefinition.tableName); // "users"
console.log(tableDefinition.fields); // Array of field definitions
```

### Adding Fields

```typescript
const newFields: Field[] = [
  {
    name: "phone",
    type: "string",
    nullable: true,
    maxLength: 20,
  },
];

const updatedTable = await db.schema.addFields("users", newFields);
```

### Deleting Tables and Fields

```typescript
// Delete an entire table
await db.schema.deleteTable("old_table");

// Delete a specific field
await db.schema.deleteField("users", "old_field");
```

### Managing Indexes

```typescript
// Create an index
const index = await db.schema.createIndex("users", "email");
console.log(index.indexName); // "email"

// Delete an index
await db.schema.deleteIndex("users", "email");
```

<Callout type="info">
Schema management operations require appropriate access privileges on your FileMaker account. Operations will throw errors if you don't have the necessary permissions.
</Callout>

---

# Schema Definitions
URL: https://proofkit.dev/docs/fmodata/schema



import { Callout } from "fumadocs-ui/components/callout";
import { TypeTable } from "fumadocs-ui/components/type-table";
import { Badge } from "@/components/ui/badge";

This library relies on a schema-first approach for good type-safety and optional runtime validation. Use **`fmTableOccurrence()`** with field builders to create your schemas. This provides full TypeScript type inference for field names in queries.

## Field Builders

Field builders provide a fluent API for defining table fields with type-safe metadata. These field types map directly to the FileMaker field types:

| Builder | FileMaker Type | Description |
|---------|---------------|-------------|
| `textField()` | Text | Text fields |
| `numberField()` | Number | Numeric fields |
| `dateField()` | Date | Date fields |
| `timeField()` | Time | Time fields |
| `timestampField()` | Timestamp | Date and time fields |
| `containerField()` | Container | Container fields |
| `calcField()` | Calculation | Calculated fields |

### Chainable Methods

Each field builder supports chainable methods:

| Method | Description |
|--------|-------------|
| `.primaryKey()` | Mark as primary key (automatically read-only) |
| `.notNull()` | Forces the type to not be null and requires you provided a value when inserting |
| `.readOnly()` | Exclude from insert/update operations |
| `.entityId(id)` | Assign FileMaker field ID (FMFID), allowing your API calls to survive FileMaker name changes |
| `.readValidator(validator)` | Transform/validate data when reading from database (list and get operations) |
| `.writeValidator(validator)` | Transform/validate data when writing to database (insert and update operations) |

## Defining Tables

Use `fmTableOccurrence()` to define a table with field builders:

```typescript title="schema.ts"
import { z } from "zod/v4";
import {
  fmTableOccurrence,
  textField,
  numberField,
  timestampField,
} from "@proofkit/fmodata";

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"), // Optional (nullable by default)
    createdAt: timestampField().readOnly().entityId("FMFID:5"),
  },
  {
    entityId: "FMTID:100", // Optional: FileMaker table occurrence ID
    defaultSelect: "schema", // Optional: "all", "schema", or function. Defaults to "schema".
    navigationPaths: ["users"], // Optional: valid navigation targets
  },
);
```

The function returns a table object that can be used in queries and operations. To reference fields in a table, use the column references (e.g. in select and filter operations use `contacts.id`, `contacts.name`). Or use the object direclty to reference the table (such as in from, navigate, and expand operations).

## Default Field Selection

What should happen when you don't specify a `select` in your query? You can configure this at the table definition level using the `defaultSelect` option.

### Option 1: `"schema"` <Badge variant="" appearance="light">Default</Badge>
With this default option, the library will ensure there is always a `$select` parameter when making the request to FileMaker. The `$select` parameter will only include the fields that are defined in the schema, so you'll never get back any extra fields that are not defined in your schema.

### Option 2: `"all"`
This option turns off the library behavior and will revert to FileMaker's default behavior. All non-container fields will be returned at runtime, but you will only get type information for the fields that are defined in the schema.
```typescript
const users = fmTableOccurrence(
  "users",
  {
    /* fields */
  },
  {
    defaultSelect: "all", 
  },
);

```

### Option 3: Custom Select
You can also provide a function that returns a custom select object. This function will be called with the table object and should return a custom select object.

```typescript
const users = fmTableOccurrence(
  "users",
  {
    /* fields */
  },
  {
    defaultSelect: (cols) => ({
      username: cols.username,
      email: cols.email,
    }), // Only select these fields by default
  },
);
```

When you call `list()` or `get()`, the `defaultSelect` is applied automatically. You can still override with explicit `select()`:

```typescript
const result = await db
  .from(users)
  .list()
  .select({ username: users.username, email: users.email, age: users.age }) // Always overrides at the per-request level
  .execute();
```

## Read/Write Validators

You can use Standard Schema validators to transform and validate data when reading from or writing to the database. We use Zod in our examples, but any other validation library that supports Standard Schema (Zod, Valibot, ArkType, etc.) will also work.

```typescript
import { z } from "zod/v4";

const users = fmTableOccurrence("users", {
  id: textField().primaryKey(),
  username: textField().notNull(),
  email: textField().notNull(),
  active: numberField()
    .readValidator(z.coerce.boolean()) // Convert 1/0 to true/false when reading
    .writeValidator(z.boolean().transform((v) => (v ? 1 : 0))), // Convert true/false to 1/0 when writing
});
```

<Callout type="info">
The validators must transform to/from the FileMaker data type. In the example above, the write validator would have thrown a type error if the result of the `transform` was a string.
</Callout>

## Required and Read-Only Fields

The library automatically infers which fields are required based on field builder configuration:

- Fields with `.notNull()` are automatically required for insert
- Fields with `.readOnly()` are excluded from insert/update operations
- Fields with `.primaryKey()` are automatically read-only

```typescript
const users = fmTableOccurrence("users", {
  id: textField().primaryKey(), // Auto-required, but excluded from insert (primaryKey)
  username: textField().notNull(), // Auto-required (notNull)
  email: textField().notNull(), // Auto-required (notNull)
  phone: textField(), // Optional by default (nullable)
  createdAt: timestampField().readOnly(), // Excluded from insert/update
});

// TypeScript enforces: username and email are required
// TypeScript excludes: id and createdAt cannot be provided
const result = await db.from(users).insert({
  username: "johndoe",
  email: "john@example.com",
  phone: "+1234567890", // Optional
});
```

---

# Running Scripts
URL: https://proofkit.dev/docs/fmodata/scripts

Execute FileMaker scripts via OData

import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";


## Simple Script Execution

```typescript
// Simple script execution
const result = await db.runScript("MyScriptName");

console.log(result.resultCode); // Script result code
console.log(result.result); // Optional script result string
```


<Callout type="warning">
OData doesn't support script names with special characters (e.g., `@`, `&`, `/`) or script names beginning with a number.
</Callout>

## Passing Parameters

Pass parameters to scripts:

```typescript
// Pass parameters to script
const result = await db.runScript("MyScriptName", {
  scriptParam: "some value",
});

// Script parameters can be strings, numbers, or objects
const result = await db.runScript("ProcessOrder", {
  scriptParam: {
    orderId: "12345",
    action: "approve",
  },
});
```

## Validating Script Results

Validate script result with a Standard Schema.

```typescript
import { z } from "zod/v4";

// NOTE: Your validator must be able to parse a string.
// See Zod codecs for how to build a jsonCodec function that does this
// https://zod.dev/codecs?id=jsonschema

const schema = jsonCodec(
  z.object({
    success: z.boolean(),
    message: z.string(),
    recordId: z.string(),
  }),
);

const result = await db.runScript("CreateRecord", {
  resultSchema: schema,
});

// result.result is now typed based on your schema
// An error will be thrown if the validator fails
console.log(result.result.recordId);
```

<Callout type="info" title="Script results will always be a string">
In the example above, we use a [Zod codec](https://zod.dev/codecs?id=jsonschema) helper function to parse the result into a JSON object before validating.
</Callout>

---

# Webhooks
URL: https://proofkit.dev/docs/fmodata/webhooks

Tell your FileMaker Server to call a URL when data or schema changes

import { Callout } from "fumadocs-ui/components/callout";
import { Card } from "fumadocs-ui/components/card";

<Callout type="info" title="Minimum Required Version">
  Webhooks require FileMaker Server 22.0.4 or newer.
</Callout>


Webhooks allow you to receive notifications when data changes in your FileMaker database. The library provides a type-safe API for managing webhooks through the `db.webhook` property.

## Adding a Webhook

Create a new webhook to monitor a table for changes:

```typescript
// Basic webhook
const result = await db.webhook.add({
  webhook: "https://example.com/webhook",
  tableName: contactsTable,
});

// Access the created webhook ID
console.log(result.webhookResult.webhookID);
```


```typescript
// With custom headers
const result = await db.webhook.add({
  webhook: "https://example.com/webhook",
  tableName: contactsTable,
  headers: {
    "X-Custom-Header": "value",
    Authorization: "Bearer token",
  },
  notifySchemaChanges: true, // Notify when schema changes
});

// With field selection (using column references)
const result = await db.webhook.add({
  webhook: "https://example.com/webhook",
  tableName: contacts,
  select: [contacts.name, contacts.email, contacts.PrimaryKey],
});

// With filtering (using filter expressions)
import { eq, gt } from "@proofkit/fmodata";

const result = await db.webhook.add({
  webhook: "https://example.com/webhook",
  tableName: contacts,
  filter: eq(contacts.active, true),
  select: [contacts.name, contacts.email],
});

// Complex filter example
const result = await db.webhook.add({
  webhook: "https://example.com/webhook",
  tableName: users,
  filter: and(eq(users.active, true), gt(users.age, 18)),
  select: [users.username, users.email],
});
```

**Webhook Configuration Properties:**

- `webhook` (required) - The URL to call when the webhook is triggered
- `tableName` (required) - The `FMTable` instance for the table to monitor
- `headers` (optional) - Custom headers to include in webhook requests
- `notifySchemaChanges` (optional) - Whether to notify on schema changes
- `select` (optional) - Field selection as a string or array of `Column` references
- `filter` (optional) - Filter expression (string or `FilterExpression`) to limit which records trigger the webhook

## Listing Webhooks

Get all webhooks configured for the database:

```typescript
const result = await db.webhook.list();

console.log(result.status); // Status of the operation
console.log(result.webhooks); // Array of webhook configurations

result.webhooks.forEach((webhook) => {
  console.log(`Webhook ${webhook.webhookID}:`);
  console.log(`  Table: ${webhook.tableName}`);
  console.log(`  URL: ${webhook.webhook}`);
  console.log(`  Notify Schema Changes: ${webhook.notifySchemaChanges}`);
  console.log(`  Select: ${webhook.select}`);
  console.log(`  Filter: ${webhook.filter}`);
  console.log(`  Pending Operations: ${webhook.pendingOperations.length}`);
});
```

## Getting a Webhook

Retrieve a specific webhook by ID:

```typescript
const webhook = await db.webhook.get(1);

console.log(webhook.webhookID);
console.log(webhook.tableName);
console.log(webhook.webhook);
console.log(webhook.headers);
console.log(webhook.notifySchemaChanges);
console.log(webhook.select);
console.log(webhook.filter);
console.log(webhook.pendingOperations);
```

## Removing a Webhook

Delete a webhook by ID:

```typescript
await db.webhook.remove(1);
```

## Invoking a Webhook

Manually trigger a webhook. This is useful for testing or triggering webhooks on-demand:

```typescript
// Invoke for all rows matching the webhook's filter
await db.webhook.invoke(1);

// Invoke for specific row IDs
await db.webhook.invoke(1, { rowIDs: [63, 61] });
```

## Complete Example

Here's a complete example of setting up and managing webhooks:

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

// Add a webhook to monitor active contacts
const addResult = await db.webhook.add({
  webhook: "https://api.example.com/webhooks/contacts",
  tableName: contacts,
  headers: {
    "X-API-Key": "your-api-key",
  },
  filter: eq(contacts.active, true),
  select: [contacts.name, contacts.email, contacts.PrimaryKey],
  notifySchemaChanges: false,
});

const webhookId = addResult.webhookResult.webhookID;
console.log(`Created webhook with ID: ${webhookId}`);

// List all webhooks
const listResult = await db.webhook.list();
console.log(`Total webhooks: ${listResult.webhooks.length}`);

// Get the webhook we just created
const webhook = await db.webhook.get(webhookId);
console.log(`Webhook URL: ${webhook.webhook}`);

// Manually invoke the webhook for specific records
await db.webhook.invoke(webhookId, { rowIDs: [1, 2, 3] });

// Remove the webhook when done
await db.webhook.remove(webhookId);
```

<Callout type="info">
Webhooks are triggered automatically by FileMaker when records matching the webhook's filter are created, updated, or deleted. The `invoke()` method allows you to manually trigger webhooks for testing or on-demand processing.
</Callout>