Node.js and Database

Opi Danihelka

Don't be afraid of Database

NoSQL DB

Relation DB

Pick DB type

www.mongodb.com

Heroku addon - mLab

mongodb://heroku_wgm04mmw:spvp2lja84e4tn0uphoqbfk8ip@ds141328.mlab.com:41328/heroku_wgm04mmw

www.mysql.com

Heroku addon - ClearDB

mysql://bdccf6a24f5ce5:8a4dcda4@eu-cdbr-west-01.cleardb.com/heroku_afba625cdba01a7?reconnect=true

Intall DB locally

Robomongo

DB Clients

Sequel Pro

DB Clients

mongoose

https://github.com/Automattic/mongoose

- adds model schema

- pre-save data validation

mysql

https://github.com/mysqljs/mysql

- no promises :(

- solves connection pooling

- prevent from SQL injection

Pick NPM module

Establish DB connection

• connection pool
• database credentials in env
• establish connection on server start

Establish DB connection

const config = require('./config');
const mongoose = require('mongoose');

// Use native promises
mongoose.Promise = global.Promise;

mongoose.connect(config.MongoDB.url)
  .then(() => {
    console.log('Database connected');
  })
  .catch((error) => {
    console.log(`Database connection failed: ${error}`);
  });

Establish DB connection

const config = require('./config');
const mysql = require('mysql');


const connectionPool = mysql.createPool({
  connectionLimit: 10,
  waitForConnections: true,
  queueLimit: 0,
  host: config.MySQL.host,
  user: config.MySQL.user,
  password: config.MySQL.password,
  database: config.MySQL.database,
  waitTimeOut: 28800,
});

connectionPool.getConnection((err, connection) => {
  if (err) {
    console.error('Error MySQL connecting:', err.stack);
    return;
  }

  console.log('MySQL connected as id:', connection.threadId);
});

Define DB Model

const mongoose = require('mongoose');

const EmployeeSchema = mongoose.Schema({
  email: {
    type: String,
    unique: true,
    index: true,
    required: true,
  },
  department: String,
  manager: {
    type: mongoose.Schema.ObjectId,
    ref: 'Employee',
  },
  name: String,
  phone: String,
});

/* verify phone number */
EmployeeSchema.pre('validate', function(next) {
  if (this.phone && !isPhoneValid(this.phone)) {
    const msg = `"${this.phone}" is not a valid phone number.`;
    return next(new Error(msg));
  }
  next();
});

module.exports = mongoose.model('Employee', EmployeeSchema);

Query

const mongoose = require('mongoose');
const Employee = mongoose.model('Employee');


exports.getEmployees = (req, res, next) => {
  Employee
    .find({ department: req.query.department })
    .select('department email name')
    .sort({ email: 1 })
    .lean()
    .exec()
  .then((employees) => {
    res.json(employees);
  }).catch(next);
};

Query

const db = require('../connectionPool');


exports.getEmployees = (req, res, next) => {
  const sql =
    `SELECT department, email, name 
    FROM employees 
    WHERE department = ? 
    ORDER BY email`;

  db.query({ sql }, [req.query.department], (error, employees) => {
    if (error) {
      next(error);
      return;
    }
    res.json(employees);
  });
};

Joins

const mongoose = require('mongoose');
const Employee = mongoose.model('Employee');


exports.getEmployees = (req, res, next) => {
  Employee
    .find({ department: req.query.department })
    .select('department email manager name')
    .populate('manager', 'email name')
    .sort({ email: 1 })
    .lean()
    .exec()
  .then((employees) => {
    res.json(employees);
  }).catch(next);
};

Joins

const db = require('../connectionPool');


exports.getEmployees = (req, res, next) => {
  const sql =
    `SELECT department, email, name, manager.email, manager.name 
    FROM employees 
    JOIN employees AS manager ON manager_id = manager.id
    WHERE department = ? 
    ORDER BY email`;

  db.query({ nestTables: '_', sql }, [req.query.department], (error, employees) => {
    if (error) {
      next(error);
      return;
    }
    res.json(employees);
  });
};

Questions?

MySQL

https://github.com/actum/timesheet-client/blob/master/server/controllers/Project.js

Mongo

https://github.com/actum/ews-api-server/blob/master/code/controllers/Employee.js