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

221–230 of 288 posts

Re: Python: Just Write SQL

#221

Post by hot_gril »

hot_gril wrote: post #169
Rudism wrote: post #166

The next logical step after writing the code given in the article is to abstract common boilerplate SQL into a library so you're not spending 50% of your time writing and re-writing basic SQL insert, update, and select statements every time your models need to be updated. At which point all you've done is write your own ORM. If you want to go full-blown SQL you can use something like PostGraphile, which allows you to…

The SQL inserts/updates have never felt tedious for me even in large projects, partially owed to careful use of jsonb for big objects where it makes sense (e.g. user settings dicts). Other than that, keeping a tight schema design.

Of course, jsonb didn't exist until 2014ish. IMO this was a serious gap in SQL before, and it likely spawned the concepts of NoSQL and ORMs to begin with, which may have been the inspiration for jsonb. Hurray for competition.

Re: Python: Just Write SQL

#222

Post by joaodlf »

boxed wrote: post #198
joaodlf wrote: post #96

Earlier quoted context omitted.

> This is just reimplementing Djangos ORM, bud badly. I guess this is a good thing, as "reimplementing" Django's ORM is the opposite of what I wanted to do here :) > ORM queries compose. That's why [Python] programmers prefers them. You can create a QuerySet in Django, and then later add a filter, and then later another filter, and then later take a slice (pagination). This is hugely important for maintainable and co…

Djangos ORM isn't highly opinionated. That's just wrong. > With the Django ORM, you are completely locked in to Django Another bit of nonsense again. You have a dependency. Sure. Just like you have a dependency on Python. But it's an open source dependency, and the ORM part is a tiny part that you can just copy paste into your own code base if you want. Also, worrying about being "locked into" something that you depe…

> Djangos ORM isn't highly opinionated. That's just wrong.

It's a fully featured ORM... Including migrations, query API (which is HIGHLY opinionated, it looks nothing like SQL), supports async (via asgiref!), custom model definition... It's almost the definition of opinionated. Not that you can build a fully featured ORM without being opinionated. That's not a dig at Django btw.

> Also, worrying about being "locked into" something that you depend on is madness. Where does it end? Do you worry about being "locked into" Python? Of course not.

Ermm, my premise is that you DON'T have to depend on it. It's not that crazy to not want lock in when it comes to the software that handles my database. Other programming language communities seem to handle that just fine.

The rest is a bit too ad hominem for my liking, so I'll pass.

Re: Python: Just Write SQL

#223

Post by lelandbatey »

boxed wrote: post #82

This is just reimplementing Djangos ORM, but badly. ORM queries compose . That's why [Python] programmers prefers them. You can create a QuerySet in Django, and then later add a filter, and then later another filter, and then later take a slice (pagination). This is hugely important for maintainable and composable code. Another thing that's great about Djangos ORM is that it's THIN. Very thin in fact. The entire impl…

Can you explain a bit more about the Django ORM being very thin and easy to read? It does seem like the Django ORM is thin (from an architecture perspective), but it doesn't seem to be small, it seems to be pretty big. Maybe I'm not understanding it though, so here's what I see:

The "ORM" part of Django seems to be everything in `django.db.models.Model`, which seems to require you to declare your Models as subclasses of the aforementioned class. Looking into that code though, it seems like the implementation supporting all this is around ~20,000 lines of Python: https://github.com/django/django/tree/main/django/db/models

That doesn't strike me as a super lightweight. For comparison, all of Flask (a Python WSGI web app framework, but mostly a 10+ year old project to compare to, and excluding tests) is ~4,000 lines of Python.

Is there a small subsection of the code in `django/db/models/` that is all that's necessary to use the ORM part? Or maybe I'm missing something about the "core" of the ORM?

Re: Python: Just Write SQL

#224

Post by gregw2 »

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 had a boss once with a similar viewpoint, so I learned from him how they automated database deployments for Java/Scala apps using Liquibase+Maven and found I could apply and integrate the same principles to testing and deploying a data-layer stored procedure engine I had previously built into a joint product we were building together.

For that project I was able to put SQL DDL+DML+stored procedures in version control, create/run stored procedure (TDD even) unit/integration tests on mock data against other stored procedures, had pass-fail testing/deployment in my CICD tool right alongside native app code, and did some rollback support (although that was more trouble than I think it was worth), all using Liquibase change sets (+git+Jenkins). Flyway could also have worked but we were using Liquibase.

It did require some creative thinking to apply changesets and preconditions and post conditions and ability to have stored procedures execute and read results of other stored procedures.

Last I checked, the system had been used over many years to process/evaluate $50 billion in order transactions.

Re: Python: Just Write SQL

#225

Post by squeaky-clean »

DangitBobby wrote: post #153
nicoburns wrote: post #149

Earlier quoted context omitted.

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

It's been a bit since I've used Django, but I believe you can just swap the order so the kwarg comes last.

    filter(     Q(column_b__isnull=True)|Q(column_b='value2'),
      column_a='value1',
    )
Or just turn it into another Q

    .filter(Q(column_a='value1'), Q(column_b__isnull=True)|Q(column_b='value2'))

Re: Python: Just Write SQL

#226

Post by ak217 »

I've seen many iterations of this type of debate by now, and learned to recognize the patterns. The people arguing for the ostensibly simpler solution are really asking you to trust their ability to architect apps out of simpler building blocks without using an abstraction that they don't like. This can work, but it often leads to situations like someone writing a bespoke system, then leaving the job or otherwise imposing extra complexity on the team. In the immediate term, what often gets overlooked is

- The ORM is an externally maintained open-source project with a plurality of contributors; "just write SQL" is not

- The ORM is designed to support the full lifecycle of the application including migrations; "just write SQL" is not

- The ORM is documented to be legible to newcomers; "just write SQL" is not (for all but the simplest of applications)

- The ORM is composable and extensible with opinionated and customizable interfaces for doing so (I've lost track of the number of times I've had my mind blown by how elegant and smart Django and SQLAlchemy's query management tooling is)

- The ORM has a security posture that allows you to both reason about your application's security and receive security updates when bugs are found

- The ORM is a platform for many other modules responsible for different layers of the application (DRF, OpenAPI, django-admin, testing utilities, etc. etc.) to plug into and allow the application to grow sustainably

I now try to guide people to a middle ground. Yes, both Django's and SQLAlchemy's ORMs can be annoying, have performance issues, etc. But for large applications maintained by multiple people over time, their benefits usually outweigh the drawbacks. Both have extensible architectures that allow customization and opinionated restriction of the interface that the ORM presents. If you're unhappy with your organization's ORM, I suggest you try that route first.

Re: Python: Just Write SQL

#227

Post by squeaky-clean »

boxed wrote: post #189
nicoburns wrote: post #88

Earlier quoted context omitted.

Values were still provided separately. The string-interpolated SQL would include a placeholder just like static SQL does. That's pretty easy to audit for in code review: no variables in interpolated code.

That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.

    sortable_fields = ["name", "age", "gpa"]
    
    selected_filter = sortable_fields[form.filterIndex]

    if form.sortBy == "asc":
      query += "ORDER BY {} ASC"
    elif form.sortBy == "desc":
      query += "ORDER BY {} DESC"
Doesn't have any opportunity for SQL injection unless you have rogue programmers able to change code running in prod.

Re: Python: Just Write SQL

#228

Post by zzzeek »

joaodlf wrote: post #218
zzzeek wrote: post #200

Earlier quoted context omitted.

yah I read Neward's thing, and it was one of the main reasons I wrote SQLAlchemy in the first place, because he was just so wrong. It read like he tried to write some object relational thing and it didn't work out, so he goes off and rant rant ORMs are wrong. Kind of proving that post wrong was one of the primary goals of SQLAlchemy, really, where I sought to change the question of "impedance mismatch" and "leaky abs…

I don't think it is farfetched to say that, for the good and bad, modern software development is moving away from a single project having to handle "1200 tables". As we see the growth of "services" (gasp, microservices!), the scope for codebases is reduced, hence why the pattern in my post (OP here) is so common to see in Go. Are Go developers masochists? No... But when you're working on (micro)services and your imme…

I'm going to propose, really just out of my butt so to speak, that most Go apps that use databases nonetheless are themselves middleware kinds of services that are involved with software infrastructure, as opposed to what we might call "business cases". That is, American Express might have a bunch of Go services that are deriving data from small configurationally-oriented databases all around the organization, but "the database with everyone's account information and credit card statements" is absolutely not a five table DB with a single Go application on top of it. 1200-table databases at the center of business cases will continue to exist, it's just Go applications are not themselves centralized business applications, Go is currently an infrastructure language (I googled around for this conjecture and it's obviously debated, but is still a pretty prevalent assertion I can find being made a lot). The boring business stuff is still in places like Java, Python, C#, etc.

Re: Python: Just Write SQL

#229

Post by fmajid »

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.

SQL scripts with anonymous PL/pgSQL DO blocks to do unit tests, with a library of utility functions to do things like assertions, just as you would in any imperative language. Granted, the PL/pgSQL debugger tooling is not ideal but it works, and we wrote our line-by-line own profiler to identify hotspots.

Re: Python: Just Write SQL

#230

Post by habitue »

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

Honestly, sqlalchemy is such a different breed of ORM, whenever people slander ORMs, I'm imagining they're thinking of like Django ORM or ActiveRecord which are like the duplos to SQLalchemy's Legos.
Post reply on HN