[HN Gopher] SQLAlchemy 1.4
___________________________________________________________________
SQLAlchemy 1.4
Author : mkl95
Score : 218 points
Date : 2021-03-17 08:30 UTC (14 hours ago)
(HTM) web link (www.sqlalchemy.org)
(TXT) w3m dump (www.sqlalchemy.org)
| savrajsingh wrote:
| SQLalchemy is powerful! Peewee is a lot lighter and easier to get
| started with, check it out.
| ergo14 wrote:
| But can it do everything sqlalchemy can do? It is not a data
| mapper ORM it seems.
| calpaterson wrote:
| 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.
| fastball wrote:
| 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.
| calpaterson wrote:
| 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.
| theptip wrote:
| 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!
| nickjj wrote:
| 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.
| monus21 wrote:
| Thank you as always zzzeek for the hard work.
| tda wrote:
| 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!
| zzzeek wrote:
| 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...
| tda wrote:
| Wow great, I was not aware. Looks like everything I could
| hope for, congratulations on this great release!
| iainctduncan wrote:
| Fantastic, massive congrats to the team! SQLAlchemy is by fat the
| best DB tool I've ever worked with.
| Aachen wrote:
| > 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?
| pta2002 wrote:
| 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.
| masklinn wrote:
| > 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.
| asplake wrote:
| "Python 3 only"
| pta2002 wrote:
| Thought they were asking about how it's being kept
| compatible with both _right now_.
| Shared404 wrote:
| That's what I read.
| Aachen wrote:
| So python 2 partially now, is what that sounds like to me.
|
| But I understand that's apparently not the intention.
| bajsejohannes wrote:
| They will drop python 2 support completely in SQLAlchemy 2.0.
| That's at least how I read it.
| Aachen wrote:
| Oooh it's already fully python 3, they'll just drop backwards
| compatibility. That's a bit different...
| acdha wrote:
| 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.
| Aachen wrote:
| 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.
| sradman wrote:
| _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
| haolez wrote:
| One feature that I was waiting for is finally here: JSON column
| support for SQL Server.
| leetrout wrote:
| I love SQLAlchemy and Alembic. I've done many migrations from
| other languages to Python and to Django controlled databases
| using these tools.
|
| 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.
| ericvanular wrote:
| 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
| leetrout wrote:
| 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.
| raziel2p wrote:
| Why didn't you just make Django models for the PHP MySQL
| tables?
| leetrout wrote:
| This was almost 7 years ago so I honestly don't remember
| the context of the decision only the tooling I wrote.
| welder wrote:
| 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-...
| amenod wrote:
| 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. :)
| pm wrote:
| 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.
| jart wrote:
| Saying SQLAlchemy abstracts databases is generous since good
| abstractions save us from complexity and when they don't it's
| just a big clumsy veneer. If you look up leaky abstraction in
| the dictionary you'll find a photo of SQLAlchemy.
| scrollaway wrote:
| 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.
| leetrout wrote:
| 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.
| skrtskrt wrote:
| 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.
| theptip wrote:
| 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.
| IgorPartola wrote:
| 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.
| leetrout wrote:
| 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.
| skrtskrt wrote:
| 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.
| theptip wrote:
| 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.
| skrtskrt wrote:
| 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.
| scrollaway wrote:
| > _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.
| ttymck wrote:
| 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
| collyw wrote:
| Maybe I am just a god at writing Django, but I don't write
| ball of mud apps.
| theptip wrote:
| How many LoC?
| iainctduncan wrote:
| So true. I've seen them too much. The SQLAlchemy data
| mapper is so much better.
| leetrout wrote:
| 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.
| neillyons wrote:
| 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.
| raverbashing wrote:
| > 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
| gabereiser wrote:
| 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.
| tyingq wrote:
| 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
| StevePerkins wrote:
| 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.
| tyingq wrote:
| 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.
| grenoire wrote:
| 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.
| jamil7 wrote:
| 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.
| grenoire wrote:
| 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!
| simonw wrote:
| 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...
| hantusk wrote:
| 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!
| hantusk wrote:
| 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
| mbar84 wrote:
| Broke my build. It appears SQLAlchemy predates SemVer.
| may4m wrote:
| avoid sqlalchemy like a plague, instead try using dataclasses and
| something like https://pugsql.org/
| plq wrote:
| 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!
| ddevault wrote:
| 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.
| brandmeyer wrote:
| +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.
| nicwolff wrote:
| 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.
| collyw wrote:
| 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.
| gmac wrote:
| 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).
| linux2647 wrote:
| 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.
| klibertp wrote:
| 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?
| lawik wrote:
| 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.
| madelyn wrote:
| 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.
| bitwalker wrote:
| I believe Ecto is largely inspired by LINQ from C#, but I
| wouldn't be surprised if SQLAlchemy was an inspiration as well.
___________________________________________________________________
(page generated 2021-03-17 23:02 UTC)