DataTables logo
≡

Reading data

The DataTable class provides a read only interface to a database table, reading the data from the columns configured and returning it to the client-side for display in response to a DataTables ajax request. It also supports server-side processing for large data sets and advanced filtering options for ColumnControl and SearchBuilder.

Basic initialisation

In this documentation we'll assume a db variable is available with the Knex.js connection, per the installing documentation.

Construct a JS DataTable instance using:

import { DataTable, Column } from 'datatables.net-editor-server';

const table = new DataTable( db, tableName );

Where:

  • db is the Knex.js database connection
  • tableName is the database table name that you want to read data from

Primary key name

It can often be useful to be able to uniquely identify rows in a DataTable (e.g. for row selection, or performing actions on the data). This can be done by telling the DataTable class what your table's primary key is with the optional third parameter (by default it will assume id is the column name):

const table = new DataTable( db, 'staff', 'staffId' );

An array can also be given for the third parameter if your table has a compound key.

Columns

Once the DataTable instance has been created and configured for what database table it should read from, it needs to be instructed what columns it should read data from and send back to the client. This is done through the DataTable.columns() method which is passed Column class instances. The columns() method can take as many Column instances as you wish to define and can also be called multiple times to add additional columns.

In the example below the DataTable instance is configured with five simple columns.

const table = new DataTable( db, 'staff' )
    .columns(
        new Column( 'first_name' ),
        new Column( 'last_name' ),
        new Column( 'position' ),
        new Column( 'email' ),
        new Column( 'office' )
    );

Column instances also provide the ability to operate on columns for filtering via ColumnControl and SearchBuilder as needed. Please refer to the documentation for those sections for detailed information.

Parameter names

By default the column name given as the first parameter to the Column instance constructor will also be the name that the column is given in the JSON sent to the client, and the name of the column that the libraries will look for in the data submitted to the server from the Editor form. This name (JSON and submitted data) can be modified using an optional second parameter for the Column constructor - e.g.:

new Column( 'first_name', 'fname' )

This can be useful if you wish to obfuscate the SQL columns names so the client will never see the actual database naming, or simply want to reduce the size of the JSON object transmitted to the client.

SQL functions

While you will usually wish to just obtain data from SQL columns, as shown above, it can also be useful to use SQL functions for certain operations. The Column instance can be specified with an SQL function - this is found in libraries simply by the use of parenthesis. In this case you will likely wish to use the optional second parameter to name the data in the JSON sent to the client.

In the following example, the MySQL function timestampdiff is used (note that in other SQL dialects, functions can also be used). The method is used to calculate the difference between a field (start_date) and now, in days, then naming the field as ago in the JSON sent to the server.

new Column( 'timestampdiff(DAY, now(), start_date)', 'ago' )

Data processing

When DataTables and Editor make a request to the server to get or update data, they send the data using HTTP parameters (POST is the default for Editor). Exactly how you will be able to access this information in your Node.js application will depend upon the web-server framework you are using, but typically in Express and similar frameworks they are in request.body.

The data is processed using the process() method of the DataTable class, with the data sent from the client being passed into it. At this point the DataTable instance will perform whatever action the client-side has asked of it - be it to create a new row, update an existing row, etc. These actions will be automatically detected.

It is important to note that the process method is asynchronous! It needs to make a call to the database to perform whatever actions are required. It returns a Promise which we can await:

const table = new DataTable( db, 'staff' )
    .columns(
        new Column( 'first_name' ),
        new Column( 'last_name' ),
        new Column( 'position' ),
        new Column( 'email' ),
        new Column( 'office' )
    );

await table.process(req.body);

await (and its companion async) are ES7 features which are available in Node.js since v7.6.

If you prefer to use a Promise directly, or are working with an older version of Node.js, the following is the equivalent code:

table.process(req.body)
    .then( function () {
        // ... process complete
    } );

Return data

The final step is to send the data back to the client for it to process (e.g. redraw the table with the updated data if everything was successful or display an error message if validation failed). The data sent back to the client should be in the JSON data format. The DataTable.data() method will give you an object with the data to be sent to the client. Exactly how you return it to the client will depend upon the server / framework you are using, but typically you would use:

response.json( table.data() );

or

// Generic HTTP server
response.send( JSON.stringify( table.data() ) );

Complete Express example

Based on the above, and using an Express router, we've build the following full example:

import db from './db.js';
import {
    DataTable,
    Column
} from 'datatables.net-editor-server';


router.all('/api/staff', async function(req, res) {
    const table = new DataTable( db, 'staff' );
        .columns(
            new Column( 'first_name' ),
            new Column( 'last_name' ),
            new Column( 'position' ),
            new Column( 'email' ),
            new Column( 'office' )
        );

    await table.process(req.body);
    response.json( table.data() );
} );

And that's it! This will now read data from the database and send it to the client-side for display.