9 ms·
SQLAlchemy 1.4
- tyingq 6y agoI 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 https://redbeanphp.com/index.php
- simonw 6y agoIt 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#creating-tables 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#inserting-json-data https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
- hantusk 6y agoNeat! 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 6y agoYeah, see: https://dataset.readthedocs.io/en/latest/ 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
- grenoire 6y agoSQLAlchemy has a sister project called Alembic for migrations, check it out https://alembic.sqlalchemy.org/en/latest/ https://alembic.sqlalchemy.org/en/latest/ Not really 'slap JSON get database' level, but well, close.
- jamil7 6y agoFrom 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 6y agoI 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!
- StevePerkins 6y agoEvery 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 6y agoI 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.
- sradman 6y agoSQLAlchemy 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 https://news.ycombinator.com/item?id=26473654 [2] https://news.ycombinator.com/item?id=26182744 https://news.ycombinator.com/item?id=26182744
- Aachen 6y ago> 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 6y agoI'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.
- bajsejohannes 6y ago
- calpaterson 6y agoMypy 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 6y agoReluctant 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 6y agoI summarised my opinions here: https://calpaterson.com/async-python-is-not-faster.html 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.
- fastball 6y agoThanks 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=ph&test=fortune&l=zijzen-sf https://www.techempower.com/benchmarks/#section=data-r20&hw=...
- leetrout 6y agoI 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.
- scrollaway 6y agoHave 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 6y agoShort 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 6y agoI 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.
- monus21 6y agoThank you as always zzzeek for the hard work.
- plq 6y agoGreat 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!
- savrajsingh 6y agoSQLalchemy is powerful! Peewee is a lot lighter and easier to get started with, check it out.
- ergo14 6y agoBut can it do everything sqlalchemy can do? It is not a data mapper ORM it seems.
- haolez 6y agoOne feature that I was waiting for is finally here: JSON column support for SQL Server.
- ddevault 6y agoHonestly, 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.
- linux2647 6y agoThat’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.
- nicwolff 6y agoSQLAlchemy 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 6y agoI 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.
- brandmeyer 6y ago+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.
- nickjj 6y agoI 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 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.
- iainctduncan 6y agoFantastic, massive congrats to the team! SQLAlchemy is by fat the best DB tool I've ever worked with.
- klibertp 6y agoI 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?
- madelyn 6y agoYou 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 6y agoI believe Ecto is largely inspired by LINQ from C#, but I wouldn't be surprised if SQLAlchemy was an inspiration as well.
- lawik 6y agoI 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.
- tda 6y agoI 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 6y agoyou'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#table-values-table-and-column-valued-functions-row-and-tuple-objects https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#t...
- tda 6y agoWow great, I was not aware. Looks like everything I could hope for, congratulations on this great release!
- theptip 6y agoWow, 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!
- mbar84 6y agoBroke my build. It appears SQLAlchemy predates SemVer.
- may4m 6y agoavoid sqlalchemy like a plague, instead try using dataclasses and something like https://pugsql.org/ https://pugsql.org/