kanso-orm/lib/querier.js

'use strict';
const pg = require('pg');

/**
 * Postgres
 *
 */
let postgres = {

  connect: function(connection) {
    return new Promise((resolve, reject) => {
      pg.connect(connection, function(err, client, done) {
        if(err) {
          reject(err);
        }
        resolve({ client: client, done: done });
      });
    });
  },

  query: function(client, done, sql) {
    return new Promise((resolve, reject) => {
      client.query(sql, function(err, results) {
        done();
        if(err) {
          reject(err);
        }
        resolve(results);
      });
    });
  }

};

/**
 * Constants
 *
 */
const STRING = 'string';
const OBJECT = 'object';
const NUMBER = 'number';
const BOOLEAN = 'boolean';

/**
 * Token Parser
 *
 */
let token = {
  table : (table) => {
    return `"${table}"`;
  },
  columnSingle: (column) => {
    return `${(column !== '*') ? '"' + column + '"' : column}`;
  },
  columnTable: (table, column) => {
    return `${token.table(table)}.${token.column(column)}`;
  },
  column: (column) => {
    if(typeof column === OBJECT) {
      return `${token.columnTable(column[0], column[1])}`;
    }
    else if(typeof column === STRING) {
      return `${token.columnSingle(column)}`;
    }
  },
  expression: (column, operator, value) => {
    return `${token.column(column)} ${operator} ${token.value(value)}`;
  },
  expressionColumn: (column, operator, value) => {
    return `${token.column(column)} ${operator} ${token.column(value)}`;
  },
  value : (value) => {
    switch(typeof value) {
      case STRING:
        return `'${value}'`;
      case NUMBER:
        return `${value}`;
      case BOOLEAN:
        return `${value}`;
    }
  },
};

/**
 * SQL
 *
 */
let SQL = function(settings) {

  //
  // Data
  //

  let query = [];

  let container = {
    table: '',
    distinct: false,
  };

  //
  // Table
  //

  this.table = (table) => {
    container.table = token.table(table);
    return {
      distinct: this.distinct,
      select: this.select,
      insert: this.insert,
      update: this.update,
      delete: this.delete
    };
  };

  this.distinct = () => {
    container.distinct = true;
    return {
      select: this.select,
      insert: this.insert,
      update: this.update,
      delete: this.delete
    };
  };

  //
  // Transaction
  //

  this.begin = () => {
    query.push(`begin`);
    return {
      exec: this.exec
    };
  };

  this.commit = () => {
    query.push(`commit`);
    return {
      exec: this.exec
    };
  };

  this.rollback = () => {
    query.push(`rollback`);
    return {
      exec: this.exec
    };
  };

  //
  // First
  //

  this.select = (columns) => {
    columns = columns.map((column) => token.column(column));
    query.push(`select ${((container.distinct) ? 'distinct ' + columns.join(', ') : columns.join(', ') )} `);
    query.push(`from ${container.table}`);
    return {
      sql: this.sql,
      where: this.where,
      limit: this.limit,
      offset: this.offset,
      orderBy: this.orderBy,
      innerJoin: this.innerJoin,
      exec: this.exec
    };
  };

  this.insert = (data) => {
    let columns = [];
    let values = [];
    query.push(`insert into ${container.table}`);
    data.map((cols) => {
      let vals = [];
      for(let col in cols) {
        let c = token.column(col);
        let found = columns.find((column) => column === c);
        if(!found) columns.push(c);
        vals.push([token.value(cols[col])]);
      }
      values.push(`(${vals.join(', ')})`);
    });
    query.push(`(${columns.join(', ')}) values ${values.join(' ')}`);
    return {
      sql: this.sql,
      returning: this.returning,
      exec: this.exec
    };
  };

  this.update = (data) => {
    let columns = [];
    let values = [];
    query.push(`update ${container.table} set ${columns.join(', ')}`);
    for(let col in data) {
      columns.push(token.column(col));
      values.push(token.value(data[col]));
    }
    query.push(`(${columns.join(', ')}) = (${values.join(', ')})`);
    return {
      sql: this.sql,
      where: this.where,
      exec: this.exec
    };
  };

  this.delete = () => {
    query.push(`delete from ${container.table}`);
    return {
      sql: this.sql,
      where: this.where,
      exec: this.exec
    };
  };

  //
  // Join
  //

  this.join = (table) => {
    query.push(`join ${token.table(table)}`);
    return {
      sql: this.sql,
      on: this.on
    };
  };

  this.innerJoin = (table) => {
    query.push(`inner join ${token.table(table)}`);
    return {
      sql: this.sql,
      on: this.on
    };
  };

  this.outerJoin = (table) => {
    query.push(`outer join ${token.table(table)}`);
    return {
      sql: this.sql,
      on: this.on
    };
  };

  this.on = (columnA, operator, columnB) => {
    query.push(`on (${token.expressionColumn(columnA, operator, columnB)})`);
    return {
      sql: this.sql,
      where: this.where,
      whereIn: this.whereIn,
      limit: this.limit,
      offset: this.offset,
      orderBy: this.orderBy,
      returning: this.returning,
      exec: this.exec
    };
  };

  //
  // Secondary
  //

  this.where = (column, operator, value) => {
    if(column, operator, value) {
      query.push(`where ${token.expression(column, operator, value)}`);
    }
    else if(column, !operator, !value) {
      query.push(`where ${token.column(column)}`);
    }
    return {
      sql: this.sql,
      and: this.and,
      or: this.or,
      in: this.in,
      limit: this.limit,
      offset: this.offset,
      orderBy: this.orderBy,
      returning: this.returning,
      exec: this.exec
    };
  };

  this.and = (column, operator, value) => {
    if(column, operator, value) {
      query.push(`and ${token.expression(column, operator, value)}`);
    }
    else if(column, !operator, !value) {
      query.push(`and ${token.column(column)}`);
    }
    return {
      sql: this.sql,
      and: this.and,
      or: this.or,
      in: this.in,
      limit: this.limit,
      offset: this.offset,
      orderBy: this.orderBy,
      exec: this.exec
    };
  };

  this.or = (column, operator, value) => {
    if(column, operator, value) {
      query.push(`or ${token.expression(column, operator, value)}`);
    }
    else if(column, !operator, !value) {
      query.push(`or ${token.column(column)}`);
    }
    return {
      sql: this.sql,
      and: this.and,
      or: this.or,
      in: this.in,
      limit: this.limit,
      offset: this.offset,
      orderBy: this.orderBy,
      exec: this.exec
    };
  };

  this.in = (sql) => {
    query.push(`in (${sql})`);
    return {
      sql: this.sql,
      where: this.where,
      exec: this.exec
    };
  };

  this.limit = (limit) => {
    query.push(`limit ${limit}`);
    return {
      sql: this.sql,
      offset: this.offset,
      orderBy: this.orderBy,
      exec: this.exec
    };
  };

  this.offset = (offset) => {
    query.push(`offset ${offset}`);
    return {
      sql: this.sql,
      limit: this.limit,
      orderBy: this.orderBy,
      exec: this.exec
    };
  };

  this.orderBy = (column, order) => {
    query.push(`order by ${token.column(column)} ${order}`);
    return {
      sql: this.sql,
      limit: this.limit,
      orderBy: this.orderBy,
      exec: this.exec
    };
  };

  this.returning = (columns) => {
    columns = columns.map((column) => token.column(column));
    query.push(`returning ${columns.join(', ')}`);
    return {
      sql: this.sql,
      exec: this.exec
    };
  };

  //
  // Raw
  //

  this.sql = () => {
    const sql = `${query.join(' ')}`;
    if(settings.debug) {
      console.log('\n\n', sql);
    }
    return sql;
  };

  //
  // Execute
  //

  this.exec = () => {
    let sql = this.sql();

    return new Promise((resolve, reject) => {

      if(settings.transaction) {
        if(settings.debug) {
          console.log('\n\n', 'using transaction');
        }
        settings.transaction.query(sql, function(err, results) {
          if(err) {
            reject(err);
          } else {
            resolve(results.rows);
          }
        });
      } else {
        if(settings.debug) {
          console.log('\n\n', 'using transaction');
        }
        let client, done;
        postgres.connect(settings.connection).then((response) => {
          client = response.client;
          done = response.done;
          return postgres.query(client, done, sql);
        })
        .then((results) => {
          resolve(results.rows);
        })
        .catch((err) => {
          reject(err);
        });

      }
    });

  };

};

/**
 * Querier
 *
 */
let Querier = (connection, config) => {

  // Connection
  let settings = {
    connection: '',
    debug: (config && config.debug) ? config.debug : false
  };

  // Create connection
  settings.connection = `postgres://${connection.user}:${connection.password}@${connection.host}/${connection.database}`;

  // Transaction
  let client, done;

  // Start
  return {
    table: function(table) {
      return new SQL({
        connection: settings.connection,
        debug: settings.debug,
        transaction: client
      }).table(table);
    },
    transaction() {
      return postgres.connect(settings.connection).then((response) => {
        client = response.client;
        done = response.done;
        return new SQL({
          transaction: client,
          debug: settings.debug
        }).begin().exec();
      });
    },
    rollback: function() {
      return new SQL({
        transaction: client,
        debug: settings.debug
      }).rollback().exec().then(() => {
        done();
      });
    },
    commit: function() {
      return new SQL({
        transaction: client,
        debug: settings.debug
      }).commit().exec().then(() => {
        done();
      });
    }
  };

};

module.exports = Querier;