[HN Gopher] Python: Just Write SQL
___________________________________________________________________
Python: Just Write SQL
Author : joaodlf
Score : 197 points
Date : 2023-08-14 08:14 UTC (14 hours ago)
(HTM) web link (joaodlf.com)
(TXT) w3m dump (joaodlf.com)
| baq wrote:
| It's fine advice... if you don't ever need to build queries
| programmatically (you will, probably) and don't care about type
| checks (you should, it's 2023).
|
| If you don't know what you're doing on the DB-app interface,
| you're still better off with an ORM most of the time. If you
| don't know if you know, you don't know (especially if you _think_
| you know but details are fuzzy); please go read sqlalchemy docs,
| no, skimming doesn 't count.
|
| If you know what you're doing but are new to Python, use
| sqlalchemy.core.
|
| PS. zzzeek is a low-key god-tier hacker.
| rav wrote:
| It's fine advice - if you can type check your queries. My
| colleague wrote a mypy plugin for parsing SQL statements and
| doing type checking against a database schema file, which helps
| to identify typos and type errors early:
| https://github.com/antialize/py-mysql-type-plugin
| baq wrote:
| Raw sql doesn't compose, so it's a no go for me except in
| special cases, but the tool would be a great addition to
| sqlalchemy.core for when those special cases occur.
| dep_b wrote:
| A good ORM knows when it needs to fuck off. I just want an easy
| and boiler plate avoiding way to do crud operations on certain
| tables and map a custom type against a custom query. The lengths
| I have to go through to just have a custom query in some ORM's is
| mind-boggling. I remember fighting Microsoft's Linq to SQL or
| whatever the incarnation was called so hard. I could do it in the
| end but it fought me all the way to the end.
| CodeWriter23 wrote:
| SQL and ORM both have merit in different situations. Pick the
| correct tool for the given use case, and don't be afraid to mix &
| match IMO.
| mkl95 wrote:
| A better title would be "just write your own ORM".
|
| I have used several Python ORMs over the years, both for SQL and
| NoSQL. SQLAlchemy is the most powerful way of interacting with a
| relational database I have experienced.
|
| I also write Go, and when I do, I do not use an ORM. But when it
| comes to Python I know my solution won't be better than
| SQLAlchemy, so why bother rolling out my own?
| Jackevansevo wrote:
| > I have spent enough time in tech to see languages and
| frameworks fall out of grace, libraries and tools coming and
| going.
|
| I feel like Django ORM and SQLAlchemy are the de-facto ORMs for
| Python and have been around for over a decade. If anything I'd
| recommend juniors to pick one of these over hand rolling their
| own solution because it's so ubiquitous in the ecosystem.
| zzzeek wrote:
| 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", like persisting and loading data
| between classes and tables that are joined in various very common
| ways to represent associations between classes:
|
| https://gist.github.com/zzzeek/5f58d007698c4a0c372edd95ab8e0...
|
| this is why whenever someone writes one of these "just write SQL"
| comments, or wow here a whole blog post! wow. I just shake my
| head. Because this is not at all what the ORM is really getting
| you. Plenty of ORMs let you write raw SQL or something very close
| to it. 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. Not to mention abstraction over all the other messy
| things the database drivers do like dealing with datatypes and
| stuff like that.
|
| It looks like in this blog post, they actually implemented their
| own nano-ORM that stores one row and queries one table. Well
| great, now scale that approach up and see how much fun it is to
| write the same boilerplate XYZRepository /
| XYZPostgresqlRepository code with the same INSERT / SELECT
| statement over, and over again. I'd sure want to automate all
| that tedium. I'd want a one-to-many collection too maybe.
|
| You can use SQLAlchemy (which I wrote) and write all the SQL 100%
| yourself as literal strings, and still use the ORM, and still be
| using an enormous amount of automation to deal with the database
| drivers and moving data between your objects and rows. But why
| would anyone really want to, writing SQL for CRUD is really
| repetitive and tedious. Computer can do that for you.
| hintymad wrote:
| Do we know what Hibernate did wrong? It used to be very popular
| among Java engineers but later seemed have become an obscure
| technology.
| akkad33 wrote:
| Do you know why? In our company we still use hibernate
| heavily in our java web app
| BeefWellington wrote:
| While I somewhat agree that a lot of these articles are people
| who just don't actually try/use the full feature set of ORMs, I
| don't agree with the overall premise you're presenting that
| they really do more than write SQL for you. The other things
| they provide are largely just abstractions around how the
| queried data is returned and some additional metadata tracking
| of the relationships. Your estimation of those parts being 60%
| of the value added is probably generally wrong for most users
| of SQLAlchemy. Not having to consider or use another language
| syntax while writing your code is probably closer to 70-80% of
| the value for most people.
|
| Your example gist is essentially just "ORMs excel at this one
| thing, anything else is worthless" and also effectively hides
| code ("the benefit of the library!!!") to make it a very much
| disingenuous comparison.
|
| > But why would anyone really want to, writing SQL for CRUD is
| really repetitive and tedious. Computer can do that for you.
|
| Performance.
|
| I wanted to love SQLAlchemy but even in some relatively simple
| things it generates _really_ asinine queries that take insane
| amounts of time. It 's just usually not _noticeably_ insane
| amounts of time at first. So you query for some data and wind
| up with a query that returns in 20ms when there 's 20 rows in
| the database but falls apart when there's 200,000.
|
| It's also bad at projecting analytics type queries onto a
| transactionally-normalized database, but then most ORMs are not
| great at that.
|
| I had both of these issues enough times that I instead just
| opted to start handcrafting queries in those cases and using a
| single "ResultSet" type class that projects as both a
| SimpleNamespace and a dict.
| TX81Z wrote:
| Agree, my datasets have multiple billions of rows and if I
| don't know the details of the query, or have the ability to
| tune it, it's utterly insufficient for my needs.
|
| I still fail to see how anybody who actually knows sql and
| works with "Big Kid" datasets would use an ORM.
| ansc wrote:
| I'm using SQLAlchemy in my job, and have worked with Python for
| many years. Never have I seen a good case of someone using
| SQLAlchemy to hydrate objects from raw SQL queries. I'll
| definitely admit -- I have not gone out of my way to search for
| it. It seems that it is a common want to do this kind of 60%
| benefit ORM you speak of, but it's definitely unclear to me how
| to pick those parts together with the daunting (and fantastic)
| piece SQLAlchemy is.
| IKantRead wrote:
| To add to what you're saying: It's not like the Object-
| relational impedance mismatch[0] is some great unknown property
| of ORMs. Since people have been putting ORMs into production
| designers of these systems have been well aware that you must
| always chose a trade-off between a full functional object
| system and a fully relational one.
|
| And the mismatch has two sides. If one's answer is "just use
| SQL!" then you're going to have new problems dealing with the
| mismatch coming from the SQL side of things.
|
| The pre-ORM solution to this was not simply to shove a bunch of
| SQL in your application logic (though this was done), but to
| have a much, much more complex set of data layer logic. I'm
| guessing most people today writing about eschewing ORMs in
| favor of pure SQL have never used: stored procedures, triggers,
| cascades etc. I personally do miss some of the features from
| that era of software, but there's _a lot_ more complexity to
| the "just write SQL" approach than most people realize.
|
| 0.
| https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
| jpc0 wrote:
| > and still be using an enormous amount of automation to deal
| with the database drivers and moving data between your objects
| and rows
|
| I've found I would generally only need the handling of database
| drivers and query building since I'm already writing a
| validation layer and to add data marshalling to that is pretty
| trivial.
|
| Likewise practicing YAGNI, what is the chances I need multiple
| database drivers for different databases, it's extremely
| unlikely that I'm going to be chopping and changing between
| different databases so I'm really only writing that code once.
|
| I would argue to start with writing the basic SQL queries and
| adding an ORM later when you know you actually need it.
|
| It's much easier to onboard someone into pure python code + a
| db driver vs having to onboard someone to SQLAlchemy, since it
| is quite a complex piece of software, necessarily complex for
| what it is trying to achieve but if you don't need it it's not
| a good fit.
| jacurtis wrote:
| > 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 suggested, if you are dying to write
| your own SQL (which does often happen as queries get more
| complex and don't fit into ORM language model) then you can
| write raw SQL but still let the ORM do the heavy lifting so
| you can take advantage of those features, which is the
| majority justification for the ORM in the first place.
|
| You are basically suggesting what the original post author is
| suggesting. The comment above provided a rebuttal to their
| argument and you replied by suggesting the same thing they
| originally rebutted to. Hence a circular argument.
| jpc0 wrote:
| > 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 author of
| doesn't do this, it generates a class with a ton of
| ancillary ORM specific functions that I don't care for and
| many ORMs do that.
|
| I don't even agree with what the article states, the
| article is effectively creating a custom ORM, I'm saying
| write a function that converts the data from your database
| driver to the type of thing you want, write a function that
| validates that data, those two can be 1 function for simple
| data but likely will be 1 function which is a composition
| of other functions.
|
| When you get to the point where that is tedious you now
| likely have a good idea of what you want in an ORM and are
| significantly better equipped to make an informed decision
| for your specific project, you might find you end up only
| wanting a query builder.
|
| 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?
|
| My ideal workflow is: 1. Query exactly what I want 2. Make
| it valid data in the form I want
|
| That valid data shouldn't have a ton of extra stuff
| attached to it, I want plain old data that is validated and
| the correct type.
|
| Why would I add a dependency that is complex before I can
| make an informed decision that I would even need it?
| [deleted]
| ris wrote:
| > Likewise practicing YAGNI
|
| This is why I have come to hate YAGNI. Nowadays when it's
| said what I hear is "I don't understand why I need it (yet)".
| jpc0 wrote:
| An app with 3 endpoints that makes 12 SQL queries won't
| need it.
|
| An app with 30 endpoints that makes 100s of queries might
| need it.
|
| In this case, it's a matter of not knowing whether you need
| it or even what iteration of it you need. Different ORMs
| have different tradeoffs, you don't even know which
| tradeoffs you want to make.
| gmassman wrote:
| 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!
| tru1ock wrote:
| I picked up some SQL knowledge through osmosis by using
| ActiveRecord and I do wish there was a better connect between
| what the ORM did and the end result. There were some tools to
| see what code generated what queries but it was not that
| intuitive and in your face like for example how you would have
| to deep dive frequently in your generated front end javascript
| and css code.
|
| In other word I think there are some tooling left on the table
| that can assist in increasing SQL literacy and comprehension.
| taeric wrote:
| My only caveat to what you are saying, is that I have yet to
| see anything that successfully scales up to many tables. I can
| also count on one hand the number of products I have seen that
| successfully migrated between big databases in a meaningful way
| without a ton of ancillary rewriting in the process.
|
| That said, I fully agree that the ORM isn't necessarily the
| problem. I point the blame at over eager data modelling that
| ignores the OLTP/OLAP divide. Or that ignores that row creation
| has a different contract than row editing, such that using the
| same object for both leads to confusion with missing fields.
| Heck, even different edits have different contracts, such that
| mapping a single "object" to a database row is often a mistake.
| dagmx wrote:
| 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.
| joaodlf wrote:
| > 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 that you can write bad code, with or
| without an ORM :). Not that this is entirely relevant to my
| post, I am simply advocating for writing more SQL, not how to
| write a good object mapper. That's a different beast, and I
| purposely kept that simple just to illustrate that it is
| possible to get started without too much pain.
| dagmx wrote:
| My point with the personal anecdote is precisely that you
| can write bad or good code with both. however that an ORM
| can allow for more consistent experience across many more
| people.
|
| My point is different than your takeaway. In my point I'm
| not blaming the technology, I'm blaming the people involved
| with using it in production.
|
| I specifically point out that a well versed engineer can
| write a SQL based system well. An ORM just means I can
| diffuse that responsibility over multiple people more
| reliably.
| lelanthran wrote:
| > My point with the personal anecdote is precisely that
| you can write bad or good code with both. however that an
| ORM can allow for more consistent experience across many
| more people.
|
| I find it interesting that you say that; my takes is that
| it's _the other way around!_
|
| SQL join statements look the same no matter what
| programming language the reader is used to, but each ORM
| differs in the way the join looks to the reader. The EF
| method of filtering your results set in C# looks _very
| different indeed_ to how the ORM for Python would do it.
|
| Every ORM looks different, which results in a very
| inconsistent experience for people, especially when you
| bring in a DB expert to figure out why there's a slowdown
| somewhere, and he cannot just visually inspect the
| EF/SQLAlchemy code and say "Well, here's what's wrong".
|
| DB experts can usually very easily do that just by
| looking at the SQL, no matter what programming language
| was used.
| dagmx wrote:
| DB experts tend to be the minority though. It's about
| indexing (pun intended) on the expert experience vs the
| generalist experience.
|
| Often those may be very competing goals , where
| nativeness to the primary language used by the team might
| be more important than the nativeness of the expert.
| waffletower wrote:
| Enough people have had bad experiences with ORMs who have
| decided to evolve and find alternatives. ORMs are bulky and
| do not provide value to many modern functional, data-first
| development paradigms.
| dagmx wrote:
| Why speak in absolutes? Swift data for example is a data
| first ORM
| https://developer.apple.com/documentation/SwiftData
|
| Nobody is saying only use an ORM, but the anti-ORM crowd
| seem to think that there's only one true way to do things.
|
| I don't know why so many programmers think purely in binary
| "good or bad". Different projects may have different
| requirements.
| waffletower wrote:
| Why hear in absolutes? I said essentially what you did:
| "Different projects may have different requirements"
|
| You find ORMs useful, I do not, they aren't mutually
| exclusive.
|
| Also, An ORM is inappropriate for certain forms of inter-
| team collaboration. Data scientists often prepare SQL
| that we need to integrate into our services. Asking all
| at a company to learn a language specific ORM isn't
| practical. SQL is, for better and worse, an important
| common tool.
| hot_gril wrote:
| > writing SQL for CRUD is really repetitive and tedious
|
| If you think of relational DBs as just CRUD machines, an ORM
| makes total sense, but that's the original mistake.
| TX81Z wrote:
| I think that's part of why I'm having trouble with this
| framing - it's treating CRUD as the entire universe of why
| you'd need to connect to a database.
|
| Some of us do very intense compute in very large datasets,
| and ORM are not capable in those tasks. At all.
| hot_gril wrote:
| Doesn't even have to get very intense. A simple timeseries
| schema gets slow/annoying quickly with an ORM.
| audunw wrote:
| Is it a mistake if that's all you need?
|
| I'm kind of on the side of avoiding ORM unless you have a
| clear need for it. But I've seen projects where they are very
| valuable. If you mainly want to register, update and delete a
| bunch of structured data, it's not a bad idea to put it in a
| dabatase. And if you do those operations a lot in some part
| of the application, it's not a bad idea to use an ORM there.
| For analysis and reports and such I would probably suggest
| just writing SQL directly though.
| hot_gril wrote:
| Yeah, part of an application might be CRUDing big pieces of
| structured data, and for that I think jsonb is the best
| compromise (https://news.ycombinator.com/threads?id=hot_gri
| l#37123355). Comes with the limitation that you can't have
| FKs into the objects, but you shouldn't anyway.
|
| It's hard to be sure that CRUD is all you need. Maybe
| you'll be fine, maybe you'll get stuck between keeping a
| slow DB or rewriting everything (I've seen this movie
| several times). It doesn't cost much to go proper
| relational from the start, especially cause that involves
| the least tooling/boilerplate. And if you're really sure
| you only want objects, NoSQL DBMSes are actually designed
| for that.
| lelanthran wrote:
| 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 digital equivalent of pounding
| a square peg into a round hole.
|
| I _know_ you 've read Ted Neward's "The Vietnam of Computer
| Science." (it's a short read, so search for it), and he
| articulated the same thoughts I had each time I had to work
| with an ORM in a mainstream language.
|
| There is an impedance mismatch of sorts between relational data
| and hierarchical data. In cases where there is no hierarchy,
| _you can do away with the ORM completely_ and turn out much
| easier to read code, for example with `sqlc` and `Go`.[1]
|
| The problem with the ORM is the programming language - one in
| which hierarchical objects are the idiomatic way to code (all
| of the mainstream OO languages) is always going to require some
| wrangling.
|
| > still be using an enormous amount of automation to deal with
| the database drivers and moving data between your objects and
| rows.
|
| Unless you're using `sqlc` and Go, where the automation is
| provided
|
| [1] Now you may argue that sqlc is technically an ORM, but then
| where do we draw the line for calling something an ORM? Is it
| the case that any method for "generating boilerplate for
| mapping relational datasets to in-program structures" is going
| to be called an ORM? Because to my mind, the result is so
| different for in-program representation that they aren't the
| same thing.
| zzzeek wrote:
| 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 abstraction" and all that and redefined the ORM / SQL
| abstraction layer as *automation*, not any attempt to "hide"
| anything.
|
| 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.
|
| Yeah if your app has one table and two queries, write the SQL
| and marshall the data yourself. I think everyone should do
| that approach anyway for awhile so they know what's actually
| going on. But if you are writing for 1200 tables, it's just
| not practical. if the Go thing is also automating that and
| generating SQL / data marshalling boilerplate code for 1200
| classes, yes that's kind of ORMish. that's a totally valid
| way ORMs are written and if I had multiple lives I'd probably
| write a stored procedure ORM that does something like that
| too.
| joaodlf wrote:
| 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 immediate work only touches 5
| tables and the relationship between them, it's really not
| inconceivable to just reach for a database adapter and
| simple abstractions.
| zzzeek wrote:
| 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.
| ckmar wrote:
| > 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.
|
| If anyone is looking for this "pure" ORM (no query builder API)
| in Node there is https://github.com/craigmichaelmartin/pure-orm
| SPBS wrote:
| > 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.
|
| That's easy. It's a database mapping library. Write the query,
| give it an object and it fills it in or reads from it. You can
| do this with annotations, or struct tags in the Go world.
| There's no need to introduce abstractions over SQL joins which
| I find very off-putting, because the abstractions are never
| perfect and suddenly you have to learn invented concepts just
| because you didn't want to write a JOIN in an SQL query but
| rather have some clever framework introspect some classes and
| automagically write the JOINs for you.
| xp84 wrote:
| > writing SQL for CRUD is really repetitive and tedious
|
| Agreed, and this is the primary reason that ORMs are a
| necessary tool. And possibly that it's easier to train a junior
| developer to use one than it is to get them to a basic level of
| proficiency (and security awareness!) in SQL.
|
| That said, I think this is one of those areas where It's
| Complicated, because inefficient database calls due to ORM
| usage is one of the primary ways I see applications completely
| break down. For most types of apps I've seen, which are very
| read-heavy and which aren't doing intensive writes, roughly all
| your writes should probably be using the ORM (including such
| niceties as validations, default scopes, all that nice stuff),
| and if it's simple enough, your "show" actions (fetch and
| display one entity) may be fine as well, but every "dashboard"
| and "index" action (show many entities, basically things with
| joins) likely need to be written in SQL.
|
| In my experience (Rails), the object instantiation cost is
| insane, much greater than the actual time talking to the DB, so
| not only do you need to write SQL, but you need to handle the
| data that comes back without instantiating ActiveRecord models.
|
| This is much harder work (more specifically, it needs much
| greater skill and experience, and is easier to make a mess of)
| versus using the ORM and models, but only on apps with tiny
| amounts of data per request, or very low request volume, can
| the ORM be a serious exclusive option for this task. Unless you
| want to end up like an app I once was asked to help fix, where
| they were on the $9,000 per month Heroku postgres instance, and
| since that's the biggest one, they could "scale" no bigger.
| (Okay, this wasn't their only problem, their main one was not
| understanding that you don't sprinkle analytics DB writes all
| over the place because now the simple high-volume "read"
| pageviews can't be generated using just a read replica).
| siva7 wrote:
| badass mic drop. i will refer to your comment whenever i see
| this discussion coming up again and it will come up again as
| long as people are still learning the art of software
| development.
| habitue wrote:
| 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.
| TX81Z wrote:
| I'm sorry but 90% of the time I encounter somebody who swears
| by an ORM I'll figure out the reason they use it is because
| they didn't know SQL to start with and didn't commit to
| learning a new language.
|
| The number of people who know SQL well and still choose an ORM
| seems to be very, very low in my experience.
| joaodlf wrote:
| 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.
| marcosdumay wrote:
| The one distinguishing feature of an ORM is that it drops SQL
| and the relational paradigm and places the developer completely
| within the OOP world. If you drop this, you have merely a
| database connector library, with much more freedom of behavior
| than an ORM.
|
| Yes, since they are an notoriously bad abstraction, every ORM
| will give you an escape hatch for the minority of tasks that it
| can't abstract at all. That escape hatch is not in any way a
| defining feature of the system.
|
| Now, about that extra freedom that you get from dropping the
| requirement that your connector is an ORM... well, neither your
| data at rest, the presentation to the user, the abstraction for
| a service client, nor your validation rules benefit from OOP.
| Proof of that is that OOP interfaces from all of those things
| precede the current popular ones, and all of them were
| abandoned due to fundamental dissonance between the model and
| the data.
|
| The rationale for an ORM is that, even though none of the
| interfaces you actually want for your data is best done in OOP,
| somehow OOP is still the best way to integrate them so that you
| can reuse data and metadata. This thought is not completely
| without merit, but there is very little empirical motivation
| for it, and the tiny amount that exists is more than completely
| explained by the OOP hype that only started to die around a
| decade ago.
|
| EDIT: Oh I saw you wrote SQL Alchemy! First, thank you for that
| great piece of software.
|
| Now, SQL Alchemy does provide a lot of useful ways to postpone
| the object representation or even to map your values into dumb
| data. My comment up there is on theoretical limitations, but on
| practice, I do think it's the best option available on Python.
| (And maybe about the best option afforded, since the language
| is intrinsically biased into OOP.)
| ruuda wrote:
| One challenge working with SQL from statically typed languages
| (including Python + Mypy) is that you have to convert the query
| inputs/outputs to/from types and it's a lot of boilerplate. I
| started an experiment to generate this from annotated queries.
| [1] Python support is still incomplete, but I'm using it somewhat
| successfully for using SQLite from Rust so far.
|
| [1]: https://github.com/ruuda/squiller
| tantaman wrote:
| similar project that generates types for queries into an
| intermediate representation that can be consumed by, say
| TypeScript, to get static Types: https://github.com/vlcn-
| io/typed-sql
| sakex wrote:
| C++: Just write Assembly
| ggregoire wrote:
| I've been using PugSQL to write SQL in Python [1].
|
| With this package, you write the SQL inside SQL files so you can
| benefit from syntax highlighting, auto formatting, static
| analysis, etc. At the difference of writing strings of SQL inside
| Python files. I'm surprised this is not more popular.
|
| [1] https://pugsql.org
| duckmysick wrote:
| Great concept, based on the Clojure library HugSQL.
|
| Unfortunately it depends on a specific version of SQLAlchemy
| and won't run with the latest version.
| bbojan wrote:
| 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.
| somat wrote:
| sometimes the idea is that the database lives it's own life
| outside the application. Probably not the case here, but under
| that viewpoint the application is just one of perhaps many that
| access the data and as such creating tables, indexes,
| migrations and relations are none of it's business.
| m000 wrote:
| But it is the application's business. You may not be altering
| the database schema from your application, but you still need
| to make sure that its code is in-sync with it.
|
| This means that you will need extra tooling, and if you're
| DIYing you will need to write it yourself.
| aforwardslash wrote:
| The effort is the same, regardless of the approach. If
| you're consuming a third-party database and the underlying
| schema changes, you'd have to patch your model definitions
| accordingly - or in the presented example, the dataclass
| definition. Creating code to dump dataclasses from a
| database is actually trivial.
|
| In ORMS like Django, models are defined as code-first, not
| schema-first. Yeah, you can use inspectdb, but in any
| sufficiently complex application, odds are you need to add
| to the generated models any custom behaviors you already
| implemented, and verify all the names and whatnot, because
| data definition and operations on data are actually mixed
| in the same class. More often than not, if the change is
| profound (eg. imagine switching from reading a User model
| from the database to fetch it from an external service),
| you may have to refactor a large portion of your code due
| to the way it interacts with the model - eg. search
| operations won't be proxied via orm, but by using external
| service endpoints, etc. There is no free lunch. And don't
| even get me started on field names that differ on the
| database.
| joaodlf wrote:
| When it comes to migrations, I've been fine with
| https://github.com/golang-migrate/migrate
|
| There are a multitude of extra things to consider, but none of
| those things are, in my opinion, imperative to having success
| with SQL in Python. Will it be hard to achieve the same level
| of convenience that modern ORMs provide? Absolutely. But there
| is always a cost.
|
| I firmly believe that for most projects (especially in the age
| of "services"), an approach like this is very much good enough.
| Also, a great way to onboard new developers and present both
| SQL and simple abstractions that can be applied to many other
| areas of building software.
| tracker1 wrote:
| Agreed, I've seen plenty of what wind up being very byzantine
| and complex migration strategies over the years, and in the
| end simple SQL scripts tends to work the best. I will note,
| that it's sometimes easier to do a DB dump for the likes of
| sprocs, functions, etc, if you want the "current" database
| bits to search through.
| m000 wrote:
| And good luck with writing tests for your sql code.
| jeltz wrote:
| Why would the be an issue? I have written plenty of tests for
| SQL code an it is no harder than writing tests for e.g. Ruby
| or Python code. Especially if you have an ORM involved.
| waffletower wrote:
| I really dislike SQL, but recognize its importance for many
| organizations. I also understand that SQL is definitely
| testable, particularly if managed by environments such as DBT
| (https://github.com/dbt-labs/dbt-core). Those who arrived
| here with preference to python will note that dbt is largely
| implemented in python, adds Jinja macros and iterative forms
| to SQL, and adds code testing capabilities. No ORM required
| whatsoever.
| [deleted]
| jredwards wrote:
| There's a huge module in our python codebase that approaches
| building queries in roughly raw SQL. Let me tell you, tracking
| how data moves from one stage to the next in that "ETL pipeline"
| is an absolute nightmare. Never again.
| ploppyploppy wrote:
| Low quality naive summary.
| jlnho wrote:
| How is this article different from your comment, then?
| sanderjd wrote:
| I always ctrl-f to search for the word "composability" when I
| come across arguments like this. I could take or leave ORMs, but
| relational _query-building_ libraries are invaluable for
| composability, compared to proliferating mostly-duplicative raw
| SQL in format strings all over the place.
| hobbescotch wrote:
| I've been a data engineer for many years and have lots of
| practice optimizing SQL touching many parts of the language and I
| still enjoy using SQLAlchemy for its tight, elegant integration
| with flask/django. Of course some queries make sense to optimize
| with raw sql but I think here, like with many other things,
| there's no black/white conclusion to draw from these situations.
| sergioisidoro wrote:
| 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 and your code will diverge.
|
| If I would get a Schema / migration manager, that would allow to
| do type checks and that would work well with a language server
| for autocompletes, but use SQL or a very very thin wrapper around
| SQL, that would be my Goldilock solution.
| pak9rabid wrote:
| Hmm, is AR's Migration framework not a migration manager?
| joaodlf wrote:
| This is also why I really like Peewee in Python. If I am not
| going to write SQL, at least give me an API that looks and
| feels like it. When I look at Peewee code, I can often see the
| end result query.
| chucke wrote:
| Thea answer to your prayers already exists:
| http://sequel.jeremyevans.net/.
|
| By far the best database toolkit (ORM, query builder, migration
| engine) I have seen for any programming language.
| megaman821 wrote:
| 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?
| ggregoire wrote:
| If you don't want to maintain several queries, you could write
| something like SELECT * FROM shoes
| WHERE (CASE WHEN :brand_id IS NOT NULL THEN brand_id =
| :brand_id ELSE TRUE END) AND (CASE WHEN :size IS NOT
| NULL THEN size = :size ELSE TRUE END) AND (CASE WHEN
| :style IS NOT NULL THEN style = :style ELSE TRUE END)
| megaman821 wrote:
| That is pretty good. Much more readable than gluing a bunch
| of strings together.
| mitch3x3 wrote:
| 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.
| stuaxo wrote:
| Django isn't just about the ability to programmatically stick
| together things to make your query or the migrations. It's that,
| combined with tools that help you debug database issues, and most
| importantly the patterns that it imposes.
|
| As a Django developer it's straightforward to go from one Django
| project to another, which isn't the case with other stuff as you
| don't know where everything is going to be.
| lifewallet_dev wrote:
| I can already see this doesn't have connection pooling which all
| those ORMs he listed have without you knowing what a connection
| pool is it just works, and scales, doing that on your own is not
| easy.
| dotdi wrote:
| Yes, this!
|
| I've been burned countless times by Hibernate (and consorts) and
| now I argue in favour of plain SQL wherever I can. I do not imply
| that Hibernate is in itself bad, I just have collected many years
| of observations about projects built upon it, and they all had
| similar problems regarding tech debt and difficult maintenance,
| and most of them sooner or later ran into situations where
| Hibernate had to be worked around in very ugly ways.
|
| Yes, I can understand some of the arguments for ORMs, especially
| when you get a lot of functionality automagically a la Spring
| Boot repositories.
|
| And since nowadays I have more influence, I do advocate for plain
| SQL or - the middle ground - projects like jOOQ, but without code
| generation, without magic, just for type safety. We've been quite
| happy with this approach for a very large rewrite that is now
| being used productively with success.
| izoow wrote:
| To those who write plain SQL in Python, what do you use for
| migrations?
| tpoacher wrote:
| A combination of Ibuprofen and Paracetamol usually does the
| trick.
|
| ... oh wait, I thought you said _migranes_.
| Dowwie wrote:
| You'll eventually write your own dynamic query building logic if
| you take this development path
| boxed wrote:
| 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 implementation is pretty tiny and
| super easy to read. You can just fork it by copying the entire
| thing into your DB if you want.
| joaodlf wrote:
| > 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.
| boxed wrote:
| 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
| depend on is madness. Where does it end? Do you worry about
| being "locked into" Python? Of course not.
|
| > You build around the framework, the framework never bends
| to your will.
|
| You don't actually seem to understand Django at all. It's
| just a few tiny Python libraries grouped together: an ORM,
| request/response stuff, routing, templates, forms. That's it.
| You do NOT need to follow the conventions. You can put all
| your views in urls.py. You can not use urls.py at all.
|
| You do NOT bend to the frameworks will. That's just false.
| You bend to it by your own accord, don't blame anyone else on
| your choice.
| joaodlf wrote:
| > 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.
| lelandbatey wrote:
| 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?
| sanderjd wrote:
| Ha, should have read the comments before I wrote mine. Yep,
| it's this composability aspect that never seems to have
| occurred to the authors of this kind of think-piece.
|
| I used to be pretty anti-ORM myself because I loathed the
| complexity of ActiveRecord (in the Rails world), but then I
| discovered arel, the nice composable relational query builder
| underneath, and saw the light. A composable layer of
| abstraction over SQL is critical in an application. (I still
| prefer raw SQL for analytical queries.)
| mrj wrote:
| This is what I came here to say.
|
| For example, I'm working on an project now that long ago added
| a "sellable things" store that used plain sql. There are many,
| many stores like this one, but it did some logic to figure out
| what items are sellable and return the set. Easy, developer
| happy, ticket closed.
|
| Some time later, it was needed to have "sellable items of a
| specific type." Well the "sellable things" store was too much
| to clone so the developer simply pulled all the sellables and
| filtered in memory. Hey it's Go so it's fast right?
|
| This continued for a couple years and now I'm joining a project
| with a p99 of >15s. It would have been a natural fit to return
| a query set of sellable things and the other callers could
| further refine it however they wanted. Now I'm looking at a
| ball of logic that should have been in the database and it's
| beginning to break down at scale.
|
| This article is just that pattern with syntax sugar. It will
| lead to sadness.
| molly0 wrote:
| An ORM makes sense if you need to make very dynamic SQL queries,
| ie advanced logic at runtime.
|
| If your app can work well with static queries then you should not
| add an ORM.
| NewEntryHN wrote:
| Any serious application beyond the example given in this article
| will include conditional SQL constructs which go beyond SQL query
| parameters and will therefore require string formatting to build
| the SQL.
|
| Think a simple UI switch to sort some result either ascending or
| descending, which will require you format either an `ASC` or a
| `DESC` in your SQL string.
|
| 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.
| drdaeman wrote:
| IMHO the real proper solution is to have an SQL parser, so you
| can have your SQL represented as an AST, do some operations on
| it, then compile it back to a query.
|
| Sadly, I'm not aware of any good solutions to this. SQLAlchemy
| Core can build an operates on an AST, but it doesn't parse raw
| SQL into a query (so one has to write their queries in Python,
| not SQL). Some parser libraries I've seen were able to parse
| the query but didn't have much in terms of manipulations and
| compiling it back.
| nicoburns wrote:
| > 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 about matching up position arguments. That along
| with JavaScripts template string interpolation actually made
| the string-formatted version pretty nice to work with.
| boxed wrote:
| Sounds like just begging for SQL injection attacks.
| nicoburns wrote:
| 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.
| boxed wrote:
| That makes no sense. What are you interpolating? Some
| variable. And you now have to audit that THAT VARIABLE is
| safe.
| nicoburns wrote:
| > What are you interpolating? Some variable.
|
| Nope, I'm generally interpolating an inline expression
| consisting entirely of string literals.
| squeaky-clean wrote:
| 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.
| ZitchDog wrote:
| You can avoid this entirely with JavaScript's tagged
| template literals. Here is an example library:
| https://github.com/blakeembrey/sql-template-tag
| williamdclt wrote:
| There's a world between a query builder and an ORM. The point
| of ORMs isn't to build queries, if that's the only need might
| as well just use a query builder which is a lot more
| lightweight and doesn't come with all the downsides of orms
| masklinn wrote:
| The OP literally says to ignore query builders, not just
| ORMs. When they state "just write SQL" that's their actual
| thesis.
| tracker1 wrote:
| Depends on the abstraction... for example .Net's extensions for
| LINQ are pretty good at this, I haven't generally used the LINQ
| syntax, but the abstraction for query constructs are pretty
| good, combined with Entity Framework. Of course, there's a lot
| that I don't care for and would prefer Dapper. In the end, the
| general environment of .Net dev being excessively "enterprisey"
| has kept me at bay the past several years.
| dqv wrote:
| Just looked at LINQ and it looks like Ecto [0] used a lot of
| its ideas for inspiration! I haven't used LINQ, but in Ecto,
| there are so many useful constructs for composing queries. If
| you get a stinker of a query, you have multiple escape
| hatches such as fragments [1] or just writing the queries
| directly as needed [2].
|
| For beginners, the Elixir language constructs can be a little
| clunky, but once you get it, it's so productive and I miss
| that productivity when doing more advanced queries in other
| languages.
|
| [0]: https://hexdocs.pm/ecto/Ecto.Query.html [1]:
| https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments [2]:
| https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4
| coldtea wrote:
| What you describe just needs a query builder (e.g. in Java
| something like jOOQ), not necessarily an ORM.
| masklinn wrote:
| OK but TFA is not just against ORMs, it's also against query
| builders. That's what GP is replying to.
| Improvotter wrote:
| It might be worth mentioning LiteralStrings from [PEP
| 675](https://peps.python.org/pep-0675/) and how you should use
| them to prevent SQL injections. I'm not sure this blog adds much
| to the discussion when it comes to when to write SQL and when not
| to. It does not cover the struggles, the benefits, and the
| downfalls.
| sams99 wrote:
| For those looking for a rubyish approach to this see:
| https://github.com/discourse/mini_sql
| felipetrz wrote:
| "without using ORMs"
|
| ...
|
| Proceeds to create an ad-hoc ORM.
| kervantas wrote:
| Every ORM basing post is like this. Some dude is dissatisfied
| with Hiberante/GORM/SQLAlchemy, declares ORMs as an "anti-
| pattern", then proceeds to reinvent the wheel.
| felipetrz wrote:
| The main antipattern involved in this post is Go.
|
| Rob Pike's messed up ideology made people think abstraction
| is bad.
| never_inline wrote:
| The premise is that Go language users only use standard library
| SQL package.
|
| Anecdotally, I haven't seen a place where Go is used without
| something like gorm or sqlc.
| rowanseymour wrote:
| We've always used https://github.com/jmoiron/sqlx which is just
| the standard package + mapping to/from structs.
| impulser_ wrote:
| If you want to try out something cool, check out
|
| https://github.com/sqlc-dev/sqlc
|
| It's written in Go and it converts your sql migrations and
| queries into typesafe code that you use access your database.
|
| It currently has a plugin for Python that's in Beta, but what
| essentially does something similar to what this post is saying.
|
| https://github.com/sqlc-dev/sqlc-gen-python
|
| You write your migrations, and queries and a config file and it
| does the rest.
| badcppdev wrote:
| If you are just going to "Just Write SQL" then I really don't
| think you should be coding your own Object and Repository
| classes.
|
| My vision of the "Just Write SQL" paradigm would be a "class" or
| equivalent that would take a SQL command and return the response
| from the server. Obviously the response has a few different forms
| but if you're "just writing SQL" then those responses are
| database responses and not models or collections of models.
|
| (For the record I think simple ORM type functionality is actually
| quite useful as your use case moves past the scale of small
| utility scripts.
| davidthewatson wrote:
| I'm happy to see someone mention peewee, having used it for
| numerous startup prototypes since its inception.
|
| Coleifer does not get enough credit IMHO:
|
| https://github.com/coleifer/
|
| Peewee has been solid since I began using it a decade ago.
| Coleifer's stewardship is hard to see at once, but I've
| interacted with him numerous times back then and the software
| reflects the mindset of its creator.
| radus wrote:
| Peewee is excellent! I've especially enjoyed using it with
| SQLite - there are a number of handy extensions and very good
| support for user defined functions.
| jrichardshaw wrote:
| I'd definitely like to second how great peewee is. It's been a
| core part of running our telescope for the last 8 years. It
| strikes a nice balance between power and simplicity, a
| significant set of useful extensions and great documentation.
|
| Sometimes I find it impossible to believe that @coleifer is
| just one person. Peewee has 2300 issues and 500 PR's none of
| which are open and outstanding, and almost all of which he has
| personally responded too in a genuinely helpful way. He pipes
| up on Stackoverflow for peewee questions too.
| tantaman wrote:
| I was sad to find that the author proclaimed "just write SQL"
| then fell into the trap of modeling his data as objects.
|
| If you're going to model your data this way... you might as well
| use an ORM.
|
| A better way is to just just write SQL (or datalog) and model our
| data, from DB all the way up to the application, as relations
| rather than objects.
|
| Rather than re-hash, this idea has previously been discussed on
| HN here: https://news.ycombinator.com/item?id=34948816
| ak217 wrote:
| 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.
| metalforever wrote:
| What happens at big companies is that they will build a custom
| ORM over time , and it will be way shittier and more vulnerable
| than if you had just used one in the first place.
| hprotagonist wrote:
| If you haven't yet, check out https://pugsql.org/ . all the power
| of sqla-core, none of the ORM fuss. PugSQL is a
| simple Python interface for using parameterized SQL, in files,
| with any SQLAlchemy-supported database.
| fmajid wrote:
| 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.
| baq wrote:
| 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.
| claytonjy wrote:
| 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.
| gregw2 wrote:
| 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.
| fmajid wrote:
| 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.
| felipetrz wrote:
| You can test them very easily by using database containers.
| tracker1 wrote:
| The setup/teardown, and even working with schema migrations
| can get complex, and potentially excessively so for the
| benefit of doing everything in SPs. Also, the developer
| experience and discoverability are definitely less than
| ideal.
| fb03 wrote:
| 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.
| plopz wrote:
| I come from the PHP world and have used a variety of ORMs/query
| builders in that ecosystem but the most common issue I
| encounter that they don't handle well is when I want to do a
| "left join foo where foo.id is null"
| Rudism wrote:
| 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 create all of your business
| entities as tables or views and then write all your business
| logic as SQL stored procedures, which get automatically
| translated into a GraphQL API for your clients to consume, but
| once you move beyond basic CRUD operations and reporting it
| becomes incredibly difficult to work with since there aren't
| really any good IDEs that help you manage and navigate huge pure-
| SQL code bases.
|
| If you're really dead set against using a powerful ORM, it's
| probably still a good idea to find and use a lightweight one--
| something that handles the tedious CRUD operations between your
| tables and objects, but lets you break out and write your own raw
| queries when you need to do something more complex. I think
| there's a sweet spot between writing every line of SQL your
| application executes and having an ORM take care of boilerplate
| for you that will probably be different in every case but will
| never be 100% at one end or the other.
| hot_gril wrote:
| 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.
| hot_gril wrote:
| 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.
| waffletower wrote:
| As a data engineer, the pattern the OP shares is very familiar. I
| find it much preferable to use of ORMs for wide variety of
| reasons. However, I view implementing with SQL as an antiquated
| problem rather than a pragmatic feature. The evolution of this
| pattern would be to integrate database querying into languages
| more directly and eliminate SQL entirely. While this could be
| achieved in Python, I find that a language like Clojure, via
| functional programming (FP) primitives and transducers, is a
| natural candidate, particularly for JVM implemented databases.
| Rather than encapsulating SQL via query building or ORM based
| APIs, an FP core could be integrated into database engines to
| allow, via transducers, complex native forms to be executed
| directly across database clusters. Apache Spark is an analog of
| this. In particular the Clojure project, powderkeg
| (https://github.com/HCADatalab/powderkeg), as an Apache Spark
| interface, demonstrates the potential of utilizing transducers in
| a database cluster context.
| robertlagrant wrote:
| With SQLAlchemy, I come for the type checking. I stay for the
| Alembic migrations.
| TwentyPosts wrote:
| I feel this, sort of. I taught myself how to code by writing a
| Python bot (among other things), and eventually needed some
| sort of database handling to make things work. I decided on
| teaching myself basic SQLite, and just did it raw with
| `sqlite3`.
|
| Currently 50% of my anxiety when it comes to my bot is related
| to database matters. There's no type checking so I gotta be
| careful when writing them, and stuff might blow up weirdly at
| runtime. Refactoring tables is also a major pain, or at least
| was until I (sort of) figured out a 'routine' of how to do it.
|
| It's doable, and I assume that teaching myself some basic SQL
| and using it in production was a great learning experience, but
| once I'd reached the point where I was inventing database
| migration tooling from first principles and considering how to
| implement that, I realized that I probably just want to look at
| SQLAlchemy again.
| robertlagrant wrote:
| I like writing things in Python purely because of SQLAlchemy.
| I think it's completely great.
| BozeWolf wrote:
| Indeed. So much more than this simple example. It gets
| interesting for more advanced use cases. If i now rename a
| field on the model, it will not be renamed in the database. If
| i want that to match, i have to change the query. And make a
| migration. But that is probably another simple blog post.
|
| Putting it all together is another blog post. And if you have
| colleagues: probably needs documentation. Which you also have
| to maintain yourself.
| ckdot wrote:
| Congrats, you just wrote your own ORM. Please mind that ORM
| doesn't necessarily mean ActiveRecord, which could be considered
| an anti pattern.
| extasia wrote:
| What's Active Record and why is it an anti pattern?
| ckdot2 wrote:
| It's a pattern where a single object (the "active record")
| not only represents a single database row, but also usually
| is responsible for saving/inserting data into the database
| (via save method) and retrieving them (via find methods).
| Because of this it breaks SRP. If this is neglectable or not,
| I don't want to argue here. Personally, I would not use it
| anymore because of bad experience in the past.
| tantalor wrote:
| https://guides.rubyonrails.org/active_record_basics.html
| iamflimflam1 wrote:
| _The active record pattern is an approach to accessing data
| in a database. A database table or view is wrapped into a
| class. Thus, an object instance is tied to a single row in
| the table. After creation of an object, a new row is added to
| the table upon save. Any object loaded gets its information
| from the database. When an object is updated, the
| corresponding row in the table is also updated. The wrapper
| class implements accessor methods or properties for each
| column in the table or view._
|
| https://en.wikipedia.org/wiki/Active_record_pattern
|
| It was fashionable for a while to say it was an anti-pattern
| because that was a contrary view and ActiveRecord is very
| tied into building Rails applications.
| robertlagrant wrote:
| It's not about fashion. Observations about fashion are no
| deeper than fashion itself.
|
| It scales badly with table size, I think by design. That's
| why SQLAlchemy's and Hibernate's Data Mapper pattern is
| slightly more cumbersome to write, but works out much
| better.
| khazhoux wrote:
| He didn't really. The SQL is right there, and this is
| important.
|
| What I've experienced (unfortunately) across multiple projects
| is that people who understand databases will write SQL with a
| nice collection of helper and wrapper functions as needed, and
| the people that think that databases are mysterious black boxes
| will reach for ORM. I've seen the ORM-happy teams getting
| scared at the idea of a million ( _1,000,000!_ ) rows in a
| table, and they always neglect to set up even basic indexes or
| to think through what their JOINs are really doing.
|
| YMMV but that's the pattern I see again and again.
| ckdot2 wrote:
| Well, the SQL is always somewhere. If you use an ORM library,
| even if you use ActiveRecord, you will find some SQL in it.
| In the end, it always translates to SQL. In the blogpost, the
| writer created a User Python object ("O"). A corresponding
| (R) database row will be mapped (M) to the object. That's
| basically ORM. Not as heavy as the usual libraries that
| support relationships etc, but still, ORM.
| baq wrote:
| sqlalchemy allows you to separate ORM from SQL and _combine
| them when needed_. the idea that 'ORM == you don't have to
| write SQL and/or you can't write SQL' is, please excuse my
| strong words here, wrong.
|
| my biggest gripe with sqlalchemy is that sometimes I know
| what I need to write in SQL (have a working prototype
| usually) and have trouble mapping the concept to
| sqlalchemy.core constructs, but that's mostly inexperience.
| mickeyp wrote:
| I have decades of experience with databases and I happily use
| ORMs.
|
| You're conflating ORM with people who know nothing about
| databases. Why?
| jbreckmckye wrote:
| From my experience, ORMs allow folk who know nothing about
| databases to continue knowing nothing about databases
| jbreckmckye wrote:
| (this was a flippant remark, if I could edit it I would
| reword it)
| masklinn wrote:
| And from my experience, forcing people who know nothing
| about databases to write SQL will not make them learn
| about databases, all you end up with is worse SQL and
| more injections.
|
| Although the worse offenders by far are those which
| decide ORM = bad and bypass it at every opportunity.
| jbreckmckye wrote:
| That's fair, ORMs can be slow but they rule out a whole
| category of basic mistakes
| tracker1 wrote:
| And introduce plenty more. over-fetching, unindexed joins
| across many tables being two of the more common.
| boxed wrote:
| C allows people who can't build a CPU from NAND gates to
| do programming :P
| felipetrz wrote:
| NAND gates allow people who can't build transistors from
| self-mined ores to build CPUs.
| mickeyp wrote:
| So let's ban ORMs because newbies don't know the entire
| toolchain. Yes, that will definitely solve it.
|
| Why stop there? Let's ban RDBMS. If you can't contemplate
| a binary file structure and index strategy perfectly
| tailored to your application's needs ahead of using an
| RDBMS, why should we deign to let you use an abstraction
| layer with clever query planning and algorithms?
|
| It's all too easy to morally 'ban' people from technology
| and gatekeep it behind wishy-washy nonsense like "ORMs
| are bad for beginners."
| [deleted]
| [deleted]
| [deleted]
| [deleted]
| [deleted]
| wnolens wrote:
| Isn't that just a useful abstraction?
| [deleted]
| bakugo wrote:
| Good ORMs provide the best of both worlds. Basic tasks such
| as loading a single object from the database by ID and then
| writing it back after changes are made shouldn't require you
| to write any SQL, because everyone already knows what that
| SQL looks like, just let the ORM do it for you. A query
| builder component that allows you to programmatically build
| queries of medium complexity is also essential. And for
| anything not covered in the previous two cases, it should be
| possible to just write raw SQL or something like it without
| the ORM fighting you for it.
|
| My preferred ORM is Doctrine and it provides all of these
| features. It has its own variant of SQL called DQL that lets
| you effectively write complex select queries as raw SQL with
| a bunch of object-specific conveniences built in, and get
| back an array of objects.
| jamil7 wrote:
| For this reason I really like GRDB for iOS, documentation
| is also excellent.
| semrekkers wrote:
| Shameless plug, with channel support:
| https://github.com/semrekkers/sqlz
| ris wrote:
| I've been both ways on this, and ultimately I come down heavily
| in the camp of using ORMs for as far as it makes sense. Why?
| Sure, the "just use SQL because it's so simple" crowd use
| seductively simple examples, and indeed for very static use-cases
| it can be quite neat and simple.
|
| But projects (almost always) grow, and once you need to start
| conditionally adding filter clauses, conditionally adding joins,
| things start to get _very_ weird _very_ fast. And no, letting the
| database connector library do the quoting for you _won 't_ save
| you from SQL injection attacks unless you're just using it to
| substitute primitive values.
|
| And once all your logic is having to spend more space dealing
| with conditional string formatting, the clarity of what the query
| is actually trying to do is long gone.
|
| I'll refrain from digging up the piece of code where I was having
| to get the escaping correct for a field that was embedded SQL-in-
| SQL-in-SQL-in-go. And I could hear the echos of the original
| author's "YAGNI"s haunting me.
| bafe wrote:
| Just write SQL and eventually you will reinvent 50% of any ORM
| bastardoperator wrote:
| No thanks, been there, done that. Writing SQL by hand almost
| never scales.
| timmit wrote:
| Based on my personal experience, I have seen some raw SQL codes
| about 200 to 1000 lines in some production source codes,
|
| not readable at all, not easy to change, which is a terrible
| development experience.
|
| I guess if it is simple CRUD, it does not give too much problem,
| but it will definitely work in a complex case.
| Sparkyte wrote:
| Sentiments on this is that sticking close to native as possible
| reduces coherency issues between anything. Adding layers of
| abstraction on top of layers of abstraction often reduces
| contextual understandings and further dilludes the problem
| solving technique. If the abstraction is truly needed a thurough
| way to evaluate executions is needed and a proper way to
| contextualize which that is not. In-line comments or even very
| easy to navitage documentation but the former thing or even both
| is superior to the latter.
| c120 wrote:
| So far all my projects have targeted a specific database with no
| reason to change it.
|
| So what I do is write SQL commands, but keep all inside a
| specific file or module of the project. So that I can decide
| later to refactor it into an ORM.
|
| I think ORMs are great if you write libraries that target more
| than one database. Or situations, where you have more than one
| database and need a proper migration part.
|
| If you don't need migration, but in the worst case can start with
| a fresh, empty database, then write SQL.
|
| But for production system, the no/manual migration might get old
| quickly. Writing migration code that just adds fields, indexes or
| tables is easy. But writing code that changes fields or table
| structures? Not do much.
|
| Still, you don't need an ORM at the beginning of a project, just
| don't put SQL everywhere.
| phatboyslim wrote:
| Technologists have a hard time accepting an established standard.
| Email is a perfect corollary to this conversation. There is a
| graveyard of companies that have attempted to "Solve email", yet
| it is still ubiquitous and attempts to 'improve' it continue to
| fizzle out. I'm not saying that progress, or an attempt at
| progress, is pointless, but to argue that writing vanilla SQL is
| somehow antiquated or archaic is false and OP makes several valid
| points highlighting why it is a perfectly valid approach.
| rtpg wrote:
| 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 and are using some
| custom postgres extensions for certain fields and filtering...
| Django can help you organize the integration code cleanly.
|
| I totally understand people having issues with Django's ORM due
| to the query laziness making performance tricky to reason about
| (since an expression might or might not trigger a query depending
| on the context). In some specialized cases there are some real
| performance hits from the model creation. But Django is very good
| at avoiding weird SQL issues, does a lot of things correctly the
| first time around, and also includes wonderful things like a
| migration layer.
|
| You might have a database that is _really_ unamenable to
| something like an ORM (like if most of your tables don't have ID
| rows), but I wonder how much of the wisdom around ORMs is due to
| people being burned by half-baked ORMs over the years.
|
| 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.
| aforwardslash wrote:
| There are plenty of advantages of using a dataclass, being the
| most obvious the fact that behaves like a pure data object (aka
| it doesn't have underlying associated resources).
| Serialization/deserialization of data is dead simple, and a
| dataclass is a construct you can use as a data object when
| building 3-tier applications. Having pure data objects also
| gives way more flexibility when implementing cache strategies.
|
| While this separation isn't common in the Django ecosystem, it
| is very common in enterprise application design (regardless of
| usage of an ORM). On complex applications, Django models are
| often a leaky abstraction (not only because the mentioned
| resource connection problem, but also issues like for relations
| they require the inclusion of the target model, it cannot be
| lazy-loaded; a good example is a nullable foreign key to an
| optional module that may or may not be loaded), and they
| actually behave like a variation of the ActiveRecord pattern,
| that mixes two different scopes - data and operation on data.
| In many cases this is ok, but in many others this is a problem.
|
| I personally use a repository pattern, coupled with a query
| builder and something vaguely similar to dataclasses (its a bit
| more complex in the sense that the data object attribute name
| can be different from the database field name). It is basically
| an object mapper with a non-related repository class.
| rtpg wrote:
| Yeah I can understand wanting to split out the ORM model from
| a separate class that holds data. I just think absolving
| oneself of an ORM or query builder entirely for a DB schema
| that doesn't (glibly) fit on a postcard feels like a good way
| to generate a lot of busy work.
|
| I somewhat disagree about your point on caching. If you're
| working with models (that, namely, are 1:1 with DB rows)
| stale object problems are a reality no matter what, and
| having the ID be put into a pure data object generates the
| same issues. But these are things that are not very
| interesting to discuss outside of specific contexts.
|
| I am a bit of a functional programming nerd, but I've just
| found that for Python stuff in particular, swimming upstream
| is its own bug generator relative to writing concise stuff in
| a very imperative fashion. Using the fat models directly is a
| part of that calculus for me, but YMMV and every team has
| different strengths.
| aforwardslash wrote:
| > I just think absolving oneself of an ORM or query builder
| entirely for a DB schema that doesn't (glibly) fit on a
| postcard feels like a good way to generate a lot of busy
| work
|
| True, that's why I built mine as a library I can reuse in
| my projects :) I'm still eating my own dogfood, but doing
| it with a framework approach.
|
| > If you're working with models (that, namely, are 1:1 with
| DB rows) stale object problems
|
| One of my common patterns is to implement cache at the
| service layer, not the data layer - and all data operations
| are performed via services. This allows caching of actual
| business-domain computed values, not necessarily just db
| rows (in fact, more often than not, caching just db rows is
| just a waste of memory with little to no advantage). As a
| quick example, imagine a purchase order with a header, a
| list of products and a state associated with each line - it
| is trivial to cache the whole purchase order info,
| including runtime calculations such as lead time per
| product, and invalidate the cache at each update operation
| on the different tables that may represent this purchase
| order. Services would have methods manipulating "purchase
| order" (and keeping cache state) and not ad-hoc code
| messing with OrderHeaderModel, OrderDetailModel,
| OrderProductStatusModel, etc.
| rglullis wrote:
| 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.
| noirscape wrote:
| 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 that require a lot of optimization - it's
| very easy to have an ORM accidentally retrieve way more data
| than you need or to have it access a bunch of foreignkey data
| too many times (in Django you can thankfully preload the latter
| by specifying it in a queryset). That's less an issue for basic
| CRUD, but is an issue if you're doing say, mass calculation and
| only need one column and none of the foreignkey data for speed
| reasons.
|
| Basically - an ORM is good but don't let yourself feel
| suffocated by it. If it's not a good fit for an ORM, then don't
| do it in the ORM, either use SQL code to do it in the DB server
| or do a simpler SELECT (in the ORM) and do the complex
| operation in your regular application before INSERTing it back
| in the db (if that's a goal for the operation anyway). If it's
| outside of the CRUD types of DB access already, the extra
| maintenance overhead you get from having non-ORM database code
| (if you're doing the SQL approach) in the application would be
| there anyway, you'd just get a very slow application instead of
| a hard error, and the latter is easier to troubleshoot (and
| often fix), while with the former you need to start pulling up
| profiling tools.
| pydry wrote:
| >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 use some feature of
| your database engine which the ORM won't support.
| nicoburns wrote:
| I find this ends up being 90% of SELECT queries. Usually
| when selecting data you want to retrieve a bunch of related
| objects too. Often with complex criteria for which objects
| to pick. And doubly so for "list" type endpoints where
| you're selecting many records.
|
| I tend to use the ORM function CUD operations, and just
| write raw SQL for SELECTs unless they're super-simple.
|
| > 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.
|
| I disagree on this. It's almost no extra work to just write
| these optimised in the first place (if you're not trying to
| squash everything into an ORM workflow). So it makes sense
| just to write them all optimised. Ditto for doing batch
| inserts and updates rather than looped inserts/updates
| (although you can usually use the ORM for this).
| pydry wrote:
| >I find this ends up being 90% of SELECT queries. Usually
| when selecting data you want to retrieve a bunch of
| related objects too. Often with complex criteria for
| which objects to pick.
|
| What is it that you do here that can't be handled by,
| say, django's workhorses - filter and select_related?
|
| If it's impossible to write 90% of your queries in an ORM
| my suspicion would be that you're either not using the
| ORM correctly or you're using a crappy ORM.
| nicoburns wrote:
| I'm unfamiliar with Django's ORM specifically. But the
| problem wasn't that it _couldn 't_ be done with the ORM,
| but that the ORM code quickly became unreadable. Things
| like:
|
| - Complex joins
|
| - Complex WHERE clauses with mixes of AND and OR (with
| parentheses)
|
| - JSON aggregation
|
| - Window functions
|
| tend to require quite heavyweight syntax in ORMs (e.g.
| nested lambda functions). Whereas the corresponding SQL
| tends to introduce much less noise.
|
| It's basically just another case of a dedicated language
| being nicer to use than a DSL embedded into a general
| purpose language. Normally it's not worth creating a
| whole language just for nicer syntax, but in the case of
| SQL the language already exists! So why not use it.
| pydry wrote:
| >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
| issue is just about becoming more familiar with a decent
| ORM.
| nicoburns wrote:
| > 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.
| pydry wrote:
| 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.
| [deleted]
| nicoburns wrote:
| Perhaps it depends what you're doing?
|
| IMO: .where('column_a', '=', 'value1')
| .and(q => q.isNull('column_b').orWhere('column_b', '=',
| 'value2')))
|
| is a lot less readable than: WHERE
| column_a = 'value1' AND (column_b IS NULL OR
| column_b = 'value2')
| DangitBobby wrote:
| In Django that would be .filter(column_a='value1',
| Q(column_b__isnull=True)|Q(column_b='value2'))
|
| And obviously you can use whatever indentations you like.
| nicoburns wrote:
| 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)
| DangitBobby wrote:
| 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
| squeaky-clean wrote:
| 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'))
| LinXitoW wrote:
| One of these my IDE can typecheck and apply code
| hightlighting, the other is just a blob of text.
| winrid wrote:
| It's a ton of extra work. It's like 10x slower than using
| the Django ORM. Let's say it takes you ten seconds to
| write that query. I wrote it in one second with the ORM
| and my IDE.
|
| That adds up, with almost no downside most of the time.
| michaelmior wrote:
| > It's almost no extra work to just write these optimised
| in the first place
|
| Not everyone is capable of quickly optimizing SQL, and I
| don't think it's an absolutely necessary skill to build a
| decent application. Junior devs can pick up on this skill
| over time and as long as they can manage to avoid any
| obvious footguns, using the ORM is fine most of the time.
|
| Writing queries that are optimized in the first place
| just means now you have to maintain a bunch of SQL and
| you have to rely on anyone modifying that SQL later
| understanding those optimizations. Sometimes it's
| necessary, but if it's not, I think it's much nicer to
| stay in ORM-land even if the query might not be optimal.
| nicoburns wrote:
| > Not everyone is capable of quickly optimizing SQL
|
| I mean that's true, but equally not everyone is capable
| of using an ORM. I don't think SQL is inherently any
| harder to learn.
|
| At my last job, I had juniors who had never used SQL at
| all productive in SQL within a couple of weeks, and using
| "complex" SQL like JSON aggregation and windows function
| with a few months. They were a little intimidated by it
| when they started, but didn't find it too hard to learn
| in the end.
| sgarland wrote:
| Honestly, I think I could get someone with no raw SQL
| experience writing code that's at least 50% faster within
| a few hours. There are so many footguns that ORMs
| completely ignore, and never warn you about.
|
| Using a case-sensitive filter (default for Django) in a
| DB with case-sensitive collation (default in Postgres)?
| Django will helpfully cast the tuple and your query to
| UPPER to match it for you, and the former wrecks
| indexing.
|
| Checking if a string ends with something else? Goodbye,
| index.
|
| I _think_ the latter can be worked around in PG with a
| GIN index, but I'm not positive (I work with MySQL much
| more). And in any case, you'd have to know to create
| that, and I imagine most devs won't.
|
| Fixing seemingly tiny things like that have a massive
| impact on large table query speed.
| michaelmior wrote:
| With your example of optimization, I think anyone who is
| able to make this optimization could certainly also
| implement it on top of an ORM.
| dgb23 wrote:
| Or you use something much simpler and more light weight,
| like query builders or a tool that generates code from sql
| queries.
|
| This approach is more bottom up. You end up with uni
| directional data flow, better separation of concerns, data
| coupling instead of object dependencies and better
| performance right out of the bat.
|
| The cost? In my experience just some basic familiarity with
| SQL.
| CuriouslyC wrote:
| Nah, ORMs just encourage a lot of bad behavior, and come
| with edge cases and code bloat. You're better off using an
| API generator such as postgrest/hasura for the simple
| cases, and hand crafted queries for anything more complex
| than basic crud.
| hobs wrote:
| The problem is that what you typically get is one group of
| people who have no idea how to do anything outside of the
| ORM and play off the problems because hey, its only a
| problem query or two that use up 100% of the system
| resources of the databases and bring them crashing down,
| but might as well throw more system resources at it because
| we've spent no time understanding queries the last N years
| of building.
| agumonkey wrote:
| I realized too late that objects, as of now, are not capable
| of synthetizing a new class/type based on joins.
| Silhouette wrote:
| It's strange that this point doesn't get more attention.
|
| ORMs by their nature tend to be built around a 1:1 mapping
| between fields on some object type and columns in some
| table. Bulk queries get you multiple objects corresponding
| to multiple rows. Relationships get you multiple objects
| with some of the fields being references to the other
| objects. Obviously I'm simplifying here and there have also
| been some attempts to do things in other ways but this is
| basically how most of the popular ORMs work today.
|
| However in reality a lot of useful queries return a list of
| flat data structures or even just a single flat data
| structure with some subset of the columns of all of the
| relevant tables and maybe a few extra columns that are
| calculated on demand and not stored directly in any
| database table. If that's the data I've read then what I
| really want is something like a properly-typed dataclass
| with exactly those fields/columns and nothing else that
| might add confusion or ambiguity.
|
| Unfortunately that doesn't really fit the classic ORM and
| OO model. Instead we often have to work with multiple
| objects with some form of nesting to follow the
| relationships, ambiguity about which fields have actually
| been read from the database and can safely be accessed,
| possibly some inaccuracy with the types such as nullable
| fields that have just been read from not null columns in
| the database, and a lottery to see what happens if we try
| to access fields on those objects that might not have been
| read by any previous database query anywhere in the system
| at any point since that particular ORM-backed object was
| created.
|
| I find it's one of those things where the popular approach
| - using an ORM in this case - works for relatively simple
| needs and in practice a lot of work does only have
| relatively simple needs so that's OK. But when I start
| doing more complicated things it can become a pain to work
| with because the whole model fundamentally doesn't fit what
| I'm actually doing.
| wruza wrote:
| Something like this is implemented in russian 1C system.
| They use an extended query language (bilingual as the
| whole system) and the query executor returns a special
| dataset object with all values wrapped into regular
| business-logic classes. So when you "select ..., agent,
| ... from ... join ...", you can access
| record.agent.manager.phone in your code later. Everyone
| knows the difference between selecting it in query and in
| code. All primitive types get wrapped too: dates to
| dates, bools to bools. It has no static typing, but the
| query result fields are all of "platform" types, not raw
| values like ids or json/int dates.
|
| Don't get me wrong, 1C products are regular enterprise
| crap on top of shitty language that stuck in the last
| century. The latest attempt to refit it as-is to www was
| a paradigmal disaster. But the platform (the runtime)
| itself may teach Django a volume or two about query
| integration. They do it since the '90s and 1C developers
| who traditionally weren't even considered developers had
| no issues with programming these systems without any deep
| stack knowledge. There's no stack basically, it's all
| homogeneous once you learn the fundamentals.
|
| Edit: yes, I find it very strange too. There's no
| popular/generic and simple open source platform which
| could bind it all together into a nice runtime. The whole
| ORM vs SQL and pitfalls feels so strange, as it's not
| something hard in my book.
| Izkata wrote:
| 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.
| agumonkey wrote:
| how is it called in the docs ?
| OJFord wrote:
| 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/topi
| cs/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.
| Izkata wrote:
| Yes, but also it's been a long time since I've had any
| reason to do this, and had gotten "managed = False" mixed
| up with abstract classes. Abstract classes won't let you
| do this, but you probably want "managed = False" to
| prevent migrations from doing stuff in the database, if
| it's going to be a reporting-only query that doesn't have
| a backing table.
|
| Also you need to return an "id" column since Django needs
| a primary key.
|
| On the flipside, you can put that query in the database
| as a VIEW and point the model at it, also with "managed =
| False".
| radus wrote:
| I occasionally do this with views managed by
| https://github.com/xelixdev/django-pgviews-redux/.
| agumonkey wrote:
| very nice
| lijok wrote:
| > 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
| generic queries that can be stitched together however in the
| business logic
| baq wrote:
| Have this right now. Fortunately no custom query builder.
| It's enough of a hell as is. Cleanup will take years.
| lijok wrote:
| What problems are you finding with it?
| baq wrote:
| Injection potential through the roof, copypasta galore,
| refactoring the same join pattern in a hundred different
| queries gets old rather fast... the usual suspects.
| lijok wrote:
| Oh god, good luck
| evantbyrne wrote:
| The first agency I worked at did this on their Java projects.
| They should have just used a fully baked ORM. Basically, they
| ended up creating a massive query layer in the program which
| contained all the different queries organized into different
| interfaces. To edit a simple API endpoint you would have to
| open like 5 different files at a minimum. And because queries
| were usually tailored to logic in a specific controller, they
| were not typically reusable. It was always a relief to go
| back to Django after dealing with that.
| rtpg wrote:
| So what happens is that you have the one query that runs for
| a specific page and fetches the data and the relevant fields?
| I could definitely see that working for many projects, at
| least while your objects don't have too many tiny little
| details to pull out of thhe DB
| [deleted]
| llanowarelves wrote:
| 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.
| rowanseymour wrote:
| > issues with Django's ORM due to the query laziness making
| performance tricky to reason about
|
| It's infuriating that this is still not a thing you can disable
| (https://code.djangoproject.com/ticket/30874). Pretty much my
| only gripe with the Django ORM which I'm a huge fan of (and I
| also write lots of SQL).
| Daishiman wrote:
| There's a library that solves this for you, thankfully
| https://github.com/charettes/django-seal.
| cushychicken wrote:
| I read this title and immediately thought "...but why wouldn't
| you just use Django?"
|
| Having written the sort of SQL-inline code the author talks
| about, then refactored the whole thing to use Django: Django's
| ORM solves waaaay more problems than it creates in this regard.
| philwelch wrote:
| > 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.
|
| One successful pattern I've seen treats the database as its own
| service layer. Service code does not send arbitrary SQL to the
| database--instead, all of the SQL queries are set as stored
| procedures in the DB. People sometimes freak out when I say
| this, so I should clarify that the stored procedures, table
| schemas and other things of that nature were version controlled
| and deployed with some minimal build tooling we'd developed in
| house.
|
| I really liked this pattern. I think anything that you need to
| talk to across a network should be treated as a service in
| itself, with a well defined interface. This simplifies testing
| and monitoring as well. The big risk with an ORM,
| architecturally, is that you end up treating your database as a
| sidekick to your service code, or even a dumb data store--some
| shameful implementation detail your service keeps locked in the
| basement--when they're capable of much more than that.
| ilovetux wrote:
| The only problem I have with using the django ORM is that it
| relies on the django project structure. While there are ways to
| use the ORM independently, they are full of hacks and trade-
| offs.
|
| Granted, this problem goes away if you are building a web app
| or a REST API, but if I just want an ORM for a command line
| application, I am using django's management command
| functionality which is OK, but it doesn't really scale easily.
| Too wrote:
| Yuck indeed. The way it dictates the order of imports,
| forcing you to import settings before any models can be
| imported, is reason enough not to use it. This problem
| spreads to any of your other files, leaving you in the end
| with everything depending on being launched in a full Django
| context. Shame, given it's otherwise very user friendly.
| jononor wrote:
| The most stupid issues with "active record" type ORMs is the
| implicit queries on member access, especially in collections -
| leading to the N queries problem. But in SQLAlchemy one can
| actually turn that off - that is, make it throw an exception
| when undeclared table dependencies are attempted to be
| accessed. This restores sanity. And one gets to keep goodies
| you mention, plus Alembic migrations (mentioned by another).
| Also one can write direct SQL too with SQLAlchemy, or use the
| "core" layer to keep a DSL but avoid ORM.
| WesolyKubeczek wrote:
| 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.
| p4bl0 wrote:
| I always write SQL code directly, but that's mostly because I
| actually enjoy writing SQL queries :).
| seunosewa wrote:
| Same here. SQL is arguably the best language for writing
| queries. That's what it was designed for.
| hot_gril wrote:
| You don't need an ORM, but this isn't how you avoid one. If
| you're thinking of your DB as a mere object store / OOP connector
| like this article is, you're better off with an ORM or NoSQL than
| this basically equivalent DIY solution. It's best to instead
| learn how to use a relational DB like a relational DB, and the
| rest will follow.
|
| Also, I'm not one of those people who dislike easy things (and
| will often whine about JS or Python existing). I'm all for ease
| and focusing on the business goals. It's just that ORMs and bad
| schema design will make things harder.
| atoav wrote:
| Recently I was wondering myself whether I should just write SQL
| as I didn't particularly enjoy working with SQLAlchemy.
|
| Then I discovered peewee. I am happy now.
| promiseofbeans wrote:
| > ... 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
| dikei wrote:
| Also, DB-API 2.0 is a standard that's followed by most database
| drivers, similar to what JDBC is for Java, though not as
| strictly enforced.
| uranusjr wrote:
| Python also has the DBAPI specification, which defines what
| interface a library must support to be considered a database
| driver. The author claiming Go's sql package encourages writing
| SQL directly while Python doesn't really seems a bit awkward.
| zknill wrote:
| 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".
|
| The problem with ORMs is that they are often designed to do
| number 1, and are used to do number 3. This means there's often
| 'magic' in the ORM, when really all someone wanted to do was
| generate the code to read/write data from the database. In my
| experience this pushes engineers to adopt number 2.
|
| I'm a big fan of projects like sqlc[1] which will take SQL that
| you write, and generate the code for reading/writing that
| data/objects into and out of the database. It gives you number 3
| without any of the magic from number 1.
|
| [1] https://sqlc.dev/
| tantaman wrote:
| 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/
| waffletower wrote:
| There is definitely a fourth category -- "I want to build
| database queries natively using the paradigms of the language I
| am developing with, without use of SQL or an intermediary which
| translates into SQL."
| tracker1 wrote:
| I'm pretty firmly in #2... it's relatively straight forward in
| a scripting language, and easy enough with something like C#
| with Dapper. In the end ORMs tend to over-consume, and often
| poorly. And even when they don't in most cases, they start to
| in more difficult cases. That doesn't even get into the amount
| of boilerplate for ORMs. You have to buy in to far more than
| their query model(s).
| sanderjd wrote:
| Well put!
| karmakaze wrote:
| 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).
___________________________________________________________________
(page generated 2023-08-14 23:01 UTC)