• Hmmmm (unregistered)

    Might be YodaSQL

  • (nodebb)

    I like it. Twice as much WTF for my money today.

  • randy (unregistered)

    I presume jOOQ is an acceptable builder. I'm a fan.

  • Scott (unregistered)

    Perhaps I'm missing something, so please educate me: how is using a SQL builder NOT "just concatenation with extra steps"? I mean, at the end of it all, it's just going to emit the query as a string, right?

  • Hmmmm (unregistered) in reply to Scott

    It checks variable types and escapes/quotes properly.

  • (nodebb) in reply to Scott

    It's all about abstraction levels.

  • (author) in reply to Scott

    I mean, yes, the final output is a string, so at some level, it's going to emit a string of characters, thus making anything "concatenation with extra steps". But, if you have a decent builder API, what it absolutely prohibits is constructing invalid statements: you construct the syntax tree as a data structure, manipulate it, and then only when it goes to the database, do you convert it to SQL. The canonical representation is the syntax, not the string, and it's an exception to construct anything but a valid statement.

  • Vilx- (unregistered)

    Personally I've never understood the appeal of builders. What you end up is the same thing as SQL, except a lot more verbose with a lot more noise. Reading it is an order of magnitude harder than reading just a plain SQL query. Not to mention the fact that you'll usually have to give up all the special features your particular RDBMS might have and need to settle for lowest common denominator amongst all supported RDBMSes. Yes, concatenation is the other extreme, but why not just fixed SQL statements with parameters? That covers about 90% of your everyday needs.

  • my name (unregistered)

    I'm in favor of stored procedures and on the client side using parameterized queries

  • Tim R (unregistered) in reply to my name

    Sorry I can't be doing with stored procedures. inevitably you end up with application logic split between 2 different codebases and possibly running on separate servers.

  • (nodebb) in reply to Tim R

    +1 for my code. But if someone else, often someone with just a spreadsheet, needs to connect to the data, views and SPs and no table access is mandatory. Keeps implementation details hidden and the interface and data clean.
    (This comment regards smaller internal applications.)

  • DavidS (unregistered)

    If you don't link stored procedures, just store the SQL query as a file in your code base and use parameterize queries to access it. Then you SQL is under source control and is more readable than string concatenation or builder syntax.

  • (nodebb) in reply to Scott

    Because that's not what a decent SQL builder does at all. There are three corner stone where they shine:

    • Security
    • Validation
    • Optimization

    All those three come down to three core issues manually generated SQL will face:

    • Database quirks
    • Parameter quirks
    • Version quirks

    In other words, since every vendor implements their database in a specific way and changes it over time, it is humanly implausible to generate well optimized, valid SQL since there's already hundreds of permutations out there and even if you keep track of them, it will result in a very long if-else unreadable chain.

    Good examples are:

    • How nested group selects are often cheaper than joins on MS SQL compared to other databases.
    • How NULL parameters greatly change the behavior how queries perform compared to non-NULL parameters.
    • How Oracle is famous for selling bugs as "features" while taking a ton of time to fix them. During those years, you need to generate different and often less performant SQL as a work-around.

    So yeah, if you do SQL by-hand you're just doing it wrong. Even if you put in the effort, it would take ages and result in unreadable code. Not to mention that you have to revisit the code over and over again, since behaviors constantly change even with bug fixes. So even decent manually generated SQL may be garbage tomorrow.

  • (nodebb) in reply to Scott

    how is using a SQL builder NOT "just concatenation with extra steps"?

    How is using a high level language compiler not just assembly with extra steps? I mean, at the end of it all, it's just going to emit the code as machine code.

    Well what are the extra steps?

    The builder will generate a type safe query.

    The builder will generate a syntactically correct query

    The builder will properly handle parameters.

    The builder can adjust the output to the SQL dialect of the target database.

    I think these are enormously valuable extra steps.

  • Cmdr Javik (unregistered)

    In my cycle, those who wrote SQL queries using builders and concatenation were burried in the desert up to their necks and we let the wildlife feast on their eyes.

  • 516052 (unregistered) in reply to MaxiTB

    In theory you are correct. Under realistic conditions however you do not have to care about different vendors and versions of SQL. All you have to care about is the one used by your organization. Vendor changes happen basically newer and updates almost as rarely.

    And at that point well written hand crafted SQL can and will in fact beat any builder simply because you can afford to spend autistic amounts of time and effort on optimizing your code for that precise single use case where as they have to spend time on other things as well.

  • ichbinkeinroboter (unregistered) in reply to Vilx-

    agree. SQL is much simpler to understand, maintain and optimise than a multi-levels deep SQL builder iibrary. But I have a relationship with SQL that goes back to 1989... maybe I am underestimating how useful builders might be to others. Bit I kinda think a dev should be pretty easily abe to learn it?

Leave a comment on “Build Up”

Log In or post as a guest

Replying to comment #700038:

Log Out

« Return to Article