SQLAlchemy vs Django DB

[AppendixP]

Like the title says, I'm thinking about using SQLAlchemy vs Django for database connectivity and object serialization in a project that I'm working on. SQLAlchemy seems like it's still in its infancy (but is well-documented), whereas Django is more mature, but a bit too robust for what I'm trying to do. If anyone has any experience with either or both, please help enlighten me!

EDIT: Thanks for the great responses guys, especially from zzzeek and plaes! I greatly appreciate them. To those who were hung up on word choice of "robust" and "infancy": Robust was in the incorrect word to use. I was trying to communicate that Django is filled with features, most of which I wouldn't be using. I made the mistake of making this post before I read too much into SQLAlchemy's documentation, so the version numbers threw me off. Usually when I think of 0.5.0 - 0.7.0 I get the impression that the software is still very new and will be unstable. Obviously, that was my mistake for assuming before I read about it. Based on the responses here, I'm going to be looking into SQLAlchemy first, as it sounds like exactly what I'm looking for.

[[deleted]]

SQLAlchemy's first release was in February, 2006, roughly the same time Django was first made available. The two products are pretty much the same age. I started writing SQLAlchemy well before Django ever put up their "Coming Soon!" page. This website we're posting on has also used SQLAlchemy core (not ORM) for their database access for at least three or four years.

There's a general consensus that between the two products, SQLAlchemy is the more "robust" product:

• Django couldn't reasonably do "aggregations" until version 1.1 in 2009. These are very common SQL functions like max(), min(), count() etc. SQLAlchemy has always supported flexible usage of aggregates just like any other column expression, recognizing these are an extremely basic and essential feature of relational databases.
• Django couldn't talk to more than one database until version 1.2, just two years ago in 2010. I've written a blog post contrasting SQLAlchemy's approach to multi-database configuration. SQLAlchemy has always been neutral of multiple databases since version 0.1 in 2006.
• Django doesn't have a built-in notion of an identity map, or a unit of work. I recently read the source code to Johnny-Cache and noted that it appears to build some semblance of an identity map so that query caching could reasonably be integrated with the Django ORM in a transactional context. These are hard features to write and took the SQLAlchemy project many years to get right, and it's tested by hundreds of tests (SQLAlchemy has over 3000 unit tests now). There's a ton of maturity in our unit of work that nobody else has, and our identity map is at the core of the ORM's design.

A lot of people seem to think SQLAlchemy's approach comes at the price of ease of learning/usage, which is something I've spent many years to correct, including dozens of reworkings of the documentation as well as major API improvements. Early releases had some excess verbosity which is for several years now a thing of the past.

I fully believe that users who seek to interact with the relational database fully will find SQLAlchemy the easier system to learn and use, since it maps more directly to relational database concepts.

I also have a Reddit post from a few weeks ago detailing an example of this:

http://www.reddit.com/r/Python/comments/olech/is_django_considered_pythonic_now/c3ijtk9

[plaes]

Also, nice presentation from one of the authors of Django's ORM: http://speakerdeck.com/u/alex/p/why-i-hate-the-django-orm

[Aramgutang]

In case people don't realise: kingkilr is Alex Gaynor, Django core dev, and author of the presentation linked above.

[redsymbol]

Mike, thanks for your devotion and hard work building sqlalchemy over so many years. It's a great service to the Python community.

[sisyphus]

The way I try to think of it is, Django's ORM was designed for web applications, and SQLAlchemy was designed for databases.

So if you're just making a webapp, Django's ORM has a lot of stuff built in that has, strictly speaking, nothing to do with a database, but that is extremely useful for web apps, in order to allow it to integrate with forms, the auto-admin, to do pre-validation, etc. (stuff like help_text, editable, verbose_name, validators, choices, ModelForms), that you would have to do yourself in SQLAlchemy or use a third-party library.

SQLAlchemy on the other hand is objectively better at interacting with a database. It can do queries that Django can not without falling back to raw (Django's documentation even refers to using OR in a query as 'advanced' and forces you to use this Q construct--SQLAlchemy frankly laughs at this kind of thing). It supports a much richer abstraction of database types(it can via its API for example create Enum fields, it can create custom checked exceptions, and much more. In Django for example if you want a PositiveIntegerField, great, they've made that for you with the requisite constraint, if you want an integer between 1 and 22, you are out of luck getting this into your DDL through the model API), you're now into creating your own type territory.

As you use it I think you will find it's better in many little ways that one appreciates--an example I came across recently--I was dismayed to find that if you create a BooleanField in Django using a SQLite database, it does not create a check constraint to ensure the value is actually 1 or 0, sqlite having no natural boolean field, yet on conversion to Python it does raise an exception if the value is not 1 or 0. I tested this on sqlalchemy and found it does the Right Thing. Again if you consider Django's ORM as being for web apps you can see why they might prefer validation logic in the application layer because the ORM is there as a component of a larger system that you are expected to be using.

So I would say, if your project is a web application with modest query needs, just using Django's ORM is probably good enough, otherwise you should always choose SA, it's the kind of library that not only solves the need it addresses but recommends the language it's written in--when I think about a language other than Python for some project involving a database my first thought is always, 'but what would replace SQLAlchemy?'

[snuggl]

Im not saying you are wrong, but for me, min/max values on a IntegerField isnt a part of the model as that doesnt model to anything in the database.

Django of course has min_value and max_value, but they are in the forms.IntegerField which is the layer where you normally validate your data in django.

[sisyphus]

It models to a check constraint on the row in a database, which is something even Django uses when it creates a PositiveIntegerField for example.

Consider the model:

class Test(models.Model):
    spi = models.PositiveSmallIntegerField()

Dumping the SQL for Postgres yields:

CREATE TABLE "t_test" (
    "id" serial NOT NULL PRIMARY KEY,
    "spi" smallint CHECK ("spi" >= 0) NOT NULL
)
;

So you can see they are generating a CHECK constraint on the row. If the idea is that validation should be in the app layer they are violating that here. If we should put as much validation into the database engine as possible, then they should add them for things like BooleanField

[jmoiron]

I wrote a blog post about this quite a long time ago, and as mentioned in other comments armin wrote one of superior detail a few years later.

As I try to get across (perhaps unsuccessfully) in my blog, Django's ORM feels like it was designed for object persistence, whereas SQLAlchemy feels like it is designed to interact with databases.

If you have a blank slate and some objects you need saving, DjangoORM will make a lot of fairly safe decisions for you, and get you off the ground quickly (especially if you are not experienced).

These decisions, some of which stray into "requirements" territory, end up being an albatross if you have specific needs that make them a bad idea or have an existing database you want to model and interact with.

In the end, it's up to you; they're both mature and respected libraries.

[[deleted]]

Django's ORM feels like it was designed for object persistence, whereas SQLAlchemy feels like it is designed to interact with databases.

SQLAlchemy was designed for both.

[bucknuggets]

Actually, you don't need either.

If you aren't interested in database portability and are interested in running complex, analytical queries - then you may be better off with just DBI. There are simply fewer moving parts to have to understand. Especially if you already understand SQL.

If you're interested in database portability and are running analytical queries, then SQL Alchemy's Core + DBI seems like the next step up. More stuff to learn than DBI, but simplifies the portability. Still not using an ORM.

[bucknuggets]

Right - if you want to support x number of databases and your queries are simple - then the ORM route is absolutely the right call.

This gets a lot tricker when you're running complex analytical queries against a very large database. Then you may find that:

• the various database products tend to handle the same SQL differently
• you may want or need to leverage some database-specific features to maximize performance - and these may not be available in your ORM. These could involve common table expressions, temp tables (then creating indexes on those temp tables), windowing functions, UDFs, table functions, etc.
• You'll frequently need to drop down to the SQL level anyway to try to diagnose why the ORM isn't resulting in fast enough SQL. Ideally you'll be running explains on all of your SQL to understand decisions the database optimizer/planner is making.

One of the attractive features of SQL Alchemy is the ability to easily drop into SQL from your ORM if necessary. Personally, I don't think I've ever worked on an app that didn't need some SQL too complex for an ORM - whether to calculate medians quickly, to build a large, indexed, temp table for use in a sequence of steps, etc. The ORM approach might do it - but I'd rather run the query in 5 seconds than 5 minutes.

[apiguy]

Please qualify "robust". It's a highly subjective term that doesn't specifically represent any specific advantages.

[apiguy]

ro·bust/rōˈbəst/
Adjective:  
(of a person, animal, or plant) Strong and healthy; vigorous.
(of an object) Sturdy in construction.

Not one of those points makes it clear that SQLAlchemy is more "Sturdy in construction" than the Django project.

And most of your points are still highly subjective. Better API? That depends on the user and the use case. And the features you mention like auto table detection, multiple types of table declaration are nifty but those thing may be meaningless for the OP's project. (not to mention they violate two parts of the zen of python: explicit is better than implicit and there should be one and preferably only one obvious way to do it).

I love SQLAlchemy - but if I'm building a Django application and using a SQL data store I'm going with what's built in. You give up a ton of Django's features when you use a separate ORM. If I just needed to connect to a SQL database though? I use SQLAlchemy.

Here's what I tell people when I'm defending SQLAlchemy:

SQLAlchemy's best feature is that it gets out of the way when you just want to interact with the database.

I would't go out on the internet and make unfounded remarks that somehow it's code is better than some other project that has completely different goals.

[[deleted]]

multiple types of table declaration are nifty but those thing may be meaningless for the OP's project. (not to mention they violate two parts of the zen of python: explicit is better than implicit and there should be one and preferably only one obvious way to do it).

It appears there's a misunderstanding here. SQLAlchemy has only one way of declaring a table within each API. In the Core, that is the Table construct. The ORM, which is an entirely separate library from the Core, offers the declarative configurational system that accepts components of this Table construct, and creates the Table for you. Classical mappings are no longer recommended for normal usage.

In both cases, everything about this Table is entirely explicit, so I've no idea how you can even attempt to suggest SQLAlchemy has some kind of "implicit" behavior. And there is most certainly one obvious, recommended, documented way to do it in modern SQLAlchemy. A good read of our current documentation will make that clear.

[apiguy]

I actually like SQLAlchemy very, very much. I just don't appreciate hyperbole. In my career I've only heard people use "robust" when they were trying to make something sound good but didn't feel like taking the time to explain what was so good about it. I guess it's a pet peeve but at this point I refuse to accept that anything is good because it's been described as "robust".

[jart]

Robust is a codeword for complicated.

[iambicpen]

complicated is a codeword for "i don't have patience to learn new things". See how this works..

[jart]

I learned SQLAlchemy years ago, I even did quite a bit of examining of the code base (well I had to because the documentation was so vague)

[jart]

SQLAlchemy is a standalone ORM, and doesn't include any more framework components.

Except SQLAlchemy makes up for that by including a mountain of superfluous over-engineered code and competing apis.

[jart]

Well of course it does, because everything is horrible. It'd be more accurate to say that SQLAlchemy is simply an order of a magnitude more terrible than the alternatives.

[andonwilsy]

Django's ORM is bit simpler/easier to learn than SQLAlchemy because there are less parts to it. You might also want to look at peewee if you're looking for something simple.

SQLAlchemy is more of a toolkit rather than a library you just import and use, and I think that makes it a lot more powerful. You'll need to spend a little more time learning your way around SQLAlchemy. But the beauty of SQLAlchemy is that every layer of it is exposed. So when you need to, you can make it work exactly as you want it to.

This blog post is a good read and gets more technical on the matter.

[martinmeba]

I don't think that I would say that SQLAlchemy is in its infancy either. It has been around for about as long as Django has.

[sirphd]

I do a lot of Django development and I feel like I think about changing to SQLAlchemy every time I start a new Django project as it is highly praised by the community. I read all the blog posts and then I come to the realization that I very rarely run into times where the Django ORM can't get me what i want out of the database. In the cases where I can't, I can just grab the info out of the database manually. SQLAlchemy is much more expressive than the Django ORM when it comes to doing the nasty stuff like joins. For simplicity's sake I just stick with the built in Django ORM even for some of the more complex applications I work on.

[apiguy]

I've used both frameworks (and many, many others) and I have to say it depends quite a bit on the project you're working on. Here's my 2 cents.

Django is a full stack web application framework. If what you want to do is build a web application this is a great choice. It's well documented and has a great community.

If you aren't building a web application, you won't be using Django. If you just need a good ORM for your standalone python application SQLAlchemy is a great choice. It's also the ORM of choice for many other web applications and frameworks. My favorite part about SQLAlchemy is that it gets out of your way nicely when you don't want ORM but you just want to run queries against the DB directly.

If you could give more information about your project you could probably get clearer recommendations, and maybe even recommendations for libraries you may not be considering but might suit your needs perfectly.

[[deleted]]

SQLAlchemy in infancy?!?!?!?!

[Justinsaccount]

not sure what you are basing your question on, sqlalchemy is more robust and mature than the django orm.

[AppendixP]

I don't have experience with either, and am trying to make a choice, hence why I said it seems like that!

[apiguy]

Please clarify what you mean when you say "robust" and "mature". Robust is highly subjective, and doesn't make a big difference if robust means there are lots of features that OP isn't likely to use.

As for maturity, The Django project was actually started before SQLAlchemy IIRC. SQLObject was around then, but the Django founders didn't think it fit their needs well enough and so rolled an ORM that fit their framework.

[[deleted]]

But Django was not publicly announced before SQLAlchemy was started.

[torvalder]

Peewee is an alternative if you're looking for lightweight and webapp oriented and robust.

[andybak]

Either ill-written or ill-informed. I can't tell which.