sql_cql_query_builder.js

const camelToSnakeCase = str => str.replace(/[A-Z]/g, letter => `_${letter.toLowerCase()}`);

function mapFieldsToStorageName(name) {
  const map = new Map()
  map.set('userId', 'owner_id')
  return map.get(name) ?? camelToSnakeCase(name)
}

function generateQuery(tableName, filters, ordering, options) {
  const values = []
  
  let where = ""
  for(const camelCaseId in filters) {
    const snakeCaseId = mapFieldsToStorageName(camelCaseId)
    const operations = filters[camelCaseId]

    for(const {value, operation} of operations) {
      if(where.length > 0) {
        where += " AND "
      }
  
      if(operation === 'in') {
        const repeated = '?, '.repeat(value.length - 1) + '?'
        where += `${snakeCaseId} ${operation} (${repeated})`
        values.push(...value)
      } else {
        where += `${snakeCaseId} ${operation ?? '='} ?`
        values.push(value)
      }
    }
  }
  where = (where.length > 0 ? 'WHERE ' : '') + where

  let orderBy = ""
  for(const camelCaseId in ordering) {
    const snakeCaseId = mapFieldsToStorageName(camelCaseId)
    const direction = ordering[camelCaseId] ?? 'asc'

    if(orderBy.length > 0) {
      orderBy += ", "
    }
    orderBy += `${snakeCaseId} ${direction}`
  }
  if(orderBy.length > 0) {
    orderBy = `ORDER BY ${orderBy}`
  }

  let limitQuery = ""
  if(options.limit) {
    limitQuery = `LIMIT ${options.limit}`
  }

  const sql = `select * from ${tableName} ${where} ${orderBy} ${limitQuery}`.trim()
  return [sql, values]
}


const query = generateQuery('invoices', {
  vehicleId: [{ operation: '=', value: '12' }, { operation: '!=', value: '76' }],
  startedAt: [{ operation: '>', value: new Date() }, { operation: '<', value: new Date() }],
  userId: [{ operation: 'in', value: [1, 2, 3, 4]}]
}, {
  vehicleId: 'asc',
  startedDate: 'desc',
  userId: 'desc'
}, {
  limit: 100
})
console.log(query)
/*
[
  'select * from invoices WHERE vehicle_id = ? AND vehicle_id != ? AND started_at > ? AND started_at < ? AND owner_id in (?, ?, ?, ?) ORDER BY vehicle_id asc, started_date desc, owner_id desc LIMIT 100',
  [
    '12',
    '76',
    2022-11-27T17:07:05.607Z,
    2022-11-27T17:07:05.607Z,
    1,
    2,
    3,
    4
  ]
]
*/