Question about connecting React Router to a database

I am trying to connect to a database from my React app (which has routes). This get request using Express and MySQL was successful:

db/index.js

import mysql from 'mysql'

let pool = mysql.createPool({
    connectionLimit: 10,
    password: 'app',
    user: 'app',
    database: 'chirprapp',
    host: 'localhost',
    port: '3306'
});

let chirprdb = {};

chirprdb.all = () => {
    return new Promise((resolve, reject) => {
        pool.query('SELECT * FROM chirps', (err, results) => {
            if (err) {
                connection.end()
                return reject(err);
            }
            return resolve(results);
        })
    })
}

chirprdb.one = (id) => {
    return new Promise((resolve, reject) => {
        pool.query('SELECT * FROM chirps WHERE id = ?', [id], (err, results) => {
            if (err) {
                return reject(err);
            }
            return resolve(results[0]);
        })
    })
}

route

import { Router } from 'express';
import db from '../db'

router.get('/:id?', async (req, res) => {
    let id = req.params.id;
    if (id) {
        try {
            let results = await db.one(id);
            res.json(results);
        } catch (e) {
            console.log(e);
            res.sendStatus(500);
        }
    } else {
        try {
            let results = await db.all();
            res.json(results);
        } catch (e) {
            console.log(e);
            res.sendStatus(500);
        }
    }
});

But when I try to make a simpler get request like

db/index.js

let mysql = require('mysql')

let connection = mysql.createConnection(
    {
        host: 'localhost',
        database: 'chirpapp',
        user: 'app',
        pasword: 'app'
    }
);
module.exports = connection;

route

import { Router } from 'express';
import connection from '../db'

let router = Router();

router.get('/', (req, res) => {
    connection.connect()
    connection.query('SELECT * FROM chirps', (err, results) => {
        if(err) {
            connection.end();
            res.sendStatus(500);
            return console.log(err);
        }
        res.json(results);
        return connection.end();
    })
})

I get a status 500 internal service error and SyntaxError: Unexpected token I in JSON at position 0. From what I've read online, the syntax error means the server is sending back HTML instead of JSON.

Why would I get two different responses for basically the same request?

This is the first time I've done this, so any insight from you is guaranteed to shed some light.

Comments

[Pascal Maniraho]

I am not sure but here are two things:

• return dataset expected on your downstream points(react router or other system)
• Don't return console.log()
• make error more explicit( the error object is forwarded to downstream points(react router) to get more ideas on what is not working in your case.

//....
connection.query('SELECT * FROM chirps', (err, results) => {
    var rs = Object.assign({}, results || {});    
    connection.end();
    if(err) {
            console.log(err);
            return res.status(500).json({error: err || "Something went wrong"});
        }
        return res.status(200).json(rs);
    });

I hope this helps!

[Christy]

That did help, thanks! It gave me the error 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR' which led me to this post: github.com/mysqljs/mysql/issues/900. I think it's a little beyond me, but basically using a single connection leads to this common error; so it's better to use a pool.

[Pascal Maniraho]

I am glad that it helped ~ Happy coding ;-)