API Reference

The defineTableController function creates a server-side controller that handles requests from table pages. It connects to your database and defines what data is available to the table component and how it behaves.

This function is used in the optional api.server.js file within a page directory and should be exported as the default export.

Basic usage

Example:

app/pages/users/api.server.js

tsx

import { app } from '../../_server/app';
import page from './page.json';

const controller = app.defineTableController({
  ...page.config,
});

export default controller;

Extending page configuration

When you need customization beyond what the visual editor provides, you can add additional configuration to the page.json settings in the controller file.

Example:

app/pages/users/api.server.js

tsx

import { app } from '../../_server/app';
import page from './page.json';

const controller = app.defineTableController({
  ...page.config,
  // Add additional configuration here
});

export default controller;

For customization of nested tables:

app/pages/users/api.server.js

typescript

import { app } from '../../_server/app';
import page from './page.json';

const controller = app.defineTableController({
  ...page.config,
  // Add additional configuration for the main table

  nested: {
    ...page.config.nested,
    ordersByUserIdTable: {
      ...page.config.nested.ordersByUserIdTable,
      // Add additional configuration for the nested table
    },
  },
});

export default controller;

Usage

The defineTableController function takes two arguments:

config: A configuration object that defines the behavior of the table and its nested tables.
serverProcedures: An optional object that allows you to define custom server procedures for the page. See the Custom server API section for more details.

If you only need to change the way columns and fields are rendered, you can use the TablePage component's properties like customColumns, columnTransformer, or columnOverrides. This approach is useful for modifying the display of columns and fields without changing the backend logic.

Parameters

fetchStrategy
"databaseTable" | "rawSqlQuery" | "customFetch", required
Specifies the strategy for fetching data for the table. The available options are:
- "databaseTable": Fetches data directly from a database table.
- "rawSqlQuery": Executes a raw SQL query to fetch data.
- "customFetch": Uses a custom data fetcher function (customDataFetcher) to retrieve data.
dataSource
string, optional
Used to specify the data source for the table. This is required if you are using the databaseTable or rawSqlQuery fetch strategies. The value should match the name of a data source defined in your project.
table
string, optional
Specifies the name of the database table. This is required if you are using the databaseTable fetch strategy.
primaryKeyColumn
string, optional
Specifies the primary key column in the table. Typically, this is set to "id".

pageSize
number, optional
Specifies the number of records to display per page. If not specified, the default value is 20.
defaultSortColumn
string, optional
Specifies the default column to sort the table by. If not specified, the default value is the primary key column.
defaultSortDirection
"asc" | "desc", optional
Specifies the default sort direction. If not specified, the default value is "desc".
columns
TablePageConfigColumn[], optional
Specifies the configuration for the columns in the table.
Learn more: Columns
customDataFetcher
function, optional
Defines a custom function to retrieve data for the table. This function is required if you are using the customFetch fetch strategy.
Learn more: Custom data fetcher
allowInsert
boolean, optional
Allows users to insert new records into the table. If not specified, the default value is true.
allowedRoleIdsToInsert
string[], optional
Specifies the role IDs that are allowed to insert records into the table. If not specified, all users can insert records unless allowInsert is set to false.
validateRecordBeforeInsert
function, optional
Defines a function to validate the record values before they are inserted into the table. This function is called with the record values and should return a boolean indicating whether the values are valid.
It can also throw an error with a message to indicate why the record is invalid.
Example
typescript
```
validateRecordBeforeInsert: (values) => {
  if (!record.email.includes('@')) {
    throw new Error('Invalid email');
  }

  return true;
}
```

transformRecordBeforeInsert

function, optional

A function to transform the record values before they are inserted into the table. This function is called with the record values and should return the transformed values.

Example

typescript

transformRecordBeforeInsert: (values) => {
  const secret_token = generate_random_token();
  const created_at = new Date();
  const updated_at = new Date();

  return {
    ...values,
    secret_token,
    created_at,
    updated_at
  }
}

afterInsert
function, optional
A function executed after the record is inserted into the table. This function is often used to perform additional actions, such as sending notifications or updating related records.
Example
typescript
```
afterInsert: (primaryKey, values) => {
  console.log(`Record with ID ${primaryKey} was inserted with values:`, values);
}
```
allowUpdate
boolean, optional
Allows users to update records in the table. If not specified, the default value is true.
allowedRoleIdsToUpdate
string[], optional
Specifies the role IDs that are allowed to update records in the table. If not specified, all users can update records unless allowUpdate is set to false.
validateRecordBeforeUpdate
function, optional
Defines a function to validate the record values before they are updated in the table. This function is called with the primary key and record values, and should return a boolean indicating whether the values are valid.
It can also throw an error with a message to indicate why the record is invalid.
Example
typescript
```
validateRecordBeforeUpdate: (primaryKey, values) => {
  if (!values.email.includes('@')) {
    throw new Error('Invalid email');
  }

  return true;
}
```
transformRecordBeforeUpdate
function, optional
Defines a function to transform the record values before they are updated in the table. This function is called with the primary key and record values, and should return the transformed values.
Example
typescript
```
transformRecordBeforeUpdate: (primaryKey, values) => {
  return {
    ...values,
    updated_at: new Date()
  }
}
```
afterUpdate
function, optional
A function executed after the record is updated in the table. This function is often used to perform additional actions, such as sending notifications or updating related records.
Example
typescript
```
afterUpdate: (primaryKey, values) => {
  console.log(`Record with ID ${primaryKey} was updated with values:`, values);
}
```
allowDelete
boolean, optional
Allows users to delete records from the table. If not specified, the default value is true.
allowedRoleIdsToDelete
string[], optional
Specifies the role IDs that are allowed to delete records from the table. If not specified, all users can delete records unless allowDelete is set to false.
validateRecordBeforeDelete
function, optional
Defines a function to validate the record before it is deleted from the table. This function is called with the primary key and should return a boolean indicating whether the record can be deleted.
It can also throw an error with a message to indicate why the record cannot be deleted.
Example
typescript
```
validateRecordBeforeDelete: (primaryKey) => {
  if (primaryKey === 'admin') {
    throw new Error('Admin user cannot be deleted');
  }

  return true;
}
```
afterDelete
function, optional
A function executed after the record is deleted from the table. This function is often used to perform additional actions, such as sending notifications or deleting related records.
Example
typescript
```
afterDelete: (primaryKey) => {
  console.log(`Record with ID ${primaryKey} was deleted`);
}
```
customSqlQuery
string, optional
Specifies a custom SQL query to retrieve the records for the table. This property is required if you are using the rawSqlQuery fetch strategy.
Learn more: Raw SQL queries
customSqlCountQuery
string, optional
Specifies a custom SQL query to count the records for the table. This query should return a single numeric value.
Learn more: Raw SQL queries
knexQueryModifier
(knex: Knex.QueryBuilder) => Knex.QueryBuilder, optional
A function that modifies the Knex query before it is executed. This function is often used to add custom where clauses, joins, or other query modifications. Learn more in the Knex documentation.
Example with where clause
typescript
```
knexQueryModifier: (knex) => {
  return knex.where('is_active', true);
}
```
Example with group by
typescript
```
knexQueryModifier: (knex) => {
  return knex.groupBy('user_id');
}
```
calculatedColumns
TablePageConfigCalculatedColumn[], optional
Specifies the configuration for the calculated columns in the table.
Each calculated column should have the following properties:
- label
  string, optional
  Specifies the display name of the calculated column. If not specified, the label will be generated automatically based on the column alias.
- alias
  string, required
  Specifies the alias for the calculated column. This is used to reference the column after querying the database.
- sqlExpression
  string, required
  Specifies the SQL expression to calculate the value. This expression should return a single value.
  The SQL expression can reference the main table using the main alias.
  For example, if you want to count the number of orders for each user, you can use the following SQL expression:
  sql
```
SELECT COUNT(*) 
FROM orders 
WHERE orders.user_id = main.id AND orders.status = 'completed'
```
- position
  number, optional
  Specifies the position of the calculated column in the table. If not specified, the calculated columns will be displayed at the end of the table.
Learn more: Adding calculated columns
nested
An object that defines configurations for nested tables. Each key in this object corresponds to a nested table configuration.
Each nested table configuration has all the properties of a regular table configuration.
relationships
Specifies the configuration for the relationships the table has with other tables.
By default, Kottster detects relationships between tables based on foreign keys.
Learn more: Relationships
Each relation should have key property, which specifies the name of the relation. The key is used to access the relation in the table configuration.
Each relation object can have the following properties:
- hiddenInTable
  boolean, optional
  Specifies whether the relation should be hidden in the table. If not specified, the default value is false.
- position
  number, optional
  Specifies the position of the relation in the table. If not specified, relationships columns will be displayed in the end of the table.

Columns

Specifies the configuration for the columns in the table. Each column configuration should have column property, which specifies the name of the column in the database table.

If configuration or its properties are not specified, the default values will be used. The default values are determined automatically based on the database schema.

Parameters

Each column can have the following properties:

column
string, required
Specifies the name of the column. Could be a real column name in the database table or a custom name for a calculated column.
label
string, optional
Specifies the display name of the column. If not specified, the label will be generated automatically based on the column name.
hiddenInTable
boolean, optional
Specifies whether the column should be hidden in the table. If not specified, the default value is false.
hiddenInForm
boolean, optional
Specifies whether the column should be hidden in the form.
sortable
boolean, optional
Specifies whether the column can be sorted. If not specified, the default value is false.
searchable
boolean, optional
Specifies whether the column can be searched. If not specified, the default value is false.
filterable
boolean, optional
Specifies whether the column can be filtered. If not specified, the default value is false.
position
number, optional
Specifies the position of the column in the table. If not specified, the columns will be displayed in default order.
width
number, optional
Specifies the width of the column in pixels.
prefix
string, optional
Specifies the prefix to be added before the column value. E.g., "$" for currency columns.
suffix
string, optional
Specifies the suffix to be added after the column value. E.g., "%" for percentage columns.
fieldInput
FieldInput, optional
Specifies the field input configuration for the column.
Learn more: Field inputs
fieldRequirement
"none" | "notEmpty" | "notZero", optional
Specifies the requirements for the form field.
hiddenInForm
boolean, optional
Specifies whether the column should be hidden in the form. If not specified, the default value is false.
formFieldSpan
"12" | "8" | "6" | "4", optional
Specifies the span of the form field in the grid. The default value is 12, which means the field will take the full width of the form.
relationshipPreviewColumns
string[], optional
If the column is a foreign key and has a one-to-one relationship with another table, this property allows to specify the columns to display for linked record preview. The value is an array of column names to display. Works only for one-to-one linked relations.
Example
json
```
relationshipPreviewColumns: ["id", "email"]
```
render
(record: Record<string, any>) => any, optional
A custom function used to render content in the table cells. It receives the record data as an argument. This parameter only available on the client side.
Example
typescript
```
{
  label: 'Full name',
  column: 'full_name',
  render: (record) => `${record.firstName} ${record.lastName}`   
}
```

Field inputs

By default, form fields are automatically generated based on your database schema and defineTableController settings.

But you can also define the form input explicitly using the formInput property in the column configuration.

Example

typescript

columns: [
  {
    'column': 'description',
    'label: 'Description',
    'formInput: {
      'type': 'textarea'
    }
  },
  {
    'column': 'balance',
    'label': 'Balance',
    'prefix': '$',
    'formInput': {
      'type': 'numberInput',
      'allowDecimal': true
    }
  }
]

Field input types

The type property defines the field type and supports additional properties depending on the type.

Below is a list of supported field input types and their interfaces:

input
A single-line text input.
typescript
```
{
  type: 'input'
}
```

numberInput

A number-only input field.

typescript

{
  type: 'numberInput',
  
  /** Determines whether decimal values are allowed, true by default */
  allowDecimal?: boolean;
}

textarea
A multi-line text input.
typescript
```
{
  type: 'textarea'
}
```

select

A searchable dropdown menu.

typescript

{
  type: 'select',
  
  /** List of selectable options */
  options: { 
    /** Display label for the option */
    label: string; 
    
    /** Value associated with the option */
    value: string; 
  }[]
}

checkbox
A checkbox for boolean input.
typescript
```
{
  type: 'checkbox'
}
```

datePicker

An inline date (datetime) picker.

typescript

{
  type: 'datePicker',
  
  /** Adds time picker */
  withTime: boolean,
  
  /** Adds seconds input to time picker */
  timeWithSeconds: boolean
}

timePicker

An inline time picker.

typescript

{
  type: 'timePicker',
  
  /** Adds seconds input */
  withSeconds: boolean;
}

recordSelect
Select records from another table.
Requires a one-to-one relationship defined in the database schema or in the relationships property of the table configuration.
typescript
```
{
  type: 'recordSelect',
  
  /** The key of the relationship */
  relationshipKey?: string;
}
```

Custom field input

If you need to use a custom field input, you can define it using the type custom and the renderComponent function. This requires you to add index.jsx file in your page directory.

Learn more: Table Page Component

This function receives the following parameters:

value: The current value of the field.
values: The current values of the form.
record: The original record data including the primary key.
updateFieldValue(key: string, value: any): A function to update the field value.
meta: An object containing metadata about the field, including:
- hasError: A boolean indicating if the field has an error (e.g., validation error).
- readOnly: A boolean indicating if the field is read-only.

Example with custom textarea component

tsx

{
  type: 'custom',

  renderComponent: ({ value, updateFieldValue }) => {
    return (
      <textarea 
        value={value} 
        onChange={e => updateFieldValue('description', e.target.value)} 
      />
    );
  }
}

Custom server API

You can extend your table controller with custom server procedures to handle specific business logic that goes beyond standard table operations. These procedures can be called from the frontend using the useCallProcedure hook.

Adding custom server procedures

Example:

app/pages/users/api.server.js

tsx

import { app } from '../../_server/app';
import page from './page.json';

const controller = app.defineTableController({
  ...page.config,
}, {
  // Custom server procedures
  sendWelcomeEmail: async (data) => {
    const { userEmail } = data;
    
    // Send email logic here
    console.log(`[server] Sending welcome email to ${userEmail}`);
    
    return { success: true };
  },
});

export default controller;

Calling procedures from the frontend

Use the useCallProcedure hook to call your custom procedures from the table page:

Example:

app/pages/users/index.jsx

tsx

import { TablePage, useCallProcedure } from '@kottster/react';
import { Button } from '@mantine/core';

export default () => {
  const callProcedure = useCallProcedure();
  
  const handleSendWelcomeEmail = async (userEmail) => {
    try {
      const result = await callProcedure('sendWelcomeEmail', { userEmail });
      if (result.success) {
        console.log('Email sent successfully');
      } else {
        console.error('Failed to send email');
      }
    } catch (error) {
      console.error('Failed to send email:', error);
    }
  };
  
  return (
    <TablePage
      customActions={[
        {
          label: 'Send Welcome Email',
          onClick: (record) => {
            handleSendWelcomeEmail(record.email);
          },
        },
      ]}
    />
  );
};

To learn more about defining custom server procedures, visit the Custom API and Calling API sections.

API Reference ​

Basic usage ​

Extending page configuration ​

Usage ​

Parameters ​

fetchStrategy ​

dataSource ​

table ​

primaryKeyColumn ​

pageSize ​

defaultSortColumn ​

defaultSortDirection ​

columns ​

customDataFetcher ​

allowInsert ​

allowedRoleIdsToInsert ​

validateRecordBeforeInsert ​

transformRecordBeforeInsert ​

afterInsert ​

allowUpdate ​

allowedRoleIdsToUpdate ​

validateRecordBeforeUpdate ​

transformRecordBeforeUpdate ​

afterUpdate ​

allowDelete ​

allowedRoleIdsToDelete ​

validateRecordBeforeDelete ​

afterDelete ​

customSqlQuery ​

customSqlCountQuery ​

knexQueryModifier ​

calculatedColumns ​

label ​

alias ​

sqlExpression ​

position ​

nested ​

relationships ​

hiddenInTable ​

position ​

Columns ​

Parameters ​

column ​

label ​

hiddenInTable ​

hiddenInForm ​

sortable ​

searchable ​

filterable ​

position ​

width ​

prefix ​

suffix ​

fieldInput ​

fieldRequirement ​

hiddenInForm ​

formFieldSpan ​

relationshipPreviewColumns ​

render ​

Field inputs ​

Field input types ​

input ​

numberInput ​

textarea ​

select ​

checkbox ​

datePicker ​

timePicker ​

recordSelect ​

Custom field input ​

Custom server API ​

Adding custom server procedures ​

Calling procedures from the frontend ​

API Reference

Basic usage

Extending page configuration

Usage

Parameters

fetchStrategy

dataSource

table

primaryKeyColumn

pageSize

defaultSortColumn

defaultSortDirection

columns

customDataFetcher

allowInsert

allowedRoleIdsToInsert

validateRecordBeforeInsert

transformRecordBeforeInsert

afterInsert

allowUpdate

allowedRoleIdsToUpdate

validateRecordBeforeUpdate

transformRecordBeforeUpdate

afterUpdate

allowDelete

allowedRoleIdsToDelete

validateRecordBeforeDelete

afterDelete

customSqlQuery

customSqlCountQuery

knexQueryModifier

calculatedColumns

label

alias

sqlExpression

position

nested

relationships

hiddenInTable

position

Columns

Parameters

column

label

hiddenInTable

hiddenInForm

sortable

searchable

filterable

position

width

prefix

suffix

fieldInput

fieldRequirement

hiddenInForm

formFieldSpan

relationshipPreviewColumns

render

Field inputs

Field input types

input

numberInput

textarea

select

checkbox

datePicker

timePicker

recordSelect

Custom field input

Custom server API

Adding custom server procedures

Calling procedures from the frontend