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)

wozniak.ca

Post reply on HN
Print view View original

291–300 of 305 posts

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

#291

Post by Gustek »

IndianAstronaut wrote: post #100
Gustek wrote: post #9

I like to say "ORMs make easy thing easier and hard things harder". What i mean by that is, any simple CRUD operations are much easier in ORM. The hard things, i mean any complex queries that need more than one join you are probably better of writing yourself. In the end i prefer to do inserts, updates and deletes with ORM (or some other database abstraction tools) but most SELECTs i write myself, fetching exactly wh…

Do uou use metadata from the db driver to do type mappings?

Not sure what you asking about. Do you mean if I define database structure in my code? The answer is: yes. I don't know about any database abstraction library that would provide you with convenience methods to query database with type checking and stuff without giving it the schema.

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

#292

Post by snaky »

PeCaN wrote: post #47
StevePerkins wrote: post #34

Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at yo…

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…

> 30 lines of Perl to wrap DBI

In Java it's even easier with JPA @NamedStoredProcedureQuery annotations

http://www.thoughts-on-java.org/call-stored-procedures-jpa/

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

#293

Post by snaky »

manigandham wrote: post #129
PeCaN wrote: post #47

Earlier quoted context omitted.

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…

> A lot of things people use ORMs for are rather easily solved with stored procedures More like 1 thing. ORMs are meant to make interfacing through the object/relational impedance mismatch easier and through the regular code in your application. Stored procedures do not come anywhere close to this and are usually the same as just calling any other SQL query as you would when not using an ORM. If you think any majorit…

> interfacing through the object/relational impedance mismatch

Do we really need the regular code with it's traditional OOP and fat application server to convert result set to JSON and spit it out - that most of the time is all that code is doing actually? I suppose these typical tasks might be pretty much covered not only by PostgREST [1], but with just a sweet combination of two PostgreSQL functions - array_to_json() and array_agg().

[1] http://postgrest.com/

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

#294

Post by BurningFrog »

Jweb_Guru wrote: post #190
BurningFrog wrote: post #134

Earlier quoted context omitted.

One problem with the stored procedure approach is that it scales terribly. If your logic is in app servers and your state in a DB, you can add more app servers, and it will be a long time until your DB get overwhelmed. When your DB is doing both , the choking point is much earlier.

Look, this is just wrong. All the best-performing research databases use stored procedures because they perform much better than other approaches. Your bottleneck when it comes to validating your data is, generally speaking, your database, so you're not going to scale better by moving the logic farther away from it (and introducing a bunch of round-trip latency) and this is empirically confirmed in benchmarks of stuf…

That stored procedures scale badly is different from if they perform badly.

With application logic on separate stateless app servers, you can quickly spin up as many of those as you need when your site goes viral. If it's in your DB, you can't.

If the stored procedures are fast or slow doesn't really enter into that equation. It's about the difference between 1 and N.

I actually did work at a startup that got into serious trouble because much of their application logic was in the DB.

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

#295

Post by btilly »

Jweb_Guru wrote: post #285
btilly wrote: post #282

Earlier quoted context omitted.

Scalability IS NOT performance. I like to say that scalability is like a Mac truck. Good if you have to move a lot of stuff, but not necessarily the best tool for getting your groceries. Moving logic from stored procedures to the application adds latency and network traffic. This is never going to be good for how fast you process an individual request. However it can let your system handle more requests per second.

That's only true if there aren't resources taken up by your in-flight transactions, and if those resources don't exceed the cost of just running the logic in-place. Otherwise, you're not sacrificing latency for throughput, you're just sacrificing latency and throughput. Unless you have a very heavy application-level thing to do that doesn't require periodic new access to data, that's usually not the case, especially…

True.

But my experience with Oracle specifically is that handling connections is just general overhead, while the real failure modes have to do with overloading random latches somewhere deep inside of the system. I also found that logic in stored procedures caused us to hit limits faster than leaving it in the application.

The specific case I saw this in was a simple set of queries to test if you were in an A/B test, and if not to assign you to a random variant. After I left my application logic was moved into a complex stored procedure, and they then had scalability limitations that they didn't have before. For political reasons they declared success and ran fewer A/B tests...

(I've used a lot of other databases as well, but Oracle is the only one I've really pushed to its scalability limits.)

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

#296

Post by takeda »

Jweb_Guru wrote: post #192
btilly wrote: post #185

Earlier quoted context omitted.

I don't know what you consider "empirical proof", but it seems obvious to me. And should be obvious to anyone competent who has ever had to scale stuff. A system fails to scale when it has a bottleneck, and that bottleneck gets overwhelmed. You make it scale better by scaling the bottleneck, which can be done by moving work out of the bottleneck, or by parallelizing it in some way. The natural bottleneck for any syst…

When it comes to data validation, none of what you just said applies, because you need to perform your work inside a transaction. You can validate the data outside your database and then confirm that nothing's changed (optimistic concurrency control) but you can do that just as easily inside the database, with lower latency and greater throughput (and in situations with lots of contention this can lead to many more a…

I believe the poor performance of database is often caused by people who are new to the subject writing applications in such way that they do N+1 problem.

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

#297

Post by takeda »

manigandham wrote: post #139
mixedCase wrote: post #137

Earlier quoted context omitted.

You could argue that anything that interfaces with a database is an ORM, but it is certainly no ORM in the Hibernate/ActiveRecord/EntityFramework sense. It does very little "magic" and you're in full control of the SQL from the start.

Sure, let's say an ORM is anything capable of turning normal app code into SQL statements by itself. What do ORMs do that's magic? What are the problems? Are you not in full control of them? It's your code after all that's using them. I really don't get how they suddenly force any issues on you that you don't create yourself. The output SQL doesn't really matter if it gets the job done, and in cases it does you still…

The issue with it is that SQL is a bit different kind of language. When using it we don't tell the database what to do, but instead we say what we want. That does not translate well.

Second issue is that tables and their relations don't map too well to objects and their relations.

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

#298

Post by spronkey »

snuxoll wrote: post #217
gozur88 wrote: post #116

Earlier quoted context omitted.

This. Ye Gods, this. A project with nontrivial data relationships should start with the data model, not with a code model superimposed on data. ORM leads to bad data models and performance problems that are unfixable.

If only people would listen to this advice. The number of times I have started a project by trying to think about the data model only to get told "just build the models, we can change it later" drives me insane. Code is easy to change, your data model is not once it is in production.

Only if you use a single, concrete data model. If your domain supports the use of something like event sourcing, your "data model" is surprisingly easy to change. Or in this case, is = are, and data model = data models, and they're actually derived from your domain events, which can be replayed. At any time.

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

#299

Post by Jweb_Guru »

BurningFrog wrote: post #294
Jweb_Guru wrote: post #190

Earlier quoted context omitted.

Look, this is just wrong. All the best-performing research databases use stored procedures because they perform much better than other approaches. Your bottleneck when it comes to validating your data is, generally speaking, your database, so you're not going to scale better by moving the logic farther away from it (and introducing a bunch of round-trip latency) and this is empirically confirmed in benchmarks of stuf…

That stored procedures scale badly is different from if they perform badly. With application logic on separate stateless app servers, you can quickly spin up as many of those as you need when your site goes viral. If it's in your DB, you can't. If the stored procedures are fast or slow doesn't really enter into that equation. It's about the difference between 1 and N. I actually did work at a startup that got into se…

Were you serving something that had to live in the database, or were you serving cached copies that didn't actually need to be updated or read within a transaction? There's a crucial difference there. The moment you are required to access the database at all, the equation changes (which is usually true for data validation, inserts, updates or deletes, and constraint management). If all you're doing is reading static data, there's no need to touch the database more than once and you can do much better. Moreover, if all you need is causal or eventual consistency (for application-specific reasons), you can use databases that provide those semantics. In pretty much all cases, once you've chosen the correct database / semantics for your use case, you'll be better off from a performance perspective with stored procedures; 1:N doesn't really enter into it because databases can run with their own isolated state on multiple nodes just as well as applications can (indeed, there are some caching systems built around this observation)!

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

#300

Post by toast0 »

rkuykendall-com wrote: post #267
coldtea wrote: post #262

Earlier quoted context omitted.

> Do you work in a team? Or do you plan to hand over that code to someone else for maintenance at some point in time? If not, then by all means, go ahead. Did anything of what the parent wrote sounded like it was worse for teams?

Because toast0 knows what they wrote, but a team member wouldn't. So instead of googling "Flask add cookie to response" you have to start digging through all of toast0's code to see if they even have a way. At least that's why I would consider working on a team like that much worse.

This is the first link for 'php send cookie' or 'php add cookie'

http://php.net/manual/en/function.setcookie.php

grep -r setcookie in my code will let you find out if i sent any cookies. Incidentally, this is the same grep I would have to do on a framework, but the framework (pick one) is probably bigger than all of my code.

As an example -- we used to have a WordPress blog; now we have a custom blog that's a total of 3628 lines of php that lives on the frontend and I included the Makefile for deployment and some extra include files cause I don't want to spend the time to consider which ones aren't needed. Content is from text files pushed with another process.

By comparison Wordpress includes 298,643 lines of php. Wouldn't you rather dig through all of my code than all of theirs? (Yes, Wordpress has a bunch of features -- but even when you turn them off, the code is there lurking, and sometimes it still runs)

Someone else from my team fixed up the mobile support and handles the css/javascript -- they were able to just look at the code and do what needed to be done. (The mobile page was broken in our install of Wordpress too, so I got it to parity anyway)

It took me about a week to write it and polish it (three days dedicated, including exporting the content from mysql, two days I was doing other things too), but on the plus side, I don't need to spend half a day to figure out how to deploy a Wordpress upgrade every time they have a security release; there have been 12 security releases since then, so I've earned a day of my life back.

Post reply on HN