Getting started

The NodeJS Editor libraries make it super quick for you to provide complex CRUD based web applications to end users. In order to achieve this we need to introduce the basic concepts of how the libraries operate and how you can use the interfaces that they provide to access your own databases. This page summarises how the Editor libraries operate and the concepts involved in using them.

Concept

It is important to understand the big picture of the approach the Editor NodeJS libraries take for interfacing between the database and the client-side DataTable / Editor before delving into the detail of how to actually read and write data.

The libraries provide an Editor class, an instance of which is created and will relate, primarily, to a single SQL table which it will read and write data from and to.

The Editor instance is then instructed about the database table columns that it should operate on and how. These columns are used to populate both the DataTable and Editor, and Editor has the ability to write to them. Fields from additional tables can also be included through the use of join statements.

Finally the data being sent by the client side is processed. There are four basic request types that DataTables and Editor can make:

Read - 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 four 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 to complete the processing.

Inclusion

As with other modules which have been installed using npm or yarn, the datatables.net-editor-server package can be used in your Javascript with a simple require:

let editorServer = require('datatables.net-editor-server');

This provides an object which contains properties such as Editor, Field and Validate (among others) - all of the classes that are needed to build the Editor configuration. To make life easier, so you don't need to type editorServer every time you want to create a new Editor or Field instance we can do:

let editorServer = require('datatables.net-editor-server');
let
    Editor = editorServer.Editor,
    Field = editorServer.Field;
    Validate = editorServer.Validate;
    Format = editorServer.Format;
    Options = editorServer.Options;

Alternatively, an easier method is available through the destructuring assignment feature introduced in ES6. The code above can be reduced to the following:

let {
    Editor,
    Field,
    Validate,
    Format,
    Options
} = require('datatables.net-editor-server');

Destructuring assignment is used in the Node.JS example package for Editor and will be used throughout the remainder of this documentation due to its brevity. Note that you only need to extract the class methods that you want - if you don't use the Options class for example, simply don't include it in the list of variables to extract.

ES6 modules

If you prefer using TypeScript, Babel or some other transpiler which supports ES6 modules, you can use an import such as:

import {
  Editor,
  Field,
  Validate,
  Format,
  Options
} from "datatables.net-editor-server";

Database connection

The Editor Node.JS libraries are designed to operate with an SQL database - effectively as the logic layer between the client and the database. The connection to the database is made through Knex.JS, allowing the libraries to work with a number of different database types and engines. The Knex.JS documentation does an excellent job of explaining its own connection options - you essentially just need to create a Knex connection and pass that on to the Editor Node.JS libraries to use.

Typically the database connection would be performed in a file such as db.js which can then be required in any file that needs to query the database. For example:

let knex = require('knex');

module.exports = knex({
    client: 'mysql', // pg, mssql, etc

    connection: {
        database:    '...',
        host:        '...',
        password:    '...',
        user:        '...',
        dateStrings: true
    }
});

The following discussion will assume that the Knex.JS connection will be loaded from a file called db.js, although you can of course change that to suit your needs.

Initialisation

With the Editor libraries included and a connection to the database available, we can start making using of this libraries to define our Editor instance.

Construct a Node.JS Editor instance using:

let db = require('./db');
let {
    Editor,
    // ...
} = require('datatables.net-editor-server');


let editor = new Editor( db, 'staff' );

The first parameter to the Editor constructor is the Knex.JS database connection. The second is the database table name that the Editor instance will operate on.

Primary key name

Editor requires that the database table it is set up to edit have a primary key. By default it looks for a column called id. This can be altered using the optional third parameter for the Editor constructor - for example:

let editor = new Editor( db, 'staff', 'staffId' );

Compound keys

It is sometimes useful to be able to make the primary key out of the values in two different columns - this is called a compound keys. To use a compound key, simply pass the primary key information into the third parameter for the Editor constructor as an array:

let 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

Once the Editor instance has been created and configured for what database table it should read from, it needs to be instructed what fields it should access on that table. This is done through the Editor.fields() method which is passed Field class instances. Like the Editor class these are constructed using new Field() and the parameter given is the name of the column to read / write data, and also the name that will be used to return the data to DataTables and Editor.

The fields() method can take as many field instances as you wish to define and can also be called multiple times to add additional fields.

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

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

Field instances provide validation and formatting options as well as simple get / set options. 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 Field instance constructor will also be the name that the field is given in the JSON sent to the client, and the name of the field 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 Field constructor - e.g.:

new Field( '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 Field instance can be specified with an SQL function - this is found by Editor 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.

Important Fields which make use of an SQL function can only be read. They cannot be written to. If an attempt is made to write to such a field, an error will be thrown. Use Field.set( false ) to ensure that the field is not written to.

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. Note also that the Field.set() method is used to ensure that the field is not written to:

new Field( 'timestampdiff(DAY, now(), start_date)', 'ago' )
    .set( false )

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 Editor class, with the data sent from the client being passed into it. At this point the Editor 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:

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

await editor.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:

editor.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 Editor.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:

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

// 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:

let db = require('./db');
let {
    Editor,
    // ...
} = require('datatables.net-editor-server');


router.all('/api/staff', async function(req, res) {
    let editor = new Editor( db, 'staff' );
        .fields(
            new Field( 'first_name' ),
            new Field( 'last_name' ),
            new Field( 'position' ),
            new Field( 'email' ),
            new Field( 'office' )
        );

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

And that's it! That is all that is required on the server-side, when using the Editor Node.JS libraries to add read / write ability to your database table. Additional fields can be trivially added and other tables also set up for editing.

TypeScript

It is worth noting that the Editor Node.JS libraries are written in TypeScript and transpiled to generic Javascript. As such, when you include datatables.net-editor-server in your software, if you are using TypeScript you will automatically get the benefits of type checking that TypeScript offers (and code completion if your code editor supports that). If you are not using TypeScript, you won't notice anything different!

Next steps

The next step is to add validation to your fields to ensure that the data sent is the data you expect.