Django ORM vs SQL Alchemy

The world of Data Sciences is an ever-changing place, new applications and requirements appear on a daily basis. With all that, a professional SQL guru who can optimize their interactions with databases is valued in his weight in gold. Luckily for us, we have just such a master.

In this post, we hope to explore the world of ORMs (particularly Django ORM vs SQL Alchemy) with our Python specialist and get his opinion on which he prefers! And provide some nifty examples to boot.

Hello, my name is John

Hello World! Pleased to make your acquaintance; Friends call me Ion, Ivan, and Vanea but you, stranger, can call me “Mr. John” 😀

A little bit about me – I am a developer with 2 years of experience in various projects, proficient in Django, FastAPI, Flask, Laravel, PostgreSQL, MySQL, MongoDB among other things. Lately, however, I have focused most of my workflow on Django with my wonderful team, here at EBS Integrator.

So, there I am, day by day, perfecting my craft with Django Rest framework and I am super satisfied with my choice, but my curiosity got the best of me and I decided to try “FastAPI” with SQL Alchemy. And boy oh boy am I glad I did; because I liked it, A LOT, and certainly will make sure to make the best of my discovery in future projects.

For this article, I decided to share my experience through “show and tell comparison” and make a simple database that contains tables with different object relationships. I will show examples of how SQL Alchemy and Django ORM generate SQL with the same end goal, in different ways.

Choose your “ORM”

Here is a short summary of things we will discuss...

Object Relational Mapper (aka ORM)

In our case, an ORM is a tool, or better said an interpreter, used by developers to work with SQL and in turn, interact with databases. Allowing you to pull/request – query – data from the database in your preferred programming language, in our case it’s Python. This doesn’t make vanilla SQL obsolete, in some cases with sufficiently complex application structures and requirements, you absolutely must write in your own SQL sequences.

In all other instances, I suggest you use an ORM. A happy developer is a developer who knows what’s going on in his code. And using an ORM in your preferred language is one way to do it. Next, It hides the SQL or any other database query away from application logic.

Finally, it helps in maintenance, sometimes you simply don’t have enough SQL specialists around, so it helps keep everything in as close to Python as possible, for your teammates.

Django ORM

Django ORM is a part of a bigger Python framework called, you guessed it, Django. Built by experienced professionals, and designed for rapid development and clean design. Django takes away the hassle of web development, allowing you to focus on writing your app from the get-go.

It's lightweight, fast, secure, and scalable. And its integrated ORM is equally robust.

SQL Alchemy

SQL Alchemy (aka SQLA) is a dedicated Python SQL toolkit with an optional dedicated ORM that gives your app the full benefit and flexibility of SQL for your Python code.

Designed with a complementarity-oriented approach; It puts on the forefront what other ORM/tools want to hide. It exposes everything within a series of composable tools and takes on the job of automating redundant tasks, giving a larger degree of freedom to the developer of how the database is organised and how SQL is generated.

SQL Alchemy vs Django: How are they different?

The main difference is that Django ORM uses the “active record implementation”, and SQL Alchemy uses “data mapper implementation”.

​​​​​​​

It means that Django ORM cannot use our models for queries if every row is not linked with the overall model object. This allows for more readability and allows not predefining the schema to use the properties in the code. Also, you can call “just save()” as all properties are mapped to a specific row in the table.

This is a different story from SQL Alchemy because there is a separation between the database structure and the object structure.

Consequentially, this separation makes it a little harder to save records in the DB but enables more flexibility, for there is no direct relationship between the code and the database.

Yet another difference between these two frameworks is that Django can create primary keys automatically for your tables.

Whereas SQL Alchemy won’t do that. You will have to manually create them for each table. It’s both a pro and a con. When it comes to handling “relationship” Django ORM lacks in versatility compared to SQLA.

But I will show that in practice with our further examples.

5 Examples – Django vs SQLA

For our bout. Let us create a data model, both in SQL Alchemy and Django ORM.

As a use-case, we will consider the most basic process that happens in a university: evaluating the academic performance of a student. Of course, we are taking a barebone model here that allows us to register all the students for a course, compute their grades for different courses, and pull relevant information in regards to their achievements.

We’ll make use of “one to many” and “many to many” relationship queries to illustrate in what instances Django and SQL Alchemy differ and give my subjective opinion on who does it better.

Here’s what our Django ORM model looks like:

​​​​​​​

And here’s our SQLA sample:

Right off the bat, you can see the difference in syntax, and ultimately that’s the whole gist of this piece. But let us go round by round.

Round 1: A simple selection of students’ table of contents

Our first example will fall under the “one to many” relationship. Simply put, it will screen all registered students and the “Title” with his register.

In this case, I personally think that Django is more approachable/readable but SQL Alchemy gives us more opportunities such as “lazy=’joined’”.

# all options available here

1. lazy = ‘select’ (or True)

lazy = ‘select’ is the default setting. It emits a SELECT statement when loading.

1. lazy = ‘dynamic’

When querying with lazy = ‘dynamic’, a separate query gets generated for the related object.

1. lazy = ‘joined’ (orFalse)

lazy = ‘joined’ literally joins the two tables and returns the results.

1. lazy = ‘subquery’

​​​​​​​

1. lazy = ‘subquery’ is the same as ‘joined’. **

** Except that subquery uses subquery directly in model property in case it needs to be loaded every time when “model is query”.

In Django’s case, you can do this only by overriding the model object manager.

The original code might be more appealing in Django but if we look at the “generated SQL” we notice that SQLA does a better job by providing more information regarding object relationships.

So, let’s give both contenders a point and call it a draw for the first round!

Round 2: Let us find a student that goes into the register for “English”.

Yep, we are exploiting another “one-to-many” relationship here to screen how many students took up the “English course”:

There is not much to say here, aside from the fact that Django is not as verbose hence I prefer it in this case, so the point goes to Django!

Round 3: Let’s look for students who got at least one grade equal to 10, regardless of the course

​​​​​​​

Same as the example before, except we’re exploiting a “many to many” relationship, but the point stands that Django simply is more understandable and straightforward.

Point to Django.

Round 4: Now, let’s select all the students, and for each, screen the courses where that student got a 10

​​​​​​​

In this example, we can see that SQL Alchemy is better, and what is surprising – more elegant when it comes to retrieving SQL Data for “many to many” relationships.

Django generates two “selects” for one attribute, requiring more performance, and more code to accomplish the same task SQLA does it, admirably, in just 1 “select”.

The point goes to SQL Alchemy.

Round 5: Identifying the “high rollers” in academic achievements

Here we will pull students on a requirement of at least 3 courses with a grade gte=10.

​​​​​​​

​​​​​​​

First, notice how Django creates redundant statements for the same result, we get the COUNT line twice and it just looks messy.

SQL Alchemy’s generated sequence, looks great and readable, with no redundancies.

Point to SQLA!

And the winner is = Friendship!

Most of the time SQL Alchemy is more functional and flexible with what you want, allowing greater precision, when working with databases. And can even generate some queries that Django cannot.

Django on the other hand, looks more readable and prettier, and it’s a massive pro when it comes to us, Pythonists! However, it seems that is not always as you might have noticed in this bout.

I like both ORMs and I predict I will be using them throughout different projects and even both for specific circumstances, and I advise you to do the same!

But, at the end of the day, when it comes to super complex tasks nothing trumps down vanilla SQL.