What ORMs have taught me: just learn SQL (2014) • Hacker News Board

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

#501

Post by jillesvangurp » Sat, Sep 21, 2019, 9:14 AM UTC

shkkmo wrote: post #412

jillesvangurp wrote: post #346

Earlier quoted context omitted.

I've ripped out broken ORM on multiple projects with over-engineered domain models designed by people with no apparent knowledge of how to do a proper database design. This is the key problem with ORM. It leads to lots of unnecessary joins just so you can pretend databases do inheritance or all those tiny objects you will never query on need dedicated tables with indexed columns. It's stupid. It's also stupidly slow,…

> ORMs don't have to be a problem but they nudge people into doing very sub optimal things. I don't think good ORMs do any nudging. The issue arises when people assume that because they are using an ORM they don't have to learn the underlying DB. ORMs should be treated as tools that sit on top of your SQL knowledge and allow you to do certain types of things easier. Like any tool, there are inappropriate uses cases.…

I don't do object relational mappings generally. I instead query by id or a few other columns and construct objects from json documents stored in text columns.

Frameworks for that are awesome and a lot easier to deal with and serializing/deserializing overhead is typically minimal. Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some constraints, and raw data (json or for simple structures some primitive values. Some databases even allow you to query the json directly but in my experience this is kind of fiddly to set up and not really worth the trouble. The nice thing is that most domain model changes don't require database schema changes this way because the only thing affected is your json schema. This makes iterating on your domain model a lot easier. You still have to worry about migrations of course.

The added value of using a database is being able to manipulate them safely with transactions and query them efficiently. Bad ORM ends up conflicting with both goals and the added value of well implemented ORM is usually fairly limited. At best you end up with a lot of tables and columns you did not really need mapped to your objects and classes.

A good table structure often makes for a poor domain model and vice versa. The friction you get from the object relational impedance mismatch is best avoided by treating them as two things instead of one. Bad ORM shoves this under the carpet and in my experience does not address this (other than by providing the illusion this is not a problem).

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

#502

Post by cturner » Sat, Sep 21, 2019, 9:15 AM UTC

setr wrote: post #255

mapgrep wrote: post #205

Earlier quoted context omitted.

Ya I’ve heard this one a lot. It’s kind of funny to say and does humorously underline the complexity of the problem but people take it seriously. So to take it seriously for a second: There was no good reason to be in Vietnam; even taking the stated rationale as a given, which many people did not, it was a concern many levels removed from the actual safety or functioning of American society. ORMs in contrast achieve…

Tbh you could easily claim that the explicit goal, mapping to objects, is incorrect. The real value is to reduce the damage of the SQL language itself — the unnecessarily ordered clauses, the arbitrary inconsistencies in syntax, the worthless parser errors, the lack of any static typechecking — which cause so much code bloat and debug headaches. There are two reasons to use the ORM: to not learn SQL, and to generate…

    > What we really need is a less shitty version of SQL.

My view is the opposite. The power of SQL perpetuates a low-quality software culture. The root issue is a dev culture that can't see past databases.

A lot of software design runs like this: (1) translate business patterns into a relational schema; (2) build interactions with that schema; and (3) as that gets harder, use SQL arcana and ORMs and views and stored procedures to squeeze out flexibility.

I worked like this for the first decade of my career. My systems got some use, and struggled along, but they are failed projects.

Repeatedly I had this feeling: the project is almost done, but there are some concurrency issues where I would not even know how to start addressing them.

The database-centric design made it impossible to work past that.

After much searching, I came to this: Stored State is a brittle and unwieldy thing, and you want as little of it in your life as possible. The more you have, the harder you have to work to get anything done. Databases are an institution of Stored State.

As an alternative, you can derive state from messages.

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

#503

Post by GordonS » Sat, Sep 21, 2019, 9:15 AM UTC

timmy-turner wrote: post #475

gmac wrote: post #467

Earlier quoted context omitted.

Agreed. Helpers (and indeed types) can make working with SQL an actual pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres solution, in this vein: https://github.com/jawj/mostly-ormless/blob/master/README.md ).

Wow this is great! Very well written README. What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails…

> Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper

I'd argue that learning SQL is essential for any developer.

It's also a "reusable" skill that will stand them in good stead for decades - whereas learning how to use the fancy wrapper is only useful until the next new shiny comes along.

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

#504

Post by Shorel » Sat, Sep 21, 2019, 9:22 AM UTC

headmelted wrote: post #468

In these cases, I’ve elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It’s a lot less trouble than anything else I’ve used so far. I was with the spirit of the article save for this. Recently I've been developing some work for one of my client's in the .NET world. There had been ongo…

Make it a product and sell it/github it.

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

#505

Post by eknkc » Sat, Sep 21, 2019, 9:26 AM UTC

sword_smith wrote: post #480

I use Entity Framework for C# and I have grown to appreciate it. I get libraries for in-memory databases which makes it easy to write thorough unit tests, the `Include` function uses join to include foreign key objects in an optimal manner, and scaffolding tools make it easy to map from SQL to C# classes. The resulting SQL from the Linq expressions is logged which makes it easy to see what is going on, if you already…

EF is fantastic to use. I think it's because while other ORMs try to integrate their logic into a language, EF was developed alongside linq, IQueryable stuff and language extensions. Basically, they changed the language to better accomodate stuff like EF. And it shows.

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

#506

Post by Nicksil » Sat, Sep 21, 2019, 9:29 AM UTC

speedplane wrote: post #469

I'm okay with ORMs, but I can't stand lazy evaluation. It often leads to situations where it's entirely unclear when the program is making a database call. For example, given the small django program: dbcall = models.Purchases.objects.filter(amount = 100) if dbcall: do_something() if len(dbcall) > 5: do_something_else() third_thing(dbcall[0]) Does the above app make 1, 2 or 3 database requests? There is an answer, bu…

>There is an answer, but it's not at all clear to the developer.

A cursory reading of the documentation is usually a first step in acclimating to an otherwise unfamiliar system.

It just so happens, for your example here, a complete explanation[1] can be found at the very top of what's likely the most vital subsystem's documentation. During due diligence, this information will be among the first encounters.

[1]https://docs.djangoproject.com/en/stable/ref/models/queryset...

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

#507

Post by leowoo91 » Sat, Sep 21, 2019, 9:31 AM UTC

Using both should be fine.. SQL alone encourages early optimization that might slow down the development, especially when you don't have the complete non-functional requirements. It sounds like a pitfall to compare it against using ORM. The way I see it, ORM stands good when it's understood as an additional capability.

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

#508

Post by shkkmo » Sat, Sep 21, 2019, 9:34 AM UTC

jillesvangurp wrote: post #501

shkkmo wrote: post #412

Earlier quoted context omitted.

> ORMs don't have to be a problem but they nudge people into doing very sub optimal things. I don't think good ORMs do any nudging. The issue arises when people assume that because they are using an ORM they don't have to learn the underlying DB. ORMs should be treated as tools that sit on top of your SQL knowledge and allow you to do certain types of things easier. Like any tool, there are inappropriate uses cases.…

I don't do object relational mappings generally. I instead query by id or a few other columns and construct objects from json documents stored in text columns. Frameworks for that are awesome and a lot easier to deal with and serializing/deserializing overhead is typically minimal. Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some cons…

> I instead query by id or a few other columns and construct objects from json documents stored in text columns.

> Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some constraints, and raw data (json or for simple structures some primitive values.

You are basically describing a sort of ad-hoc document store with potentially limited ability to query. You've lost many of the benefits provided by a relational DB. Your ability to do run large update queries or reports will be limited (unless your DB provides native json support, which as you said, is fiddly).

If you are going to do this, why not use a NoSQL document store with support for transactions? Then you will get a tool that is designed to work with your use case.

Edit: If you use an ORM, a hybrid approach is possible. Where you store some properties as separate columns and then store the less frequently accessed (or more dynamically structure) data in a json field (which you can deserialize on hydration or on request). The main downside of this hybrid approach is that moving a propery out of the json field into a normal column would require using that fiddly native json support or a fairly slow migration that would need to go through and serialize each json field.

> A good table structure often makes for a poor domain model and vice versa.

Can you clarify what you mean? This has not at all been my experience so I am curious and would love to see some examples.

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

#509

Post by hdfbdtbcdg » Sat, Sep 21, 2019, 9:50 AM UTC

bob1029 wrote: post #144

hdfbdtbcdg wrote: post #107

Earlier quoted context omitted.

> Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies > complex, rapidly-shifting business models Your business logic classes have 1000+ properties. And you plan to not migrate them when the schema changes but leave many instances with old versions of the schema sitting in the datastore. Your application logic is going to get nasty!

The other aspect here is that the lifetime of these objects is very brief for our application. Typically 10-60 minutes. Schema changes, while breaking, are tolerable along business cycle boundaries.

Why use SQL at all? It sounds like you needed a key value store?

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

#510

Post by ljm » Sat, Sep 21, 2019, 9:54 AM UTC

GordonS wrote: post #503

timmy-turner wrote: post #475

Earlier quoted context omitted.

Wow this is great! Very well written README. What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails…

> Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper I'd argue that learning SQL is essential for any developer. It's also a "reusable" skill that will stand them in good stead for decades - whereas learning how to use the fancy wrapper is only useful until the next new shiny comes along.

I’d add that it’s essential so you can understand how to optimise and debug a query. You lose a lot of power if you can’t open up a console to describe or explain things.

The long-standing ORMs do a pretty decent job of writing efficient queries these days though. You can go pretty far without knowing much and that’s not a bad thing either.