Blog

Pro-Tip: Django ORM vs SQLAlchemy in 2021

EBS Integrator
Mar 8, 2021,

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 master.

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

Meet John

Meet John Django Vs SQLA

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

A little bit about me – I am a developer worth 2 years of experience in various projects, proficient in: Django, FastAPI, Flask, Laravel, PostgresSQL, MySQL, MongoDB among other things. Lately, however, I focus 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 about 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 the 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”

but just in case 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 data bases. 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 keeping everything in as close to Python as possible, for your teammates.

Django ORM

Our first contender:
Django ORM logo 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.

Its lightweight, fast, secure, and scalable. And its integrated ORM is equally robust.

SQL Alchemy

In the other corner we have:

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 complementarity-oriented approach; It puts on the fore front what other ORM/tools want to hide. It exposes everything within series of composable tools and takes on the job of automating redundant tasks, giving larger degree of freedom to the developer of how the database is organised and how SQL is generated.

Differences – SQL Alchemy vs Django

Django ORM vs SQL Alchemy 2021

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.

Which is a different story with 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 how our Django ORM model looks like:


from django.db import models  # noqa

class Book(models.Model):
    title = models.CharField(max_length=191)

    class Meta:
        db_table = 'books'

class Course(models.Model):
    title = models.CharField(max_length=191)

    class Meta:
        db_table = 'courses'

class Student(models.Model):
    first_name = models.CharField(max_length=191)
    last_name = models.CharField(max_length=191)
    created_at = models.DateTimeField(auto_now=True)
    courses = models.ManyToManyField(Course, through="StudentCourse")
    register = models.ForeignKey(Book, null=True, on_delete=SET_NULL) # noqa

    class Meta:
        db_table = 'students'

class StudentCourse(models.Model):
    final_grade = models.IntegerField()
    student = models.ForeignKey(Student, on_delete=CASCADE) # noqa
    course = models.ForeignKey(Course, on_delete=CASCADE) # noqa
    book = models.ForeignKey(Book, on_delete=CASCADE) # noqa

    class Meta:
        db_table = 'student_courses'

And here’s our SQLA sample:


import datetime

from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship

from db.base_class import Base


class Book(Base):
    __tablename__ = 'books'  # noqa

    id = Column(Integer, primary_key=True)
    title = Column(String)
    students = relationship("Student")
    owner_course_book = relationship("StudentCourse")


class StudentCourse(Base):
    __tablename__ = 'student_courses'  # noqa

    id = Column(Integer, primary_key=True)
    final_grade = Column(Integer)
    student_id = Column(Integer, ForeignKey('students.id'), primary_key=True)
    course_id = Column(Integer, ForeignKey('courses.id'), primary_key=True)
    book_id = Column(Integer, ForeignKey('books.id'))
    courses = relationship("Course", cascade="all,delete", back_populates="students")
    book = relationship("Book", cascade="all,delete", back_populates="owner_course_book")
    student = relationship("Student", cascade="all,delete", back_populates="student_course")

class Course(Base):
    __tablename__ = 'courses'  # noqa

    id = Column(Integer, primary_key=True)
    title = Column(String)
    students = relationship("StudentCourse", back_populates="courses")

class Student(Base):
    __tablename__ = 'students'  # noqa

    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    student_course = relationship("StudentCourse", back_populates="student")
    register_id = Column(Integer, ForeignKey('books.id'))
    register = relationship("Book", back_populates="students")

And 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 select 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.

# Django ORM
Student.objects.select_related('register')
# sql generated:
SELECT "students"."id",
       "students"."first_name",
       "students"."last_name",
       "students"."created_at",
       "students"."register_id",
       "books"."id",
       "books"."title"
FROM "students"
         LEFT OUTER JOIN "books" ON ("students"."register_id" = "books"."id")
# SQL Alchemy
db.query(Student).options(joinedload(Student.register)).all()
#***********
#OR in model add argument lazy='joined':
#***********
register = relationship(
"Book", lazy='joined', back_populates="students")
# sql generated:
SELECT students.id          	AS students_id,
		students.first_name  	AS students_first_name,
		students.last_name   	AS students_last_name,
		students.created_at  	AS students_created_at,
		students.register_id 	AS students_register_id,
		books_1.id           	AS books_1_id,
		books_1.title        	AS books_1_title
	FROM students
	LEFT OUTER JOIN books AS books_1 ON books_1.id = students.register_id

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

  • lazy = ‘select’ (or True)

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

  • lazy = ‘dynamic’

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

  • lazy = ‘joined’ (orFalse)

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

  • lazy = ‘subquery’
  • 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 relationship.

So, let’s give both contenders a point and call it draw for 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”:

# Django ORM
Student.objects.filter(register=Subquery(
       Book.objects.filter(title__contains='english'
).values('id')))
# sql generated:
SELECT "students"."id",
       "students"."first_name",
       "students"."last_name",
       "students"."created_at",
       "students"."register_id"
FROM "students"
WHERE "students"."register_id" = (SELECT U0."id" FROM "books" U0 WHERE U0."title"::text LIKE 'english')
# SQL Alchemy
db.query(Student).filter(
    Student.register_id == db.query(Book.id).filter(
      Book.title.contains('english')
   ).subquery()
)
# sql generated:
SELECT students.id          AS students_id,
       students.first_name  AS students_first_name,
       students.last_name   AS students_last_name,
       students.created_at  AS students_created_at,
       students.register_id AS students_register_id
FROM students
WHERE students.register_id = (SELECT books.id
	FROM books
	WHERE (books.title LIKE '%%' || 'english' || '%%'))

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

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

# Django ORM
Student.objects.filter(id=Subquery(
      StudentCourse.objects.filter(
      student=OuterRef('id'),final_grade__gte=10
)[:1].values('student')))
# sql generated:
SELECT "students"."id",
       "students"."first_name",
       "students"."last_name",
       "students"."created_at",
       "students"."register_id"
FROM "students"
WHERE "students"."id" = (SELECT U0."student_id"
                         FROM "student_courses" U0
                         WHERE (U0."final_grade" >= 10 AND U0."student_id" = "students"."id")
                         LIMIT 1)
# SQL Alchemy
db.query(Student).filter(
    Student.id == db.query(StudentCourse.student_id).filter(
        StudentCourse.student_id == Student.id, StudentCourse.final_grade >= 10
    ).limit(1).subquery()
)
# sql generated:
SELECT students.id          AS students_id,
       students.first_name  AS students_first_name,
       students.last_name   AS students_last_name,
       students.created_at  AS students_created_at,
       students.register_id AS students_register_id
FROM students
WHERE students.id = (SELECT student_courses.student_id
                     FROM student_courses
                     WHERE student_courses.student_id = students.id
                     AND student_courses.final_grade >= 10
LIMIT 1)

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

# Django ORM
Student.objects.prefetch_related(
    Prefetch('studentcourse_set', queryset=StudentCourse.objects.filter(
        final_grade__gte=10).select_related('course'), to_attr='course_objects'))
# sql generated:
SELECT "students"."id",
       "students"."first_name",
       "students"."last_name",
       "students"."created_at",
       "students"."register_id"
FROM "students"
#*****************
#-- Second sql
#*****************
SELECT "student_courses"."id",
       "student_courses"."final_grade",
       "student_courses"."student_id",
       "student_courses"."course_id",
       "student_courses"."book_id",
       "courses"."id",
       "courses"."title"
FROM "student_courses"
         INNER JOIN "courses" ON ("student_courses"."course_id" = "courses"."id")
WHERE ("student_courses"."final_grade" >= 10 AND "student_courses"."student_id" IN (1))
# SQL Alchemy
db.query(Student).options(
    joinedload(Student.student_course).options(
    joinedload(StudentCourse.courses))
)
# sql generated:
SELECT students.id                   AS students_id,
       students.first_name           AS students_first_name,
       students.last_name            AS students_last_name,
       students.created_at           AS students_created_at,
       students.register_id          AS students_register_id,
       courses_1.id                  AS courses_1_id,
       courses_1.title               AS courses_1_title,
       student_courses_1.id          AS student_courses_1_id,
       student_courses_1.final_grade AS student_courses_1_final_grade,
       student_courses_1.student_id  AS student_courses_1_student_id,
       student_courses_1.course_id   AS student_courses_1_course_id,
       student_courses_1.book_id     AS student_courses_1_book_id
FROM students
         LEFT OUTER JOIN student_courses AS student_courses_1 ON students.id = student_courses_1.student_id
         LEFT OUTER JOIN courses AS courses_1 ON courses_1.id = student_courses_1.course_id

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, more code to accomplish the same task SQLA does it, admirably, in just 1 “select”.

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.

# Django ORM
StudentCourse.objects.annotate(good_students=Count('student', filter=Q(final_grade__gte=10))).filter(
        good_students__gte=3
    )
# sql generated:
SELECT "student_courses"."id",
       "student_courses"."final_grade",
       "student_courses"."student_id",
       "student_courses"."course_id",
       "student_courses"."book_id",
       COUNT("student_courses"."student_id") FILTER (WHERE "student_courses"."final_grade" >= 10) AS "good_students"
FROM "student_courses"
GROUP BY "student_courses"."id"
HAVING COUNT("student_courses"."student_id") FILTER (WHERE ("student_courses"."final_grade" >= 10)) >= 3
# SQL Alchemy
db.query(StudentCourse).filter(db.query(
func.count(StudentCourse.id)).filter(
  StudentCourse.final_grade > 10).as_scalar() > 3)
# sql generated:
SELECT student_courses.id          AS student_courses_id,
       student_courses.final_grade AS student_courses_final_grade,
       student_courses.student_id  AS student_courses_student_id,
       student_courses.course_id   AS student_courses_course_id,
       student_courses.book_id     AS student_courses_book_id
FROM student_courses
WHERE (SELECT count(student_courses.id) AS count_1
       FROM student_courses
       WHERE student_courses.final_grade > 10) > 3

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, no redundancies.

Point to SQLA!

Conclusion

And the winner is = Friendship!

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

Django on 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, end of the day, when it comes to super complex tasks nothing trumps down vanilla SQL.

Farewell

That's all folks DJango vs SQLAlchemy

The gong is ringing and the crowd goes wild! Truly a match for the ages! And even though it ended in a draw, we can all agree we have learned something!

What is your favourite ORM? Do you prefer going plain SQL for all your apps? If so, what is your psychiatrists’ number? Tell us in the comments! We’d sure love to hear your opinion!