Hacker News Reader

Comment by mattigames | Hacker News Reader

So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way?

Then you call your alternative anti-orm and it generates... orm specs, and then you say "migration commands based on the git history"... what does that even mean? What does git history has to do with the database?

View on HN

Any SQL wrapper worth its salt provides some mechanism to specify positional (or even named) parameters within that string (without resorting to string interpolation or concatenation) and pass them in when executing the query. Usually it'll be something like

    foos = execute_sql("SELECT * FROM foo WHERE bar = ? AND baz = ?", [bar, baz])

If you're resorting to concatenating strings, then you're almost certainly opening yourself up to SQL injections (and rather needlessly, I might add!).

Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.)

Also, getting the library to properly expose the formatting interface is also a trick. We would do massive loads/dumps that we did not want to use Python for (but we worked in Python) as the DB-API pulls the entire result set into RAM, and we were pulling >RAM (and we didn't want to do something lower-level w/ the driver). We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. And the Python library really doesn't (last I checked) have a public interface to the templater portion of the library. (I think this is b/c PG just sends the parameters, and the binding is done server-side. So, really, psql needs an interface to supply those values as arguments to the CLI.)

> We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. [...] So, really, psql needs an interface to supply those values as arguments to the CLI.

I haven't tried this before, so caveat emptor, but per psql's manpage this should be supported:

    QUERY="SELECT * FROM sometable WHERE name = :'foo'"
    echo $QUERY | psql -v foo=asdf # ... other args ...

Basically: you can use colon-prefixed variables in the query text (with the variable name optionally quoted to indicate how the result should be quoted, apparently?), and set them with the -v option (which is equivalent to using the \set command within the query string itself).

This also happens to work for table names, at least per the example in the manpage:

    testdb=> \set foo 'my_table'
    testdb=> SELECT * FROM :"foo";

Except in this case it'd be more

    echo 'SELECT * FROM :"foo";' | psql -v foo=my_table # ... other args ...

Still no option there for positional parameters, but it's a start, right?

> Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.)

Thin SQL wrappers/dsl's like the SQLAlchemy expression library are great. IMO those thin wrappers are what most people should reach for first, over a full blown ORM. A good mimimal sql wrapper will:

* Save people from pain, problems, and security issues involved in building queries through string manipulation.

* Map very closely to raw SQL

* Make it easy to compose query expressions and queries, using language native features.

* Help devs develop their SQL skills. Learning the wrapper IS learning SQL, for the most part.

I have a hard time seeing many good reasons to add any more layers of abstraction on top. That last point is particularly important to me. My first exposure to many advanced SQL techniques, was through such libraries. Since the code maps to sql quite naturally, that learning can be applied in a much wider variety of contexts. Teach someone how to do aggregations using Django's ORM, and you've taught them to do aggregations in Django's ORM, and basically nowhere else.

I think the issue the parent is referring to is when you have optional clauses in your statement. Like if you have a table with 5 update-able columns (where someone might want to update any 1 to 5 columns that you don't know in advance), and you want some generic code that updates a row, if you're writing raw SQL then you have to do concatenations to build up the "set clause":

    UPDATE sometable SET {set clause} WHERE id = ?

The "set clause" might just be "foo = ?", but it might be "foo = ?, bar = ?", or "foo = ?, baz = ?", etc.

SQL's got you covered here:

    UPDATE sometable SET
        foo = COALESCE(?, foo),
        bar = COALESCE(?, bar),
        baz = COALESCE(?, baz),
        bam = COALESCE(?, bam),
        bat = COALESCE(?, bat)
    WHERE id = ?

And then you'd pass in NULLs for any columns not being updated at the moment.

Confirmed that this works exactly as expected in SQLite:

    $ sqlite3 optional_column_test.db
    sqlite> CREATE TABLE sometable (id INTEGER PRIMARY KEY, foo, bar, baz, bam, bat);
    sqlite> INSERT INTO sometable (foo,bar,baz,bam,bat) VALUES ('You', 'are', 'a', 'bold', 'one');
    sqlite> SELECT * FROM sometable;
    1|You|are|a|bold|one
    sqlite> UPDATE sometable SET
       ...> foo = COALESCE(NULL, foo),
       ...> bar = COALESCE('ain''t', bar),
       ...> baz = COALESCE(NULL, baz),
       ...> bam = COALESCE(NULL, bam),
       ...> bat = COALESCE(NULL, bat)
       ...> WHERE id = 1;
    sqlite> SELECT * FROM sometable;
    1|You|ain't|a|bold|one

I'd never heard of COALESCE before! I'm very very far from a SQL expert, but I'm surprised that's never popped up for me before.

Are there any bad performance implications here? Like, will it still rewrite columns that don't need to be updated?

Good question.

The answer to that is almost certainly "it depends on your DB". Most query planners should be smart enough to not try to write anything, but some are smarter than others. For example, SQL Server (if I'm understanding right) does perform a log write even if it should be obvious that the value won't change: https://www.sql.kiwi/2010/08/the-impact-of-non-updating-upda...

That being said, I'd skeptical of the performance impact (if any) being all that significant; even in a worst-case scenario of "yeah, it's going to stupidly write every field every time", the columns for each row should be pretty close together both in-memory and on-disk, so unless you're writing a whole bunch of giant strings or something at once (and even then) there shouldn't be a whole lot of seeking happening.

tl;dr: assuming there's a minor performance impact is reasonable, but it should probably be measured before trying to optimize it away.

So you mean I'm writing code like this, in a language somewhat like Typescript but simplified to my end.

   function update(id: ID_TYPE, value: Partial<sometable>) {
      const set_clause = value.keys.map(k => k + " = ?").join(", ")
      const params = value.values.concat([id])

      db.query("UPDATE sometable SET " + set_clause + " WHERE = ?", params)
   }

How is code not terrible? It shouldn't pass code review. Once you concatenate strings to generate your sql, all bets are off. There will be a day when your object doesn't match your assumptions. Either you have a series of different functions, each of which will change under different circumstances; or you should just be generating this function like this directly from the schema in which case you might as well just generate code you want at build time so there's string concatenations anywhere.

Is there some other circumstance when you want that?

It is terrible. That's the problem. No one wants to write code like that, so they turn to an ORM to do it for them, in a hopefully safer way.

So I want to respond to this because there is a genuine question behind your rhetoric. And that question is "How do I cleanly do complex things in SQL without an ORM?"

And there is an answer to that. The idealistic answer is good DB design from the start. The realistic answer is by using views (sometimes materialized views) to create a clean representation out of odd tables.

The monster query can be decomposed into simple and clean operations against these views (and if necessary, views upon views).

The only caveat is you need a SQL expert to understand the performance if you want to do this at scale.

I wrote SQLAlchemy after a five year job where all we did was work with enormous views in an Oracle database. They performed nightmarishly bad. The slightest attempt to query from one of these views using something as heretical as a join would kill the database. The source code for the views was locked up tight by DBAs who had no idea how our application worked, didn't care, and they could never be bothered to help us with their horrendously inefficient views or to give us new ones that our application sorely needed in order to get data from the database in an efficient manner.

SQLAlchemy exists exactly because of the pain the "just use views" approach causes. The entire point is that you can structure a query of arbitrary complexity in a succinct manner using Python structures, while not losing any of the relational capabilities. Of course you can write queries that are too complex, if you're then lucky enough to be able to have materialized views at your disposal, you can turn the SQL you wrote into one. But that's an optimization you can use if you need it, or not.

On the one hand the article itself is offering a anecdote about being unhappy with ORMs. On the other hand you have an experience in which you were unhappy with views.

That would leave us at an impasse (he said / she said), unless we get more nuanced. So let's.

It sounds like some of your frustration is at some DBAs you knew. Firstly I've never worked with a DBA, I'm just a senior software engineer / DevOps and to me that includes an intimate knowledge of SQL perfomance, so maybe this is why I don't mind an in-database approach.

I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).

Now maybe you agree with that, and see ORMs as a tool for the kind of job where the dev is locked out of the database. If so then I can't really disagree as I haven't worked at such places much at all.

> I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).

if you have the option to use materialized views, that can be helpful when appropriate, however materialized views have their own issues, not the least of which is that they need to be refreshed in order to have current data, they can take up a vast amount of storage, not to mention a lot of databases don't support them at all. It should not be necessary to emit CREATE MATERIALIZED VIEW with super-user privileges and to build the infrastructure to store them and keep them up-to-date for every query one needs to write or modify that happens to have some degree of composed complexity.

you don't need an ORM for these queries either, just a composable query builder (like SQLAlchemy Core) that allows the composition task to be more easily organizable in application code.

none of this means you can't use views but it's unnecessary to dictate that they should be the only tool available for dealing with large composed queries.

SQL Alchemy will spit out SQL query. Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.

> Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.

No, on two levels. Firstly, there are many features in SQL that an ORM will not support (e.g. no substitute for a materialized view).

Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column). Part of being expert in DB technologies is knowing how/why some ways of writing a query are very fast, and others are very slow. The idea of an ORM is to hide that complexity, which is fine for a toy todo-mvc app. But once you start querying tables with 100k rows you need to understand that complexity and master it if you want to write a fast query.

> Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column).

SQLAlchemy supports case-insensitive searches, per-column / per-expression collation settings, index and other SQL hint formats for all databases that support them, e.g. SQLAlchemy's query language supports most optimizing SQL syntaxes, with the possible exception of very esoteric / outdated Oracle things like which table you list first in the FROM clause (use index hints instead). We are adding esoteric performance features all the time to support not just SQL-level tricks but driver level tricks too which are usually much more consequential, such as the typing information applied to bound parameters matching up for indexes as well as special driver-level APIs to improve the speed of bulk operations.

SQLAlchemy has been around for thirteen years, plenty of SQL experts have come along and requested these features and we implement them all. Feel free to come up with examples of SQL-expert level performance optimizations that SQLAlchemy doesn't support and we'll look into them.

Yeah, don't get me wrong, I haven't used SQL alchemy and I'm not trying to besmirch it. I'm not even sure we disagree.

What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work. My point about views is that in my experience, the answer to disgusting queries is cleaning up the DB design (and views can be the tool to accomplish this). My point about case-insensitive searching (you can create a case-insensitive index if you want) is that a lot of db-performance stuff just can't be solved on the query side alone anyways.

It sounds like SqlAlchemy is designed with a lot of flexibility around how queries are run, so maybe you agree that understanding what's going on beneath the hood is important to handle these complicated cases.

> What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work.

Thank you for this response and I agree, we are likely on the same page. I don't know that anyone actually espouses that philosophy. This is the anxiety that ORM skeptics have, and certainly you get beginners who rush into using ORMs not knowing what they are doing, but if someone wants to be a DB expert, I'm pretty sure they go to read about databases :) These ORM beginners will fail either with the ORM or without out. I guess the idea is you'd prefer they "fail fast", e.g. the ORM covers for them while they proceed to screw up more deeply? This is arguable; if you've seen much of the non-ORM raw DB code I've seen, it too fails pretty hard. But even with this argument, if ORMs produce the problem of incompetents who are not found out fast enough, why hoist the denial of useful tools to those developers who do know what they're doing.

You have shifted the discussion from ability of DB engine to optimize queries to the ability of a developer to write fast queries.

But in any case, we are not discussing some abstract ORM whose "idea is to hide complexity", but SQL Alchemy whose idea (one of them, at least) is to allow you to write composable queries yet still retain full power of SQL.

You are correct about needing to understand the underlying dB to be able to optimise it. I consider myself lucky to have come from a raw sql background and to have a deep understanding about how the query planners work.

You are incorrect in your other assertion. Sqlalchemy allows for optimising your queries at runtime in a way that just isn’t available to sql views. The beauty of sqlalchemy is that allows for arbitrary composition of blocks of sql. I too have had to fight with dbas in a previous life because they didn’t want me to construct a query in code (non sqla), but I eventually won because I was magnitudes faster because I had runtime knowledge they couldn’t use.

Many of us also know stories about how ORMs can be slow if developers don't pay sufficient attention. In some cases told by database experts who see the query code locked up tight by the app team, etc :)

Having a separate "DBA" from the developers or having an adverserial relationship with said person is not really a technical problem I think.

I've had similar issues, but I got the added benefit of being "DBA-splained" on why what we were doing was terrible.

I feel like if a DBA keeps thinking of the database as the solution to all of the problems (by implementing views, triggers, stored procedures, etc) then maybe that person should be willing to support them, otherwise they really shouldn't complain that the developers moved to an ORM.

Okay, obviously I've never worked in an enterprise enough company. I've always assumed a DBA was just a member of a backend team who likes thinking about SQLy stuff. Or a sysadmin who upgrades the computer the db runs on. Basically, a hat someone wears.

Do I seriously make the correct assumption that the people who are writing the backend of many apps are actually separated from the people who decide how the data those apps process the data, and you may have no direct say about the database schema? that effectively just a client - perhaps the only client, but nothing more than a client.

Obviously you've described the disadvantage of this approach; what advantage does it have?

It makes some sense when the data is extremely valuable, like in a bank. As a developer you can only access the db through stored procedures created by the database team. Which limits the damage you can do. I haven't worked in a big enterprise in years though so I don't know how common this still is.

Wait, so you wrote this library to solve the political issue of crap DBAs?

Code inside a database is a nightmare to maintain.

Just like Data in a codebase ;-)

> The idealistic answer is good DB design from the start.

The problem with this statement is that DB schemas like code can evolve badly over time, even if they're maintained by a DBA sometimes (since they're human afterall too).

And then the DBA starts to suggest that you use things like triggers in your DB, which may or may not be good, but often surprise developers. "I just wrote 0 into the DB, why did it turn into NULL? What line of code did that? Ohhhh... there's a trigger."

There were multiple literal questions embedded in his rhetoric; which without answers, invalidate the author's solution.

> The idealistic answer is good DB design from the start.

I don't feel like this answers the question. My ORM supports views.

> What does git history has to do with the database?

Hopefully a lot. Non-versioned database schemas are the devil.

Indeed this is a bad idea for many reasons.

I tried to think about it from a gitflow release cycle perspective to be generous. This would mean constant churn of migration code for each branch of the repo.

If you have complex migrations which go beyond simply renaming columns, it would require you to write special migration code on a release branch which would then need to be merged back. yuck!

In what way is manually tracking the needed DB changes per feature branch even possibly better? Even if you are not running your migrations automatically on deploy, I would still check those migrations into version control.

Having auto-runnable migrations checked in alongside the code that depends on them allows you to review and test those migrations as part of your normal code review and CI processes.

I think there must be some misunderstanding here because I don't understand why you say either of these things:

> This would mean constant churn of migration code for each branch of the repo.

> it would require you to write special migration code on a release branch

There should only a be at most a few migrations per feature branch and merging is only an issue when you have two feature branches with inconsistent schema definitions. You should only have to add custom migrations to a release branch to fix bugs and those should be merged back into your develop branch as soon as possible to minimize effort spend merging later.

I think you and the post you're replying to have opposite opinions, despite your post seemingly starting off by agreeing with them.

No, I agree with the post I replied to in that migrations should be human versioned as opposed to auto-versioned via a VCS. Again, I was being generous to the author trying to imagine a scenario where this would not create a dumpster fire.

Hopefully I have cleared that up.

This isn't quite the same as being solely git-based, but there are numerous examples in the industry of successful use of auto-versioned declarative schema management. By this I mean, you have a repo of CREATE statements, and a schema change request = making a new commit that adds, modifies, or removes those CREATE statements. No one actually writes a "migration" in this approach.

Facebook, who operate one of the largest relational database fleets in the world, have used declarative schema management company-wide for nearly a decade. If engineered well, you end up with a system that allows developers to change schemas easily without needing any DBA intervention.

This is interesting but I am curious about some of the implications. I am under the impression that migrations encompass more than schema changes. It may require data transformations, index rebuilds, or updates to related tables. I would agree that these things are preferably avoided, but in practice it seems that they happen frequently.

I viewed the author as targeting smaller dev shops who don't have a dedicated DBA/expert. Groups who might use an ORM's migration framework for example. In which case, it would appear that there is significant loss in migration flexibility if you remove application code from the architecture.

When operating at scale, it becomes necessary to have separate processes/systems for schema changes (DDL) vs row data migrations (DML) anyway.

For example, say you need to populate a new column based on data in other columns, across an entire table. If the table is a billion rows, you can't safely run this as a single DML query. It will take too long to run and be disruptive to applications, given the locking impact; it will cause excessive journaling due to the huge transaction; there can be MVCC old-row-version-history pileups as well.

Typically companies build custom systems that handle this by splitting up DML into many smaller queries. It's then no longer atomic, which means such a system needs knowledge about application-level consistency assumptions.

> "migration commands based on the git history"... what does that even mean?

It actually seems like a really cool idea. E.g.

* Commit A: create a schema.sql file "create table t (a INT);"

* Commit B: update the schema.sql to "create table t (a INT, b INT);"

* The tool can generate a migration from A..B "alter table t add column b INT;"

It seems like it probably would work really well for creating new tables and columns in simple projects. In more complex projects I've worked on, we usually had more complicated migrations to update the data to fit the new schema, and we often had to be careful to interleave schema, data and code updates (especially if you wanted to be able to rollback).

Automatic migrations using only source-code snapshots don’t work for things like column renames or splitting/combining columns. Every tool I’ve used requires some migration metadata.

> "migration commands based on the git history"... what does that even mean? What does git history has to do with the database?

I'm not sure what git-specific interaction the author is envisioning, but more generally: declarative schema management tools are based on the notion of using a repo to store only CREATE statements. It's an infrastructure-as-code style approach, where to modify an existing table, you just modify the CREATE definition. The tool figures out how to translate this to an ALTER.

I'm the author of an existing tool in this space, Skeema [1], which supports MySQL and MariaDB. Other recent tools in this space include migra [2] and sqldef [3]. From what I understand, in the SQL Server world there are a number of others.

[1] https://www.skeema.io

[2] https://github.com/djrobstep/migra

[3] https://github.com/k0kubun/sqldef/

> So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way?

Those aren't the only alternatives. Query builders exist.

A lot of languages have pleasant multi-line string literals. Also if you have a 1000 line hand-crafted SQL query, it's probably not going to be pleasant in your ORM either.

Reply on news.ycombinator.com