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

Python: Just Write SQL

joaodlf.com

Post reply on HN
Print view View original

151–160 of 288 posts

Re: Python: Just Write SQL

#151

Post by marcosdumay »

zzzeek wrote: post #113

ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…

The one distinguishing feature of an ORM is that it drops SQL and the relational paradigm and places the developer completely within the OOP world. If you drop this, you have merely a database connector library, with much more freedom of behavior than an ORM.

Yes, since they are an notoriously bad abstraction, every ORM will give you an escape hatch for the minority of tasks that it can't abstract at all. That escape hatch is not in any way a defining feature of the system.

Now, about that extra freedom that you get from dropping the requirement that your connector is an ORM... well, neither your data at rest, the presentation to the user, the abstraction for a service client, nor your validation rules benefit from OOP. Proof of that is that OOP interfaces from all of those things precede the current popular ones, and all of them were abandoned due to fundamental dissonance between the model and the data.

The rationale for an ORM is that, even though none of the interfaces you actually want for your data is best done in OOP, somehow OOP is still the best way to integrate them so that you can reuse data and metadata. This thought is not completely without merit, but there is very little empirical motivation for it, and the tiny amount that exists is more than completely explained by the OOP hype that only started to die around a decade ago.

EDIT: Oh I saw you wrote SQL Alchemy! First, thank you for that great piece of software.

Now, SQL Alchemy does provide a lot of useful ways to postpone the object representation or even to map your values into dumb data. My comment up there is on theoretical limitations, but on practice, I do think it's the best option available on Python. (And maybe about the best option afforded, since the language is intrinsically biased into OOP.)

Re: Python: Just Write SQL

#152

Post by jrichardshaw »

davidthewatson wrote: post #71

I'm happy to see someone mention peewee, having used it for numerous startup prototypes since its inception. Coleifer does not get enough credit IMHO: https://github.com/coleifer/ Peewee has been solid since I began using it a decade ago. Coleifer's stewardship is hard to see at once, but I've interacted with him numerous times back then and the software reflects the mindset of its creator.

I'd definitely like to second how great peewee is. It's been a core part of running our telescope for the last 8 years. It strikes a nice balance between power and simplicity, a significant set of useful extensions and great documentation.

Sometimes I find it impossible to believe that @coleifer is just one person. Peewee has 2300 issues and 500 PR's none of which are open and outstanding, and almost all of which he has personally responded too in a genuinely helpful way. He pipes up on Stackoverflow for peewee questions too.

Re: Python: Just Write SQL

#153

Post by DangitBobby »

nicoburns wrote: post #149
DangitBobby wrote: post #141

Earlier quoted context omitted.

In Django that would be .filter(column_a='value1', Q(column_b__isnull=True)|Q(column_b='value2')) And obviously you can use whatever indentations you like.

Ah, that's quite a bit nicer. You can't do that in JavaScript on two counts: - No keyword arguments - No operator overloading (so you can't override | to get the nice "or" syntax)

I actually messed it up a little because I'm not sure you can mix positional and kwargs in filter, and you definitely can't use kwargs first. Still, the idea is there.

In JS, theoretically you could design an API like

.where({column_a: 1}, Q(column_b__isnull=True).or({column_b: 2}))

Which really isn't bad IMO

Re: Python: Just Write SQL

#154

Post by waffletower »

dagmx wrote: post #122
zzzeek wrote: post #113

ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…

I completely agree with you. Every single time someone says: “Just write your own abstraction over an sql generator ”, it eventually devolves into a full blown ORM. I swear the majority of these opinion posts against ORMs are from people who must have worked in a badly implemented project that left them with a bad experience and they blamed the pattern rather than the technology. One of the best bits about an ORM is…

Enough people have had bad experiences with ORMs who have decided to evolve and find alternatives. ORMs are bulky and do not provide value to many modern functional, data-first development paradigms.

Re: Python: Just Write SQL

#155

Post by ggregoire »

megaman821 wrote: post #115

Question to the SQL-only people, how would you handle something dynamic? If I have a database of shoes and want people to be able to find them by brand, size, style, etc., what does that look like?

If you don't want to maintain several queries, you could write something like

    SELECT *
    FROM shoes
    WHERE (CASE WHEN :brand_id IS NOT NULL THEN brand_id = :brand_id ELSE TRUE END)
    AND (CASE WHEN :size IS NOT NULL THEN size = :size ELSE TRUE END)
    AND (CASE WHEN :style IS NOT NULL THEN style = :style ELSE TRUE END)

Re: Python: Just Write SQL

#156

Post by waffletower »

zknill wrote: post #67

Seems like there's 3 groups of opinions on ORMs: Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)" Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects". Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".…

There is definitely a fourth category -- "I want to build database queries natively using the paradigms of the language I am developing with, without use of SQL or an intermediary which translates into SQL."

Re: Python: Just Write SQL

#157

Post by philwelch »

rtpg wrote: post #7

If you're going to end up querying all the fields and putting them into a model like this dataclass anyways... Django can do that for you. If you're going to later pick and choose what fields you query on the first load, and defer other data til later.... Django can do that for you. If you're going to have some foreign relations you want to easily query.... Django can do that for you. If you're doing a bunch of joins…

> I am curious as to what a larger codebase with "just SQL queries all over" ends up looking like. I have to imagine they all end up with some (granted, specialized) query builder pattern.

One successful pattern I’ve seen treats the database as its own service layer. Service code does not send arbitrary SQL to the database—instead, all of the SQL queries are set as stored procedures in the DB. People sometimes freak out when I say this, so I should clarify that the stored procedures, table schemas and other things of that nature were version controlled and deployed with some minimal build tooling we’d developed in house.

I really liked this pattern. I think anything that you need to talk to across a network should be treated as a service in itself, with a well defined interface. This simplifies testing and monitoring as well. The big risk with an ORM, architecturally, is that you end up treating your database as a sidekick to your service code, or even a dumb data store—some shameful implementation detail your service keeps locked in the basement—when they’re capable of much more than that.

Re: Python: Just Write SQL

#158

Post by BeefWellington »

zzzeek wrote: post #113

ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…

While I somewhat agree that a lot of these articles are people who just don't actually try/use the full feature set of ORMs, I don't agree with the overall premise you're presenting that they really do more than write SQL for you. The other things they provide are largely just abstractions around how the queried data is returned and some additional metadata tracking of the relationships. Your estimation of those parts being 60% of the value added is probably generally wrong for most users of SQLAlchemy. Not having to consider or use another language syntax while writing your code is probably closer to 70-80% of the value for most people.

Your example gist is essentially just "ORMs excel at this one thing, anything else is worthless" and also effectively hides code ("the benefit of the library!!!") to make it a very much disingenuous comparison.

> But why would anyone really want to, writing SQL for CRUD is really repetitive and tedious. Computer can do that for you.

Performance.

I wanted to love SQLAlchemy but even in some relatively simple things it generates really asinine queries that take insane amounts of time. It's just usually not noticeably insane amounts of time at first. So you query for some data and wind up with a query that returns in 20ms when there's 20 rows in the database but falls apart when there's 200,000.

It's also bad at projecting analytics type queries onto a transactionally-normalized database, but then most ORMs are not great at that.

I had both of these issues enough times that I instead just opted to start handcrafting queries in those cases and using a single "ResultSet" type class that projects as both a SimpleNamespace and a dict.

Re: Python: Just Write SQL

#159

Post by dagmx »

waffletower wrote: post #154
dagmx wrote: post #122

Earlier quoted context omitted.

I completely agree with you. Every single time someone says: “Just write your own abstraction over an sql generator ”, it eventually devolves into a full blown ORM. I swear the majority of these opinion posts against ORMs are from people who must have worked in a badly implemented project that left them with a bad experience and they blamed the pattern rather than the technology. One of the best bits about an ORM is…

Enough people have had bad experiences with ORMs who have decided to evolve and find alternatives. ORMs are bulky and do not provide value to many modern functional, data-first development paradigms.

Why speak in absolutes? Swift data for example is a data first ORM https://developer.apple.com/documentation/SwiftData

Nobody is saying only use an ORM, but the anti-ORM crowd seem to think that there’s only one true way to do things.

I don’t know why so many programmers think purely in binary “good or bad”. Different projects may have different requirements.

Re: Python: Just Write SQL

#160

Post by xp84 »

zzzeek wrote: post #113

ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…

> writing SQL for CRUD is really repetitive and tedious

Agreed, and this is the primary reason that ORMs are a necessary tool. And possibly that it's easier to train a junior developer to use one than it is to get them to a basic level of proficiency (and security awareness!) in SQL.

That said, I think this is one of those areas where It's Complicated, because inefficient database calls due to ORM usage is one of the primary ways I see applications completely break down. For most types of apps I've seen, which are very read-heavy and which aren't doing intensive writes, roughly all your writes should probably be using the ORM (including such niceties as validations, default scopes, all that nice stuff), and if it's simple enough, your "show" actions (fetch and display one entity) may be fine as well, but every "dashboard" and "index" action (show many entities, basically things with joins) likely need to be written in SQL.

In my experience (Rails), the object instantiation cost is insane, much greater than the actual time talking to the DB, so not only do you need to write SQL, but you need to handle the data that comes back without instantiating ActiveRecord models.

This is much harder work (more specifically, it needs much greater skill and experience, and is easier to make a mess of) versus using the ORM and models, but only on apps with tiny amounts of data per request, or very low request volume, can the ORM be a serious exclusive option for this task. Unless you want to end up like an app I once was asked to help fix, where they were on the $9,000 per month Heroku postgres instance, and since that's the biggest one, they could "scale" no bigger. (Okay, this wasn't their only problem, their main one was not understanding that you don't sprinkle analytics DB writes all over the place because now the simple high-volume "read" pageviews can't be generated using just a read replica).

Post reply on HN