Displaying a MySQL/Mariadb table on a web page using Ruby Sinatra and JS DataTables
My son was working on a project where he needed to display data from a MySQL/Mariadb table on a web page. Having no prior HTML/JS experience he had no clue how to do this.
He already had a Sinatra based API for the same project so I decided to build him a small demo using Sinatra as the backend API and "web server" and JavaScript DataTables as the front-end.
First let's take a look at a simple database with a single table:
CREATE database demo_datatables;
USE demo_datatables;
DROP TABLE employees;
CREATE TABLE IF NOT EXISTS employees (
name VARCHAR(255),
position VARCHAR(255),
office VARCHAR(255),
extn VARCHAR(255),
start_date VARCHAR(255),
salary VARCHAR(255)
);
GRANT ALL ON demo_datatables.* TO my_user@localhost IDENTIFIED BY "my_password";
INSERT INTO employees VALUES ('Tiger Nixon','System Architect','Edinburgh','5421','2011/04/25','$320,800'),('Garrett Winters','Accountant','Tokyo','8422','2011/07/25','$170,750'),('Ashton Cox','Junior Technical Author','San Francisco','1562','2009/01/12','$86,000');
Ok the datatypes used suck and are inappropriate, but this is not the scope here.
Next let's create our Sinatra API (api.rb):
require "sinatra"
require "sequel"
require "json"
mysql_host = 'localhost'
mysql_user = 'my_user'
mysql_pass = 'my_password'
mysql_db = 'demo_datatables'
# Initialize out database connector and table dataset
db ||= Sequel.connect(
adapter: 'mysql2',
host: mysql_host,
username: mysql_user,
password: mysql_pass,
database: mysql_db,
max_connections: 10
)
table = db[:employees]
# Data from MySQL
get '/data' do
a = table.all.map { |h| h.values }
{ data: a }.to_json
end
# Page web root (this must be the last method as out pages data is in /)
get '/:name' do
if File.file?(params['name'])
send_file params['name']
else
"File #{params['name']} does not exists"
end
end
As you can see we have a /data endpoint that will fetch the entire content of the table, format it as an array compatible with DataTables.
We also have a catch all endpoint on / that is used as a poor-man web server. It could be made much better but for this demo it makes the point. This endpoint will simply take any file passed as endpoint and if they exist send them over.
We can start this simply by running ruby api.rb. The output should be similar to this:
[root@monitor1 ~]# ruby api.rb
[2019-06-19 19:38:07] INFO WEBrick 1.4.2
[2019-06-19 19:38:07] INFO ruby 2.5.1 (2018-03-29) [x86_64-linux]
== Sinatra (v2.0.5) has taken the stage on 4567 for development with backup from WEBrick
[2019-06-19 19:38:07] INFO WEBrick::HTTPServer#start: pid=19804 port=4567
So our server is running on port 4756. We can easily test out API by running curl -s http://localhost:4567/data:
[root@monitor1 ~]# curl -s http://localhost:4567/data
{"data":[["Tiger Nixon","System Architect","Edinburgh","5421","2011/04/25","$320,800"],["Garrett Winters","Accountant","Tokyo","8422","2011/07/25","$170,750"],["Ashton Cox","Junior Technical Author","San Francisco","1562","2009/01/12","$86,000"]]}6,000"}]}
We see that out endpoint to get the data works.
Let's now create out base HTML file (index.html):
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<meta name="author" content="">
<meta name="description" content="">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet">
</head>
<body>
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Extn.</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
</table>
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="script.js"></script>
</body>
</html>
He already had a Sinatra based API for the same project so I decided to build him a small demo using Sinatra as the backend API and "web server" and JavaScript DataTables as the front-end.
First let's take a look at a simple database with a single table:
CREATE database demo_datatables;
USE demo_datatables;
DROP TABLE employees;
CREATE TABLE IF NOT EXISTS employees (
name VARCHAR(255),
position VARCHAR(255),
office VARCHAR(255),
extn VARCHAR(255),
start_date VARCHAR(255),
salary VARCHAR(255)
);
GRANT ALL ON demo_datatables.* TO my_user@localhost IDENTIFIED BY "my_password";
INSERT INTO employees VALUES ('Tiger Nixon','System Architect','Edinburgh','5421','2011/04/25','$320,800'),('Garrett Winters','Accountant','Tokyo','8422','2011/07/25','$170,750'),('Ashton Cox','Junior Technical Author','San Francisco','1562','2009/01/12','$86,000');
Ok the datatypes used suck and are inappropriate, but this is not the scope here.
Next let's create our Sinatra API (api.rb):
require "sinatra"
require "sequel"
require "json"
mysql_host = 'localhost'
mysql_user = 'my_user'
mysql_pass = 'my_password'
mysql_db = 'demo_datatables'
# Initialize out database connector and table dataset
db ||= Sequel.connect(
adapter: 'mysql2',
host: mysql_host,
username: mysql_user,
password: mysql_pass,
database: mysql_db,
max_connections: 10
)
table = db[:employees]
# Data from MySQL
get '/data' do
a = table.all.map { |h| h.values }
{ data: a }.to_json
end
# Page web root (this must be the last method as out pages data is in /)
get '/:name' do
if File.file?(params['name'])
send_file params['name']
else
"File #{params['name']} does not exists"
end
end
As you can see we have a /data endpoint that will fetch the entire content of the table, format it as an array compatible with DataTables.
We also have a catch all endpoint on / that is used as a poor-man web server. It could be made much better but for this demo it makes the point. This endpoint will simply take any file passed as endpoint and if they exist send them over.
We can start this simply by running ruby api.rb. The output should be similar to this:
[root@monitor1 ~]# ruby api.rb
[2019-06-19 19:38:07] INFO WEBrick 1.4.2
[2019-06-19 19:38:07] INFO ruby 2.5.1 (2018-03-29) [x86_64-linux]
== Sinatra (v2.0.5) has taken the stage on 4567 for development with backup from WEBrick
[2019-06-19 19:38:07] INFO WEBrick::HTTPServer#start: pid=19804 port=4567
So our server is running on port 4756. We can easily test out API by running curl -s http://localhost:4567/data:
[root@monitor1 ~]# curl -s http://localhost:4567/data
{"data":[["Tiger Nixon","System Architect","Edinburgh","5421","2011/04/25","$320,800"],["Garrett Winters","Accountant","Tokyo","8422","2011/07/25","$170,750"],["Ashton Cox","Junior Technical Author","San Francisco","1562","2009/01/12","$86,000"]]}6,000"}]}
We see that out endpoint to get the data works.
Let's now create out base HTML file (index.html):
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<meta name="author" content="">
<meta name="description" content="">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet">
</head>
<body>
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Extn.</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
</table>
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="script.js"></script>
</body>
</html>
It's a simple HTML that has the skeleton for a table and fetches the required dependancies from global CDN sources.
And our small Javascript snippet (script.js):
$(document).ready(function () {
$("#example").DataTable({
"ajax": "/data"
});
});
Again very simple DataTables usage where we will fetch out data from the /data endpoint and have DataTables render in in the table with ID #table.
And let's look at the result:
Not bad for some ~70 lines of code.
Note that this is by no mean secure, failures trap would have to be added and scaleability would have to be evaluated, but it's simple as hell and I hope a good starting point.
Comments
Post a Comment