Best practices for MySQL + Node/Express + Angular Stack

Question

I am currently using MySQL for the db instead of the popular mongodb, since that is the case there isn't much documentation out there as far as architecture and getting set up. This is my current structure

client
-- angular files
routes
-- index.js
views
-- 404 page
app.js

I don't understand how I can implement controllers or models into this structure. I'm currently grabbing data from the db or sending it with the routes..I'm not sure what the added layer of controllers would do. Maybe this is a dumb question but I would just like to have a clear baseline so that my project will scale well. I feel like there should be way more to this than what I currently have.

index.js

const express = require('express');
const mysql = require('mysql');
const router = express.Router();

const db = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'password',
    database : 'db'
});

// Connect
db.connect((err) => {
    if(err){
        throw err;
    }
    console.log('MySql Connected...');
});

// Select Data
router.get('/getData', (req, res) => {
    let sql = 'SELECT * FROM data';
    let query = db.query(sql, (err, results) => {
        if(err) throw err;
        console.log(results);
        res.send(results)
    });
});

module.exports = router;

app.js

const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');
const path = require('path');
const cors = require('cors');
const compression = require('compression');
const helmet = require('helmet')
const expressSanitizer = require('express-sanitizer');

const index = require('./routes/index');

const app = express();
const port = 3000;

var corsOptions = {
    origin: 'http://localhost:8100',
    optionsSuccessStatus: 200 // some legacy browsers (IE11, various SmartTVs) choke on 204 
}

// var logger = (req, res, next) => {
//     console.log('logging...')
//     next();
// }

//added security
app.use(helmet())

// //set logger
// app.use(logger)

//cors options
app.use(cors(corsOptions))

//body parser middleware
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({extended: false}))

// Mount express-sanitizer here
app.use(expressSanitizer()); // this line follows bodyParser() instantiations

//set static path
app.use(express.static(path.join(__dirname, 'client')));

// set our default template engine to "ejs"
// which prevents the need for using file extensions
app.set('view engine', 'ejs');

//gzip compression
app.use(compression())

//set views for error and 404 pages
app.set('views', path.join(__dirname, 'views'));

app.use('/', index);
app.use('/fp/trips', trips);

app.listen(port, () => {
    console.log('server started on port 3000')
})

there isn't much documentation out there ... this is relatively bad reason to choose a relational over non relational database. MySQL and Mongo do very different things. What are your data needs here? As for your actual question, if you are still having trouble getting MySQL to work with Node, which is heavily documented in many places, then you may have a learning curve problem generally. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 5, 2018 at 6:06
I don't really have an option here as far as choosing relational to non relational. As far as getting it to work, it does work - I just feel like I'm missing something here. — userlkjsflkdsvm
– userlkjsflkdsvm, Commented Mar 5, 2018 at 6:08

odino · Accepted Answer · 2018-03-05 09:19:51Z

2

When working on Node apps I tend to favor a scheme where controllers are (almost) services -- I think it works really well for small applications.

This is an example:

index.js

let app = express()
let users = require('./services/users')

app.get('/users/:id', async function(req, res, next) => {
  try {
    res.json(users.getByid(req.params.id))
  } catch() {
    next(err)
  }
})

app.listen(8080)

services/users.js

let db = require('./db')

async function getById(id) {
  let conn = await db.connect()
  let user = conn.query('SELECT * FROM user WHERE id = ?', [id])

  if (!user) {
    throw new Error("404")
  }

  return user
}

module.exports = {getById}

services/db.js

let realDb = require('some-open-source-library-to-interact-with-db')
realDb.initialize(process.env.DB_CREDENTIALS) // pseudo-code here

module.exports = realDb

This though, won't work well when you're building large, complex apps -- I think you will require more structure in that case.

PS: I wouldn't suggest to build a large, complex app ever -- split it into smaller ones where patterns like the one I presented work nicely.

answered Mar 5, 2018 at 9:19

odino

1,07911 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

userlkjsflkdsvm Over a year ago

awesome! I appreciate it!

Sherwin Ablaña Dapito · Accepted Answer · 2018-05-31 15:55:29Z

0

You can use Sequelize as ORM (Object Relational Mapper) for your MySQL DB to make your code more readable and to allow you to create better structure of your app. It also has support for PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL.

There are samples out there how to integrate Sequelize with Express. I'm not sure if I'm allowed to post a github repository here but here it is:

https://github.com/jpotts18/mean-stack-relational

PS. I don't own this repository but this might help you somehow.

answered May 31, 2018 at 15:55

Sherwin Ablaña Dapito

9791 gold badge17 silver badges35 bronze badges

Collectives™ on Stack Overflow

Best practices for MySQL + Node/Express + Angular Stack

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related