Question about connecting React Router to a database

#react #database #mysql

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.

Top comments (3)

Pascal Maniraho • Oct 10 '18 • Edited

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 • Oct 10 '18

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 • Oct 10 '18

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

DEV Community

Question about connecting React Router to a database

Top comments (3)

Read next

Exploring new AWS Aurora DSQL. What is it ? Why it is important ? How to quickstart ?

Multi-Region Distributed SQL Transaction Latency

Day 1: Getting Started with SQL - Basics | Beginners' Guide : Mastering

Redis