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

121–130 of 288 posts

Re: Python: Just Write SQL

#121

Post by gmassman »

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",…

Thanks for you perspective, Mike. Completely agree that the interface between data and code should be handled by a single tool. That tool must meet some minimum complexity, because it’s solving a very hard problem! Also just want to say that my team has benefited greatly from your work on SQLAlchemy, and we appreciate you immensely!

Re: Python: Just Write SQL

#122

Post by dagmx »

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 making it consistent for non-db users on the team to simply grok and work with, without creating monstrous and hard to debug joins everywhere. But when badly set up it can lead to a lot of debugging spaghetti. Which is the same as can happen with SQL but I suppose people think that at least there’s one less layer to debug while ignoring the problem is actually how they got where they are and not the technology

I switched a project from manually written sql to sql alchemy on a project that’s used by multiple Oscar nominated films daily for reviews. The SQL version was gross and impossible for the team to manage because it had bloated over the years, with no nice way to detangle the statement generations and joins. SQL Alchemy made it so any one of the technical directors on the team could step in and add new functionality, without serious performance footguns. Instead of me having to clean up bad sql every year (projects would fork per film and merge at the end) to keep performance up, I could trust the ORM to do that for me.

At its worst, it was way too easy for TDs to get the raw SQL to be tens of minutes per review session by structuring their logic incorrectly, but it was so difficult to see. Switching to an ORM meant I could get the performance down to seconds per session and they couldn’t destroy the performance in subtle ways.

Re: Python: Just Write SQL

#123

Post by fb03 »

Alright, let's use the custom approach. And then you need another field. and then you need some slight type checking or (de)serialization, which can change over time. You'll end up writing your own custom, kludgy ORM over time.

I have seen people write their own custom crazy version of GraphQL ("I've created a JSONified way of fetching only some fields from an API call) over ego or just ignorance. It's never a good path.

Why bother moving away from SQLAlchemy, which will do all of that for you in a simplified, type-checked and portable way? SQLAlchemy is literally one of the best ORMs out there, it's ease of use and maintainability is insane.

People that complain about ORMs might have never really used SQLAlchemy. It is that good. I'm a fan and zzzeek is huge force behind why it is so good.

And as always, if you need an escape hatch, you can use raw sql for that ONE sql statement that the ORM is giving you grief for.

Re: Python: Just Write SQL

#124

Post by OJFord »

agumonkey wrote: post #117
Izkata wrote: post #98

Earlier quoted context omitted.

Django lets you define an abstract model for the resulting set of columns, then you can use raw SQL on that model to get something that looks like a normal model to the rest of the code. As long as the raw query has the right number of columns, and of the right data type, Django doesn't care how it's populated. Then you can just stick the query behind a classmethod on the abstract model so you don't have to worry abo…

how is it called in the docs ?

If I understand GP correctly, it's 'performing raw SQL queries', or specifically here, 'mapping query fields to model fields': https://docs.djangoproject.com/en/4.2/topics/db/sql/#mapping...

Basically you call `.raw("...")` from some model's queryset, but there's no requirement you actually query that model's table at all.

    class SomeModel:
        name = models.CharField()

    class OtherModel:
        foobar = models.CharField()

    SomeModel.objects.raw("select foobar as name from thisapp_othermodel")
will yield `SomeModel`s with `name`s that are actual `OtherModel` `foobar` values.

Re: Python: Just Write SQL

#125

Post by mitch3x3 »

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 statements that either add conditional statements or blank lines to the SQL block. There are a lot of tradeoffs to the pure SQL method but I prefer being able to look up exact snippets in the codebase to find things.

Re: Python: Just Write SQL

#126

Post by WesolyKubeczek »

My god, object-relational impedance mismatch seems to be more polarizing than US politics. I've been observing this field for more than 15 years, and it's always "JUST WRITE SQL!!!!!" versus "DRY!!! DRY!!! USE ORMs SO YOU DON'T HAVE TO WRITE SQL!!!! BUSINESS LOGIC!!!" shouting matches. It's like this topic itself takes 30 IQ points away from each participant and the conversation then devolves into complete chaos.

Maybe there's some professional trauma at work, as many of us have been traumatized by shitty databases and shitty code working with them alike, ORM or not. But ORMs do come and go, promise bliss, deliver diddly, and I'm reading the same stuff I've been reading in 2008, as if nothing ever changed since.

Re: Python: Just Write SQL

#127

Post by joaodlf »

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",…

First of all, thank you for SQLAlchemy! If I ever had to make a final choice in how I would interact with a database for a very large project that involves a considerable dev team, I would always bet on SQLAlchemy. Not that I would necessarily like all aspects of it, but when it comes to Python and SQL - “Nobody ever got fired for picking SQLAlchemy.”.

With that out of the way, despite ORMs doing much more than "just writing SQL", it is exactly on that point that I flinch: Most devs should be exposed to SQL. And if your project allows you to build around simple enough abstractions so that you aren't reinventing the wheel, you should definitely be writing SQL. Especially if you don't know SQL yet - which is the growing case of new devs coming into the job market.

You can achieve a lot with SQlAlchemy Core, a tool that I absolutely recommend, but my post is just a simple alternative to get developers to think about their approach. If that results in some devs reconsidering using "full fat" SQLAlchemy and to try SQLAlchemy Core, that's a win for me!

Your gist tries to highlight the difficulty of doing certain things without an ORM. Migrations (as just 1 example) doesn't need to be hard, simple tools like flyway, or migrate (https://github.com/golang-migrate/migrate) achieve a similar result (while also keeping you on the path of writing SQL!). Deep and complex relationships between objects also don't need to be hard - typically people approach this subject with a requirement to be very flexible in the way they want to build queries and objects, but that to me in a sign that maybe they should reconsider their business logic AND reconsider that, just maybe, their project doesn't require all that flexibility, it is fairly straightforward to extend objects and introduce some more complex representations as and when it is needed - will all of this make me write code faster? Absolutely not. That is why you have spent so much time perfecting SQLAlchemy, but then again, I am not advocating for devs to go and replace their usage of ORMs, just presenting an alternative that may or may not fit their needs for a new project + give devs the chance to learn something that the ORM might have taken away.

Re: Python: Just Write SQL

#128

Post by [deleted] »

lijok wrote: post #52
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. But I think my bias is influenced by always working on software where there are just so many columns per table that it would be way too much busywork to not use something. You end up writing a query per usecase, rather than writing…

[deleted]

Re: Python: Just Write SQL

#129

Post by pak9rabid »

sergioisidoro wrote: post #22

I've used Rails AR and Django/SQL Alchemy orms, and the more I use it, the more I wish for a fusion of both. Django ORM is amazing for Schema management and migrations, but I dislike their query interfaces (using "filter" instead of "where"). I really like Rails AR way of lightly wrapping SQL, with a almost 1-1, and similar names, but does not have a migration manager - and there is always the chance that your schema…

Hmm, is AR's Migration framework not a migration manager?

Re: Python: Just Write SQL

#130

Post by claytonjy »

baq wrote: post #93
fmajid wrote: post #91

I'd take it a step further and move all SQL into stored procedures and call those using a functional interface. That's because of PostgreSQL's excellent stored procedure support, it might be harder with, e.g. MySQL. One major benefit of stored procedures, in addition to separation of concerns, is that you can declare them SECURITY DEFINER and give them access to tables the Python process doesn't (in a way reminiscent…

Last project we've explicitly decided to not have any stored procedures ever since you basically can't test nor deploy them in any sane way. I'm all ears how you make it work.

I used sqitch to have very explicit tests for procedures, written in PL/pgSQL. Worked great though writing that code was a little weird due to lack of IDE support I'm used to in any other language. Could take it even further with pgTAP.
Post reply on HN