For me, the best part about the jQuery DataTables library is how quick and easy it is to get a functioning table. Because of this I avoided using server-side tables for the longest time. I mean, why would I want to do so much heavy lifting just to display a table? My client-side tables are doing just fine...until they weren't.

One day I noticed a page timing out on one of the sites I'd been working on. Turns out the database was growing pretty fast and the row count was closing in on 10,000. From what I hear, that is the magic number for being obligated to convert your client-side table into a server-side one.

Shit.

At first it was a little tedious to set up (modifying the SQL procedure, adding a custom export function, defining columns), but the end result was fantastic. This led me to build a few more and eventually I came up with a pretty slick process for it.

First, just be aware that you need to rewrite whatever database retrieval function that you have in order to return the properties that DataTables is looking for. You should be paging your data as well.

Here is a sample of what the server-side DataTable instance will be sending to your endpoint:

{
    "draw": "1",
    "columns": [
        {
            "data": "id",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "first_name",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "last_name",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "dob",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "age",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        },
        {
            "data": "id",
            "name": "",
            "searchable": "true",
            "orderable": "true",
            "search": {
                "value": "",
                "regex": "false"
            }
        }
    ],
    "order": [
        {
            "column": "0",
            "dir": "asc"
        }
    ],
    "start": "0",
    "length": "10",
    "search": {
    "value": "",
    "regex": "false"
}

Your first job is to take this table config and use it to retrieve your data. For the sake of this example, I'm just going to pull some mock data out of a JSON file (1000 records) that looks like this:

[
    {
        id: 1,
        first_name: 'Anson',
        last_name: 'Keetley',
        dob: '07/02/1978',
        age: 41,
    },
];

Here is my 'database' pull:

const mockData = require('../../data.json');

/**
 * Acts as our stored procedure - serves filtered, sorted mock data
 * @param {Number} page - Page to return
 * @param {Number} limit - How many items per page to return
 * @param {String} orderBy=id - Which column to order by
 * @param {String} orderDir=asc - Which direction to order by
 * @param {String} search - Search string
 * @returns {Object[]} - Array of filtered, sorted data
 */
function parseMockData({
    page,
    limit,
    orderBy = 'id',
    orderDir = 'asc',
    search,
}) {
    return (
        mockData
            // filter out the search term
            .filter(item => {
                if (!search) return item;
                let match = false;
                for (const prop in item) {
                    if (
                        item[prop]
                            .toString()
                            .toLowerCase()
                            .includes(search.toLowerCase())
                    )
                        match = true;
                }
                return match;
            })
            // sort results
            .sort((a, b) => {
                if (a[orderBy] > b[orderBy]) {
                    if (orderDir === 'asc') return 1;
                    return -1;
                }
                if (a[orderBy] < b[orderBy]) {
                    if (orderDir === 'asc') return -1;
                    return 1;
                }
                return 0;
            })
            // limit the results
            .slice(0, limit || mockData.length)
    );
}

I'll also need my main endpoint where most of the magic happens (I'm using express):

router
    .route('/')

    // Send the index.html for our example
    .get((req, res, next) =>
        res.sendFile(path.join(__dirname, './dist/index.html'))
    )

    // Datatables server-side route
    .post((req, res, next) => {
        const data = parseMockData({
            page: +req.body.start / +req.body.length + 1,
            limit: +req.body.length || 10,
            orderBy: req.body.columns[+req.body.order[0].column].data,
            orderDir: req.body.order[0].dir,
            search: req.body.search.value || null,
        });

        // We need to tell DataTables a few things
        res.json({
            // The data
            data,
            // How many total records in table
            recordsTotal: mockData.length,
            // How many records are left after filtering with the search box
            recordsFiltered: data.length,
        });
    });

Now for the client-side, we're going to do something like this:

import $ from 'jquery';
import 'datatables.net-bs4';
import 'datatables.net-buttons-bs4';

const $table = $('#mytable');

$table.DataTable({
    // Sorry for the confusing DOM stuff. I'm using bootstrap4 and I love flexbox
    dom: '<"btn-container d-flex align-items-center justify-content-center justify-content-sm-between flex-wrap flex-md-nowrap pb-3"B><"d-flex align-items-center justify-content-center justify-content-sm-between flex-wrap flex-md-nowrap pt-2"lf>r<"table-responsive pb-3"t><"d-flex align-items-center justify-content-center justify-content-sm-between flex-wrap flex-md-nowrap pt-3"ip>',
    serverSide: true,
    processing: true,
    deferRender: true,
    ajax: {
        url: './',
        type: 'POST',
    },
    lengthMenu: [
        [10, 25, 50, 100, 999999],
        [10, 25, 50, 100, 'All'],
    ],
    pageLength: 10,
    order: [[0, 'asc']],
    buttons: [
        {
            text: 'Download Excel',
            action: (e, dt) => {
                // Set the file and sheet names
                const fileName = 'people';
                const sheetName = 'People';

                // Organize table data
                const order = dt.order();
                const orderDir = order[0][1];
                const orderBy = dt.init().columnDefs[order[0][0]].data;
                const search = dt.search() || null;
                const columns = JSON.stringify(dt.init().columnDefs);

                // This is used to send to the backend, where a
                // cookie will be created. We will later check that
                // cookie against this. When it exists, that means
                // our download is complete (pretty hacky)
                const token = Date.now();

                // Dynamically create and submit a form
                function nonAjaxPost(path, params, method = 'POST') {
                    const tempForm = document.createElement('form');
                    tempForm.setAttribute('method', method);
                    tempForm.setAttribute('action', path);

                    for (const key in params) {
                        if (params.hasOwnProperty(key)) {
                            const hiddenField = document.createElement('input');
                            hiddenField.setAttribute('type', 'hidden');
                            hiddenField.setAttribute('name', key);
                            hiddenField.setAttribute('value', params[key]);
                            tempForm.appendChild(hiddenField);
                        }
                    }

                    document.body.appendChild(tempForm);
                    tempForm.submit();
                    tempForm.remove();
                }

                // Get cookie by name
                function getCookie(cname) {
                    const name = cname + '=';
                    const decodedCookie = decodeURIComponent(document.cookie);
                    const ca = decodedCookie.split(';');
                    for (let i = 0; i < ca.length; i++) {
                        let c = ca[i];
                        while (c.charAt(0) === ' ') {
                            c = c.substring(1);
                        }
                        if (c.indexOf(name) === 0) {
                            return c.substring(name.length, c.length);
                        }
                    }
                    return '';
                }

                // Delete cookie by name
                function deleteCookie(name) {
                    document.cookie = `${name}=; Max-Age=-99999999;`;
                }

                // Show a spinner when the download starts
                $('#spinner').show();

                // Initialize download
                nonAjaxPost('./excel-download', {
                    search,
                    orderBy,
                    orderDir,
                    columns,
                    fileName,
                    sheetName,
                    token,
                });

                // Now we need to check for the existence of a cookie that
                // signals the end of the download. Then we can hide
                // our spinner.
                const checkIfDownloadIsComplete = setInterval(() => {
                    if (getCookie('DownloadComplete') === token.toString()) {
                        clearInterval(checkIfDownloadIsComplete);
                        $('#spinner').hide();
                        deleteCookie('DownloadComplete');
                    }
                }, 500);
            },
        },
    ],
    // Define all columns
    // 'numberFormat' and 'exportable' are custom properties that we are adding
    // in order to communicate with the backend for Excel file creation
    columnDefs: [
        {
            targets: 0,
            data: 'id',
            title: 'ID',
        },
        {
            targets: 1,
            data: 'first_name',
            title: 'First Name',
        },
        {
            targets: 2,
            data: 'last_name',
            title: 'Last Name',
        },
        {
            targets: 3,
            data: 'dob',
            title: 'Date of Birth',
            // Set the number format in Excel
            numberFormat: 'mm/dd/yyyy',
        },
        {
            targets: 4,
            data: 'age',
            title: 'Age',
            className: 'text-center',
            // Set the number format in Excel
            numberFormat: '0',
        },
        {
            targets: 5,
            data: 'id',
            title: 'Ignored Column',
            className: 'text-center',
            // This column will be ignored in the Excel download
            exportable: false,
            render() {
                return 'Ignored in Excel';
            },
        },
    ],
});

The Excel Download Code:

I wrote a piece of middleware for this because I end up using it all over the place.

const Excel = require('exceljs');

/**
 * Convert a dataset to an Excel file
 * @param {Object} req
 * @param {Object} req.body
 * @param {Object} req.body.fileName - Name of file
 * @param {Object} req.body.sheetName - Name of sheet
 * @param {Object[]} req.body.columns - Array of column definitions
 * @param {(String|Number)} [req.body.token] - Optional token to set a cookie when complete
 * @param {Object} res
 * @param {Function} next
 */
function dataToExcel(req, res, next) {
    // First we need to get the data from the previous middleware
    const sheetData = res.locals.results;

    // Create the workbook
    const workbook = new Excel.Workbook();

    // Set some metadata
    workbook.creator = 'Justin Williamson';
    workbook.lastModifiedBy = 'Justin Williamson';
    workbook.created = new Date();
    workbook.modified = new Date();
    workbook.date1904 = true;

    // (optional) - Freeze the header
    workbook.views = [
        {
            state: 'frozen',
            ySplit: 1,
        },
    ];

    // Create the worksheet
    const worksheet = workbook.addWorksheet(req.body.sheetName || 'Sheet 1');

    // Set up the columns
    const columns = JSON.parse(req.body.columns)
        .filter(column => column.exportable === undefined || column.exportable)
        .map(column => ({
            header: column.title || '',
            key: column.data || '',
            formula: column.formula || '',
            width: 20,
            style: {
                numFmt: column.numberFormat || '',
                font: {
                    name: 'Arial',
                    size: 10,
                },
            },
        }));

    worksheet.columns = columns;

    // Add the row data
    worksheet.addRows(
        sheetData.map(row =>
            columns.reduce((array, column) => {
                array.push(row[column.key]);
                return array;
            }, [])
        )
    );

    // Format the header text
    worksheet.getRow(1).font = {
        name: 'Arial Black',
        size: 10,
    };

    // Set headers for download
    const fileName = `${req.body.fileName}.xlsx`;
    res.type('application/octet-stream');
    res.set('Content-Disposition', `attachment;filename="${fileName}"`);

    // Sometimes we need to notify the client when the download is complete.
    // We do that by setting a cookie
    if (req.body.token) {
        res.set({
            'Access-Control-Allow-Credentials': true,
            'Set-Cookie': `DownloadComplete=${req.body.token}; Path=/;`,
        });
    }

    // Response
    return workbook.xlsx
        .writeBuffer()
        .then(buffer => res.send(buffer))
        .catch(next);
}

module.exports = dataToExcel;

Now we just need to set up our Excel download endpoint:

const dataToExcel = require('../middleware/data-to-excel');

router
    .route('/excel-download')

    .post((req, res, next) => {
        // Important to get the data the same way as in the previous endpoint
        const data = parseMockData({
            page: 1,
            limit: 999999,
            orderBy: req.body.orderBy,
            orderDir: req.body.orderDir,
            search: req.body.search !== 'null' ? req.body.search : null,
        });

        // MUST set the results on the res.locals object
        res.locals.results = data;
        next();
    }, dataToExcel); // Call our middleware here

Now everything should be working - our Excel download button will kick off a POST to our '/excel-download' route, where the Excel file is built. Once the download completes, a cookie will be set on the server that our front-end code will detect and hide the spinner.

If you want to test out the demo, you can download the code on github .