SQLAlchemy 1.4(sqlalchemy.org)
sqlalchemy.org
SQLAlchemy 1.4
https://www.sqlalchemy.org/blog/2021/03/15/sqlalchemy-1.4.0-released/
77 comments
I hate SQLAlchemy and Alembic.* I still use them both, because they are at least some kind of standard and SQLAlchemy plays with FastAPI very nicely - and I love API input validation and docs for free.
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. :)
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. :)
I was using SQLAlchemy with FastAPI and databases, but still had issues with the lack of async in SQLAlchemy, even though it's supposed to be coming in 2.0. Ended up moving to Tortoise ORM, which is an async copy of Django ORM, but there are some odd design decisions, and I'm not a fan of the way it plays with pydantic. I've yet to try a migration with it too, so the jury's out whether it was a worthwhile decision.
[deleted]
Came here to say the same thing. SQLAlchemy + Alembic is so powerful. Also, I think this crowd might like this blog I wrote about serializing SQLAlchemy models into JSON: https://wakatime.com/blog/32-flask-part-1-sqlalchemy-models-...
Have you ever ported a Django application to SQLAlchemy while leaving it in the Django ecosystem? Or used SQLAlchemy in Django in any way. I suppose that once you remove the orm, if you're not using forms etc there isn't much left but I'm curious how easy such a port can be.
I am convinced (through experience) that overuse of the Django ORM (and Django REST Framework, which is tightly coupled to the ORM) is a pattern that leads to intractable ball-of-mud apps.
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.
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.
One of the worst pieces of advice leads to over packaging into apps. Once you start making foreign keys across apps (especially ignoring content types as the connective tissue) you’re headed to a path of needless complexity.
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.
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.
> but they don’t immediately grok that models, views, etc can be packages with multiple modules
Ah yeah but the MVC/MVT/"SOLID" theologians don't like that
Ah yeah but the MVC/MVT/"SOLID" theologians don't like that
I prefer using packages over apps too. My rule of thumb is, use packages unless the code is reuseable, then it can be an app, but if the code is reuseable it makes no sense for it to be in my repo, so should be extracted to an external repo on pypi.
> 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.
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.
I think DRF Viewsets may be easy to replicate with the help of class based views from fastapi-utils[0]
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
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
I strongly agree with this view. Some things in particular that I think become anti-patterns as your Django app grows:
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.
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.
1. When it comes to middleware implementations, that's a feature, not a bug. Inside your views, do what you want, they are either a short to medium size function, or a small class. In your templates, I agree, so don't call ORM functionality from there directly. You can also just pass a `"books": tuple(books_queryset)` to the template engine to avoid having them be able to add additional filter conditions.
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.
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.
I actually prefer #5 because you are explicitly “exporting” the parts Django cares about and ensures you aren’t stomping on other models. The part that stinks is dealing with cyclical imports.
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.
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.
In my experience, within an organization that runs services/microservices, having a service template is a good way to handle having a consistent approach across services instead of each team picking their own favorite libraries to flesh out a small/micro frameworks like FastAPI or Flask.
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.
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.
I think the "Djangonic" approach would be to make this configurable, but with defaults that give you convenient magic for the simple/happy path. For example template paths are completely configurable (indeed you can plug new lookup strategies in addition to just overriding the default paths), but the defaults are "look in this well-defined location".
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.
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.
This really closely tracks my opinions/experience.
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.
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.
My experience backs the ball of mud claim. Large multi “app” Django stack with no clear architecture or direction. Everything’s a model. It was a nightmare adding anything new.
So true. I've seen them too much. The SQLAlchemy data mapper is so much better.
Short answer: nope. As you identified everything is so tied to the models you aren’t left with much that makes Django worth using.
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.
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.
Big fan of SQLAlchemy & Alembic as well - I had originally assumed that these tools were for people using Flask and the like. Curious how you use it with Django? Django has a built in migration manager or so I thought
Ah sorry that wasn’t clear.
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.
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.
Mypy support in the core will be great - dropboxes stubs are good but will be better to get full typechecking when projects have it enabled.
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.
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.
Reluctant to use asyncio? How come? I think it's great. Very happy SQLAlchemy has finally added support, I was slowly dropping it in favor of solutions that supported asyncio.
I summarised my opinions here: https://calpaterson.com/async-python-is-not-faster.html
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.
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.
Thanks for the link!
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=...
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=...
I am a big fan of SQLAlchemy, though I often use it more as an advanced pythonic query builder than as an ORM It can sometimes be a bit more work to write something in SQLAlchemy than raw SQL, especially when doing complicated queries, but I really like composing the queries with python functions, defining CTE's, window functions, computed columns in separate functions.
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!
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!
you're welcome! Not sure if you saw that 1.4 has an all-new series of table valued function support, which previously was nonexistent, I hope it improves the situation in that area: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#t...
Wow great, I was not aware. Looks like everything I could hope for, congratulations on this great release!
SQLAlchemy 1.4.0 Released – featuring major rethink of the APIs in Core and ORM [1] HN thread from yesterday and Migrating to SQLAlchemy 2.0 [2] from 26 days ago.
[1] https://news.ycombinator.com/item?id=26473654
[2] https://news.ycombinator.com/item?id=26182744
[1] https://news.ycombinator.com/item?id=26473654
[2] https://news.ycombinator.com/item?id=26182744
I have to say, SQLAlchemy is one of my favorite ORMs. It feels like such a nice blend between convenience and lower level details. Looking forward to upgrading to 1.4 in due time.
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.
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.
Great to see the SqlAlchemy project get new dedicated maintainers. It's a colossal effort and a huge contribution to the overall Python ecosystem. Kudos to the team!
Honestly, SQLAlchemy was the biggest design mistake we made in sourcehut, and we're working to remove it now. SQL tends to be the bottleneck, and ultimately we just want to write SQL queries which do the right thing, so that we can control for their performance better. Knowing the SQL you want and convincing SQLAlchemy to produce it is an annoying process which makes it hard to make performant queries. I would recommend that anyone starting a new project would avoid using an ORM and just write SQL queries instead.
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.
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.
SQLAlchemy ORM is built on SQLAlchemy Core, a very complete and flexible SQL query generation abstraction with extensions covering just about every non-standard feature of PostgreSQL and MySQL. Our services (backend content APIs for one of the Web's biggest and busiest content providers) use Core and Alembic (and Flask or FastAPI) and never instantiate the ORM's Models or Sessions.
That’s what the SQLAlchemy Core is for. You pass around a SQL AST and modify it as you go before executing it. It’s incredibly flexible and always produces what I expect in the resulting SQL.
I’ve even written reports with it.
I’ve even written reports with it.
+1 to the siblings advocating for dropping down to Core. Additionally, in the rare cases that you need access to something that isn't available via Core's query builder, you can drop down to bare nekkid text* queries and still cooperate with the rest of the framework.
* while still relying on safe query parameterization, of course.
* while still relying on safe query parameterization, of course.
Others already pointed out that you can use a core method.
What I see is that I actually rewrote a query that had 7 joins (yes, it was not great), and rewrote it in SQLAlchemy that made for a the query to have a lot fewer total lines of code to write.
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.
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.
I get where you are coming from, but ORMs are very useful for inserting and updating data, a lot more efficient than writing SQL. Sure for complex queries it's a different story.
In general, this is also my feeling about ORMs. I'm not sure of the best options in Python, but for TypeScript this article has a very helpful section on query builders: https://phiresky.github.io/blog/2020/sql-libs-for-typescript... (disclaimer: one of them is mine).
I always found RedBean[1] to be an interesting ORM, despite being PHP based. It has a model where you put it in dev mode, and the calls you make create the database schema, including foreign relationships. Then you freeze it to remove the overhead. It does support more traditional flows as well. Is there something similar for Python?
[1] https://redbeanphp.com/index.php
[1] https://redbeanphp.com/index.php
Every major Java ORM has offered similar functionality for over 15 years now.
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.
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.
I don't find Java ORMs terribly similar. They seem to lean heavily on annotations and not code, and creation of "table creation scripts", etc. RedBean doesn't import or export anything by default, it's "live". It's the live creation and modification I found interesting. I'm also not claiming that approach as some sort of panacea. It just happens to fit a "make another CRUD app" pattern I have to deal with a lot.
It only works with SQLite, but my sqlite-utils Python library works a bit like that: you can feed it JSON objects and it will create a table schema to match: https://sqlite-utils.datasette.io/en/stable/python-api.html#...
It has a CLI tool as well: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
It has a CLI tool as well: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
Neat! you could prototype out the db in sqlite, and then have sqlalchemy introspect and write out a db-agnostic model from this.
Happy to see the support around foreign key relationships and lookup tables here.
Thanks!
Happy to see the support around foreign key relationships and lookup tables here.
Thanks!
Yeah, see: https://dataset.readthedocs.io/en/latest/
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
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
SQLAlchemy has a sister project called Alembic for migrations, check it out https://alembic.sqlalchemy.org/en/latest/
Not really 'slap JSON get database' level, but well, close.
Not really 'slap JSON get database' level, but well, close.
From using Alembic a little bit lately I've found it needs a lot more babysitting than for example Django's migrations. It's probably for the best as I end up reading and editing the migrations much more than I would in Django.
I definitely had my ass bit by Django migrations on deployment when I had cases like A->B->C (must be ran in order) but ran A->C through makemigrations, because I skipped the commit that is used to generate B.
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!
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!
I was recently learning Ecto - Elixir's DB integration library - and its design, to my untrained eyes, looked very similar to SQLAlchemy. Especially the way of writing queries looked almost identical. I never actually used either, just read a bunch of tutorials for both, so I'd be happy to hear an informed opinion: is the similarity superficial, or is Ecto modeled after SQLAlchemy? Or do they both implement a pattern which predates them both?
I dare say that Ecto does a lot of things quite differently. From my work with SQLAlchemy I never saw anything similar to changesets.
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.
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.
I believe Ecto is largely inspired by LINQ from C#, but I wouldn't be surprised if SQLAlchemy was an inspiration as well.
You can use SQLAlchemy like Ecto or in a functional manner, especially with core.
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.
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.
Thank you as always zzzeek for the hard work.
One feature that I was waiting for is finally here: JSON column support for SQL Server.
Wow, this release is stuffed full of gold! Congrats to the team on continuing to perfect an already-great project.
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!
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!
avoid sqlalchemy like a plague, instead try using dataclasses and something like https://pugsql.org/
Broke my build. It appears SQLAlchemy predates SemVer.
Fantastic, massive congrats to the team! SQLAlchemy is by fat the best DB tool I've ever worked with.
SQLalchemy is powerful! Peewee is a lot lighter and easier to get started with, check it out.
But can it do everything sqlalchemy can do? It is not a data mapper ORM it seems.
> SQLAlchemy 1.4 establishes itself as the starting point for the SQLAlchemy 2.0 project, which will move fully to Python 3
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?
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?
They will drop python 2 support completely in SQLAlchemy 2.0. That's at least how I read it.
Oooh it's already fully python 3, they'll just drop backwards compatibility. That's a bit different...
It's easy to write code which runs on both Python 2 and 3 — there are tools like six which help maintain compatibility on the relocated standard library modules, and tools like futurize/modernize which will rewrite your code automatically:
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.
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.
All the more reason to move to python 3 instead of staying on 2, hence my confusion how they still have parts running in python 2. Other replies clarified that this is about backwards compatibility, not updating code to run in python 3 at all.
I'm assuming they just write code in a compatible way. It's not _too_ dificult, but it does add a lot of version checks everywhere. For example, you can use a lot of Python 3 features in Python 2 via the future package.
> it does add a lot of version checks everywhere.
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.
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.
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.