SQL: One of the most valuable skills

Re:SQL: One of the most valuable skills by haskman on Wednesday February 13, 2019 @04:23AM (#19151661)

Preach! I just started building something like this in Purescript which might interest you - https://github.com/ajnsit/purescript-consequence.

It allows writing things like -

    type PersonRel = Relation (name :: String, age :: Int)
    type EmployeeRel = Relation (employeeName :: String, managerName :: String)

    -- Get all managers older than 60
    oldManagers :: PersonRel
    oldManagers =
      employeeTable
        # renameField (SProxy :: SProxy "managerName") (SProxy :: SProxy "name")
        # join (RProxy :: RProxy (name :: String)) personTable
        # filter (\r -> r.age > 60)
        # project (RProxy :: RProxy (name :: String, age :: Int))

[ Parent | on HN ]

Re:SQL: One of the most valuable skills by ako on Wednesday February 13, 2019 @04:38AM (#19151722)

Resembles R dplyr pipelines using magrittr, although i find the dplyr version easier to read.

[ Parent | on HN ]

Re:SQL: One of the most valuable skills by haskman on Wednesday February 13, 2019 @05:34AM (#19152010)

I am not familiar with R dplyr.

The code snippet I posted doesn't adequately demonstrate it but the DSL is strongly typed with full type inference at every step. So for example, it knows that the type of the table after the "join" is has a field "age" of type "int", and that filtering on it is okay. Trying to filter on a non-existent field will cause a compile (not runtime) error.

Second, the "steps" are actually plain functions. `#` is simply the reverse function application operator defined as `a # f = f a`. Which means you can abstract operations. For example, you can define a generic function that can take any relation with an age field and filters for age > 60. Thanks to strong types, using it incorrectly (i.e. on a relation without an age field of type integer) would cause an compile error.

Also, the language itself is a deeply embedded DSL. So "ordinary" code also compiles down to SQL. You can do things like loops etc. (for example, create a function that filters over a list of age limits rather than just one) and it will try to generate the best SQL possible.

of course the SQL generation is currently not implemented, so all this is theoretical, but I'm getting there!

[ Parent | on HN ]