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

https://github.com/ivanceras/fluent-sql/

by ivanceras • 13 years ago

35 28 13 years ago

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

zeroDivisible• 13 years 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• 13 years ago

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

steve_barham2• 13 years 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.

jhh2• 13 years 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_barham2• 13 years 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• 13 years 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).

ivanceras4• 13 years 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.

lukaseder5• 13 years 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• 13 years 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

lukaseder5• 13 years 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-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-predica...

- Row value expression predicates: 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

Both vendor-specific SQL and embedded SQL have their merits.

jhh2• 13 years ago

This looks very similar to jOOQ in my first impression. http://www.jooq.org/

I wonder if the author was aware of the existence of jOOQ?

ivanceras4• 13 years ago

Hi, i wrote the code 3 days ago. I wasn't aware of jooq, it looks cool though.

jkrasnay2• 13 years 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• 13 years ago

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

icedchai• 13 years ago

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

siddboots2• 13 years 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• 13 years 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).

cpfohl• 13 years 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.

ivanceras4• 13 years ago

would consider adding it

lukaseder5• 13 years ago

That helps, specifically with static imports, e.g.

import static com.example.Query.*;

time4tea• 13 years ago

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

ivanceras4• 13 years 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.

lukaseder5• 13 years ago

ALL CAPS has been discussed on the [jOOQ](http://www.jooq.org) User Group as well: 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• 13 years 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-co...

grandpoobah• 13 years 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

lukaseder5• 13 years ago

What was the tool you found for C#?

jkrasnay2• 13 years ago

Looks cool! I wrote a similar library that's been working out well in my projects:

https://github.com/jkrasnay/sqlbuilder

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

Related Stories