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

211–220 of 360 posts

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

#211

Post by erfgh »

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.

Doesn't work because it completely screws up query optimization.

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

#212

Post by scarface74 »

zkomp wrote: post #173
blaisio wrote: post #171

Earlier quoted context omitted.

But you can decouple the database logic from the app logic anyway, without using stored procedures. They don't actually help you do this since you still need code that knows what stored procedures to call. Also, I'm not sure how this makes security easier? It seems like security would be the same or maybe a little harder since you now have to track the stored procedures you're currently using as well. I'm not really…

As far as I know, in my experience. Stored procedures in postgres are good when you really use the database and care about the data, you need transactions, need to handle races and concurrency etc. Whereas ORMs break down at this point or prevent you even getting to a point when you can use your database as a database. Why pretend your SQL database is about objects? It is not... (it is about data) A stored procedure…

Every single implementation where I've seen "business logic in the database" has been an unmitigated disaster.

On the other hand, having well factored microservices (out of process) or in process modules have worked out really well with modern devops and software engineering principals - easy push button deployments and rollbacks, unit testing, A/B deployments, etc.

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

#213

Post by ZenoArrow »

mattmanser wrote: post #196
rjbwork wrote: post #60

Earlier quoted context omitted.

I've been writing C# professionally for ~5 years at this point. While I was initially quite infatuated with LINQ2SQL and EF, I have gone through the same situation as this fellow. I just write SQL in-line using Dapper for parameterization/data mapping, and use stored procs when I need some of the more arcane features of SQL (merges, CTE, etc).

I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday…

> "And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right."

Are you referring to EF Core? I was considering learning it. What's this lazy loading issue? It's not one I've heard of before.

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

#214

Post by pjmlp »

scarface74 wrote: post #202
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…

So the easiest way to avoid using an ORM is to create your own ORM? What AI do you need? You map your tables to objects and relationships between objects via FK relationships.

I can get very creative with SELECTs, making use of Prolog style queries, which are fully done server side on the database.

Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.

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

#215

Post by ZenoArrow »

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.

> "Then you lose the ability to do unit testing without a database dependency"

Not really, you just mock the database calls in the code you're unit testing.

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

#216

Post by icebraining »

erfgh wrote: post #211
ako wrote: post #118

Earlier quoted context omitted.

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

Doesn't work because it completely screws up query optimization.

Maybe in some crappy RDBMSs, but not in Postgres.

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

#217

Post by icebraining »

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.

For that I'd need to dynamically create such views, which requires a SQL generator anyway :)

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

#218

Post by spdionis »

erikpukinskis wrote: post #79
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…

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.

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

#219

Post by ZenoArrow »

erfgh wrote: post #211
ako wrote: post #118

Earlier quoted context omitted.

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

Doesn't work because it completely screws up query optimization.

If you encounter such an issue, there are ways around it:

https://www.red-gate.com/simple-talk/sql/performance/control...

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

#220

Post by cybergoat »

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.

I want to give thanks to all the SqlAlchemy devs!

I am a huge fan of the custom column types -- they have allowed me to have code that works against SQLite as well as a production databases with ease!

Post reply on HN