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:
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:
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:
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"
, requiredSpecifies 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
, optionalUsed to specify the data source for the table. This is required if you are using the
databaseTable
orrawSqlQuery
fetch strategies. The value should match the name of a data source defined in your project.table
string
, optionalSpecifies the name of the database table. This is required if you are using the
databaseTable
fetch strategy.primaryKeyColumn
string
, optionalSpecifies the primary key column in the table. Typically, this is set to
"id"
.
pageSize
number
, optionalSpecifies the number of records to display per page. If not specified, the default value is
20
.defaultSortColumn
string
, optionalSpecifies the default column to sort the table by. If not specified, the default value is the primary key column.
defaultSortDirection
"asc" | "desc"
, optionalSpecifies the default sort direction. If not specified, the default value is
"desc"
.columns
TablePageConfigColumn[]
, optionalSpecifies the configuration for the columns in the table.
Learn more: Columns
customDataFetcher
function
, optionalDefines 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
, optionalAllows users to insert new records into the table. If not specified, the default value is
true
.allowedRoleIdsToInsert
string[]
, optionalSpecifies the role IDs that are allowed to insert records into the table. If not specified, all users can insert records unless
allowInsert
is set tofalse
.validateRecordBeforeInsert
function
, optionalDefines 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.
typescriptvalidateRecordBeforeInsert: (values) => { if (!record.email.includes('@')) { throw new Error('Invalid email'); } return true; }
transformRecordBeforeInsert
function
, optionalA 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.
typescripttransformRecordBeforeInsert: (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
, optionalA 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.
typescriptafterInsert: (primaryKey, values) => { console.log(`Record with ID ${primaryKey} was inserted with values:`, values); }
allowUpdate
boolean
, optionalAllows users to update records in the table. If not specified, the default value is
true
.allowedRoleIdsToUpdate
string[]
, optionalSpecifies the role IDs that are allowed to update records in the table. If not specified, all users can update records unless
allowUpdate
is set tofalse
.validateRecordBeforeUpdate
function
, optionalDefines 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.
typescriptvalidateRecordBeforeUpdate: (primaryKey, values) => { if (!values.email.includes('@')) { throw new Error('Invalid email'); } return true; }
transformRecordBeforeUpdate
function
, optionalDefines 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.
typescripttransformRecordBeforeUpdate: (primaryKey, values) => { return { ...values, updated_at: new Date() } }
afterUpdate
function
, optionalA 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.
typescriptafterUpdate: (primaryKey, values) => { console.log(`Record with ID ${primaryKey} was updated with values:`, values); }
allowDelete
boolean
, optionalAllows users to delete records from the table. If not specified, the default value is
true
.allowedRoleIdsToDelete
string[]
, optionalSpecifies the role IDs that are allowed to delete records from the table. If not specified, all users can delete records unless
allowDelete
is set tofalse
.validateRecordBeforeDelete
function
, optionalDefines 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.
typescriptvalidateRecordBeforeDelete: (primaryKey) => { if (primaryKey === 'admin') { throw new Error('Admin user cannot be deleted'); } return true; }
afterDelete
function
, optionalA 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.
typescriptafterDelete: (primaryKey) => { console.log(`Record with ID ${primaryKey} was deleted`); }
customSqlQuery
string
, optionalSpecifies 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
, optionalSpecifies 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
, optionalA 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.
typescriptknexQueryModifier: (knex) => { return knex.where('is_active', true); }
typescriptknexQueryModifier: (knex) => { return knex.groupBy('user_id'); }
calculatedColumns
TablePageConfigCalculatedColumn[]
, optionalSpecifies the configuration for the calculated columns in the table.
Each calculated column should have the following properties:
label
string
, optionalSpecifies the display name of the calculated column. If not specified, the label will be generated automatically based on the column alias.
alias
string
, requiredSpecifies the alias for the calculated column. This is used to reference the column after querying the database.
sqlExpression
string
, requiredSpecifies 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:
sqlSELECT COUNT(*) FROM orders WHERE orders.user_id = main.id AND orders.status = 'completed'
position
number
, optionalSpecifies 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
, optionalSpecifies whether the relation should be hidden in the table. If not specified, the default value is
false
.position
number
, optionalSpecifies 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
, requiredSpecifies 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
, optionalSpecifies the display name of the column. If not specified, the label will be generated automatically based on the column name.
hiddenInTable
boolean
, optionalSpecifies whether the column should be hidden in the table. If not specified, the default value is
false
.hiddenInForm
boolean
, optionalSpecifies whether the column should be hidden in the form.
sortable
boolean
, optionalSpecifies whether the column can be sorted. If not specified, the default value is
false
.searchable
boolean
, optionalSpecifies whether the column can be searched. If not specified, the default value is
false
.filterable
boolean
, optionalSpecifies whether the column can be filtered. If not specified, the default value is
false
.position
number
, optionalSpecifies the position of the column in the table. If not specified, the columns will be displayed in default order.
width
number
, optionalSpecifies the width of the column in pixels.
prefix
string
, optionalSpecifies the prefix to be added before the column value. E.g.,
"$"
for currency columns.suffix
string
, optionalSpecifies the suffix to be added after the column value. E.g.,
"%"
for percentage columns.fieldInput
FieldInput
, optionalSpecifies the field input configuration for the column.
Learn more: Field inputs
fieldRequirement
"none" | "notEmpty" | "notZero"
, optionalSpecifies the requirements for the form field.
hiddenInForm
boolean
, optionalSpecifies whether the column should be hidden in the form. If not specified, the default value is
false
.formFieldSpan
"12" | "8" | "6" | "4"
, optionalSpecifies 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[]
, optionalIf 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.
jsonrelationshipPreviewColumns: ["id", "email"]
render
(record: Record<string, any>) => any
, optionalA 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.
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.
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.
{
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:
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:
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.