Python: Just Write SQL

Post by jeltz » Mon, Aug 14, 2023, 2:03 PM UTC

m000 wrote: post #37

bbojan wrote: post #18

The article is missing the code for creating the "users" database table. What about indexes? Migrations? Relations to other tables? I mean you can just write SQL instead of using the ORM if your project consists of a single table with no indexes that will never change, sure.

And good luck with writing tests for your sql code.

Post by felipetrz » Mon, Aug 14, 2023, 2:05 PM UTC

Post by nicoburns » Mon, Aug 14, 2023, 2:12 PM UTC

pydry wrote: post #97

nicoburns wrote: post #89

Earlier quoted context omitted.

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

    .where('column_a', '=', 'value1')
    .and(q => q.isNull('column_b').orWhere('column_b', '=', 'value2')))

    WHERE
       column_a = 'value1'
       AND (column_b IS NULL OR column_b = 'value2')

Post by hobs » Mon, Aug 14, 2023, 2:14 PM UTC

pydry wrote: post #45

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…

>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group. You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize. There is also a small % of queries which us…

Post by felipetrz » Mon, Aug 14, 2023, 2:15 PM UTC

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.