Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What ORMs have taught me: just learn SQL (2014)

woz.posthaven.com

Post reply on HN
Print view View original

311–320 of 360 posts

Re: What ORMs have taught me: just learn SQL (2014)

#311

Post by lmm »

> But the damn migration issue is a real kick in the teeth: changing the model is no big deal in the application, but a real pain in the database. After all, databases are persistent whereas application data is not. ORMs simply get in the way here because they don't help manage data migration at all.

If you ORM doesn't help you manage data migration, get a better one. Any ORM worth its salt should let you generate migrations; at least Django and Hibernate (+ Liquibase) can. I find the key to making everything work nicely is to let the definition in the application/ORM be the source of truth for what the DDL looks like. If you want a particular SQL table layout, figure out how to tell the ORM to generate it. If you try to retrofit an ORM onto an existing table schema (which it seems is this author's preferred approach), you're in for a world of pain.

> These two things don't really get along because you can really only use database identifiers in the database (the ultimate destination of the data you're working with).

> What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier.

Use UUIDs. Generate them in the application, but use them directly as identifiers (pkeys) in the database.

> Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused. This leads to a lot of boilerplate code with exception handlers and a careful consideration of where transaction boundaries should occur. It also makes you pass session objects around to any function/method that might have to communicate with the database.

> The concept of a transaction translates poorly to applications due to their reliance on context based on time. As mentioned, dynamic scoping is one way to use this in a program, but it is at odds with lexical scoping, the dominant paradigm. Thus, you must take great care to know about the "when" of a transaction when writing code that works with databases and can make modularity tricky ("Here's a useful function that will only work in certain contexts").

Use a monad to represent "this function has to happen in a transaction", then all those problems go away.

Re: What ORMs have taught me: just learn SQL (2014)

#312

Post by zzzeek »

andrewstuart wrote: post #93
zzzeek wrote: post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

zzzeek - can I take this chance to praise your work on SqlAlchemy. People say there's not enough thanks given to open source developers... here's thanks to you. It's the work of a craftsman.

thanks very much and also to all the other great commenters on this thread!

Re: What ORMs have taught me: just learn SQL (2014)

#313

Post by _grep_ »

lmm wrote: post #307
zimpenfish wrote: post #188

Earlier quoted context omitted.

> A query builder aids you at constructing queries ... that you understand and can be sure are sensible. > while an ORM builds queries for you ... that you have to hope are sensible. That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.

Isn't that the same reasoning as "I don't like using high-level languages because they limit my visibility of what's running on my processor"? Do you write all your code in assembly?

I think the issue is that if you don't understand what a high-level language is doing under the hood, then you will constantly be surprised by side-effects. You shouldn't be using an ORM to substitute for your lack of understanding SQL; you should be using it to automate tasks.

Re: What ORMs have taught me: just learn SQL (2014)

#314

Post by zimpenfish »

icebraining wrote: post #276
zimpenfish wrote: post #262

Earlier quoted context omitted.

> The ORM we use automatically applies security access rules to the queries, for example. But surely that's better handled in the database itself?

How would you apply user-configured security rules in the database?

Good question. It would depend what kind of rules they were and how they were configured. Wasn't specified that they were "user-configured" in the original statement, though.

Re: What ORMs have taught me: just learn SQL (2014)

#315

Post by _grep_ »

lmm wrote: post #308
3riverdev wrote: post #73

(Bias: I'm one of the Hibernate ORM committers.) Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. The ORM is one piece to the puzzle, not a shield to prevent you from having to touch SQL. One pattern I typically use is a take on CQRS: Hibernate for writing/updating/fetching/dele…

> Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. Disagree. You need to understand the relational model, but you don't need to understand SQL-the-language. I've written plenty of successful systems using hibernate without needing to touch SQL, and am much happier for it.

I agree with you, but I think people (for better or worse) are using the two terms interchangeably in this thread.

Re: What ORMs have taught me: just learn SQL (2014)

#316

Post by agentultra »

In a recent project we went without an ORM.

We took a tack similar to how PostgREST and PostGraphQL are structured. We use views in the public schema to build our objects. Functions constrain our mutations. Triggers respond to events and maintain consistency.

It makes our web API code simple and hard to introduce errors that invalidate our customers’ data.

Don’t miss having an ORM. Always seemed like more abstraction and complication than was necessary given recent advances in servers like Postgres.

Re: What ORMs have taught me: just learn SQL (2014)

#318

Post by icebraining »

zimpenfish wrote: post #314
icebraining wrote: post #276

Earlier quoted context omitted.

How would you apply user-configured security rules in the database?

Good question. It would depend what kind of rules they were and how they were configured. Wasn't specified that they were "user-configured" in the original statement, though.

Fair enough, ours are :)

The kind of rules we use are boolean expressions that identify which rows the user's groups can read/write/delete. The ORM automatically combines all the rules as a single expression and applies it to the query.

Re: What ORMs have taught me: just learn SQL (2014)

#319

Post by ZenoArrow »

scarface74 wrote: post #292
ZenoArrow wrote: post #286

Earlier quoted context omitted.

> "If all of your business logic is in the stored procedures, what are you actually testing?" Depends on what you want to test. Can either write unit tests for the stored procedures or unit tests for the code that makes use of those stored procedures.

And then when you write "unit tests" for stored procedures with a lot of developers you get slow "unit tests" that don't scale across multiple developers because of Comte toon issues.

> "because of Comte toon issues"

Qué?

Re: What ORMs have taught me: just learn SQL (2014)

#320

Post by hodgesrm »

scarface74 wrote: post #209
zkomp wrote: post #133

Earlier quoted context omitted.

No. You should really wind up with a DAL. Define some stored procedures for accessing and working on the data and use only stored procedures. No need for ORM, and no inline sql logic in your application code.

Just use stored procedures? Then you lose the ability to do unit testing without a database dependency, it's a lot easier to rollback code than to rollback code and stored procedures as one and you don't get full visibility on what the code is doing just by looking at the source code.

How would you test data access in a meaningful way without a DBMS? It does not really matter whether you use tables or SPROCs. You'll still need a DBMS instance available.

For PostgreSQL and MySQL you can bring up the DBMS in Docker. That is not a built-in fixture obviously but easy enough to do locally as well as in CI/CD systems like Travis. You'll need to load SQL into the DBMS as a prerequisite to testing. That has the benefit of testing your load/upgrade sequence.

Post reply on HN