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

91–100 of 288 posts

Re: Python: Just Write SQL

#91

Post by fmajid »

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 of setuid), thus improving the security posture dramatically.

One example: you could have a procedure authenticate(login, password) that has access to the table of users and (hashed) passwords, but if the Python app server is compromised it doesn't have access to even the hashed passwords or even the ability to enumerate users of the system.

Re: Python: Just Write SQL

#92

Post by ZitchDog »

boxed wrote: post #83
nicoburns wrote: post #53

Earlier quoted context omitted.

> The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot. I used to think this, but at my last company we ended up rewriting all these queries to use conditional string formatting as we found it much more readable. The key was having named parameter binding for that string, so you didn't have to worry…

Sounds like just begging for SQL injection attacks.

You can avoid this entirely with JavaScript's tagged template literals. Here is an example library: https://github.com/blakeembrey/sql-template-tag

Re: Python: Just Write SQL

#93

Post by baq »

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.

Re: Python: Just Write SQL

#94

Post by karmakaze »

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

Maybe those are the main/popular groupings. Where I fall is that I want typesafe constructions of queries that match the current schema. The query compositions should follow the SQL-style structure so there's no 'shape-mismatch' composing the query using the library. Some may not consider this to be an ORM (though it does map relations to objects).

Re: Python: Just Write SQL

#95

Post by llanowarelves »

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…

I think it ends up being hybrid, like seen in CQRS and especially with DDD. Toss in "Vertical Slice Architecture" as well. What abstractions you want can decided on a per command or query basis and it feels natural.

Re: Python: Just Write SQL

#96

Post by joaodlf »

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…

> 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 composable code.

I don't really disagree, but there are many ways to skin a cat. You can absolutely write maintainable code taking this approach. In fact, I can build highly testable, unit, functional, code following a abstraction very similar to this. The idea that "maintainable and composable code" can only be achieved by having a very opinionated approach to interacting with a database, is flimsy. I offer a contrary point of view: With the Django ORM, you are completely locked in to Django. You build around the framework, the framework never bends to your will. My approach is flexible enough to be used in a Django project, a flask project, a non web dev project, any scenario really. I want complete isolation in my business logic, which is what I try to convey just before my conclusion.

Re: Python: Just Write SQL

#97

Post by pydry »

nicoburns wrote: post #89
pydry wrote: post #86

Earlier quoted context omitted.

>But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable. This is the problem with not using an ORM. If you cut it out and move everything to parameterized SQL queries the SLOC explodes which massively inhibits readability as well as introducing bugs. If your issue with ORMs is just that you're familiar with SQL and you don't like how ORMs look then I think the is…

> If you cut it out and move everything to parameterized SQL queries the SLOC explodes My experience has been the opposite: that raw SQL queries end up much shorter (and consequently more readable) than the equivalent ORM code. The exception to that is INSERT/UPDATE queries, where I do tend to use some kind of ORM/query builder. I have used both, and I prefer raw SQL for anything beyond very simple queries.

I have never seen an ORM in my life that didn't reduce the total amount of code written. Not even the Java monstrosities increased the SLOC.

Re: Python: Just Write SQL

#98

Post by Izkata »

agumonkey wrote: post #64
noirscape wrote: post #23

Earlier quoted context omitted.

The main problem I've encountered with complaints surrounding ORMs usually tend to be the result of trying to overfit the ORM in a certain way. ORMs are, for the most part, good at the CRUD operations - that is to say, they easily translate SELECT, UPDATE, INSERT and DELETE operations between conventional class objects and database rows. Things they usually aren't very good at are when you start trying to do things t…

I realized too late that objects, as of now, are not capable of synthetizing a new class/type based on joins.

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 about the columns not matching up wherever it's used.

Re: Python: Just Write SQL

#99

Post by rglullis »

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…

Add refactoring, migrations and testing to all the reasons you mentioned, and it quickly becomes an adapted case of Greenspun's Tenth Rule for ORMs.
Post reply on HN