> For the complete documentation index, see [llms.txt](https://jacksonkasi.gitbook.io/tablecraft/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://jacksonkasi.gitbook.io/tablecraft/core-concepts/5-extending.md).

# Extending

The TableCraft Engine is designed to handle 90% of your API needs (CRUD, filtering, sorting, joins). However, for complex analytics, CTEs, window functions, or highly specific database optimizations, you might need to drop down to raw Drizzle or SQL.

You don't have to lose the benefits of TableCraft (pagination, consistent response format, export logic) just because you write a manual query.

## 1. Using `manualResult`

The `manualResult` utility allows you to write *any* Drizzle query you want, and then format the output exactly like a standard Engine response. This ensures your API remains consistent across the entire application.

### Example: Complex Analytics Report

Suppose you need a report with `GROUP BY`, `HAVING`, and a subquery—features that might be cumbersome or impossible to configure purely via `defineTable`.

{% stepper %}
{% step %}

### Define Formatting Config

Define a config just for formatting (labels, hidden fields). This doesn't control the query, just the output shape.

```typescript
// src/config/reports.ts
import { defineTable } from '@tablecraft/engine';
import { orders } from '@/db/schema';

export const reportConfig = defineTable(orders)
  .label('totalRevenue', 'Revenue')
  .toConfig();
```

{% endstep %}

{% step %}

### Write the Route Handler

Use full control with raw Drizzle/SQL features.

```typescript
// src/routes/analytics.ts
import { db } from '@/db';
import { orders } from '@/db/schema';
import { sql, desc, count, sum } from 'drizzle-orm';
import { manualResult } from '@tablecraft/engine';
import { reportConfig } from '../config/reports';

export async function GET(request: Request) {
  const page = 1;
  const pageSize = 25;

  // 1. Your Complex Query (Raw Drizzle)
  const data = await db
    .select({
      month: sql<string>`DATE_TRUNC('month', ${orders.createdAt})`,
      totalRevenue: sum(orders.total),
      orderCount: count(orders.id),
    })
    .from(orders)
    .groupBy(sql`DATE_TRUNC('month', ${orders.createdAt})`)
    .orderBy(desc(sql`DATE_TRUNC('month', ${orders.createdAt})`))
    .limit(pageSize)
    .offset((page - 1) * pageSize);

  // 2. Count Total (for pagination metadata)
  const [{ total }] = await db
    .select({ total: count() })
    .from(orders);

  // 3. Format Response
  // Wraps data + meta into the standard { data, meta } shape.
  return Response.json(
    manualResult(data, total, reportConfig, { page, pageSize })
  );
}
```

{% endstep %}
{% endstepper %}

{% hint style="success" %}
**The Result:** The API response will look standard, identical to auto-generated endpoints.

```json
{
  "data": [
    { "month": "2023-10-01", "totalRevenue": 5000, "orderCount": 10 }
  ],
  "meta": {
    "total": 100,
    "page": 1,
    "pageSize": 25,
    "totalPages": 4
  }
}
```

{% endhint %}

## 2. Manual Exports

If you need to export data from a raw query (e.g., to CSV), use `manualExport`. This applies the same transforms and column labels defined in your config.

```typescript
import { manualExport } from '@tablecraft/engine';

// ... (fetch data as above, usually without limit/offset for exports) ...

const csv = manualExport(data, 'csv', reportConfig);
// Returns string: "Month,Revenue,Order Count\n2023-10-01,5000,10..."
```

## 3. Hybrid Approach: Wrapping the Engine

Sometimes you don't need a full manual query, but you want to inject custom logic *before* or *after* the standard engine execution. You can wrap the engine instance.

```typescript
import { createTableEngine } from '@tablecraft/engine';

const engine = createTableEngine({ db, config });

// Custom wrapper
const customEngine = {
  ...engine,
  async query(params, ctx) {
    // 1. Modify Params (e.g. enforce a filter)
    const newParams = {
      ...params,
      filters: { ...params.filters, status: 'active' }
    };

    // 2. Run Standard Engine
    const result = await engine.query(newParams, ctx);

    // 3. Modify Result (e.g. add a calculated field in JS)
    result.data = result.data.map(row => ({
      ...row,
      flag: row.value > 100 ? 'high' : 'low'
    }));

    return result;
  }
};
```

This gives you the flexibility to use the engine for 90% of the work and custom code for the remaining 10%.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://jacksonkasi.gitbook.io/tablecraft/core-concepts/5-extending.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.