Creating one to many relationship in Flask-SQLAlchemy

In this post, I want to make clear how to set up a one-to-many relationship in Flask SQLAlchemy. Properly placing the foreign keys and references to the parent tables might be confusing especially at the beginning. At least for me, it was. Also because there are actually multiple ways to do it.

So here is how it works. First of course I need some boilerplate code to instantiate the flask app and set up a database. I will use SQLite for simplicity, but it would work the same with other relational databases.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///onetomany.db'
db = SQLAlchemy(app)
db.init_app(app) 

This is just an initial setup. Now I need to create classes representing database tables.

In one-to-many relationship, we have one object of some type that holds references to many other objects of a different type. For me, it was somehow confusing to remember which should be one and which should be many, but it is actually quite straightforward.

The key idea in one-to-many is, that we have a Parrent and a Child. Just like in real life, one parent can have multiple children, and many children belong to one parent. This is the main idea that helped me to remember it very well. 

If I have to set up the tables for a store that has many products, I know that the store is the parent and the products are children.

Or an album and songs. Album has many songs. Song belongs to an album. Album is a parent, a song is a child. 

Or a user and comments. A user has many comments. Comment belongs to a user. The user is a parent, the comment is a child.

With this pattern in mind, it is very to go about and set it up. So now let's write it in the code. I will stick with the album/songs example because it is really easy to understand.

class AlbumModel(db.Model):
    """ Datamodel for album. Album has many songs."""

    __tablename__ = "Albums"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64))
    artist = db.Column(db.String(64))
    genre = db.Column(db.String(64))
    songs = db.relationship("SongModel")  # this is to link to the "Song" table


class SongModel(db.Model):
    """ Datamodel for song. Song belongs to one album."""

    __tablename__ = "Songs"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64))
    album = db.relationship("AlbumModel") # this is to link to the "Album" table
    album_id = db.Column(db.Integer, db.ForeignKey("albums.id"))  # referencing "Album" table

Notice a few things.  Each of the models has a db.relationship() which is how the link is done. The song model (the child) has a column storing id of the album, so that we know to which album it belongs. Also, notice that the db.ForeignKey("Albums.id") refers to the name of the table - not the name of the class! 

And that is all. Now we have linked the two tables in one-to-many relationship.

Now I can create some albums and songs and check it out.

db.create_all()

the_wall = AlbumModel(title="The Wall", artist="Pink Floyd", genre="Rock")
fugazi = AlbumModel(title="Fugazi", artist="Marillion", genre="Rock")

db.session.add(the_wall)
db.session.add(fugazi)
db.session.commit()

s1 = SongModel(title="Hey you!", album_id=1)
s2 = SongModel(title="Goodbye cruel world..", album_id=1)

s3 = SongModel(title="Assassing", album_id=2)
s4 = SongModel(title="Incubus", album_id=2)
s5 = SongModel(title="Emerald lies", album_id=2)

I created two albums and five songs, three of the songs belong to Fugazi album from Marillion, two of the songs belong to The Wall. When I type:

print(the_wall.songs)

it will list the songs that belong to The Wall.

If on the other hand I have a song and want to know which album it is on, it can do

print(s4.album.title)

and get Fugazi.

If I would not have the object, first I need to query against it

song = SongModel.query.filter_by(title="Incubus").first()
print(song.album.artist)

And I get Marrillion