How do I display table data (HTML) from a database through JSON Express?

Question

Hello! I would like to know a way to present to the user in an HTML table, data stored in a MYSQL Database with connection through JSON Express.

const express = require('express');
const app = express();         
const bodyParser = require('body-parser');
const port = 3309;
const mysql = require('mysql');
var formidable = require("formidable");


var obj = {};
var path = require('path');


    app.use(bodyParser.urlencoded({ extended: true }));
    app.use(bodyParser.json());

const router = express.Router();


app.get('/', function(req, res) {
    res.sendFile(path.join(__dirname + '/index.html'));
});



    app.get('/lista', function(req, res) {

        execSQLQuery("SELECT Nome, modeloCarro, status FROM Clientes",res);
        });

    app.post('/dados', function(req, res) {
        execSQLQuery("INSERT INTO Clientes(Nome,CPF,dtNascimento,modeloCarro,status) VALUES ('"+req.body.nomeMotorista+"','"+req.body.CPF+"','"
        +req.body.dataNascimento+"','"+req.body.modeloCarro+"','"+req.body.Ativo+"')", res);
    });



    app.listen(port);
    console.log('API funcionando!');

/*****************************Connection Code*************************************/

function execSQLQuery(sqlQry, res){
    const connection = mysql.createConnection({
  host     : 'localhost',
  port     : 3308,
  user     : 'root',
  password : 'teste',
  database : 'mysql'
    });

 connection.query(sqlQry, function(error, results, fields){
      if(error) 
        res.json(error);
      else
        res.json(results);
      connection.end();
      console.log('executou!');
  });
}

Answer 1

You can use the ejs engine, use a callpassar of the object array directly into the view, and then display each with jquery’s Datatables.

app.get('/lista', (req, resp) => {
execSQLQuery(sqlQry, (results) => {
    resp.render("Home", { lista: results });
   });
});

In your execSQLQuery add a parameter to the return function, then you have to create a variable that will receive your result and right after you do you pass it as parameter to the function.

The example below is for an SQL Server database, but the idea is the same.

function request(Query, func_Retorno) {
var retorno = undefined;
//Executa a query e enche o objeto que será usado
var request = new sql.Request();
(request.query(Query).then((recordset) => {

    console.log("Carrega objeto");

    sql.close();
    //Preenche a variáveç retorno com um array de objetos
    retorno = recordset.recordset;
}).catch((error) => {
    console.error(error);
    sql.close();
})).then(() => {
    //Retorna o array de objetos para a função de callback
    if (func_Retorno != undefined)
        func_Retorno(retorno);
    console.log("Retorno do objeto concluido");
    });
}

In your view you create a tambela and in the jquery function you have to use the <%- %> tag in order to use what comes from your backend.

<script>
    //Atribui a variável Lista o array de objetos que retorna do backend
    var Lista= <%- JSON.stringify(lista) %>;

    var TableDados = null;
    $(function () {

        TableDados = $('#tblDados').DataTable({
            "serverSide": false,
            "drawCallback": function (settings) {},
            "scrollX": true,
            "processing": true,
            "paging": true,
            "lengthChange": false,
            "searching": true,
            "ordering": true,
            "info": true,
            "autoWidth": true,
            "deferRender": true,
            "language":
            {
                "url": "/scripts/plugins/dataTables/languagePT-BR.json"
            },
            "columns": [
                { "data": "Nome" },
                { "data": "Sobrenome" },
                { "data": "Numero" },
                { "data": "Idade" },
            ],
            "order": [0, "asc"]
        });
        console.log(Lista);
        TableDados.rows.add(Lista)
        TableDados.draw();
    });
</script>

To better understand how Datatables works, take a look at this link