jQuery Datatable with NodeJS & MySql

Question

I want to display the values in my Datatable. I opened my chrome dev tool and go to network tab, XHR and the problem is, the response is an HTML document

server side code:

router.get('/agents', (req, res) => {
  let dB = req.dB;
  let sql = 'SELECT * FROM tbl_users WHERE role_id = 3';

  dB.query(sql, (err, agents) => {
    if (err) throw err;
    const data = { agents };
    console.log(data);

    res.render('dashboard/agents', data);
  });
});

client side script:

$('#agent_table').DataTable( {
  processing: true,
  serverSide: true,
  ajax: {
    url: '/administrator/agents',
    type: 'GET',
    dataSrc: 'agents'
  },
  columns: [
    { data: 'user_id' },
    { data: 'checked' },
    { data: 'first_name' },
    { data: 'middle_name' },
    { data: 'role_id' },
  ]
} );

client side htmlt:

<table id="agent_table" class="table table-striped table-bordered dt-responsive nowrap"
            cellspacing="0" width="100%">
            <thead>
              <tr>
                <th>User ID</th>
                <th>
                  <div class="checkbox checkbox-success select_all">
                    <input class="styled" type="checkbox" id="check_all">
                    <label for="check_all" class="check_all_label">All</label>
                  </div>
                </th>
                <th>First Name</th>
                <th>Middle Name</th>
                <th>Last Name</th>
                <th>Role</th>
              </tr>
            </thead>
          </table>

console log in server

{ agents: [ RowDataPacket { user_id: 6, first_name: 'Pamela', middle_name: 'Pearl', last_name: 'Sabes', email: '[email protected]', password: '1bd07c9db7ae63c02f2ee75471727f58', mobile_number: '09503713607', invite_code: '', birthday: 'Nov 29, 2005', gender: 'Female', address: '', image: null, contact_person: '', contact_person_number: '', role_id: 3, longitude: '', latitude: '', medical_info: '', is_verified: 1, is_deleted: 0, created_at: '0000-00-00 00:00:00', updated_at: 2018-05-30T20:42:43.000Z } ] }

Answer 1

I think the problem is your usage of the res.render. Because according to express documentation res.render() function compiles your template, inserts locals there, and creates html output out of those two things and send it. So instead try this. res.json(data)