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

To ORM or Not to ORM

eli.thegreenplace.net

Post reply on HN
Print view View original

171–180 of 300 posts

Re: To ORM or Not to ORM

#171

Post by collyw »

toast0 wrote: post #4
ordinaryperson wrote: post #3

Sample size one 1 but ORM drives me crazy. Why can't we just use SQL? How does it save time when I have to learn the ORM language, which probably has a lot less support and users? The OP here says it "reduces boilerplate" -- rarely have I created an application and thought its biggest problem was too much boilerplate. But everyone at work loves them so I must be wrong somehow.

All the time allegedly saved is also more than compensated for when the ORM makes bad queries that are roughly impossible to fix.

Most ORMs allow you to write SQL, no? So why not just use that when appropriate?

Re: To ORM or Not to ORM

#172

Post by GordonS »

ozim wrote: post #111
bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

Actually "writing queries in some language other than SQL" which has static typing and catching issues in compile time is quite big for me. Add automatic database migrations that are also keeping types in line with code and whole bunch of "mess of mapping code" goes away. Though I use .NET EntityFramework which by now is really mature and heavily invested into by MS. Not sure how it is with other environments but I t…

I'm very comfortable with SQL, but I also like the static typing and compile-time safety that linq brings.

Automatic migrations I like less. In particular, Entity Framework and Entity Framework Core migrations.

Firstly, it seems that the second you stray from the most basic of scenarios, thry generate incorrect DDL.

Secondly, I'm just not a fan of 'magic' code gen.

Thirdly, every entity/model change a dev makes does not need to equate to a schema migration - that's how you end up quickly accumulating hundreds of schema migrations.

When I have to use MS SQL Server, the approach I favour is to use CQRS, using EF Core for the majority of cases, then using Dapper when I need more control. And for migrations I like DbUp, because it provides me with full control over when it's time to create a migration file, and what better DSL for migrations than... SQL.

Re: To ORM or Not to ORM

#173

Post by cies »

echelon wrote: post #165

In my experience ORM always shoots you in the foot when your use case exceeds what an ORM can provide. ActiveRecord has been a terrible mess for us because the facade masks the underlying behavior (transactional behavior) or often doesn't do what it advertises (commit hooks, timestamps, auto-increment). ORM is great for startups trying to build out an MVP, but beyond that scale it's an anti-pattern. The best solution…

I came here to say that any article discussing the benefits/downsides of ORMs should include JOOQ. It takes such an interesting spot in the design space: not an ORM, but a super flexible, type-safe query builder with lots of helpers so you dont write more code than necessary.

Re: To ORM or Not to ORM

#174

Post by d4mi3n »

bunderbunder wrote: post #115
d4mi3n wrote: post #82

Earlier quoted context omitted.

There's another big aspect of ORMs a lot of people tend to skip in discussion: Security. Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability. A good ORM can be a fairly effective layer of safety.

It can be, but it is hardly the only solution to that problem. I've also seen it solved, for example, with a git commit hook that just bounced any non-parameterized queries.

Oh certainly. Any good security is done in layers. A way to sanitize SQL, however you do it, is one of them.

Re: To ORM or Not to ORM

#175

Post by collyw »

scarface74 wrote: post #45
mireq wrote: post #28

Earlier quoted context omitted.

I am very happy with ORM. I'm currently working on an e-shop. I need to display many tables with different filters. Administrator wants products starting with string? OK: if filter['category']: qs = qs.filter ... Administrator wants products without category? Ok: if filter['category']: qs = qs.filter ... Add multilang supprt? Ok: subclass model, mark fields and translatable, run migrate script I can combine any filte…

This is about the only use case where I find ORMs useful

Inserting data into the database is usually where ORM's save the most time.

Re: To ORM or Not to ORM

#176

Post by mmusc »

ozim wrote: post #111
bunderbunder wrote: post #97

I've come to a couple conclusions, over the years. First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature. Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature. Fortunately, there are tools out there that solve the "…

Actually "writing queries in some language other than SQL" which has static typing and catching issues in compile time is quite big for me. Add automatic database migrations that are also keeping types in line with code and whole bunch of "mess of mapping code" goes away. Though I use .NET EntityFramework which by now is really mature and heavily invested into by MS. Not sure how it is with other environments but I t…

I much prefer having an vs sql project side by side with the ef models. Keep the schema up to date in there. It can generate migration scripts by comparing the dB you want to target. Then just generate ef models from the dB.

Re: To ORM or Not to ORM

#177

Post by collyw »

megous wrote: post #61
chaostheory wrote: post #40

Earlier quoted context omitted.

Have you ever tried it? Your coworkers are right. It also helps you avoid common mistakes like SQL injection. When the ORM doesn't work for a specific case, they usually have ways where you can override the ORM and use your own SQL calls. Virtually every major, popular ORM does this across different languages (can personally confirm with Ruby, Java, & Python ORMs - confident that JS ones support it too).

SQL injection is prevented by not using user input as a part of the SQL query. It's orthogonal concern to whether to use ORM or not.

You can't avoid use user input. How would you login with a username and password without user input?

Django's ORM will sanitize input when you pass it in as raw SQL.

Re: To ORM or Not to ORM

#178

Post by GordonS »

Dayshine wrote: post #167
dragonwriter wrote: post #130

Earlier quoted context omitted.

> Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". [...] It's hard to do that in sql in a maintainable way. Seems a textbook case of using a view for sharing query logic. > If I decide to change a datetime field called time to split it into a da…

>Seems a textbook case of using a view for sharing query logic. Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update. You can't combine views easily, you can't cache results, you don't get static type checking.

I don't like to say "you're doing it wrong", but TBH it sounds like you are.

> restricted to SQL structures

So is your ORM; it just adds a layer of abstraction

> require absurd hacks or custom dlls

Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs"

> not properly source controlled

Eh? You can store your view DDL files in source control just fine. I mean, they're just SQL files; why couldn't you?

> difficult to perform performance analysis on

I don't see how ORMs offer any advantage here - if anything, SQL has the edge here, since your typical SQL GUI will draw pretty graphs to help visualise EXPLAIN, and some (e.g. SSMS) will even suggest missing indexes

> hard to update

It's just SQL, and for views typically rather basic SQL at that.

I use ORMs on 99% of the projects I work on, sometimes heavyweights like Entity Framework Core or Marten (which I love, BTW), and sometimes more lightweight like Dapper - but I know when to use the right tool for the job, and sometimes that's SQL views. I just don't get any of your arguments against them.

Re: To ORM or Not to ORM

#179

Post by collyw »

Johnny555 wrote: post #98
slaymaker1907 wrote: post #96

An ORM is a technology that has the problem of trying to make easy things easier (CRUD) while making difficult things more difficult (complex joins, SQL lock management, indices, etc.). Why spend so much $$$$$ on a fancy database if you are going to make 70-90% of its features impossible to use?

Why spend so much $$$$$ on a fancy database You need to answer that question first. What feature do you really need from that fancy database?

A large proportion of devs seem to struggle with basic joins.

Re: To ORM or Not to ORM

#180

Post by TomMarius »

hcho wrote: post #131
tmefq wrote: post #123

Earlier quoted context omitted.

Well said. I bitterly hated sqlalchemy because many times I knew perfectly well how to write a query in plain sql but for the life of me I couldn't figure out how to write the same query using the sqlalchemy language.

Doesn't alchemy have an execute method, to which you can pass a raw sql?

That doesn't seem like something I'd accept from a developer on my team
Post reply on HN