Using DataTables with CouchDB

DataTables is an Open Source jQuery plugin that adds controls to any HTML table. Usually DataTable takes an existing HTML table and adds functionality to it. It can also pull data from a remote JSON and create table on the fly. It's a useful feature. Although for big datasets it doesn't work as whole dataset gets pulled at once. For such datasets DataTable supports server-side processing. Which does pagination, sorting etc on server side and just displays the data on the front end. Since I store a lot of data on CouchDB, I was wondering if server-side support of DataTable can be used to pull data from CouchDB using CouchDB HTTP APIs. With some hacking around I was able to achieve it.

dataTable uses jQuery ajax for making calls to back-end. Here I am using _all_docs API to query and hence the "dataSrc" is rows. Code below uses Cloudant as CouchDB. Make sure your database is public readable or has a read API credentials that can be set into the header in "beforeSend". dataTable expects returned values in certain format hence I have modified the return in "dataFilter". Also dataTable sends the query data that is not compatible with CouchDB. I have added "data" attribute to modify the request parameters before its sent to CouchDB.

oTable =  $('#example').dataTable( {
            "bSort" : false,  //sorting disabled
            "searching": true,
            "processing": true,
            "serverSide": true,
            "ajax": {
                      "url": "https://username.cloudant.com/my_couchdb_database/_all_docs?include_docs=true",
                      "dataSrc": "rows",
                      "data": function ( d ) {
                              d.limit = d.length;
                              d.skip =d.start;
                              if(d.search && d.search["value"] && d.search["value"] != "" ){
                                    d.key='"'+d.search["value"]+'"';     
                                    delete d.search["value"];
                                    delete d.search["regex"];          
                                }
                              console.log(d);
                        },
                      "beforeSend": function (xhr) {
                          xhr.setRequestHeader ("Authorization", "Basic " + btoa(cred)); // cred = "username:password" only if the API is not public read
                        },

                      "dataFilter": function(data) {
                          console.log(data);
                          var data = JSON.parse(data);
                          data['recordsTotal']= data["total_rows"];
                          data['recordsFiltered']= data["total_rows"];
                          return JSON.stringify(data);
                        }
              }, //END AJAX
              "columns":[    //columns to display
                  {"data":"doc._id"},
                  {"data":"doc.ssid"},
                  {"data":"doc.capability"},
                  {"data":"doc.frequency"},
                ]
        } );// End: DataTable


   //FITER SETUP START
     $('#example_filter input').unbind();
     $('#example_filter input').bind('keyup', function(e) {
         if(e.keyCode == 13) {
          oTable.fnFilter(this.value);   
      }
     });
     //FILTER SETUP END

Using _all_docs I could do pagination and key based filtering. I couldn't do the sorting. To do sorting one has to create a CouchDB View. Thats probably another blog post.