Usage

Summary

Running sequential SQL statements in NODEJS is not as straightforward and easy to read as using the procedural programing language such as PHP. Using nested callbacks or Promises clutters up your code. This is where Async/Await comes to the rescue.

To further clean and speed up the database query procedures, I added two database abstraction layers that wrap all the NPM mysql2 functionality. Database.js provides the common methods for all database needs. DbObject.js further abstracts the methods to provide one-to-one mapping of a database table to an object.

Installation

yarn add alanzhao-mysql-orm-async or npm install alanzhao-mysql-orm-async

Example: main.js

main = async () => {
  // Parse your enviornment variables saved in .env file
  require('dotenv').config();

  // Main database class
  const Database = require('alanzhao-mysql-orm-async/Database');
  
  // DbUser extends from DbObject
  const DbUser = require('./DbUser');

  // Construct database configs
  const dbConfigs = {
    'dbHost': process.env.DB_ENDPOINT,
    'dbUser': process.env.DB_USER,
    'dbPassword': process.env.DB_PASSWORD,
    'dbName': process.env.DB_NAME,
    'dbPort': process.env.DB_PORT,
    'dbConnectTimeout': process.env.DB_CONNECT_TIMEOUT
  };

  // Instantiate database
  const database = new Database(dbConfigs);
  
  // Connect to database
  await database.connect();

  //
  // Examples on using the main Database class
  //

  // Basic query
  const query = 'SELECT * FROM users ORDER BY ID ASC';
  const users = await database.query(query);

  for (let i = 0; i < users.length; i++) {
    let user = users[i];
    console.log(user.firstName + ' ' + user.lastName);
  }

  // Output total users in Database
  const totalUsers = await database.getAllCount('users');
  console.log('Total users: ' + totalUsers);

  // Delete user ID of 10
  await database.delete('users', 10);
  console.log('Deleted a user #10');

  //
  // Examples on using the DbObject extended class
  //

  // Instantiate DbUser, pass the database connection
  const dbUser = new DbUser(database);

  // Call method on the DbUser
  const specialUsers = await dbUser.getSomeVerySpecialUsers();
  for (let i = 0; i < specialUsers.length; i++) {
    let user = users[i];
    console.log(user.firstName + ' ' + user.lastName);
  }

  // Use the inherited methods

  // User ID #10 exists?
  const userExists = await dbUser.exists(10);
  console.log('User #10 exists: ' + userExists);

  // Update an user
  await dbUser.update(10, { firstName: 'New First Name', lastName: 'New Last Name' });
  console.log('User #10 has been updated');
}

main().catch(error => {
    // All errors will get caught here
    console.log('Main error: ' + error.message);
});

Example: DbUser.js

const DbObject = require('alanzhao-mysql-orm-async/DbObject');

module.exports = class DbUser extends DbObject {
  constructor(db) {
    super(db);

    // Users table
    this.tableName = 'users';
  }

  async getSomeVerySpecialUsers() {
    const query = "SELECT * FROM users WHERE status = 'special'";
    const users = await this._db.query(query);
    return users;
  }
}

API

Database

Kind: global class
Params: array configs The database connection configurations

Database
- new Database()
- .dbClasses ⇒ void
- .dbHost ⇒ string
- .dbPort ⇒ number
- .dbConnectTimeout ⇒ number
- .dbUser ⇒ string
- .dbPassword ⇒ string
- .dbName ⇒ string
- .insertedId ⇒ number
- .lastResults ⇒ array
- .lastQuery ⇒ string
- .affectedRows ⇒ number
- .changedRows ⇒ number
- .connect(ssl, sslCerts) ⇒ boolean
- .close() ⇒ boolean
- .escape(value) ⇒ string
- .escapeId(value) ⇒ string
- .format(query, values) ⇒ string
- .execute(query, values) ⇒ array
- .query(query, [values]) ⇒ array
- .get(table, id) ⇒ Object
- .getAll(table, orderBy) ⇒ array
- .getAllCount(table) ⇒ integer
- .getBy(table, criteria, [limit], [orderBy]) ⇒ array
- .insert(table, values) ⇒ boolean
- .update(table, id, values) ⇒ boolean
- .updateBy(table, criteria, values) ⇒ boolean
- .delete(table, id) ⇒ boolean
- .deleteBy(table, criteria) ⇒ boolean
- .exists(table, id) ⇒ boolean
- .existsBy(table, criteria, [excludeId]) ⇒ boolean
- .array(query, [column]) ⇒ array
- .kvObject(query, key, value) ⇒ Object
- .row(query) ⇒ array
- .scalar(query) ⇒ string | number | boolean
- .bool(query) ⇒ boolean
- .integer(query) ⇒ number
- .decimal(query, [decimal]) ⇒ number
- .tableExists(The) ⇒ boolean
- .transaction(queries) ⇒ boolean
- .duplicateTable(from, to) ⇒ boolean
- .truncate(table) ⇒ boolean
- .drop(table) ⇒ boolean
- .setEnvVar(name, value) ⇒ boolean
- .getEnvVar(name) ⇒ array
- .getTableColumns(name, [ignoreColumns]) ⇒ array
- .getTableColumnDefaultValues(name, [ignoreColumns]) ⇒ Object
- .getTableColumnDataTypes(name, [ignoreColumns]) ⇒ Object
- .export(results) ⇒ array
- .saveCache(cacheId, value) ⇒ void
- .clearCache(cacheId) ⇒ void
- .clearAllCache() ⇒ void
- .getCache(cacheId) ⇒ array
- .clearConnection() ⇒ void
- .getDb(args) ⇒ array