querybuilder.js

const print = (value) => console.log(value)
class QueryBuilder {
  #sqlText = ''
  #sqlTable = null
  #sqlSelect = '*'
  #sqlAlias = {}
  #sqlFields = []
  #sqlSelectAlias = {}
  #sqlValues = []
  constructor ({ table, as = [], select = '*', fields = [] }) {
    this.#sqlTable = table
    this.#sqlAlias = this.#parseAlias(as)
    this.#sqlFields = this.#parseFields(fields)
    this.#sqlSelect = this.#parseSelect(select)
  }
  
  // parse as ['table as alias']
  #parseAlias (as) {
    const items = {}
    as.forEach((item) => {
      const aliasArr = item.split('as')
      const name = aliasArr[0].trim()
      const alias = aliasArr[1].trim()
      items[name] = alias
    })
    return items
  }
  
  // this method, return table name or table alias
  #getTable (tableName) {
    return this.#sqlAlias[tableName] ?? tableName
  }

  #parseSelect (fields) {
    // if fields is void
    if (fields === '*' || fields === '')  {
      return [this.#sqlSelect]
    }
    // Process fields
    const items = {}
    const itemJoinTable = []
    const fieldsArr = fields.split(',')
    fieldsArr.forEach((item) => {
      const aliasArr = item.split('as')
      const name = aliasArr[0].trim()
      const alias = (aliasArr[1] || '').trim() ?? ''
      const nameSplit = name.split('.')
      // append table name or alias
      let sqlJoinTable = ''
      if (nameSplit.length === 2) {
        const tableName = this.#getTable(nameSplit[0])
        if (tableName) {
          sqlJoinTable = `${tableName}.${nameSplit[1]}`
        } else {
          const tableName = this.#getTable(this.#sqlTable)
    	  	sqlJoinTable = `${tableName}.${nameSplit[1]}`
        }
      } else {
        const tableName = this.#getTable(this.#sqlTable)
        sqlJoinTable = `${tableName}.${nameSplit[0]}`
      }
      if (alias !== '') {
        sqlJoinTable += ` as ${alias}`
      }
      itemJoinTable.push(sqlJoinTable)
      items[name] = alias 
    })
    this.#sqlSelectAlias = items
    return itemJoinTable
  }

  #from () {
    const isAlias = !!this.#sqlAlias[this.#sqlTable]
    const sqlAs = isAlias ? ` AS ${this.#getTable(this.#sqlTable)}` : ''
    return ` FROM ${this.#sqlTable}${sqlAs}`
  }
  
  #generateAs (table) {
    const isAlias = !!this.#sqlAlias[table]
    const sqlAs = isAlias ? ` AS ${this.#getTable(table)}` : ''
    return ` ${table}${sqlAs}`
  }
  
  #parseField(field) {
    const item = {}
    const isUnderscores = field.includes('__')
    const isDot = field.includes('.')
    const isSingleField = !(isDot || isUnderscores)
    const itemSplit = field.split(isDot ? '.' : '__')
    const tableName = this.#getTable(isSingleField ? this.#sqlTable : itemSplit[0])
    item.table = tableName
    item.field = itemSplit[isSingleField ? 0 : 1]
    return item
  }

  #parseFields (fields) {
    const items = []
    fields.forEach(item => {
      const parsedItem = this.#parseField(item)
      items.push(parsedItem)
    })
    return items
  }
  
  // return true if this field is valid or generate an Fatal Error
  #checkField (table: string, field: string) {
    const findCallback = i => i.table === table && i.field === field
    const tableName = this.#getTable(this.#sqlTable)
    const isField = this.#sqlFields.find(findCallback)
    if (!isField) {
      this.#catch(`Field ${table}.${field} its not allowed`)
    }
    return true
  }

  #catch = (message: string) => {
    throw new Error(message)
  }

  // sql public
  select () {
    const fields = this.#sqlSelect.join(', ')
    this.#sqlText = `SELECT ${fields}${this.#from()}`
    return this
  }
  
  paginate(page: number = 1, size: number = 10) {
    this.#sqlValues = [page, size]
    const values = this.#sqlValues
    const pageVal = `$${values.length}`
    const sizeVal = `$${values.length - 1}`
    this.#sqlText += ` LIMIT ${pageVal}  OFFSET ((${sizeVal} - 1) * ${pageVal})`
    return this
  }
  
  where(field: string, value: any) {
    this.#sqlValues.push(value)
    const pField = this.#parseField(field)
    const tableName = this.#getTable(this.#sqlTable)
    const values = this.#sqlValues
    
    this.#checkField(pField.table, pField.field)

    this.#sqlText += ` WHERE ${pField.table}.${pField.field}=$${values.length}`
    return this
  }
  
  innerJoin(props) {
    const tableOn = this.#getTable(props.on.table)
    const tableJoin = this.#getTable(props.join.table)
    const tableJoinAs = this.#generateAs(props.join.table)
    const fieldOn = props.on.field
    const fieldJoin = props.join.field
    this.#sqlText += ` INNER JOIN${tableJoinAs} ON ${tableJoin}.${fieldJoin} = ${tableOn}.${fieldOn}`
        return this
    }

  sql () {
    // return this.#sqlText
  	return {
      text: this.#sqlText,
      values: this.#sqlValues,
      fields: this.#sqlFields
    }
  }
}

const qs = new QueryBuilder({
  table: 'musician',
  as: ['musician as m', 'country as c'],
  fields: ['id', 'first_name', 'last_name', 'country__name', 'city.name'],
  select: 'musician.name as nombre, age as edad, city.id, country.name, c.age as country_age'
})

const sql = qs.select().sql()
const sql2 = qs.select().paginate().sql()
const sql3 = qs.select().where('last_name', 'Claudio').paginate().sql()
const sql4 = qs
	.select()
  .innerJoin({
      join: { table: 'country', field: 'id' },
      on: { table: 'm', field: 'city_fk' }
  })
  .innerJoin({
      join: { table: 'city', field: 'iso' },
      on: { table: 'c', field: 'country_iso_fk' }
  })
	.where('country.name', 'Santiago').paginate().sql()
print(qs)
print(sql)
print(sql2)
print(sql3)
print(sql4)