Hacker News Reader

Comment by FLUX-YOU | Hacker News Reader

SQL strings in source code are worse than ORMs, even if you move it out to a resources file of some kind.

No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source. If you don't do that, you're dropping all of the productivity that syntax checkers do for you and risk losing productivity to typos or other dumb mistakes.

View on HN

There is a middleground too - for instance sqlalchemy in python allows you use code to build queries. It's python, it does quoting right (lowering lots of sqli concerns) and it has an orm layer on top of the query builder.

You get to use the orm for a lot of braindead simple, and mild complexity stuff, and can drop down to the sql builder as it makes sense for complexity or performance reasons.

It also understands the various databases very well, so if you choose e.g. postgres, you can access most of the features without needing much (if any) "raw sql".

I adopt a idea (not remember the original library where I say it) where ALL the sql strings are in a single .sql file.

Exactly as with sql upgrade scripts.

It look like this:

    --name: post-event
    SELECT post_log(@theId, @ENTITY, @ACTION, @CHANGEBY, @DATA, @VERSION)

    GO

    --name: get-location
    SELECT * FROM "Location"
    WHERE
        id = @id

    GO

    --name: list-location
    SELECT * FROM "Location"
    ORDER BY country, state, city;

    GO

Then I just parse this file (note the names with --) once and use this alike (in F#):

    module Location =
    let ENTITY = "Location"
    let SQL_LIST = SQL_CMDS.["list-location"]

    type LocationRecord = {id:int64 option; address:string; country:string; state:string; city:string; version:int64}

    type LocationQuery =
        | All
        | ById of int64

    let query q =
        use con = openConn()

        let toRec = Db.toRecord<LocationRecord>

        match q with
        | All ->
            Db.select con SQL_LIST []
            |> Seq.map toRec

        | ById(theId) ->
            let id = [P("@id", theId)]
            Db.select con SQL_BYID id
            |> Seq.map toRec

And plug a micro-orm (mainly just a very thin layer over ADO.NET in .NET. I do similar over swift and python).

This take me like a few hours. Let me test easily the sql. I can build the sql exactly as I want.

The only cons is the repetition on the scripts - because SQL is a terrible language that not allow composability, like similar to CSS - . Probably I will later use a template parser (like mustache or similar) but I think this is the closest to the holy grail ;)

So, you update a 5,000+ line file every time you need to change a query?

What do you do when you have five similar queries, and each needs to be changed if one changes?

This seems like smaller projects might work, but I'd be concerned about using a strategy on a larger project.

Here's a clojure library designed around it. (https://github.com/layerware/hugsql)

Not a fan of this strategy in particular, but I can see arguments for it.

>What do you do when you have five similar queries, and each needs to be changed if one changes?

In short, I truly utilize a DB with full effects.

I don't follow the mantra of using a DB "only" as a dumb datastore. Something that I get validated as when people use NoSQL and have not option that use all of it.

This mean, that I use views and stored procedures/functions.

This cut a lot. Also, proper modeling of tables also help (a lot). I refactor DBs.

I mean, I truly treat a DB as "code".

Obviously, this strategy is not flawless, and it will benefit from some kind of ORM-ish layer to auto-generate the mechanical parts of the querys. I'm building that part myself.

But so far, is have show to be good.

We are happily using JOOQ with Java + Postgres.

I strongly recommend it. Having statically typed representation of the schema has many pros and the trade-off is a little overhead when changing the db schema to regenerate classes.

Some of the benefits include knowing exactly what part of the code stops compiling after changing the db schema, easily map POJOs to-from query results.

Also, there's simply no pressure that you have to be absolutely certain what each and every knob on your ORM does else you have bad data in your database. Been burned before with Hibernate defaults, and the thought of a junior dev creating wrong conf is just not worth it.

The database is basically the place were you least want a risk of misuse to have any unseen side effects.

>No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source.

Tbh thats currently my preferred methodology, just because it's usually nicer to clean up a mistaken query in something like sequel-pro, and its generally easy to verify correct output visually

And I've had to do the same thing with ORMs like sqlalchemy, and the only real difference is I first have to get the ORM to tell me what it generated (which is a pain, sometimes).

Intellij tools are capable to parse, lint, and autocomplete sql in strings.

To my own surprise, it has become my favorite way to handle queries with multiple/variable joins.

Also, in languages that support HEREDOCS, if you call the HEREDOC "SQL" then VIM and the Intellij editors will syntax-highlight the SQL.

It's not only that: It connects to your database an you have autocompletion for tables, columns and any schema objects, as well as linting. All that inside any string.

By example:

$sql = "select myColFromTableB from tableA";

Inspection will give you have a "unknown column" warning.

(ps: sorry for late reply)

Thank you, I did not know that! It is not working for me, but I'll poke and peek around for a setting.

Maybe I'm doing it wrong, but I like to keep simple queries in an ORM, and then execute anything complicated as a stored procedure.

No, that's a perfectly practical approach. You're much less likely to make mistakes on procedure names vs. full queries. The trade off is managing procedure versioning and deployments of procedures.

Ultimately it's a style preference but I've seen larger projects with thousands of lines of SQL in source with some of those queries spanning 6-7 joins with dozens of where clauses (plus string manipulation functions, ugh), so that is why I'm pretty fiercely against SQL in strings.

Sometimes I see monster queries like that and think that they could have saved themselves several lines of code if they were willing to export the data and run some of the logic in the native language.

Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.

Well, it depends.

At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made.

Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server.

But either way, I wouldn't dump a monster like that right in source code - that's crazy!

What do you mean by "set-manner than a C-in-SQL manner"?

(Not OP, but I feel like I've seen this phenomenon before.) In C, the natural idiom would be to read in a giant file line by line, doing something with each line and eventually doing something else based on some summary of everything read so far. Lots of dumb SQL code also does something like this, e.g. by generating some ginormous join and then whittling it down to something usable. Better SQL code filters before the join, and often uses indexes to do so.

There are people in this world who do things like use cursors in place of joins, for example.

The latency of the network is comparatively very high.

This is why the N+1 is such a big deal and you can get results magnitude faster of you query through the database.

> No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source.

And how exactly are you making sure your ORM code works?

I rely on the simple operations to be pretty reliable. Most have some way to get to the actual SQL that will be sent to the DB though, so you can check that if you're suspicious.

That sounds more like a tooling problem.

Actually, I started to put SQL directly into my source code. IntelliJ, for instance, has really good support for embedded SQL code (syntax highlighting, auto-completion, executing queries including placeholders etc.).

SQL strings should always be in the hands of a DBA, not a developer. The RDBMS should get your request in the form of a SP call and get the data you need in the form of cursors.

So, SQL strings belong to the metal, not the UI.

There are plenty of devs out there perfectly capable of writing sql (and plenty of DBAs who are actually just developers). It's not rocket science and is entirely based in CS.

Sounds like you should face your fears and learn SQL if you think it's so arcane that somebody else needs to write every and all queries for you. It's not as hard as you think.

Though there are certainly cases where it helps to be able to find someone more experienced and knowledgable than you.

Or perhaps you're a DBA that's tooting your horn so loudly that you don't believe anyone can do what you do.

So wrong.

I thought this thinking was 10 years dead at this point.

That ... depends where you work. Some places (I've, uh, heard, yes) have DBAs that won't let the devs touch the database and either enforce ORM usage or vet every query before allowing it to release.

And, to be fair, having seen some of the abominations people have committed with SQL, I only half blame them.

Well, it's simply an organizational decision that a company is making at a certain scale. Division of labor and expertise. Fair enough.

But the issue is with saying "no developer should be writing SQL strings, ever".

It's like saying "no designer can handle HTML, leave that to the professionals".

Reply on news.ycombinator.com