Sql helper for operating mysql on node

_db.js

import mysql from 'mysql';
import moment from 'moment';
import config from './config';
import logger from './logger';

export default class DB {

  constructor() {
  }

  // Create a connection with mysql server
  static connect() {
    var mysqlConfig = config.mysql;
    var connection = mysql.createConnection(mysqlConfig);
    connection.connect((err) => {
      if (err) {
        logger.error(err, mysqlConfig, 'DB connection error');
      }
    });
    return connection;
  }

  // Execute sql query string with parameters
  static execute(sql, params) {
    return new Promise((resolve, reject) => {
      var con = this.connect();
      var query = con.query(sql, params, (err, results) => {
        if (err) {
          logger.error(err, query.sql, 'Failed to execute sql');
          reject(err);
        } else {
          resolve(results);
        }
      });
      con.end();
    });
  }

  // Insert a record to db, and return the new id
  static insert(sql, params) {
    return this.execute(sql, params)
      .then((results) => results.insertId);
  }

  /**
   * Get only one result
   * e.g.
   *     sql: "select * from users where id = 1"
   *     result: {id: 1, username: "user1" ... }
   */
  static getObject(sql, params) {
    return this.execute(sql, params)
      .then((results) => {
        return results.length ? results[0] : null;
      });
  }

  /**
   * Executes the query, and returns
   * the first column of all row in the result
   * e.g.
   *     sql: "select id from users"
   *     result: [1, 2, 3]
   */
  static getValues(sql, params) {
    var vals = [];
    return this.execute(sql, params)
      .then((results) => {
        return results.map(DB.getFirstProperty);
      });
  }


  /**
   * Executes the query, and returns
   * the first column of the first row in the result
   * e.g.
   *     sql: "select id from users where username = 'user1'"
   *     result: 1
   */
  static getValue(sql, params) {
    return this.execute(sql, params)
      .then((results) => results.length ? DB.getFirstProperty(results[0]) : null);
  }


  /**
   * Execute sql string with parameters and id array
   * e.g.
   *     sql: "update users set username like ? and id in (?)"
   *     parmas: ['%jim%']
   *     ids: [1, 2, 3]
   */
  static executeWithParamsAndIds(sql, params, ids) {
    if (!ids || ids.length === 0) {
      return Promise.resolve([]);
    }
    if (!params) {
      params = [];
    } else if (Object.prototype.toString.call(params) !== '[object Array]') {
      params = [params];
    }
    params.push(ids);
    return this.execute(sql, params);
  }


  /**
   * Execute sql string with id array
   * e.g.
   *     sql: "select * from users where id in (?)"
   *     ids: [1, 2, 3]
   *     results: [
   *                 {id: 1, username: "user1" ... },
   *                 {id: 2, username: "user2" ... },
   *                 {id: 3, username: "user3" ... }
   *             ]
   */
  static executeWithIds(sql, ids) {
    return this.executeWithParamsAndIds(sql, [], ids);
  }


  /**
   * Check if there is any result exists
   * e.g.
   *     sql: select count(*) from users where username = 'jim'
   *     results: true
   * @param  {[type]}   sql      [description]
   * @param  {[type]}   params   [description]
   * @return {[type]}            [description]
   */
  static exists(sql, params) {
    return this.getValue(sql, params)
      .then((results) => (results && results > 0));
  }

  static now() {
    return moment().unix();
  }

  static getFirstProperty(object) {
    if (object && Object.keys(object).length) {
      return object[Object.keys(object)[0]];
    }
  }
}

config.js

export default {
  mysql: {
    host: "localhost",
    port: "3306",
    user: "root",
    password: "",
    database: "dbname",
    multipleStatements: true
  }
};

users.js

import _ from 'lodash';
import moment from 'moment';
import db from './db';
import logger from './logger';

export default class Users {

  // Get users by ids
  static getByIds(ids) {
    const queryStr = `
          SELECT
            id, email, username, screenName,
            avatarUrl,
            bannedUntilAt, createdAt
          FROM users
          WHERE id IN (?)`;
    return db.executeWithIds(queryStr, ids);
  }

  // Get memebership by id
  static getMembership(id) {
    const queryStr = `SELECT
                        passwordHashed, passwordFormat, passwordSalt
                      FROM users
                      WHERE id = ?`;
    return db.getObject(queryStr, id);
  }

  // Create a new user
  static create(user) {
    const queryStr = `
          INSERT INTO
            users
          (
            username, email, screenName,
            passwordHashed, passwordFormat,
            passwordSalt, avatarUrl, createdAt
          )
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;

    const params = [user.username, user.email,
                  user.screenName, user.passwordHashed,
                  user.passwordFormat, user.passwordSalt,
                  user.avatarUrl, user.createdAt];
    return db.insert(queryStr, params);
  }

  // change user's password
  static changePassword(id, format, hashed, salt) {
    const queryStr = `UPDATE
                        users
                      SET
                        passwordHashed = ?,
                        passwordFormat = ?,
                        passwordSalt = ?
                      where
                        id = ?`;
    return db.execute(queryStr, [hashed, format, salt, id]);
  }

  // Get user id by username
  static getUserIdByUsername(username) {
    if (!username || _.isEmpty(username)) {
      return Promise.resolve(null);
    }
    const queryStr = `SELECT id FROM users where username = ?`;
    return db.getValue(queryStr, username);
  }

  // Get user id by email
  static getUserIdByEmail(email) {
    if (!email || _.isEmpty(email)) {
      return Promise.resolve(null);
    }
    const queryStr = `SELECT id FROM users where email = ?`;
    return db.getValue(queryStr, email);
  }

}