Should I use SQLAlchemy or psycopg2?

4 minute read

Introduction

Take a look at this tech stack:

  • Python 3
  • Flask
  • PostgresSQL
  • psycopg2
  • SQLAlchemy
  • Flask-SQLAlchemy

SQLAlchemy is the foundation for building web applications in Python. The goal of this post is introduce what SQLAlchemy is?, when should use it?, what benefit does it provide over using low-level libraries like psycopg2? what are the different layers in SQLAlchemy?

Meet SQLAlchemy

SQLAlchemy is the most popular open-source library for working with relational databases from Python. It is quite popular in developing database-backed web applications.

On the SQLAlchemy docs, it describes itself as a Python SQL toolkit. In other words, it provides end-to-end tools where you can work with relational databases without writing SQL directly, as you would have to do with psycopg2.

Another thing about SQLAlchemy is that it offers a type of library called an ORM, which is really popular for interacting with databases from a web app. An ORM is otherwise known as a Object Relational Mapping library. The important thing about an ORM, is it allows us to map database tables and columns to class objects and attributes. So when you use an ORM and you interact with a database with it, you no longer work in tables and columns, instead you only work using your knowledge of OOP.

SQLAlchemy ORM vs SQL

Let’s take a simple example of creating a todos table. We would write the CREATE TABLE statement like this:

CREATE TABLE todos(
    id INTEGER PRIMARY KEY,
    description VARCHAR NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT false
);

Here’s how it would look like if you were to use SQLAlchemy ORM, so here, instead of writing the CREATE TABLE sql statement we would write the following python class.

class Todo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String(), nullable=True)
    completed = db.Column(db.Boolean, nullable=False, default=False)

To write the exact create table statement for creating the Todo table, we can instead instantiate a Todo class. It inherits db.Model which comes from SQLAlchemy ORM, which does a lot of things under the hood for actually creating the table for you.

As a result of creating this class Todo, we can do certain things with it which would mirror operations that we would do in a relational database.

So, for example, to do a typical SELECT * FROM todos we would instead interact with the class Todo, like this Todo.query.all(). This class Todo would then have an object called query that has a method all(). This would do the same thing as select all of the records/objects that belong to the class Todo.

The cool thing about this Todo.query.all() is that no matter what database system that you connect SQLAlchemy ORM to, it still executes.

Are ORMs a ‘best practice’?

Using an ORM to interact with your database is simply one of many valid approaches for how you can add additional layers of abstraction to your web application to interact with a database more easily. There are other kinds of query builder libraries you can use that are between talking to a database directly (with a database driver library like pyscopg2), and using an ORM. An ORM is considered to be the highest level of abstraction you can add to a web application for database management. Query Builder libraries are somewhere in the middle. There are many mixed opinions about whether ORMs are a best practice approach.

Thankfully, SQLAlchemy happens to offer multiple levels of abstraction you can prefer, between the database driver and the ORM, so you can customize the development of your web application to your own liking. The many granular levels of abstraction that SQLAlchemy offers, is one of the reasons that has led to its widespread popularity and diverse ways of using the library and approaching your database programming.

Some features

  • Features function-based query construction: allows SQL clauses to be built via Python functions and expressions.

  • Avoid writing raw SQL. It generates SQL and Python code for you to access tables, which leads to less database-related overhead in terms of the volume of code you need to write overall to interact with your models.

  • Moreover, you can avoid sending SQL to the database on every call. The SQLAlchemy ORM library features automatic caching, caching collections and references between objects once initially loaded.

SQLAlchemy’s Layers of Abstraction

So, we have seen that there are many reasons to use SQLAlchemy over a low-level library like psycopg2 to interact with the database.

SQLALchemy lets you traverse through all 3 layers of abstraction to interact with your database.

  • Can stay on the ORM level
  • Can dive into database operations to run customized SQL code specific to the database, on the Expressions level.
  • Can write raw SQL to execute, when needed, on the Engine level.
  • Can more simply use psycopg2 in this case

Good Design Practice (Opinion): Here’s my opinion on interacting with databases using good design practice.

  • Keep your code Pythonic. Work in classes and objects as much as possible.
  • Makes switching to a different backend easy in the feature.
  • Avoid writing raw SQL until absolutely necessary

Layers of SQLAlchemy:

  • DBAPI
  • The Dialect
  • The Connection Pool
  • The Engine
  • SQL Expressions
  • SQLAlchemy ORM (optional)

In the next post, I will cover Dialect, Connection Pool and Engine layers in depth.