Step-by-Step Guide to Preparing a perfect database with SQLAlchemy and Alembic

A Quick and Handy Tutorial on How to Set up a Database with SQLAlchemy and Alembic

A while back as I was going through many pages of great content on database-related codes in an attempt to help a friend fix a bug, I realized there was not (at least in my opinion) a quick and thorough guide to set up a database system with SQLAlchemy and alembic. This is particularly important if you’re working with Flask and Django and other web frameworks, or if you need to set up a database system in general. For instance, in Flask, the great flask_db provides a nice wrapper over SQLAlchemy and Alembic allowing you to easily do database migration and upgrades.

Anyways, I decided to write a quick blog post on this, and I hope that you find it helpful.

What is SQLAlchemy?

Please read the Readme.md from the official SQLAlchemy github repository to learn about it.

What is Alembic?

Check the github repository of alembic package, as the official Readme.md file goes over this through a nicely written introduction.

Learn by Example: Postgres

First, note that SQLAlchemy allows you to work with any type of SQL database without the need to change your codes.

As an example, set up a Postgres database in your system (e.g., for mac, this tutorial gives you a good idea of how to install it).

You can do psql postgres and go to the Postgres terminal, and create a database using create database mydb; (don’t forget the semicolon, and also the mydb part is your custom name). You can connect to this database using \c mydb myuser where myuser is the name of a role that has the permissions that you need (for more information about the roles and how to create or modify one, please refer to this link).

Having a Postgres database ready, please follow the notebook that I created for this tutorial, which is available at https://github.com/shayanfazeli/sqlalchemy_and_alembic_tutorial/blob/master/tutorial.ipynb.

I hope you find it helpful.

Thank you!