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

321–330 of 360 posts

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

#321

Post by zimpenfish »

icebraining wrote: post #318
zimpenfish wrote: post #314

Earlier quoted context omitted.

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.

As best I can tell, Postgres lets you do this albeit constraining your boolean checks to things you can efficiently do in the database. But then I can definitely see how this could get unwieldy very quickly.

https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.h...

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

#322

Post by guru4consulting »

spdionis wrote: post #218
erikpukinskis wrote: post #79

Earlier quoted context omitted.

Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. > providing patterns to map object graphs to…

5 years down the road, good luck refactoring your application to not query the same object from the database 3-4 times, and load it only once instead.

caching is a very tricky area with tons of pitfalls. In my opinion, the ORM should not be caching. Let the clients (or any other layer) cache/clear based on their needs.

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

#323

Post by icebraining »

zimpenfish wrote: post #321
icebraining wrote: post #318

Earlier quoted context omitted.

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.

As best I can tell, Postgres lets you do this albeit constraining your boolean checks to things you can efficiently do in the database. But then I can definitely see how this could get unwieldy very quickly. https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.h...

I know about row-level security, but I don't think the rules can be dynamically loaded.

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

#324

Post by nikisweeting »

normaljoe wrote: post #104
jtchang wrote: post #13

Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM or SqlAlchemy. Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder? Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL…

Django's ORM is actually a good example of where identity is an issue. It lacks composite primary keys. If I have a dependent table that has my real ID, say an order number that is a varchar I have to join to the parent table to do lookups by the order number. I can't doing something like key(order_number, line_number) so I end up hydrating a parent object for operations that only require operations on the dependent…

It doesn't have to hydrate the parent object, just use .only('relation_id').

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

#325

Post by gfiorav »

This article is valuable since it's raising some interesting pitfalls it's good to know and avoid.

That being said: you need to use the right tool for the job.

It's just hilarious how people expect the new "foo framework/paradigm" to solve ALL the problems... jeez! It's nice to know and understand new paradigms, but you really need to evaluate your case.

ORMs took away the complexity of 90% of web apps. All the "Model X has many model Y". If you step outside of that realm with the ORM then you're officially "fighting the framework", and bad things will happen.

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

#326

Post by walshemj »

scarface74 wrote: post #237
walshemj wrote: post #227

Earlier quoted context omitted.

Not in my experience though our DBA was very good (oh my first boss was Dijkstra he mentioned down the pub one lunch time) And would you not have your IDE on one monitor and your SQL IDE in another so you could look at both sets of code.

Usually with most modern automated deployments, you keep your build artifacts in a package (zip file, tar, etc.) and run a script to deploy it to your target system. Rolling back is a simple matter of installing the previous archive. That doesn't just apply to code anymore. You can treat "infrastructure as code" also. You can do A/B upgrades, rollbacks, etc. There is so much better tooling around regular code than sq…

Sounds like you have been working at companies with poor procedures and employees without the required skills.

Avoiding SQL in favour of an ORM or inline sql is 95% of the time a sign of laziness.

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

#327

Post by in9 »

jes5199 wrote: post #26
nine_k wrote: post #19

Object -relational mapping is in many cases an excessively leaky abstraction. I try keep away from it. A DSL for writing SQL in a nice, composable way is a useful thing. Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.

SQLalchemy is kind of a pain in the ass to actually use, though. The DSL doesn't feel very Pythonic, it's weird and confusing. The way it traverses the Object graph when loading associations between models is magical and opaque and I could never predict when it was going to automatically work and when it wouldn't. I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.

If you aim to have a pythonic data layer, I've heard good things about pony orm[1]. I mean,you can't get much more pythonic then the example they have on their website. But I haven't used it my self.

[1](https://ponyorm.com/)

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

#328

Post by setr »

lmm wrote: post #305
oceanswave wrote: post #240

Earlier quoted context omitted.

If type safety is so great, why isn’t sql statically type checked

Because SQL predates a lot of modern techniques. A ground-up replacement written today probably would be statically type checked.

What do you guys think the schema is?

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

#329

Post by nine_k »

ako wrote: post #118
niftich wrote: post #71

Earlier quoted context omitted.

This is a quip that misses the parent's point. SQL isn't very composable, because its syntax requires infix notation, position-dependent phrases, separators, and the like. The abstract syntax tree of SQL is much more valuable than its syntax, which is essentially just a bad English serialization that resembles a natural language sentence. A DSL which manipulates queries and then produces syntactically valid SQL is tr…

SQL is composable if you use views, just create a view for anything that you want to reuse in multiple queries.

Views are only half the answer. Try factoring out a set of conditions and use them both in a `select` and in `update`.

Also, views tend to pollute the namespace a bit: you want a set of descriptive names, per application, and possibly per application version. Schemata help here, though.

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

#330

Post by scarface74 »

hodgesrm wrote: post #320
scarface74 wrote: post #209

Earlier quoted context omitted.

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 h…

How would you test data access in a meaningful way without a DBMS?

The beauty of Linq and Expression Trees.

In the real world:

Linq (c# code) -> compiler -> expression tree -> run time Linq provider -> destination language (sql, Mongo Query etc.)

When you are unit testing you switch out the Linq provider for in memory Linq to objects provider.

https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....

Post reply on HN