Custom queries

By default, Kottster manages data fetching internally. You can also define custom fetching logic, such as using raw SQL or extracting data from an external resource.

To do this, pass a select object with an executeQuery function to createTableRpc:

export const action = app.createTableRpc(dataSource, {
  select: {
    executeQuery: async () => {
      return { records: [] };
    },
  },
});

executeQuery

Arguments:

An object with the following optional properties:

• page (number, optional): The current page number, passed if pagination is enabled.

• search (string, optional): A search term, passed if search is enabled.

Return value:

An object with the following properties:

• records (array): An array of records to display in the table.

• totalRecords (number, optional): The total number of records. Providing this enables pagination.

Example with a raw SQL query

This example demonstrates how to use a raw SQL query with Knex to fetch data.

MySQL

import { Page } from '@kottster/react';
import { Table } from '@kottster/react';
import { app } from '../.server/app';
import dataSource from '../.server/data-sources/mysql';

export const action = 
  app.createTableRpc(dataSource, {
    select: {
      executeQuery: async () => {
        const knex = dataSource.adapter.getClient();
        const [records] = await knex.raw(`
          SELECT 
            id, first_name, email 
          FROM 
            users
        `);
        
        return {
          records,
        }
      },
    },
  });

export default () => {
  return (
    <Page title='Users'>
      <Table
        columns={[
          {
            label: 'User ID',
            column: 'id',
          },
          {
            label: 'Name',
            column: 'first_name',
          },
          {
            label: 'Email',
            column: 'email',
          },
        ]}
      />
    </Page>
  );
};

PostgreSQL

import { Page } from '@kottster/react';
import { Table } from '@kottster/react';
import { app } from '../.server/app';
import dataSource from '../.server/data-sources/postgres';

export const action = 
  app.createTableRpc(dataSource, {
    select: {
      executeQuery: async () => {
        const knex = dataSource.adapter.getClient();
        const { rows } = await knex.raw(`
          SELECT 
            id, first_name, email 
          FROM 
            users
        `);
        
        return {
          records: rows,
        }
      },
    },
  });

export default () => {
  return (
    <Page title='Users'>
      <Table
        columns={[
          {
            label: 'User ID',
            column: 'id',
          },
          {
            label: 'Name',
            column: 'first_name',
          },
          {
            label: 'Email',
            column: 'email',
          },
        ]}
      />
    </Page>
  );
};

Example with pagination

This example demonstrates how to use a raw SQL query with Knex to fetch data while supporting pagination:

MySQL

import { Page } from '@kottster/react';
import { Table } from '@kottster/react';
import { app } from '../.server/app';
import dataSource from '../.server/data-sources/mysql';

const pageSize = 25;

export const action = 
  app.createTableRpc(dataSource, {
    select: {
      pageSize,
      executeQuery: async ({ page }) => {
        const knex = dataSource.adapter.getClient();
        const offset = page ? (page - 1) * pageSize : 0;
        
        const [records] = await knex.raw(`
          SELECT 
            id, first_name, email 
          FROM 
            users
          LIMIT :pageSize OFFSET :offset
        `, { pageSize, offset });
  
        const [[{ count: totalRecords }]] = await knex.raw(`
          SELECT 
            COUNT(*) AS count 
          FROM 
            users
        `);
        
        return {
          records,
          totalRecords,
        }
      },
    },
  });

export default () => {
  return (
    <Page title='Users'>
      <Table
        columns={[
          {
            label: 'User ID',
            column: 'id',
          },
          {
            label: 'Name',
            column: 'first_name',
          },
          {
            label: 'Email',
            column: 'email',
          },
        ]}
      />
    </Page>
  );
};

PostgreSQL

import { Page } from '@kottster/react';
import { Table } from '@kottster/react';
import { app } from '../.server/app';
import dataSource from '../.server/data-sources/postgres';

const pageSize = 25;

export const action = 
  app.createTableRpc(dataSource, {
    select: {
      pageSize,
      executeQuery: async ({ page }) => {
        const knex = dataSource.adapter.getClient();
        const offset = page ? (page - 1) * pageSize : 0;
        
        const { rows: records } = await knex.raw(`
          SELECT 
            id, first_name, email 
          FROM 
            users
          LIMIT :pageSize OFFSET :offset
        `, { pageSize, offset });
  
        const { rows: [{ count: totalRecords }] } = await knex.raw(`
          SELECT 
            COUNT(*) AS count 
          FROM 
            users
        `);
        
        return {
          records,
          totalRecords,
        }
      },
    },
  });

export default () => {
  return (
    <Page title='Users'>
      <Table
        columns={[
          {
            label: 'User ID',
            column: 'id',
          },
          {
            label: 'Name',
            column: 'first_name',
          },
          {
            label: 'Email',
            column: 'email',
          },
        ]}
      />
    </Page>
  );
};