SQLAlchemy 1.4
sqlalchemy.org
sqlalchemy.org
When starting a new Go project this year I insisted we roll the database with Alembic so we would have a solid migration framework. I don’t regret it but the team has replaced it with Go Migrations with raw SQL and it looks like it’s going to be great but SQLAlchemy and Alembic will always be my go-to for important, timely database work when there isn’t already an ORM and/or migration tool in place.
Edit to add (see below): SQLAlchemy makes it easy to share access to things that aren’t controlled by Django but I did not make Django model or manager shims to hide the fact SQLAlchemy was in play.
I think there are old discussions on the Django core dev mailing list about supporting alternative ORMs like the template engines in a pluggable fashion but it would be so much work.
I’ve actually been quite happy with the Django ORM and I’ve dropped out of it to do raw SQL when I’ve needed Postgres features that weren’t supported.
There's so much stuff available for Django out of the box like auth, http/REST, CSRF, etc that it makes it really hard to justify not using Django at the start of a project. But the recommended/accepted Django patterns lead to pure misery down the road due to how coupled the app becomes.
You can make a well-architected Django app, it just requires ignoring almost every common or recommended Django pattern.
Indeed I'm already ignoring a lot of them. No forms or templates of any kind, nor any of the stuff that is built for that (messages framework etc); only using DRF; stick to Postgres-only (I don't make much use of the db abstractions), etc.
The ORM is … nice, and has its limitations. I really just want to start using FastAPI by default but I'm concerned I'm setting myself up for a lot of busywork by skipping on such high-value niceties as the Django admin or DRF viewsets.
But I agree that Admin is still the missing killer feature from the fastapi ecosystem. But it looks like there is an emerging ORM/Admin combo heavily based on Django[1].
I've also considered (if you prefer SQLAlchemy) the possibility of instantiating a Flask/Flask-Admin wsgi app and mounting it on a running fastapi server, but haven't been able to confirm if that works yet. (EDIT: I just confirmed it does indeed work, and is rather painless.)
[0] https://fastapi-utils.davidmontague.xyz/ [1] https://github.com/long2ice/fastapi-admin
Didn't we get over that as a measure of ability yet?
Lots of people have learned Python and Django together much like rails and Ruby but they don’t immediately grok that models, views, etc can be packages with multiple modules and apps aren’t the only (nor the correct) abstraction for code organization.
Ah yeah but the MVC/MVT/"SOLID" theologians don't like that
1. Everything is coupled to the ORM models / QuerySet, so any part of your stack can mutate the request's query. On one hand this is great for things like dynamic APIs and adding arbitrary filter params, but it's really hard to keep your concerns cleanly separated. (You could use Django Seal to work around the QuerySet mutability issues but it requires finesse).
2. The ActiveRecord pattern of calling model.save() to write to the DB is actually really restrictive; it forces you to conflate DB logic with your business logic, where the former often more naturally spans multiple domain models (i.e. your DB session logic should be at the application layer while your business logic is at the domain layer, in DDD terminology). The NHibernate / SQLAlchemy pattern of tracking dirty writes and only making DB requests when you call Session.flush() / Session.commit() is much more flexible. An example is if you have an object with a list of other objects, you might want to write some code in a transaction like "for day in week: parent.children.add(calculate_child_for(day))". In Django you'd be saying `parent.child_set.add(...)` which immediately runs a DB query. If your logic fails on the last child, you don't need to have sent the earlier DB requests. So instead you end up having to contort your business logic to avoid DB side-effects, collecting a list of unsaved items to later save. Using dirty-tracking means you can run your algorithm in "plan mode" / no side-effects by just omitting the "Session.commit()" at the end; no spurious DB calls will be made.
3. The ActiveRecord pattern of writing "model.fieldname = foo" and having every part of the stack treat your objects like CRUD data dictionaries completely breaks encapsulation; if you're trying to write proper domain models that encapsulate your business logic, you don't want everybody to be able to poke arbitrary state. You end up having to guard against everything being in arbitrarily bad states, because every field is public. In my experience it's much easier to test and less confusing to make most class members private, and have well-defined state-transition methods. But making your model members private in Django is obnoxiously verbose and you're swimming against the current at every step; the whole system is based on the assumption that your API is CRUD; you need to say something like `_owner_name = CharField(db_column='owner_name', verbose_name='owner name', ....)` in order to avoid borking your DB schema and admin pages.
4. Django apps are a trap. The tutorials suggest you should expect to create multiple apps but this will make your codebase a pain to refactor; you can't migrate models between apps, and so you're stuck with the first app structure you go with. Just use a single app `core` and pretend that apps are not a thing, unless you are implementing a completely standalone composable app in another repository.
5. Django is in general more on the "config over convention" end of the spectrum than, say, Rails. But it still has some annoying magic like requiring all your model files to be imported in the `appname.models` module. This means you need to do wacky re-importing in `appname/models/__init__.py` if you want to avoid having all your models in one file. Why not just allow us to configure the model path(s) like we can configure template and other paths?
Having said all this, I agree with you that Django is really productive for the early phases of a project. When I started working in Flask I had to spend a few days figuring out each of a large number of things that are just batteries-included in the Django framework. So I can see the appeal, and for small side-projects I'd still probably reach for Django. But I think I'd probably start from Flask or FastAPI for new startups.
At the end of the day it comes down to discipline and leaning on the framework to help keep things clean is Not A Bad Thing(tm) IMO.
But I am not one that feels like it robs me of my expressiveness because I’d rather have something consistent to build on across multiple projects instead of trying different patterns within the web stack that flask / fastapi let you get away with.
That being said, fast api seems great especially if you don’t have to deal with the overhead of user management and such directly.
Any internal libraries should be very very light wrappers, if they exist at all. Putting more into the service template or even using code generation allows things to slowly improve and change without being pinned to some old crappy code in a shared internal library, or managing breaking library changes across teams.
This is more of a DX gripe than a reason not to use Django, it should probably not be in my list if I'm being fair.
Manually adding a domain/service layer over the models and never exposing the models across domain services or up to the application layer seems really tedious when you're doing it, but pays dividends down the line. The object returned from the domain layer interface is an attrs, pydantic, or just a plain Python class with no connection to the database.
This encapsulates the saving-to-the-datastore logic behind the domain layer interface, so other parts of the app can't just do model.a = b; model.save() wherever and whenever they want. The domain interface decides what is allowed, and can handle & hide session logic, writing or updating related records, etc.
Further, you don't have to have a bunch of @property calculated fields that include lots of business logic on the model, causing the model to bloat to a 500 line file and conflating business logic with database implementation details. Business logic can all go in the domain layer.
2. Different way of thinking, but there are lots of times when you need to explicitly call object1.save() but not object2.save() quite yet. I think having a Session.commit() and Session.rollback() might be helpful, and I'd love clean/dirty tracking. Currently, figuring out of a given instance of a model is clean is a PITA and is often times necessary.
3. I have never felt this was a problem, from the point of view that certain types of code should not change state, so just don't do it. If you don't trust your code to not change state from under you, then why are you writing such code?
4. Agreed. I usually throw all my models into a single `core` app, and the rest of the functionality lives elsewhere. This gives me a nice separation between business logic and database storage logic too.
5. Seems like a small annoyance you deal with once. Also having a model per file feels super wasteful in terms of a model that contains no logic, no custom manager, just a half dozen fields. Hunting through files and dealing with circular imports sucks way more than having slightly longer files.
We had a PHP app with MySQL and built out our new user management and billing in Django with Postgres.
I created SQLAlchemy models to map the PHP MySQL tables to Python, implemented the Django password hashing steps in PHP and let both apps access both databases as we slowly ported things to Django.
It worked very, very well even given the complexity. And running migrations all from Python kept it all in one spot conceptually.
Don't get me wrong - it's nice that there is some abstraction layer above the database. However, when it doesn't work the way you want it to, you need to find some workarounds for things that are otherwise trivial in SQL.
Alembic... meh. Just give me the SQL and I will migrate database, no problem. But looking for the yet another syntax to perform the same thing is not my idea of fun. Not to mention that all migrations I ever did were linear - I can't imagine why someone would need dependency resolution in a db migration tool. Still, it is a kind of standard, so if you are working as part of a team... shrugs
* edit: to be exact, I don't really hate hate them... I am just very frustrated with them from time to time. :)
It's also great news that the asyncio users be able to use this brilliant ORM! Like many others I (reluctantly) toil in the async mines nowadays.
TL;DR: It's not faster, prone to bugs if used incorrectly and adds new failure modes. There are some good reasons to use it but they are narrow and I think most real world usage is inappropriate.
With regard to speed, why do you think the TechEmpower benchmarks[1] (which I think actually do employ a good number of workers for the sync frameworks) have the sync frameworks getting smoked by the asyncio frameworks?
[1] https://www.techempower.com/benchmarks/#section=data-r20&hw=...
And very important: any query I can write for postgres I can write with SQLAlchemy. But as I work a lot on an application with some complicated JSONB columns, I must say the syntax for set returning functions is kind of awkward. But the session and transaction management, query composability, ORM options, and overall Pythonic way you can use SQLAlchemy really beats putting (semi) raw SQL in your code. And as a bonus you can do linting, type checking and refactoring of you queries.
Thanks for all the hard work Michael!
If anyone happens to use SQLAlchemy, Alembic and Flask a while back I open sourced a Flask CLI extension called Flask-DB at https://github.com/nickjj/flask-db.
Its focus is to quickly init Alembic configs with a few opinions, alias the official Alembic CLI for migrations and let you quickly reset and seed your database using patterns found in other frameworks (such as having a seeds.py file that you can do whatever you want in).
It's something I extracted out of building a bunch of Flask apps over the last 6 years.
One thing which is occasionally useful is a query building tool. For Go we use squirrel for this purpose. If you need unrelated parts of the code to work together to produce a single SQL query, it can help to have such a tool. This is a lot less than a full blown ORM, though, it's more like passing around a SQL AST in memory.
I’ve even written reports with it.
* while still relying on safe query parameterization, of course.
I validated it by checking out what it would generate via its internal SQL compiler.
So for me, it was more like, am I competent enough to write less code and fewer mistakes, due to functions, autocomplete, etc. versus writing the full SQL.
It's a time saving for me, and the queries are expected. I also echo the SQL query to make sure that that's the exact query I want.
Lastly, I then groused at the engineer who decided that the query pulled in so much crap from so many tables in 1 query.
It has a CLI tool as well: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
Happy to see the support around foreign key relationships and lookup tables here.
Thanks!
It doesn't do foreign relationships though, guiding you to use sqlalchemy when you reach that level of complexity. It works for all databases supported by sqlalchemy though
Not really 'slap JSON get database' level, but well, close.
I did catch it only because I expected I would run into this problem, but yeah, easy to shoot yourself in the foot when you expect it to be 100% magic!
At least in the Java community, I find that more and more shops are moving AWAY from letting their ORM library manage their database schema migrations.
The current best practice is using something like Flyway or Liquibase. In which you place your DDL migration scripts (i.e. raw SQL) into source control, either with your application or externally. If it's with your application, then the application checks for new migrations and applies them at startup. If externally, then you do this yourself with a CLI tool.
Either way, the system creates a table in your schema to track which migration scripts have been applied, along with hashes for each script. So the system can detect whether there's a new migration to perform, or throw an alert warning if someone retroactively changes an already-applied script.
Of course, you're still welcome to leverage an ORM library in dev-mode to help you create that initial schema-setup script. It's just not great to rely on that approach for ongoing migrations in production. Having a trail of migration scripts in source control makes SO MUCH difference in reliability, and making it easier to stand up a new environment (or a local dev environment) that truly reproduces the state of your production schema.
But a lot of projects I've seen around the web try to bend the sqlalchemy ORM into a more "active record" way of working.
I love working with Elixir but I would probably say that I found building queries in SQLAlchemy a bit more straight-forward than in Ecto. While I'd rather do basically everything else in Ecto.
I think most of the similarities is because they are both providing abstraction on top SQL, which tends to lead to a similar enough API surface. I don't know what primarily influenced Ecto. But I think it was quite intentionally not Django ORM or ActiveRecord. Working with Ecto and SQLAlchemy at different times I don't find them very similar beyond all the SQL terminology and API surface they share. So yeah, maybe superficial, and yes an older pattern, SQL ;)
SQLAlchemy + Alembic cover a very large feature set for ORM, query building, migrations and all of that stuff in a way I think works pretty darn well. It simplifies building SQL queries piece by piece but gets very complex for certain queries.
Async is huge. MyPy is great. More love for imperative mappers is also fantastic.
Many thanks to the SQLAlchemy team for all the hard work!
Woa. How does this even work, do they ship a py2 engine to run the old code since hosts don't have it installed anymore?
But I understand that's apparently not the intention.
Not really. It does require care (lots of constructs are off-limits) but usually you have one version check in your compat module. You may need a few others but it’s relatively rare.
https://python-future.org/compatible_idioms.html
The biggest problem now is that an increasing number of libraries dropped Python 2 support after end-of-life and so you might find that trying to support 2 in your code is fine but you need to depend on older versions of outside libraries. Most of the remaining problems tend to be cases where someone was failing to handle Unicode correctly and is blaming the required cleanup on Python 3 exposing the existing shortcomings of their code. Those problems can be harder to resolve if you have a bunch of sloppy input/output points in a codebase without good test coverage.