No matter the size or industry, companies everywhere grapple with solving a deceptively simple problem: displaying database data on the web in a table. This problem seems to be universal in the sense that all teams have experienced the inconvenience of passing around Excel spreadsheets, and want to instead move this data to the web. Yet these same teams often lack the programming experience required to securely retrieve the data and present it within a paginated, filterable and often searchable web interface.
Fortunately, using DreamFactory and off-the-shelf software such as Tabulator or DataTables, it's possible to create a powerful database-backed web interface such as the one presented in the following screenshot:
If you'd like to interact with a live example head over to https://tabulator.demo.dreamfactory.com/ and get your hands dirty!
In this blog post I'll show you how easy it is to create your own database-backed web interface using DreamFactory and Tabulator with almost no code required.
The open source edition includes support for several popular database, including MySQL and PostgreSQL, whereas the commercial edition additionally supports Microsoft SQL Server and Oracle (Check out our post comparing MySQL vs SQL Server).
Related reading: How to Connect to a MySQL Database with JavaScript
Once installed, you can generate a database-backed API in literally minutes, secured by (at minimum) an API and role-based access controls. If you're not familiar with this process head over to DreamFactory Academy and watch the following video, "Creating Your First Database API with DreamFactory":
You'll also find dozens of other videos on our Youtube channel.
Once the API created, you can query the database using a standardized API URL structure such as:
https://example.com/api/v2/corporate/_table/employees
Of course, for security reasons you'll need to additionally pass along an API key. This process is described in the aforementioned video.
DreamFactory is an API generation and management platform used by thousands of organizations around the globe. You can download our open source version from DreamFactory.com or contact us to start an on premise or hosted trial of our commercial version.
Tabulator is an open source JavaScript library used for creating interactive HTML tables. It supports data loading via a JavaScript array, JSON formatted data, or an AJAX data source. We'll use the latter approach to load data from the DreamFactory-powered database API. To begin though, you'll first need to install the library. If you're looking for the easiest solution, we recommend cloning our dreamfactory-tabulator repository, located on GitHub. Alternatively a number of other installation solutions are supported (Bower, NPM, Yarn, etc); check out the Tabulator documentation for more information.
Once installed, you only need to add a few lines of boilerplate code to load the database data into the Tabulator table. The following example contains just 35 lines of JavaScript code and will recreate a basic version of the interface presented in the earlier screenshot:
<!DOCTYPE html>
<html>
<head>
<title>Tabulator example</title>
</head>
<body>
<div id="example-table"></div><script>
var table = new Tabulator("#example-table", {
layout:"fitData",
height:"100%",
layout:"fitColumns",
pagination:"local",
paginationSize:20,
paginationSizeSelector:[20, 40, 60, 80],
placeholder:"No Data Set",
columns:[
{title:"Employee Number", field:"emp_no"},
{title:"First Name", field:"first_name"},
{title:"Last Name", field:"last_name"},
{title:"Birth Date", field:"birth_date"},
{title:"Hire Date", field:"hire_date"},
],
});$(document).ready(function(){
var ajaxConfig = {
dataType: 'json',
headers: {
"X-DreamFactory-Api-Key": '123456qwerttasdf'
},
};table.setData(
'https://example.com/api/v2/corporate/_table/employees',
{},
ajaxConfig
);
});
</script>
</body>
</html>
Let's breakdown some of the key syntax found in this example:
div identified by the example-table id will serve as the location where Tabulator will inject the table. When the Tabulator object is created, you'll see this div ID (#example-table) is passed in as the first argument so Tabulator knows what div to use.columns array should match the fields returned within the DreamFactory JSON response. In this example, each record includes five fields: emp_no, first_name, last_name, birth_date, and hire_date.ajaxConfig object defines the X-DreamFactory-Api-Key header. You'll assign the API key generated within DreamFactory to this header.setData method identifies the URL that will be contacted to retrieve the JSON data.Check out a more involved example in our GitHub repository to learn how to add other features!
If you'd like to learn more about DreamFactory, and how our platform can be used to quickly generate powerful web interfaces with almost no code required, contact us at DreamFactory.com!