This post's content
What's an ORM?
Before going into the difference between Python's ORM frameworks (Django and SQLAlchemy), let's make sure we fully understand the use of ORM frameworks in general.
ORM stands for Object Relational Mapping. Looking at each of these words will explain their use in the real world:
- Object - This part represents the objects and programming language where the framework is used, for example Python.
- Relational - This part represents the RDBMS database you're using (Relational Database Manager System). There are numerous popular relational databases out there, but you're probably using on of the following - MSSQL, MySQL, Oracle Database, PostgreSQL, MariaDB, PerconaDB, TokuDB. What's common between most relational databases is their relational structures (tables, columns, keys, constraints, etc.).
- Mapping - This final part represents the bridge and connection between the two previous parts, the objects and the database tables.
So the conclusion is that the ORM is here to connect between the programming language to the database, in order to simplify the process of creating an application that relies on data.
Comparing Django vs SQLAlchemy
Active Record vs Data Mapper
Django ORM uses the active record implementation - you'll see this implementation in most ORMs. Basically what it means is that each row in the database is directly mapped to an object in the code and vice versa. ORM frameworks such as Django won't require predefining the schema to use the properties in the code. You just use them, as the framework can 'understand' the structure by looking at the database schema. Also, you can just save the record to the database, as it's mapped to a specific row in the table.
SQLAlchemy uses the Data Mapper implementation - When using this kind of implementation, there is a separation between the database structure and the objects structure (they are not 1:1 as in the Active Record implementation). In most cases, you'll have to use another persistence layer to keep interact with the database (for example, to save the object). So you can't just call the save() method as you can when using the Active Record implementation (which is a con) but on the other hand, you code doesn't have to know the entire relational structure in the database to function, as there is no direct relationship between the code and the database.
So which of them wins this battle? None. It depends on what you're trying to accomplish. It's my believe that if your application is a mostly a CRUD (Create, Read, Update, Delete) application which no hard and complex rules to apply on the relationships between the different data entities, you should use the Active Record implementation (Django). It will allow you to easily and quickly set up an MVP for your product, without any hassle. If you have a lot of "business rules" and restrictions in your applications, you might be better with the Data Mapper model, as it won't tie you up and force you to think strictly as Active Record does.
Working with Complex Queries
In some cases, Django and SQLAlchemy can be used together. The main use case I got to see numerous times in the real world is when Django is used for all regular CRUD operations, while SQLAlchemy is used for the more complex queries, usually read-only queries.
For some more information and an example on this aspect, look into BetterWorks engineering blog (we have no affiliation with them what so ever, we just liked their blog post :)).
Primary Key Automatic Generation
Another difference between the two frameworks is that Django can create primary keys automatically for your tables. SQLAlchemy won't do that for you. You'll have to manually create them for each table yourself. It's both a pro and a con - who do you think knows best which primary key will be most suited for your table? It's up to you to decide, according to your team's knowledge and experience.
Autocommit
Django has autocommit on by default, while SQLAlchemy doesn't. It will impact the way you use the framework (transactions, rollbacks, etc.).
Supported Databases
Both Django and SQLAlchemy can be used with MySQL, PostgreSQL, Oracle and SQLite. If you're using MSSQL, you should go for SQLAlchemy, as it's fully supported by it and you'll find more information and documentation about it.
Learning Curve
It's a common opinion over the web that Django will be a lot easier to learn. Well, it's probably obvious, as it's usually used for less complex use cases. So, you should think how much you're willing to invest in learning the framework, to receive more flexibility in exchange with SQLAlchemy (in case you really need it).
Community Size
Without any doubt, SQLAlchemy has the largest community among Python ORM frameworks. If community is important to you (and I think it should be), SQL Alchemy should be your choice. It doesn't mean though that you won't find any help for other frameworks such as Django. You'll get bug fixes, answers for questions in StackOverflow and any other help you'll need, but your chances are just higher with SQLAlchemy.
Performance
I think it will be irresponsible of me to just write (X is faster than Y) here. It will be very hard to get to that conclusion with ORMs, as they have such a variety of features and capabilities, and they are different in each framework. From my experience, the way you use the framework's features will have a large impact on the overall performance of your application's database layer. So my suggestion here is not to choose a framework by its performance, but to learn how to use the framework properly to get the most out of it.
In case you're running raw SQL queries within an ORM framework, using Jooq or just not using ORM for some of your queries, you can also look into EverSQL Query Optimizer which is probably the easiest way to optimize any query.
Summary
As in any comparison, I think it's better to leave the decision making to you, the readers. Each use case is different, and a different technology can be better suited. Look into the differences specified above and let us know which decision you made.
