Databases & ORMs

#

Storing information in databases is a core part of web applications. As a micro-framework, Flask does not come bundled with a solution for databases which offers us the freedom to choose the tool that is best suited for the application we are building.

This is in contrast to larger frameworks like Django where the framework comes with its own built in ORM and it's difficult to bring your own that's best suited for your use case.

Many software as a service applications are best served by a relational database. A relational database allows us to efficiently model mappings, such as a user having many purchases or a store having many products. Commonly used relational databases include MySQL and Postgres.

Using an ORM

#

ORMs (Object Relational Mapper) provides us with an interface to interact with the database as if the records were Python objects.

For example, a record in the Users table would be an instance of the User class and fields of the record (like name could be accessed like a property of the object user.name)

ORMs also spare us the trouble of having to write SQL queries by hand and provide methods to generate and run queries. Instead of having to write a query like this

select * from users where id=30

we can write something more "Pythonic" using the User class thanks to an ORM.

users = User.query.filter_by(id=30)

This interface also allows the ORM to manage the details of figuring out how to format a query for a specific type of database (which can be useful if you use a different type of database locally and in production)

SQLAlchemy

#

SQLAlchemy is the ORM of choice for Flask applications that use relational databases. The code base is mature and has a broad range of support for databases. If you are coming from the world of Django or other ORMs, Armin Ronacher (the creator of Flask) has written about how SQLAlchemy takes a different approach, but why ultimately it is the ORM of choice for most Flask developers.

"SQLAlchemy in general just has a much larger featureset and it's the only ORM for Python which allows you to take full advantage of your database and does not stand in your way. It exposes all features of your underlying database if you want and can be heavily fine tuned. ... [The] simple cases [are] not where SQLAlchemy shines. It's the more complex situations which you can't do at all in Django that work nicely in SQLAlchemy.

As you learn SQLAlchemy, you might wonder why it's a little more verbose or require a bit more code than other ORMs you have seen, generally speaking it's usually because of a design choice to require explicitly specifying the configuration or actions you'd like to perform on the database. The Zen of Python, which summarizes the guiding principles of the Python programming language, encourages this with the second line Explicit is better than implicit.

Configuring SQLAlchemy

#

The most important setting for SQLAlchemy is the one that specifies how find the database and how to connect to it. We can specify the connection details by adding the database connection URI to the Flask configuration as SQLALCHEMY_DATABASE_URI.

If we were connecting to a MySQL database that was listening on our local machine, the connection url would look something like this

mysql://user:password@localhost:5432/dev_db

For our application, we'll be using a simple local database called sqlite in the development environment.

To point SQLAlchemy to use SQLite, we need to give it a reference to a file to use.

In our config.py code, we should point SQLite to the a file for each of the dev and test database. We want to set up a different database for the test environment because when we reset our database to run tests from scratch, we don't want to reset our development data as well.