5 ms·

Show HN: fluent SQL – a minimalistic SQL builder for Java

35 points by ivanceras 13y ago

A minimalistic Fluent SQL API for Java built with one compilation unit aimed to resemble the code to your original SQL code

zeroDivisible 13y ago
This is opinion only, so please don't take this as me being negative: I really like the idea, but there's one thing which need to happen for this to be upstream. Most decent IDE's (Idea and few others) are clever enough to understand the SQL between quotation marks and if connected to database will give you instant errors if something is incorrect - misspelled names, missing commas, etc. Sometimes this can be a big time saver.
ww520 13y ago
Looks cool. I love DSL style API for chaining calls. Make it so simple.
steve_barham 13y ago
I don't like this. I'm strongly of the opinion that SQL is a beautiful language, able to express in terse yet readable statements operations which would take many pages of Java to execute. Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL? Please don't take this as a dig at you or your code - everyone has an itch to scratch, and I'm quite sure writing this was fun.
- jhh 13y ago
  One advantage is that the compiler checks your queries. In case of the jOOQ library, it also makes your queries typesafe and abstracts away some specifics of SQL dialects. Having typesafety transcend from the database to your "main" language is of advantage so long as you are using a statically typed language anyway. All this is possible while maintaining much of the expressive power of SQL.
  - steve_barham 13y ago
    I'm with you on the type checking (although this particular library doesn't seem to offer much in that space). That said, I'd argue that modern tooling (e.g. IDEA, and by association the static analysis toolchain in Teamcity) are capable of identifying and validating embedded SQL, particularly when provided with database metadata. Essentially, I'm deeply uneasy about embedding translators from one language into another. As an example, not many people like using BigDecimal to describe arithmetic operations, despite it having a fluent syntax and improved type safety (e.g. around implicit conversions) compared to just writing an expression.
- ocharles 13y ago
  SQL as a string is not composable. I cannot give you a small query and have you arbitrarily join against it - I have to give you bits of strings and you have to glue them together in the right way. Expressing SQL as its AST lets you write combinators between ASTs, and thus get at least some composability. The stronger your type system, the better guarantees you can get that the composition is sound (closure).
- ivanceras 13y ago
  Manipulating string in java/IDE is a bit odd. I wrote the code in order to get away with the awkward strings manipulation in java. Plus I have this generator tools which generates list of schema-tables and table-columns combinations. All of my SQL code is check at dev time by the power of IDE static analyzer. So I don't have to deal with misspelled columns,tables anymore at runtime.
  - lukaseder 13y ago
    Do you have an example of this static analysis of table/column names? From what I see in your documentation, these things are string-based...? How can they be statically analysed?
- masklinn 13y ago
  > Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL? That's not what query builders (/expression languages) do. They just express the SQL in the host language to allow for compositions and the like. Because like it or not, expressing SQL as a string is a pain in the ass to compose, you can't easily build a query incrementally. With a query builder, you can. You're still writing SQL, it's not an object layer/ORM and it has (should have) a fairly direct mapping to SQL. You're just writing SQL in a way which fits better in the host languages. Oh, it can also provide some measure of database-independence by having database-specific backend take care to handle specific incompatibilities between databases. For instance for string concatenation[0] the standard is `||` which works in SQLite, Postgres and Oracle, MSSQL uses `+` instead of `||` and in MySQL `||` is the boolean OR unless you've activated a specific sql mode so you've got no choice but to use CONCAT (you could accept a performance hit and use CONCAT everywhere, except Oracle only supports an arity of 2 and SQLite doesn't support this function at all). That's the kind of stuff a builder can handle for you under the hood, providing much improved cross-platform compatibility. [0] http://troels.arvin.dk/db/rdbms/#functions-concat http://troels.arvin.dk/db/rdbms/#functions-concat
- lukaseder 13y ago
  SQL is a beautiful language, indeed. Writing vendor-specific SQL in stored procedures and views is great. But embedding SQL in other languages such as Java is still very hard in 2013. jOOQ is the first and only API to fully embed the SQL language as an internal domain-specific language in Java, using an internal BNF for API definition: http://blog.jooq.org/2012/01/19/jooqs-fluent-api-in-bnf-notation http://blog.jooq.org/2012/01/19/jooqs-fluent-api-in-bnf-nota... Being a DSL with an AST, advanced SQL transformation can be implemented to standardise SQL where common SQL constructs aren't supported. These include: - DISTINCT predicate: http://blog.jooq.org/2012/09/21/the-is-distinct-from-predicate http://blog.jooq.org/2012/09/21/the-is-distinct-from-predica... - Row value expression predicates: http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions http://blog.jooq.org/2013/05/03/sql-query-transformation-fun... - Derived column lists: http://blog.jooq.org/2013/01/07/simulating-the-sql-standard http://blog.jooq.org/2013/01/07/simulating-the-sql-standard Both vendor-specific SQL and embedded SQL have their merits.
jhh 13y ago
This looks very similar to jOOQ in my first impression. http://www.jooq.org/ http://www.jooq.org/ I wonder if the author was aware of the existence of jOOQ?
- ivanceras 13y ago
  Hi, i wrote the code 3 days ago. I wasn't aware of jooq, it looks cool though.
- jkrasnay 13y ago
  I think jOOQ is a little more heavyweight. It introspects your database and builds a Java class structure that reflects the schema. More typesafe, certainly, but more of a commitment.
mimog 13y ago
Nice, but anyone needing something like this should probably consider looking at jooq.
icedchai 13y ago
Looks cool, but personally I'd rather just use regular SQL.
- siddboots 13y ago
  The advantage here is language integration. You can write a query, wrap it in an object, add clauses to it dynamically, combine it with another query using some clever logic, and so on.
  - ygra 13y ago
    That's probably the only place where I would use it, when I have to construct a query on the fly based on various conditions. For a plain static query I think the regular SQL is much easier to read and understand (let alone paste into a query window to test its output).
    - siddboots 13y ago
      Building queries dynamically is the intended use case. This wasn't designed as a replacement for plain SQL.
deleted 13y ago
[deleted]
cpfohl 13y ago
Very slick. My only suggestion is to allow a bit more terseness by adding static versions of all the calls that can happen at the beginning of the Query. The static versions would just create a new SQL object and pass the call on to the SQL object's call. Instead of: new SQL().SELECT_ALL() You could have Query.SELECT_ALL() Ideally Java would be cool with having Static and non-Static methods with identical signatures and names, but alas, looks like a static class Query or whatever would be the way to do this.
- ivanceras 13y ago
  would consider adding it
- lukaseder 13y ago
  That helps, specifically with static imports, e.g. import static com.example.Query.*;
time4tea 13y ago
Hmm looks a bit like Squiggle SQL, http://code.google.com/p/squiggle-sql/ http://code.google.com/p/squiggle-sql/ but less
- ivanceras 13y ago
  I've seen this, but it was a poor choice of words. One thing that I also want to add is the ALL CAPS on the SQL keywords, the code adds readability.
  - lukaseder 13y ago
    ALL CAPS has been discussed on the [jOOQ](http://www.jooq.org http://www.jooq.org) User Group as well: https://groups.google.com/d/msg/jooq-user/h9qDY83H5gg/WI2XSjnidlMJ https://groups.google.com/d/msg/jooq-user/h9qDY83H5gg/WI2XSj... I guess it's a matter of taste, but it will certainly resolve ambiguities around Java's reserved words "case", "else", and "for". A future version of jOOQ will ship with an API generator that can generate one or the other version of the DSL. Given that jOOQ is based on a somewhat formal BNF, such an evolution won't be too hard to implement.
andrewcooke 13y ago
is anyone wants something similar for c, i am looking for beta users (users that are happy with a beta release, not users less impressive than alpha users...) of c-orm, which includes (a very minimal version of) this - https://bitbucket.org/isti/c-orm/wiki/SQL#markdown-header-conditional-statements https://bitbucket.org/isti/c-orm/wiki/SQL#markdown-header-co...
grandpoobah 13y ago
I wrote something like this for C# just yesterday as I couldn't find an sql builder with a real fluent interface. I found one which pretended to be fluent by returning itself after each method call, but that completely misses the point, because you want each call to create a mutation but leave the original in tact. That allows me to build a query using all of the WHEREs and then branch it off into two separate queries.. one for COUNT and one to select the actual data. Just wondering.. did you create this to be used in the read layer of a DDD/CQS based system? Edit: actually looking at the source it appears this one is similar to the one i saw in C# where each call just returns the same builder rather than a new version
- lukaseder 13y ago
  What was the tool you found for C#?
jkrasnay 13y ago
Looks cool! I wrote a similar library that's been working out well in my projects: https://github.com/jkrasnay/sqlbuilder https://github.com/jkrasnay/sqlbuilder

35 points by ivanceras 13y ago

A minimalistic Fluent SQL API for Java built with one compilation unit aimed to resemble the code to your original SQL code

zeroDivisible 13y ago

This is opinion only, so please don't take this as me being negative: I really like the idea, but there's one thing which need to happen for this to be upstream. Most decent IDE's (Idea and few others) are clever enough to understand the SQL between quotation marks and if connected to database will give you instant errors if something is incorrect - misspelled names, missing commas, etc. Sometimes this can be a big time saver.

ww520 13y ago

Looks cool. I love DSL style API for chaining calls. Make it so simple.

steve_barham 13y ago

I don't like this. I'm strongly of the opinion that SQL is a beautiful language, able to express in terse yet readable statements operations which would take many pages of Java to execute. Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL? Please don't take this as a dig at you or your code - everyone has an itch to scratch, and I'm quite sure writing this was fun.

jhh 13y ago
One advantage is that the compiler checks your queries. In case of the jOOQ library, it also makes your queries typesafe and abstracts away some specifics of SQL dialects. Having typesafety transcend from the database to your "main" language is of advantage so long as you are using a statically typed language anyway. All this is possible while maintaining much of the expressive power of SQL.
- steve_barham 13y ago
  I'm with you on the type checking (although this particular library doesn't seem to offer much in that space). That said, I'd argue that modern tooling (e.g. IDEA, and by association the static analysis toolchain in Teamcity) are capable of identifying and validating embedded SQL, particularly when provided with database metadata. Essentially, I'm deeply uneasy about embedding translators from one language into another. As an example, not many people like using BigDecimal to describe arithmetic operations, despite it having a fluent syntax and improved type safety (e.g. around implicit conversions) compared to just writing an expression.
ocharles 13y ago
SQL as a string is not composable. I cannot give you a small query and have you arbitrarily join against it - I have to give you bits of strings and you have to glue them together in the right way. Expressing SQL as its AST lets you write combinators between ASTs, and thus get at least some composability. The stronger your type system, the better guarantees you can get that the composition is sound (closure).
ivanceras 13y ago
Manipulating string in java/IDE is a bit odd. I wrote the code in order to get away with the awkward strings manipulation in java. Plus I have this generator tools which generates list of schema-tables and table-columns combinations. All of my SQL code is check at dev time by the power of IDE static analyzer. So I don't have to deal with misspelled columns,tables anymore at runtime.
- lukaseder 13y ago
  Do you have an example of this static analysis of table/column names? From what I see in your documentation, these things are string-based...? How can they be statically analysed?
masklinn 13y ago
> Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL? That's not what query builders (/expression languages) do. They just express the SQL in the host language to allow for compositions and the like. Because like it or not, expressing SQL as a string is a pain in the ass to compose, you can't easily build a query incrementally. With a query builder, you can. You're still writing SQL, it's not an object layer/ORM and it has (should have) a fairly direct mapping to SQL. You're just writing SQL in a way which fits better in the host languages. Oh, it can also provide some measure of database-independence by having database-specific backend take care to handle specific incompatibilities between databases. For instance for string concatenation[0] the standard is `||` which works in SQLite, Postgres and Oracle, MSSQL uses `+` instead of `||` and in MySQL `||` is the boolean OR unless you've activated a specific sql mode so you've got no choice but to use CONCAT (you could accept a performance hit and use CONCAT everywhere, except Oracle only supports an arity of 2 and SQLite doesn't support this function at all). That's the kind of stuff a builder can handle for you under the hood, providing much improved cross-platform compatibility. [0] http://troels.arvin.dk/db/rdbms/#functions-concat http://troels.arvin.dk/db/rdbms/#functions-concat
lukaseder 13y ago
SQL is a beautiful language, indeed. Writing vendor-specific SQL in stored procedures and views is great. But embedding SQL in other languages such as Java is still very hard in 2013. jOOQ is the first and only API to fully embed the SQL language as an internal domain-specific language in Java, using an internal BNF for API definition: http://blog.jooq.org/2012/01/19/jooqs-fluent-api-in-bnf-notation http://blog.jooq.org/2012/01/19/jooqs-fluent-api-in-bnf-nota... Being a DSL with an AST, advanced SQL transformation can be implemented to standardise SQL where common SQL constructs aren't supported. These include: - DISTINCT predicate: http://blog.jooq.org/2012/09/21/the-is-distinct-from-predicate http://blog.jooq.org/2012/09/21/the-is-distinct-from-predica... - Row value expression predicates: http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions http://blog.jooq.org/2013/05/03/sql-query-transformation-fun... - Derived column lists: http://blog.jooq.org/2013/01/07/simulating-the-sql-standard http://blog.jooq.org/2013/01/07/simulating-the-sql-standard Both vendor-specific SQL and embedded SQL have their merits.

jhh 13y ago

This looks very similar to jOOQ in my first impression. http://www.jooq.org/ http://www.jooq.org/ I wonder if the author was aware of the existence of jOOQ?

ivanceras 13y ago
Hi, i wrote the code 3 days ago. I wasn't aware of jooq, it looks cool though.
jkrasnay 13y ago
I think jOOQ is a little more heavyweight. It introspects your database and builds a Java class structure that reflects the schema. More typesafe, certainly, but more of a commitment.

mimog 13y ago

Nice, but anyone needing something like this should probably consider looking at jooq.

icedchai 13y ago

Looks cool, but personally I'd rather just use regular SQL.

siddboots 13y ago
The advantage here is language integration. You can write a query, wrap it in an object, add clauses to it dynamically, combine it with another query using some clever logic, and so on.
- ygra 13y ago
  That's probably the only place where I would use it, when I have to construct a query on the fly based on various conditions. For a plain static query I think the regular SQL is much easier to read and understand (let alone paste into a query window to test its output).
  - siddboots 13y ago
    Building queries dynamically is the intended use case. This wasn't designed as a replacement for plain SQL.

deleted 13y ago

[deleted]

cpfohl 13y ago

Very slick. My only suggestion is to allow a bit more terseness by adding static versions of all the calls that can happen at the beginning of the Query. The static versions would just create a new SQL object and pass the call on to the SQL object's call. Instead of: new SQL().SELECT_ALL() You could have Query.SELECT_ALL() Ideally Java would be cool with having Static and non-Static methods with identical signatures and names, but alas, looks like a static class Query or whatever would be the way to do this.

ivanceras 13y ago
would consider adding it
lukaseder 13y ago
That helps, specifically with static imports, e.g. import static com.example.Query.*;

time4tea 13y ago

Hmm looks a bit like Squiggle SQL, http://code.google.com/p/squiggle-sql/ http://code.google.com/p/squiggle-sql/ but less

ivanceras 13y ago
I've seen this, but it was a poor choice of words. One thing that I also want to add is the ALL CAPS on the SQL keywords, the code adds readability.
- lukaseder 13y ago
  ALL CAPS has been discussed on the [jOOQ](http://www.jooq.org http://www.jooq.org) User Group as well: https://groups.google.com/d/msg/jooq-user/h9qDY83H5gg/WI2XSjnidlMJ https://groups.google.com/d/msg/jooq-user/h9qDY83H5gg/WI2XSj... I guess it's a matter of taste, but it will certainly resolve ambiguities around Java's reserved words "case", "else", and "for". A future version of jOOQ will ship with an API generator that can generate one or the other version of the DSL. Given that jOOQ is based on a somewhat formal BNF, such an evolution won't be too hard to implement.

andrewcooke 13y ago

is anyone wants something similar for c, i am looking for beta users (users that are happy with a beta release, not users less impressive than alpha users...) of c-orm, which includes (a very minimal version of) this - https://bitbucket.org/isti/c-orm/wiki/SQL#markdown-header-conditional-statements https://bitbucket.org/isti/c-orm/wiki/SQL#markdown-header-co...

grandpoobah 13y ago

I wrote something like this for C# just yesterday as I couldn't find an sql builder with a real fluent interface. I found one which pretended to be fluent by returning itself after each method call, but that completely misses the point, because you want each call to create a mutation but leave the original in tact. That allows me to build a query using all of the WHEREs and then branch it off into two separate queries.. one for COUNT and one to select the actual data. Just wondering.. did you create this to be used in the read layer of a DDD/CQS based system? Edit: actually looking at the source it appears this one is similar to the one i saw in C# where each call just returns the same builder rather than a new version

lukaseder 13y ago
What was the tool you found for C#?

jkrasnay 13y ago

Looks cool! I wrote a similar library that's been working out well in my projects: https://github.com/jkrasnay/sqlbuilder https://github.com/jkrasnay/sqlbuilder