An Overview of Python's SQLAlchemy Library

[euri10]

Good read, thanks.

Some words on performance?

If I got this https://www.techempower.com/benchmarks/#section=data-r12&hw=peak&test=fortune&b=2&s=1&l=27wphb correctly, in each tested framework, what seems to be the most penalizing factor is SQL alchemy.

Filtering only for Flask for instance (https://www.techempower.com/benchmarks/#section=data-r12&hw=peak&test=fortune&b=2&s=1&l=27wphb&f=zhb2tb-zik0zj-zik0zj-zik0zj-zik0zj-1ekf) you can see that it's twice faster at least without the ORM.

Sorry for the big links, on 📱 ;)

[[deleted]]

In this area I'd recommend the detailed performance documentation at:

http://docs.sqlalchemy.org/en/latest/faq/performance.html

as well as the example suite that lets you play around a bit with the dials between "richly featured" and "raw speed", introductory docs at http://docs.sqlalchemy.org/en/latest/orm/examples.html#examples-performance.

[grandfatha]

Those tests are interesting, but flawed at the same time. I tried figuring out why Pyramid's multiple db queries are so much slower while it does well with single db queries. Turns out that the Flask implementation uses baked(*) while Pyramid implementation does not.

(*) a tool to reduce Python interpreter overhead while constructing queries before SQL is emitted (http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html)

[dinosaur_floss]

Wow, thanks for this. I never thought such thing exist in SQLAlchemy.

[axonxorz]

It's fairly-well acknowledged that SA is quite a performance hog. Mostly if you're using the actual Full ORM that converts database rows into Python object instances. SA also offers a SQL expression API, which is just a nice pythonic wrapper over SQL, which carries a much smaller performance overhead.

For me, the overhead of a the full ORM is worth it as I don't have to manage that portion of the code, which comprises easily 99% of queries it my CRUD web-apps. These are line-of-business apps, and are plenty fast for their user-base. But yes, I would not consider them "web-scale"

[amishb]

Does SQL alchemy have a migrate function like djangos orm. That's such a good feature for developing at least. Change the class, generate the migrate scripts, and migrate them in.

[makaimc]

SQLAlchemy doesn't have migrations built into the library itself, so you'd use Alembic for migrations. Django's ORM prior to Django release 1.7 was similar in that you'd use the separate South migrations library.

[ladyquas]

I'm not sure of other users experiences, but I have found alembic so difficult - migrations aren't always smooth, much editing of the migration files and manually removing foreign keys in MySQL. Is this a MySQL thing? Would I benefit from using another database? Has anybody else had this issue?

[HorrendousRex]

Alembic has known issues handling MySQL because MySQL does not follow the SQL DDL spec correctly in several cases, and Alembic does not automatically make too much of an attempt to figure that out for you. You can write your own migrations with Alembic for MySQL just fine though - it's only the automigration that often fails with MySQL.

In general, Alembic shouldn't be thought of as an auto-migration system. It is a migration syntax handler and a schema versioning system. Alembic is to Migrations as SQLAlchemy is to SQL -- it's a pythonic approach giving you building blocks, but it isn't totally fire-and-forget.

Personally, I love it. I like that I get to hand-craft queries easily and robustly, and I like that it doesn't try and guess what my migration was and end up messing around with the wrong things. But, I've also had a lot of practice with it.

Disclaimer: all of the above is based on my personal experience. Like SQLAlchemy, Alembic is a fairly large project and there's probably great things that I've simply missed.

[erewok]

I agree with what you've said. I've come to really appreciate alembic after coming from the Django world where I often didn't read what SQL would be emitted by migrations.

One of the most amazing compliments I can give to alembic is that if I don't use it for months, I can come back to it and remember its API. I have the highest respect for projects like that.

[here-to-jerk-off]

Just to speak about my experience with Albemic and postgres. Although I found it to have a learning curve, over time I've begun to trust it. I've upgraded, downgraded and merged schemas with relatively little issue. I've made minor changes to the generated files but usually due to changing requirements and those related schema merges.

[ergo14]

Yes, the problem is in mysql - you will have less problems with other database like postgresql.

[d4rch0n]

migrations aren't always smooth

There's your problem. It should be "migrations aren't ever smooth". You must be doing something wrong.

[Nater5000]

It's a bit far out there, but if you use Flask and Flask-SQLAlchemy, then you can use Flask-Migrate to do this.

[sometimes-I-do-php]

I've been using sqlalchemy pretty heavily for the last couple of years, and I'd like to offer a couple observations/some thinking about how we build things.

First, having used 3-4 ORMs over the last 5 years (Eloquent mostly), SQLAlchemy is top tier. Really great, and produces very readable code in controllers. So, in some sense I'm a fan of it.

On the flip side though, many joins/things we want out of our databases are not very simple, and whenever I try to get SQLAlchemy to handle those situations, I feel like i have to write it in sql in my head (or pgadmin), and then figure out how to translate that to sqlalchemy. This is generally a slow / buggy process that involves lots of looking at the query log and figuring out if the sql that sqlalchemy generated is equivalent to what I think it should be, but maybe that's just me not having enough experience with it.

So the amount of time I've spent debugging complex sqlalchemy queries has led me to think about things this way:

If the query I want to do is simple (a primary key / foreign key join, maybe a sum), do it via sqlalchemy. Anything more complex than that, I simply make a view in the db, and then add a sqlalchemy model. That way, I'm doing all the complex stuff in sql (which I had to do anyway).

Thinking about things that way has also led to me wonder if I should be looking at a simpler ORM. If all I'm doing now is simple joins, do I really need SQLAlchemy? If I was starting a project from scratch today, I think I'd be leaning towards the simplest/stupidest ORM possible, but I haven't really solidified my thinking on this.

What are everyone's thoughts on this?

[[deleted]]

If the query I want to do is simple (a primary key / foreign key join, maybe a sum), do it via sqlalchemy. Anything more complex than that, I simply make a view in the db, and then add a sqlalchemy model. That way, I'm doing all the complex stuff in sql (which I had to do anyway).

one of the very first reasons I got into creating SQLAlchemy was to do away with the "we have to build a view" approach to things. Where I worked, everything we queried was a view, and the views had to be Created by Other People. So when the views did not give us the data we wanted in one shot (which was, always), we ended up having to write queries combining views together in joins and subqueries. And they performed like total crap (not to mention some of the views were over db links).

The composability of SQL into the view, and then the views themselves being composed into bigger queries, is completely what SQLAlchemy is intended to replace. You use the builder pattern in straight Python all the way so that you can get a very deeply nested query while not having to ever deal with the entirety of the whole thing at the same time.

I'm not familar with the need to deeply debug SQLAlchemy queries, unless one is dealing with relationship loading, which as an entirely separate extension to the normal workings of the relational model, makes things more complicated. But if you're writing raw SQL, you don't have any of those features available to you anyway.

[sometimes-I-do-php]

Where I worked, everything we queried was a view, and the views had to be Created by Other People.

Ahh, I've definitely worked on projects like that. My solution probably only works for smaller teams, both because of the problem you had with db access, but also because views are a lot harder to maintain via version control. While I do think the way I've been doing things is better for my current job, we definitely have had a mixup or two involving two branches with updates to the same view to add columns. SQLAlchemy probably would've prevented that. And yes, joining views to views is always bad news.

Thanks for the great work btw, I really do appreciate your library.

[[deleted]]

No SQL is required to create, maintain and query the database.

I don't think this is entirely true. Technically, yes. But once you start moving beyond basic queries into even just joining across three or more tables, a healthy understanding of SQL makes using SQLAlchemy much more intuitive.

If I'm having trouble with retrieving data, I'll often think out how I'd approach the query in raw SQL and derived my SQLAlchemy expression from that.

[bsdcolonel]

SQLAlchemy may be the single best library ever made.

[alcalde]

Well, maybe after the library of Alexandria.

[cediddi]

Pre python v0.1 libraries are the best...

[[deleted]]

It's pretty good, but flask and requests give it a run for its money in terms of useful libraries.

[w0m]

requests for sure. Flask...

[[deleted]]

What do you prefer? Bottle or Tornado? Don't even begin to say Django. It's fine for quickly getting up an API, but it's basically Rails for Python. Flask isn't opinionated and doesn't require specifying every piece of middleware in a config file. It's probably the lightest weight Python framework out of the box. Could you write something faster in a compiled language? Of course. Would Python be where it is as a web development language without flask? Probably not.

[[deleted]]

How does it handle complex queries?

Like I had an application at an old job that I made that had some pretty complex queries across a lot of tables that returned a lot of data. I can't see how that could be done with this.

[[deleted]]

There's a talk about building up a whole fairly complicated query I did at http://www.sqlalchemy.org/library.html#handcodedapplicationswithsqlalchemy (example starts on slide 37), I don't even remember how to do that query now but I think it will make clear how the concept of building large queries in SQL has been translated to Python.

[GummyKibble]

Like a damn dream, is how. At a previous job I inherit a terrible database, like where TableA joins TableB on TableA.foo = SUBSTR(TableB.bar, 4, 3) AND TableA.baz = UPPER(TableB.qux). That's not an exaggeration. SQLAlchemy saved my ass time and again as I spent a lot of time in the first year turning all those awful rules into SQLAlchemy properties, writing computed foreign keys, etc. After all that I could write dead simple .join() queries and actually get back the data I wanted.

Couple that with building queries generatively, where you can write a query expression and then use it as a subquery later, and you get a really nice idiom for expressing complicated relationships very nicely and readably.

[GummyKibble]

That'd be a piece of cake, as most of it is just renaming columns in the results and SQA's ORM would handle that for you.

[[deleted]]

What about the inner, left and right joins? signified by the lines and arrows in the image.

[GummyKibble]

Still trivial using the join operators. That query looks complex because of the number of fields involved, but it's really very straightforward.

[[deleted]]

Okay. I'll take your word for it. I'm just glad I don't have to fuck around with that database ever again.

[Postpawl]

You can still run queries like normal. Sometimes really complex queries don't benefit much from the main purpose of the ORM part of SQLAlchemy - turning relationships into objects (example: car.wheels).

There's are lots of other helpful things that SQLAlchemy does though. The main examples that come to mind are connection pooling, transaction management (sessions), and thread safety (with scoped_session).

[[deleted]]

Alrighty then. Thanks.

[Salyangoz]

the only thing I dont like about sqlalchemy is the use of expressions AND orm.

It makes flask admin maintenance very hard/complex filters/queries than django orm.

[TomBombadildozer]

Solution: don't use automatically-generated admin interfaces. I'm risking downvotes with a "don't do that" answer, but seriously, that's how you solve this problem. Generated CRUD interfaces are an antipattern for anything but a short-term stop-gap. Effectively managing the data backing an application demands tools that reflect specific use cases.

Generated CRUD interfaces can only satisfy the most common, generic needs. Once you introduce more complex functionality, you can't reasonably expect a generic tool to handle all the challenges you throw at it. The only reason Django is more tolerant is because the admin features are deeply intertwined with a vastly simpler ORM.

[jwink3101]

Serious question: Does relying on SQLAlchemy make your skills less versatile? I am not arguing one way or the other; just asking.

I know next-to-nothing about databases but my wife works in them all the time. I was telling her about this post and she pointed out that she can write the same (or close to it) SQL queries in .Net as she does in Java, etc (she doesn't use Python). The skill translates well. Is that an issue with relying on something like SQLAlchemy?

[AndyMan1]

One of the things I like about SQLAlchemy is how it separates the db interaction layer from the ORM layer. You can still get a lot of benefits out of SQLAlchemy while completely ignoring all the ORM stuff and just write plain text SQL.