4 ms·

I am attracted to these types of DSLs but . . . I work with a number of legacy data sources that often have models that are not ORM-friendly. I fall into SQL

by tom_b 15y ago

I am attracted to these types of DSLs but . . . I work with a number of legacy data sources that often have models that are not ORM-friendly. I fall into SQL code patterns that seem left behind by tools like this on a regular basis, particularly using SQL functions without default grouping because I'll select other stuff higher up. A simple example: select col1, col2, some_function() over (partition by col1,col2 order by col1,col2) as some_ck where <some conditions> This is often part of a larger query (e.g., this is embedded in bowels of a some longer "chain" of SQL where I will be using some_ck to decide on which tuples I want in the end). I get pretty significant mileage out of this - meaning I write much less other code around it to handle the data. I guess my complaint is that I often find the SQL DSLs lacking in expressiveness that I use regularly enough to miss. Of course, this could be because I've spent too much time hacking around in legacy data in "real soon now" mode rather than in "hmm . . . let's clean up this data model" mode.

jwr 15y ago
I think writing DSLs for SQL makes little sense. SQL is already a declarative query DSL, why not just use it? Putting another declarative layer on top buys you little (syntactic sugar for most libraries, this one additionally seems to deal with one-to-many relations, and I hope that support is read-write, not just read-only) — and isolates you from the database below. In every serious application I've worked on there was a need to use something which the DSL did not support. So why do it at all?
- groovy2shoes 15y ago
  In this case, an embedded DSL brings all the goodies of Lisp's code-as-data to SQL.
- technomancy 15y ago
  Raw SQL is not very composable, while Korma emphasizes composability on the second paragraph of the page.
  - hosh 15y ago
    And composability lets you create business DSLs and macros on top of that. I've used this to good effect with Rails' Arel, but since it is still Ruby, you can't go as far unless you're willing to parse the Ruby into something more legible, like S-expressions.
- its2010already 15y ago
  It does make sense to be able to define business/application logic as reusable query components. For example, if your PERSON entity has TITLE, FIRST_NAME and LAST_NAME fields, that little bit of logic that concatenates them together in the right way to form a full name is something that is useful to define. Likewise, setting up a join of tables that is used many times in your application is something that Korma will let you do. Even though these components may be implementable on the database side (as user defined functions or views), it may not always be possible (or desirable) to do that way.
- pradocchia 15y ago
  I spend much of my time in the database, legacy and greenfield, and I would love a better DSL than SQL, something that ran directly in the database itself and targeted the planner/optimizer rather than just emitting more SQL. I would like to be able to pass a WHERE clause to a function, or a SELECT statement to a procedure, not just simple ints, or strings to be exec'd.
zzzeek 15y ago
a SQL DSL and an ORM aren't really the same thing IMHO. ORMs are about linking objects in memory to database rows, whereas the SQL DSL is just about a thin layer over the rendering of a SQL string, not unlike how you could compose a DOM object of an XML or HTML document and get a string out of it. I'm not a Clojure user but as I have some authority on this subject I can illustrate your query as SQLAlchemy: select([table.c.col1, table.c.col2, func.some_function(). over( partition_by=[table.c.col1, table.c.col2], order_by=[table.c.col1,table.c.col2]) .label('some_ck')]) .where(<some condition>)
- msluyter 15y ago
  I guess I've been corrupted by working in a heavily database oriented environment, and further corrupted by the fact that it's Oracle, but for most of our own use cases, plain SQL is preferable to this. We'll probably have to run it ad hoc against the database anyway to figure out what it's doing and/or tune it. Many of our queries go into the hundreds of lines[1][2], so anything that obscures the underlying SQL itself tends to make life more difficult. But perhaps it's just a matter of getting used to a different format. [1] Legacy environment. [2] Edited to add: most of our newer apps don't actually invoke huge queries from the app layer. We now tend to layer a view or pl/sql table function on top of the hairy query, so that at the app level it's mostly "select * from blah." For that simple a query, a DSL like this is probably fine, albeit, unnecessary.
  - ghotli 15y ago
    SQL that is hundreds of lines long can be hard to follow and harder to maintain. SQL is probably the fastest path to execution but perhaps not the most testable and maintainable. Perhaps that's the lens to view it through.
  - zzzeek 15y ago
    I wrote SQLAlchemy after spending about eight years in three all-Oracle jobs one after the next, and I wrote it specifically around the use case of being able to compose hundreds-of-lines queries from smaller pieces.

by tom_b 15y ago

jwr 15y ago

I think writing DSLs for SQL makes little sense. SQL is already a declarative query DSL, why not just use it? Putting another declarative layer on top buys you little (syntactic sugar for most libraries, this one additionally seems to deal with one-to-many relations, and I hope that support is read-write, not just read-only) — and isolates you from the database below. In every serious application I've worked on there was a need to use something which the DSL did not support. So why do it at all?

groovy2shoes 15y ago
In this case, an embedded DSL brings all the goodies of Lisp's code-as-data to SQL.
technomancy 15y ago
Raw SQL is not very composable, while Korma emphasizes composability on the second paragraph of the page.
- hosh 15y ago
  And composability lets you create business DSLs and macros on top of that. I've used this to good effect with Rails' Arel, but since it is still Ruby, you can't go as far unless you're willing to parse the Ruby into something more legible, like S-expressions.
its2010already 15y ago
It does make sense to be able to define business/application logic as reusable query components. For example, if your PERSON entity has TITLE, FIRST_NAME and LAST_NAME fields, that little bit of logic that concatenates them together in the right way to form a full name is something that is useful to define. Likewise, setting up a join of tables that is used many times in your application is something that Korma will let you do. Even though these components may be implementable on the database side (as user defined functions or views), it may not always be possible (or desirable) to do that way.
pradocchia 15y ago
I spend much of my time in the database, legacy and greenfield, and I would love a better DSL than SQL, something that ran directly in the database itself and targeted the planner/optimizer rather than just emitting more SQL. I would like to be able to pass a WHERE clause to a function, or a SELECT statement to a procedure, not just simple ints, or strings to be exec'd.

zzzeek 15y ago

a SQL DSL and an ORM aren't really the same thing IMHO. ORMs are about linking objects in memory to database rows, whereas the SQL DSL is just about a thin layer over the rendering of a SQL string, not unlike how you could compose a DOM object of an XML or HTML document and get a string out of it. I'm not a Clojure user but as I have some authority on this subject I can illustrate your query as SQLAlchemy: select([table.c.col1, table.c.col2, func.some_function(). over( partition_by=[table.c.col1, table.c.col2], order_by=[table.c.col1,table.c.col2]) .label('some_ck')]) .where(<some condition>)

msluyter 15y ago
I guess I've been corrupted by working in a heavily database oriented environment, and further corrupted by the fact that it's Oracle, but for most of our own use cases, plain SQL is preferable to this. We'll probably have to run it ad hoc against the database anyway to figure out what it's doing and/or tune it. Many of our queries go into the hundreds of lines[1][2], so anything that obscures the underlying SQL itself tends to make life more difficult. But perhaps it's just a matter of getting used to a different format. [1] Legacy environment. [2] Edited to add: most of our newer apps don't actually invoke huge queries from the app layer. We now tend to layer a view or pl/sql table function on top of the hairy query, so that at the app level it's mostly "select * from blah." For that simple a query, a DSL like this is probably fine, albeit, unnecessary.
- ghotli 15y ago
  SQL that is hundreds of lines long can be hard to follow and harder to maintain. SQL is probably the fastest path to execution but perhaps not the most testable and maintainable. Perhaps that's the lens to view it through.
- zzzeek 15y ago
  I wrote SQLAlchemy after spending about eight years in three all-Oracle jobs one after the next, and I wrote it specifically around the use case of being able to compose hundreds-of-lines queries from smaller pieces.