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

271–280 of 288 posts

Re: Python: Just Write SQL

#271

Post by aforwardslash »

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'd sure want to automate all that tedium. I'd want a one-to-many collection too maybe.

You don't actually need an ORM for that. And some of us (I know I'm a minority) actually want to have pure data objects. Some of us (talking about myself again) actually design schema-first - as in, you use DDL and your code just consumes the definition. Some of us don't care about code knowing about relationships which are an intrinsic part of the data model.

> You can use SQLAlchemy (which I wrote) and write all the SQL 100% yourself as literal strings, and still use the ORM

SqlAlchemy is an amazing piece of software, and it is relevant becaise it is actually part of the problem. You don't really have an ecosystem of database abstraction libraries in python, as you have in other languages. The options are SqlAlchemy or Django ORM. Golang? You have 50 different libraries that implement 100 ways of doing the same stuff. C#? You have probably a dozen libraries besides Entity framework that provide different level of abstractions? Java? You got low level stuff up to high level ORMS. PHP? The same.

Python? You have SqlAlchemy, Django or roll your own. Both SqlAlchemy and Django can do raw queries with little effort - but if you're doing this, arent't you discarding most of the functionality that comes with it - namely, code maintenance? If you have a big project with a bunch handwritten queries (because you can do it, and quite easily, I may add), aren't you just ignoring any of the advantages of the orm? If, when you edit a model, you need to also check handwritten sql queries, you shouldn't be using an orm in the first place.

And with SqlAlchemy and Django, there is no middle ground. You either are all in, code-first definitions, or you are in a world of pain.

That is why specialized applications tend to be rewritten in other languages. Because optimizing the data layer alone often boasts 10-100x performance increase, when compared to this kind of abstraction.

> writing SQL for CRUD is really repetitive and tedious

It is. But between that and SqlAlchemy - in most languages, there are options. Not in Python. Between orm and no orm, I'll take no orm any day of the week - at least I can understand the queries.

Re: Python: Just Write SQL

#272

Post by reportgunner »

promiseofbeans wrote: post #11

> ... Python dot not have anything in the standard library that supports database interaction, this has always been a problem for the community to solve. Python has built-in support for SQLite in the standard library: https://docs.python.org/3/library/sqlite3.html

sqlite-utils[0] is a great library for working with SQLite

[0] https://sqlite-utils.datasette.io/en/stable/

Re: Python: Just Write SQL

#273

Post by aforwardslash »

zzzeek wrote: post #200
lelanthran wrote: post #194

Earlier quoted context omitted.

Okay, you're the expert here, and I'll happily concede that I am not (and apologise in advance if I seem to be disrespectful), but ... > The SQL is not really the point. It's about the rows and objects, moving the data from the objects to the INSERT statement, moving the data from the rows you SELECTed back to the objects. I think that that is the problem: mapping a relational dataset to a hierarchical dataset is the…

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…

> But if you are writing for 1200 tables

Have you ever managed 1200 "live" (in-development) tables on a SqlAlchemy app? That is impressive. Not only because of the amount of work that entices, but also because you can just read the schema and provide a dynamic object representation of it (think dataclasses, but based on a table schema). Also, if you're querying 1200 tables, python might not be such a good idea. Impedance mismatch at a language level.

> boilerplate code for 1200 classes, yes that's kind of ORMish

Yes, please do explain how a Python ORM can save you from having 1200 classes you need to maintain, plus the need for specialized queries.

Re: Python: Just Write SQL

#274

Post by aforwardslash »

joaodlf wrote: post #136
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…

> 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. Fair. But you then proceed to detail a personal experience on the other side of the spectrum: Badly written code, without an ORM, and how it was fixed by introducing an ORM. I think we can all agree t…

> Badly written code, without an ORM, and how it was fixed by introducing an ORM.

Funny how those anecdotal bits never get published, eh? Its always "I shaved my db requirements in half by actually understanding what was going on because my orm is magic". Odd, innit?

Re: Python: Just Write SQL

#275

Post by aforwardslash »

hnfong wrote: post #254
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 mean, that was really an important point in time when there really werent ORMs that were easy to work with, there was Hibernate in a very early stage and there were overly simplistic things for Perl, so I thought it was important that this "better way" I had in mind could be put out there, before the idea that "yeah let's all avoid ORMs unconditionally" could take hold. I totally remember this time. It was around…

> try implementing HN in a week

Not all of us work with prototypes someone will rewrite later on. Design a high traffic, low budget site with Django, and you will quickly understand the trade offs. It's not all about less coding effort; in most dev positions that matter, your work is cheap compared to the runtime cost - eg. optimizing something to take 10% less has the potential to shave multiple times your salary off the global cost pool.

Re: Python: Just Write SQL

#276

Post by aforwardslash »

jpc0 wrote: post #231
jacurtis wrote: post #142

Earlier quoted context omitted.

> I would argue to start with writing the basic SQL queries and adding an ORM later when you know you actually need it. I think you missed the point of the parent comment, which is that ORM's are not about writing SQL queries (although they do that). But ORMs are about moving data around, transforming it from rows and columns into meaningful objects in the project's language and data model. As the parent comment sugg…

> But ORMs are about moving data around, transforming it from rows and columns into meaningful objects in the project's language and data model. I made my point about this but will repeat, I am generally writing validation for this, adding the code that converts this into known types isn't significantly more work, if I was never writing the validation this point would make sense. Also the ORM the parent comment is an…

> Regarding abstraction over database drivers, well how often do you need an abstraction over database drivers, are you really using two or more different databases in a single project?

Extending on this, you actually shouldn't even use different databases. There are bugs and quirks between different databases on things like string matching :) Having a multiple database project is often a nightmare, as you need to track all of these small differences and nuances that may affect the way your system works.

Re: Python: Just Write SQL

#277

Post by nicoburns »

boxed wrote: post #269
nicoburns wrote: post #199

Earlier quoted context omitted.

> What are you interpolating? Some variable. Nope, I'm generally interpolating an inline expression consisting entirely of string literals.

> generally Does not inspire confidence.

In the rare case that you're interpolating a variable, you'd need to audit it in review. This is similar to carefully auditing the rare use cases of raw SQL expression when using an ORM.

Re: Python: Just Write SQL

#278

Post by aidos »

lelanthran wrote: post #258
aidos wrote: post #256

Earlier quoted context omitted.

To be fair, in sqlalchemy you could just do “print(query)” and you’d see your sql.

> To be fair, in sqlalchemy you could just do “print(query)” and you’d see your sql. I wasn't saying that you couldn't see the raw SQL, I'm saying that the ORM syntax does not result in a consistent experience for everyone.

Sure, but if you’re working in a sqlalchemy codebase, you should really learn enough sqlalchemy to get by. I don’t jump between languages and frameworks and expect a consistent experience but ideally I’d have an experience streamlined for the context.

If you’re a db expert that’s doing drive-by optimisation of queries in sqla then you can print them to see what’s going on - is that not broadly what you’re concerned with in your original comment?

Re: Python: Just Write SQL

#279

Post by specialist »

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

> ...this pushes engineers to adopt number 2

Yup. Use of HQL/JPQL is proof that you shouldn't be using an ORM. Just use SQL instead. And once you have some SQL, it's just easier (overall) to do all SQL.

Re: Python: Just Write SQL

#280

Post by specialist »

tantaman wrote: post #180
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".…

I'm in a 4th camp: we should be writing our applications against a relational data model and _not_ marshaling query results into and out of Objects at all. Elaborations on this approach: - https://news.ycombinator.com/item?id=34948816 - https://github.com/papers-we-love/papers-we-love/blob/main/d... - https://riffle.systems/essays/prelude/

Nice share, thanks.

I was never more productive than when using Access (and dBase II before that). Why can't we have that?

My theory: Something was lost in the jump from workgroup computing to client/server.

Imagine if Access was rebuilt on top of a client/server stack. That's kind of what Riffle is trying to do.

I've been (slowly) working on the persistence stuff. It (mostly) moots the SQL vs ORM vs query builder slap-fight.

I've got some notions (and POCs) about UI, mediated via HTTP & HTML.

I'd love to have some CRDT-like smarts; learning more is on my TODO list.

I'm still thinking about the "reactive" part. I haven't imagined anything past Access VBA style programming. I'm struggling to envision a FRP-meets-CRUD future perfect world.

Post reply on HN