Some Code

Serves up project and video data for a React frontend.

Code Example:

                            
var express = require('express');
var router = express.Router();
const bcrypt = require('bcryptjs');
const uuid = require('uuid');
const jwt = require('jsonwebtoken');
const userMiddleware = require('../middleware/users.js');
const pool = require('../data/config');

router.get('/', (request, response) => {
    response.send({
        message: 'Welcome to the Node.js Express REST API!'
    });
});

//Projects

// Display all projects
router.get('/projects', (request, response) => {
    pool.query('SELECT * FROM tbl_Projects', (error, result) => {
        if (error) throw error;

        response.send(result);
    });
});


//Login

router.post('/sign-up', userMiddleware.validateRegister, (req, res, next) => {
    pool.query(
        `SELECT * FROM users WHERE LOWER(username) = LOWER(${pool.escape(
            req.body.username
        )});`,
        (err, result) => {
            if (result.length) {
                return res.status(409).send({
                    msg: 'This username is already in use!'
                });
            } else {
                // username is available
                bcrypt.hash(req.body.password, 10, (err, hash) => {
                    if (err) {
                        return res.status(500).send({
                            msg: err
                        });
                    } else {
                        // has hashed pw => add to database
                        pool.query(
                            `INSERT INTO users (id, username, password, registered) VALUES ('${uuid.v4()}', ${pool.escape(
                                req.body.username
                            )}, ${pool.escape(hash)}, now())`,
                            (err, result) => {
                                if (err) {
                                    throw err;
                                    return res.status(400).send({
                                        msg: err
                                    });
                                }
                                return res.status(201).send({
                                    msg: 'Registered!'
                                });
                            }
                        );
                    }
                });
            }
        }
    );
});
router.post('/login', (req, res, next) => {
    pool.query(
        `SELECT * FROM users WHERE username = ${pool.escape(req.body.username)};`,
        (err, result) => {
            // user does not exists
            if (err) {
                throw err;
                return res.status(400).send({
                    msg: err
                });
            }
            if (!result.length) {
                return res.status(401).send({
                    msg: '01: Username or password is incorrect!'
                });
            }
            // check password
            bcrypt.compare(
                req.body.password,
                result[0]['password'],
                (bErr, bResult) => {
                    // wrong password
                    if (bErr) {
                        throw bErr;
                        return res.status(401).send({
                            msg: '02: Username or password is incorrect!'
                        });
                    }
                    if (bResult) {
                        const token = jwt.sign({
                            username: result[0].username,
                            userId: result[0].id
                        },
                            'SECRETKEY', {
                            expiresIn: '7d'
                        }
                        );
                        pool.query(
                            `UPDATE users SET last_login = now() WHERE id = '${result[0].id}'`
                        );
                        return res.status(200).send({
                            msg: 'Logged in!',
                            token,
                            user: result[0]
                        });
                    }
                    return res.status(401).send({
                        msg: 'Username or password is incorrect!'
                    });
                }
            );
        }
    );
});
// router.get('/secret-route', userMiddleware.isLoggedIn, (req, res, next) => {
//     console.log(req.userData);
//     res.send('This is the secret content. Only logged in users can see that!');
// });

// // Display a single project by ID
// router.get('/projects/:id', (request, response) => {
//     const id = request.params.id;
//     pool.query('SELECT ProjectID, Project, Link, Platform, Language FROM tbl_Projects WHERE ProjectID Like ?', parseInt(id), (error, result) => {
//         if (error) throw error;

//         response.send(result);
//     });
// });

// // Add a new project
// router.post('/projects', (request, response) => {
//     pool.query('INSERT INTO tbl_Projects SET ?', request.body, (error, result) => {
//         if (error) throw error;

//         response.status(201).send(`project added with ID: ${result.insertId}`);
//     });
// });

// // Update an existing project
// router.put('/projects/:id', (request, response) => {
//     const id = request.params.id;
//     pool.query('UPDATE tbl_Projects SET ? WHERE id = ?', [request.body, id], (error, result) => {
//         if (error) throw error;

//         response.send('project updated successfully.');
//     });
// });

// // Delete a project
// router.delete('/projects/:id', (request, response) => {
//     const id = request.params.id;
//     pool.query('DELETE FROM tbl_Projects WHERE id = ?', id, (error, result) => {
//         if (error) throw error;
//         response.send('project deleted.');
//     });
// });

//Videos

// Display all videos
router.get('/videos', (request, response) => {
    pool.query('SELECT * FROM tbl_videos', (error, result) => {
        if (error) throw error;

        response.send(result);
    });
});

// // Display a single project by ID
// router.get('/videos/:id', (request, response) => {
//     const id = request.params.id;
//     pool.query('SELECT ProjectID, Project, Link, Platform, Language FROM tbl_videos WHERE ProjectID Like ?', parseInt(id), (error, result) => {
//         if (error) throw error;

//         response.send(result);
//     });
// });

// // Add a new project
// router.post('/videos', (request, response) => {
//     pool.query('INSERT INTO tbl_videos SET ?', request.body, (error, result) => {
//         if (error) throw error;

//         response.status(201).send(`project added with ID: ${result.insertId}`);
//     });
// });

// // Update an existing project
// router.put('/videos/:id', (request, response) => {
//     const id = request.params.id;
//     pool.query('UPDATE tbl_videos SET ? WHERE id = ?', [request.body, id], (error, result) => {
//         if (error) throw error;

//         response.send('project updated successfully.');
//     });
// });

// // Delete a project
// router.delete('/videos/:id', (request, response) => {
//     const id = request.params.id;
//     pool.query('DELETE FROM tbl_videos WHERE id = ?', id, (error, result) => {
//         if (error) throw error;
//         response.send('project deleted.');
//     });
// });

module.exports = router;