2

I want to have simple FP functions to get the records from mysql.

In Node JS:

For example:

exec(select('users'), where({active: 1})) // SELECT * FROM users WHERE active = 1

But if the where is complicated or I have a join how to make flexible.

exec(select('users'), where(condition))

In condition I want to have mysql equivalent of: '"first_name + last_name = "John Smith"' If i have conditions up front it is easier but take this for example:

WHERE num_comments > STRLEN(first_name + last_name)

I don't want to pass SQL because I want to be able to switch between MySQL, Postgres and MongoDb.

If I pass higher order function I should first get all users and filter in nodejs.

This will slow down the response very much.

Is there solution to this problem?

Improve this question
5
  • I think you should tag this with the functional programming language you are using. Commented Apr 26, 2018 at 13:05
  • i don't know but this screams mongo to me. They have a language / api for selecting and filtering data from their database. I assume you could follow that pattern. Not sure this is a "functional programming" question though. Commented Apr 26, 2018 at 13:54
  • secondly i think that you could represent this structure as an AST and then parse out the underlying languages... Commented Apr 26, 2018 at 13:55
  • This question appears to be asking how to design an SQL query builder and is too broad in its current state Commented Apr 26, 2018 at 13:55
  • @user2693928 I would definitely look at rethinkdb for some wisdom and inspiration - it has a beautiful functional api Commented Apr 26, 2018 at 13:59

2 Answers 2

Reset to default
2

You have a really broad question, but hopefully this can get you started. I would begin with some basic wrappers around the semantic components of your SQL query

const Field = (name) =>
  ({ type: Field, name })

const SqlLiteral = (value) =>
  ({ type: SqlLiteral, value })

const Condition = (operator, left, right) =>
  ({ type: Condition, operator, left, right })

Then you make an expression->sql expander

const toSql = (expr) =>
{
  switch (expr.type) {
    case Field:
      return expr.name
    case SqlLiteral:
      return JSON.stringify (expr.value) // just works for strings, numbers
    case Condition:
      return toSql (expr.left) + expr.operator + toSql (expr.right)
    default:
      throw Error (`Unhandled expression type: ${expr.type}`)
  }
}

Test some expressions

toSql (Condition ("=", Field ("name"), SqlLiteral ("bruce")))
// name="bruce"

toSql (Condition (">", Field ("earnings"), Field ("expenses")))
// earnings>expenses

toSql (Condition (">", Field ("earnings"), SqlLiteral (100)))
// earnings>100

We can just keep adding to this

const And = (left, right) =>
  ({ type: And, left, right })

const toSql = (expr) =>
{
  switch (expr.type) {
    case And:
      return toSql (expr.left) + " AND " + toSql (expr.right)
    ...
  }
}

toSql
  ( And ( Condition ("=", Field ("first"), SqlLiteral ("bruce"))
        , Condition ("=", Field ("last"), SqlLiteral ("lee"))
        )
  )
// first="bruce" AND last="lee"

Keep going... We can support things like SQL function calls like this

const SqlFunc = (func, arg) =>
  ({ type: SqlFunc, func, arg })

const toSql = (expr) =>
{
  switch (expr.type) {
    case SqlFunc:
      return expr.func + "(" + toSql (expr.arg) + ")"
    ...
  }
}

toSql
  ( Condition ( "<"
              , SqlFunc ("strlen", Field ("name"))
              , SqlLiteral (10)
              )
  )
// strlen(name)<10

Keep going!

const Select = (from, ...fields) =>
  ({ type: Select, from, fields: fields.map(Field) })

const Table = (name) =>
  ({ type: Field, name })

const Where = (select, condition) =>
  ({ type: Where, select, condition })


const toSql = (expr) =>
{
  switch (expr.type) {

    case Select:
      return `SELECT ${expr.fields.map(toSql).join(',')} FROM ${toSql (expr.from)}`

    case Field:
    case Table:
      return expr.name

    case Where:
      return toSql (expr.select) + " WHERE " + toSql (expr.condition)

    ...
  }
}

Now let's see a more advanced query come to life

toSql
  ( Where ( Select ( Table ("people")
                   , "first"
                   , "last"
                   , "email"
                   , "age"
                   )
          , And ( Condition ("=", Field ("first"), SqlLiteral ("bruce"))
                , Condition ("=", Field ("last"), SqlLiteral ("lee"))
                )
          )
  )
// SELECT first,last,email,age FROM people WHERE first="bruce" AND last="lee"

Obviously there's tons of work to do here, but the idea is once you have all the building blocks and a suitable toSql expander, you can make the magic wrappers around it all.

For example

const where = (descriptor = {}) =>
  Object.entries (descriptor)
    .map (([ k, v ]) =>
      Condition ("=", Field (k), SqlLiteral (v)))
    .reduce (And)

toSql (where ({ first: "bruce", last: "lee"}))
// first="bruce" AND last="lee"'

My general advice to you would be not to start this from scratch unless you want to build it for the sole purpose of learning how to make it. SQL is vastly complex and there are countless other projects out there that have attempted something similar in various ways. Look to them to see how to handle the more tricky scenarios.

Full program demonstration below

const Select = (from, ...fields) =>
  ({ type: Select, from, fields: fields.map(Field) })

const Table = (name) =>
  ({ type: Field, name })
  
const Field = (name) =>
  ({ type: Field, name })
  
const SqlLiteral = (value) =>
  ({ type: SqlLiteral, value })
  
const Condition = (operator, left, right) =>
  ({ type: Condition, operator, left, right })
  
const And = (left, right, ...more) =>
  more.length === 0
    ? ({ type: And, left, right })
    : And (left, And (right, ...more))

const Where = (select, condition) =>
  ({ type: Where, select, condition })

const SqlFunc = (func, arg) =>
  ({ type: SqlFunc, func, arg })

const toSql = (expr) =>
{
  switch (expr.type) {
    case Select:
      return `SELECT ${expr.fields.map(toSql).join(',')} FROM ${toSql (expr.from)}`
    case Field:
      return expr.name
    case Table:
      return expr.name
    case SqlLiteral:
      return JSON.stringify (expr.value) // just works for strings, numbers
    case SqlFunc:
      return expr.func + "(" + toSql (expr.arg) + ")"
    case Condition:
      return toSql (expr.left) + expr.operator + toSql (expr.right)
    case And:
      return toSql (expr.left) + " AND " + toSql (expr.right)
    case Where:
      return toSql (expr.select) + " WHERE " + toSql (expr.condition)
    default:
      throw Error (`Unhandled expression type: ${JSON.stringify(expr)}`)
  }
}

const sql =
  toSql(
    Where ( Select ( Table ("people")
                   , "first"
                   , "last"
                   , "email"
                   , "age"
                   )
          , And ( Condition ("=", Field ("first"), SqlLiteral ("bruce"))
                , Condition ("=", Field ("last"), SqlLiteral ("lee"))
                , Condition ( ">"
                            , Field ("age")
                            , SqlLiteral (30)
                            )
                )
          )
  )
  
console.log (sql)
// SELECT first,last,email,age FROM people WHERE first="bruce" AND last="lee" AND age>30

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

It'd be nice to place functions like And in infix position, like ´And´.
@ftor, queries are not meant to be written by hand using Select, and Field and And, etc. Instead, these are the underlying components to be used by the high-level magic api the OP wishes to build. I provide one such example, where, near the end of the updated answer
This is the best answer. I like how types are separated from the query and functions are so simple but powerfull. If i want i can abstract more not to see "sql" in the methods as "where" example: like whereFieldEq(field, val) = Condition('=', Field(field, SqlLiteral(val). This way i will not set low level details in controllers for example :) Thank you very much
@user633183 does this violates Open Closed principle. If i want to add new sql type i should add another case in toSql function. But if i do it with methods and classes it becomes OOP, for example similar to: class SqlField extends SqlExpr { toStr() { return extr.name } Is there way to make it functional and not violating Open Close principle. Thanks
@ftor i dont know if in javascript you can make infix position function 'And'
|
1

Have you thought about breaking it up. It will definitely make it a faster query and you can index the last name.

SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'john'

exec(select('users'), where({last_name: 'smith', first_name: 'john'}))
Improve this answer

1 Comment

:) this works but how about more complicated example: WHERE num_comments > STRLEN(first_name) I know it doesnt make sense its just for demo purpose

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.