Editing

The Editor class is one of the main access points for processing requests from the server-side. It can handle all requests for reading and writing data to a database for a DataTable. It is very similar to the DataTable class, providing a very similar interface style. The documentation on this page will partly refer to the DataTable documentation due to the overlap!

It is important to note that the Editor class provides all of the same features as the DataTable class for reading data (i.e. you do not need to use both for the same table!), but also adds support for writing data. There are four basic request types that DataTables and Editor can make:

• Fetch - Get the data to display
• Create - Create a new row of data
• Edit - Update an existing row with changed data
• Remove - Delete one or more rows from the database

The Editor class will automatically detect which of these requests are being made and handle it correctly for you without adjustment. The data resulting from the request is then sent back to the client for it to complete the processing.

Basic initialisation

As with the DataTable documentation here we'll assume a db variable is available with the Node.js connection.

Construct a Node.js Editor instance using:

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

const editor = new Editor( db, table, pkey="id" );

Where:

• db is an instance of the Database class, which connects to the database
• table is the table name to operate on.
• pkey is an optional parameter that specifies the primary key of the table - the default is id. This option can also be given as a string array to an overloaded constructor (see below).

Compound keys

As of v1.6 the Node.js libraries support compound keys: a key which is made up from the data in two or more table columns, rather than just a single column as used in many tables. To use a compound key, simply pass the primary key information into the third parameter for the Editor constructor as an array:

const editor = new Editor(db, 'visitors', ['visitor_id', 'visit_date']);

No special configuration is required on the client-side to support compound keys, however, when creating new rows you must submit the data for the columns that make up the compound key (an error will be shown otherwise). Editor cannot currently read information that is generated by the database. If you need to set a server-side computed value (e.g. current time), use the Field->setValue() method to set the value. This limitation is due to the database engines that Editor currently supports.

Fields

Fields for the Editor instance are defined using the Field class which has two overloads:

new Field( dbColumn );
new Field( dbColumn, httpName );

Where:

• dbColumn is the name of the column that data will used for read / write
• httpName is the name that will be used for the JSON response to the client-side and looked for in HTTP requests related to the column. This parameter is optional and if omitted the column name will be used.

In the example below an Editor instance is configured with five simple fields:

const editor = new Editor( db, 'staff' )
    .fields(
        new Field( 'first_name' ),
        new Field( 'last_name' ),
        new Field( 'position' ),
        new Field( 'email' ),
        new Field( 'office' )
    );

The column name and SQL function ability described in the DataTable documentation also applied to the Field class, although note that if a field uses an SQL function for reading data, you must use .set(false) to disable writing to that field.

Data processing

As with the DataTable class, to pass the data submitting by the client-side to the Editor instance, there is a .process() method, that will take the data and perform the actions required on it, based on the configuration. This is typically POST data:

await editor.process(req.body);

Return data

Once the data has been processed and whatever action(s) required have been handled, the resulting data needs to be sent back to the client-side. This data is retrieved with the .data() method of the Editor class. Exactly how you return it to the client will depend upon the server / framework you are using, but typically you would use:

// Express
response.json( editor.data() );

or

// Generic HTTP server
response.send( JSON.stringify( editor.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 editor.process(req.body);
    response.json( editor.data() );
} );

And with that you've got a controller that can handle create, insert, update and delete operations!