postgresql sql-builder

sql-builderl.js

import without from 'lodash/array/without';
const FIELD_SEPERATOR = ', ';

// table public fields
export const ACCOUNT_FIELDS = ['id', 'username', 'email', 'phone', 'password', 'status', 'type', 'created_at'];
export const PARENT_FIELDS = ['account_id', 'activated_at'];
export const EXPERT_FIELDS = ['account_id', 'activated_at'];
export const PROFILE_FIELDS = ['account_id', 'display_name', 'avatar_url', 'location_id', 'updated_at'];
export const LOCATION_FIELDS = ['id', 'country', 'province', 'city', 'district', 'street', 'street_number', 'lat', 'lng', 'created_at'];
export const COMMUNITY_FIELDS = ['id', 'location_id', 'name', 'description', 'signature', 'notice', 'cover_image_url', 'member_counts', 'rules', 'join_restrict', 'join_review', 'join_review_question', 'join_review_question', 'visible_on_search', 'status', 'created_at'];
export const COMMUNITY_MEMBER_FIELDS = ['id', 'community_id', 'account_id', 'role', 'status', 'joined_at'];

// alias for tables
export const ACCOUNTS_ALIAS = 'account';
export const PARENTS_ALIAS = 'parent';
export const EXPERTS_ALIAS = 'expert';
export const PROFILES_ALIAS = 'profile';
export const LOCATIONS_ALIAS = 'location';
export const COMMUNITIES_ALIAS = 'community';
export const COMMUNITY_MEMBERS_ALIAS = 'community_members';


function aliasFieldsToString(fields, alias) {
  return fields.map(field => alias ? alias + '.' + field : field).join(FIELD_SEPERATOR);
}
function jsonBuildifyFields(fields, alias) {
  let pairs = fields.map(field => `'${field}'${FIELD_SEPERATOR}${alias}.${field}`).join(FIELD_SEPERATOR);
  return `json_build_object(${pairs})`;
}
function joinUpdateFields(fields, alias) {
  return fields.map(field => `${field} = $(${field})`).join(FIELD_SEPERATOR);
}
function wrapLimitAndOffset(sql, { limit, offset } = {}) {
  limit && (sql += ' LIMIT $(limit) ');
  offset && (sql += ' OFFSET $(offset) ');
  return sql;
}

function buildInsertionSQL(table, fields) {
  return `INSERT INTO ${table}(` + fields.join(', ') + ') VALUES(' + fields.map(field => `$(${field})`).join(', ') + ') returning *';
}

export function buildAccountsDetailsSQL(accountCond, {
      parentJoinCond = `${PARENTS_ALIAS}.account_id = ${ACCOUNTS_ALIAS}.id`,
      expertJoinCond = `${EXPERTS_ALIAS}.account_id = ${ACCOUNTS_ALIAS}.id`,
      profileJoinCond = `${PROFILES_ALIAS}.account_id = ${ACCOUNTS_ALIAS}.id`,
      locationJoinCond = `${LOCATIONS_ALIAS}.id = ${PROFILES_ALIAS}.location_id`
    } = {}, {
      accountFields = ACCOUNT_FIELDS,
      parentFields = PARENT_FIELDS,
      expertFields = EXPERT_FIELDS,
      profileFields = PROFILE_FIELDS,
      locationFields = LOCATION_FIELDS
    } = {}, metas = {}) {

  if (!metas.allFields) {
    accountFields = without(accountFields, 'password');
  }

  let sql =  `
    SELECT
      ${aliasFieldsToString(accountFields, ACCOUNTS_ALIAS)},
      ${jsonBuildifyFields(parentFields, PARENTS_ALIAS)} AS parent,
      ${jsonBuildifyFields(expertFields, EXPERTS_ALIAS)} AS expert,
      ${jsonBuildifyFields(profileFields, PROFILES_ALIAS)} AS profile,
      ${jsonBuildifyFields(locationFields, LOCATIONS_ALIAS)} AS location
    FROM accounts ${ACCOUNTS_ALIAS}
    LEFT JOIN parents ${PARENTS_ALIAS} ON ${parentJoinCond}
    LEFT JOIN experts ${EXPERTS_ALIAS} ON ${expertJoinCond}
    LEFT JOIN profiles ${PROFILES_ALIAS} ON ${profileJoinCond}
    LEFT JOIN LATERAL (
      SELECT *
      FROM locations ${LOCATIONS_ALIAS} WHERE ${locationJoinCond}
    ) location ON TRUE
    WHERE ${accountCond}
  `;
  return wrapLimitAndOffset(sql, metas)
}

export function buildAccountsMetasSQL(accountCond, { accountFields = ACCOUNT_FIELDS } = {}, metas = {}) {
  if (!metas.allFields) {
    accountFields = without(accountFields, 'password');
  }
  let sql = `
    SELECT
      ${aliasFieldsToString(accountFields, ACCOUNTS_ALIAS)}
    FROM
      accounts ${ACCOUNTS_ALIAS}
    WHERE ${accountCond}
  `;
  return wrapLimitAndOffset(sql, metas);
}

export function buildLocationCreationSQL() {
  return buildInsertionSQL('locations', without(LOCATION_FIELDS, 'id', 'created_at'));
}
export function buildLocationQuerySQL(conds) {
  const sql = `
    SELECT
      ${aliasFieldsToString(LOCATION_FIELDS, LOCATIONS_ALIAS)}
    FROM
      locations ${LOCATIONS_ALIAS}
    WHERE ${conds}
  `;
  return sql;
}

export function buildProfileUpdateSQL(conds, fields) {
  const sql = `
    UPDATE
      profiles ${PROFILES_ALIAS}
    SET
      ${joinUpdateFields(fields, PROFILES_ALIAS)}
    WHERE ${conds} RETURNING *
  `;
  return sql;
}

export function buildCommunityCreationSQL() {
  return buildInsertionSQL('communities', without(COMMUNITY_FIELDS, 'id', 'created_at'));
}

export function buildCommunityMemberCreationSQL() {
  return buildInsertionSQL('community_members', without(COMMUNITY_MEMBER_FIELDS, 'id', 'joined_at'));
}