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.
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.
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.
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.
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.
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.
Active Record is NOT anti pattern. It's just one of ways to get things done related to (relational) database and your application. Active Record, Data Mapper, Raw SQL or whetever has its pros and cons.
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.
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.
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.
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.
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."
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.
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.
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.
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.
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).
> 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.
> 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.
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.
>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.
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.
>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.
> If you cut it out and move everything to parameterized SQL queries the SLOC explodes
My experience has been the opposite: that raw SQL queries end up much shorter (and consequently more readable) than the equivalent ORM code. The exception to that is INSERT/UPDATE queries, where I do tend to use some kind of ORM/query builder. I have used both, and I prefer raw SQL for anything beyond very simple queries.
I 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.
Separate chain for a different Django example, it only natively supports joins on explicit foreign keys, but because it has that extra information in the model the syntax for using it is extremely compressed. Let's say you have a "Dog" table with a foreign key to "Owner", and "Owner" has a foreign key to "City". Getting all the dogs that live in New York would be:
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.
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.
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.
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.
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.
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.
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.
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.
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".
Oh yes I forgot about that, that can be annoying. But of course, when it's annoying because it doesn't matter, it doesn't matter and you can pass anything back `as id`.
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.
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.
> 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.
> 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
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
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.
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.
Injection potential through the roof, copypasta galore, refactoring the same join pattern in a hundred different queries gets old rather fast… the usual suspects.
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.
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.
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.
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.
> 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).
> 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.
> ... 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 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.
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.
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
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.
> 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 à 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.
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.
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
> 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.
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.
In the rare case that you're interpolating a variable, you'd need to audit it in review. This is similar to carefully auditing the rare use cases of raw SQL expression when using an ORM.
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.
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.
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.
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.
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.
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.
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).
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."
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).
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.
I was never more productive than when using Access (and dBase II before that). Why can't we have that?
My theory: Something was lost in the jump from workgroup computing to client/server.
Imagine if Access was rebuilt on top of a client/server stack. That's kind of what Riffle is trying to do.
I've been (slowly) working on the persistence stuff. It (mostly) moots the SQL vs ORM vs query builder slap-fight.
I've got some notions (and POCs) about UI, mediated via HTTP & HTML.
I'd love to have some CRDT-like smarts; learning more is on my TODO list.
I'm still thinking about the "reactive" part. I haven't imagined anything past Access VBA style programming. I'm struggling to envision a FRP-meets-CRUD future perfect world.
Yup. Use of HQL/JPQL is proof that you shouldn't be using an ORM. Just use SQL instead. And once you have some SQL, it's just easier (overall) to do all SQL.
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.
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.
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.
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.
> 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.
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.
> 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.
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.
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.)
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?
287 comments
[ 5.6 ms ] story [ 252 ms ] threadPutting it all together is another blog post. And if you have colleagues: probably needs documentation. Which you also have to maintain yourself.
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.
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.
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.
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.
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.
You're conflating ORM with people who know nothing about databases. Why?
Although the worse offenders by far are those which decide ORM = bad and bypass it at every opportunity.
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."
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
- 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.
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.
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.
IMO:
is a lot less readable than:And obviously you can use whatever indentations you like.
- No keyword arguments
- No operator overloading (so you can't override | to get the nice "or" syntax)
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
That adds up, with almost no downside most of the time.
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.
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.
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.
Basically you call `.raw("...")` from some model's queryset, but there's no requirement you actually query that model's table at all.
will yield `SomeModel`s with `name`s that are actual `OtherModel` `foobar` values.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".
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.
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.
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.
You end up writing a query per usecase, rather than writing generic queries that can be stitched together however in the business logic
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.
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.
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).
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.
Python has built-in support for SQLite in the standard library: https://docs.python.org/3/library/sqlite3.html
[0] https://sqlite-utils.datasette.io/en/stable/
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.
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.
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.
This means that you will need extra tooling, and if you're DIYing you will need to write it yourself.
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.
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.
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.
By far the best database toolkit (ORM, query builder, migration engine) I have seen for any programming language.
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 à 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.
If your app can work well with static queries then you should not add an ORM.
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.
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.
Nope, I'm generally interpolating an inline expression consisting entirely of string literals.
Does not inspire confidence.
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
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.
Anecdotally, I haven't seen a place where Go is used without something like gorm or sqlc.
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.
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/
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/
Using stored procedures and triggers as much as possible.
I was never more productive than when using Access (and dBase II before that). Why can't we have that?
My theory: Something was lost in the jump from workgroup computing to client/server.
Imagine if Access was rebuilt on top of a client/server stack. That's kind of what Riffle is trying to do.
I've been (slowly) working on the persistence stuff. It (mostly) moots the SQL vs ORM vs query builder slap-fight.
I've got some notions (and POCs) about UI, mediated via HTTP & HTML.
I'd love to have some CRDT-like smarts; learning more is on my TODO list.
I'm still thinking about the "reactive" part. I haven't imagined anything past Access VBA style programming. I'm struggling to envision a FRP-meets-CRUD future perfect world.
I've never used access and have 0 familiarity with it. Are there any examples I could look at?
Yup. Use of HQL/JPQL is proof that you shouldn't be using an ORM. Just use SQL instead. And once you have some SQL, it's just easier (overall) to do all SQL.
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.
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.
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.
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.
> 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.
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.
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.
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.)
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?
> For comparison, all of Flask
That's... not a reasonable comparison. Flask does basically nothing. Of course it's small. And it does something totally different so why compare?