How to Setup ForeignKeys, Relationships, Serialize Rules and SerializerMixin When Using Flask

Flask is a great resource to use when you are working with databases. It can help your backend integrate seamlessly with frontend web applications with straightforward and familiar syntax. However, one aspect that can be confusing to new users of flask is setting up your table models to work effectively.

To set up your models you will need to get to learn how to write the code for all of your tables to interact. This is where foreignkeys, relationships and serialize rules are utilized, though they can be a little bit of a mystery a first glance. In this article we explore how to use all of these and what exactly they do.

1. ForeignKeys - These are the first step in creating tables that interact with each other. By assigning a foreignkey with an id that corresponds to your other table it will connect them so that you can get the data for both tables in a simple straightforward way. In example below you can see that we have to classes with tables. The user_id in the Post class will help us point to the user table when we need data that is shared in a relationship.

class User(db.Model, SerializerMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String)

class Post(db.Model, SerializerMixin):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    created_at = db.Column(db.DateTime, server_default=db.func.now())
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

As you can see we have a foreignkey that will be created in our posts table which will reference the data in our users table. Be sure to make sure your reference the name of the table when setting this up so your program works properly. It is especially important to take your time when setting up your models because syntax can be confusing and errors not easy to catch.

1. Relationships - Once our foreignkey is set we need to create a relationship between them. We do this in the class opposite the foreignkey, in this case the User class. In our example we want to use the the class name first, add cascade parameters that will delete the related data like the user_id when we delete a table entry. Backref is the class we are in but is lowercase so make sure your understand the syntax.

class User(db.Model, SerializerMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String)

    posts = db.relationship('Post', cascade='all, delete', backref='user')

class Post(db.Model, SerializerMixin):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    created_at = db.Column(db.DateTime, server_default=db.func.now())
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

1. - Serialize Rules - We are done creating a relationship but we might want to limit the output of data that we display that we show to just a few items. We can use serialize rules to do this. Be aware that this is where the most errors are made because of the syntax necessary to make it work, so pay attention and take your time.

class User(db.Model, SerializerMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String)

    posts = db.relationship('Post', cascade='all, delete', backref='user')

class Post(db.Model, SerializerMixin):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    created_at = db.Column(db.DateTime, server_default=db.func.now())
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    serialize_rules = ('-posts.user_id', '-posts.created_at',)

In the example here we are limiting the amount of info in the response to our database, subtracting the user_id and created_at data in our post class. If you happen to get recursion errors when you set this up it is most likely that you forgot a comma in your serialize rules. Serialize rules require a tuple which each item must end in a comma and if it doesn't will throw errors or just not subtract your data field from the output.

1. SerializerMixin - You may have noticed this in the code and wonder what it does. SerializerMixin allows you to exclude output but in a much more modular way. Serialize rules will keep the excluded output from leaving your model classes but SerializerMixin allows you to exclude data fields when you make your data requests.

If we have an app.py file which handles CRUD (create, read, update, delete) functions we might want to exclude fields here instead of having to write custom dictionaries which will be necessary if we use serialize rules. So how does it work? SerializerMixin just allow us to do the same thing as serialize rules but in our actual request. First, make sure it is installed by using this command in your terminal.

pip install SQLAlchemy-serializer

Once installed and imported into your models file you can use it to allow you to edit your output when you request them. Be aware that you will throw attribute errors if you forget the comma because once again we need to provide a tuple in our rules. Because we are using SerializerMixin we can make rules for each output not limiting our options. In the example app.py file below we don't want to show our user_id and created_at fields in our get function but lets say we want to see our created_at in our post function we will set it up just excluding user_id. As your can see this allow much more flexibility in what our responses will display.

class Posts(Resource):
    def get(self):
        posts = [post.to_dict(rules=('-posts.user_id', '-posts.created_at',)) for post in Post.query.all()]
        return make_response(posts, 200)

    def post(self):
        post = Posts()
        data = request.get_json()

        try:

            for attr in data:
                setattr(post, attr, data[attr])

            db.session.add(post)
            db.session.commit()
            return make_response(post.to_dict(rules=('-posts.user_id',), 201)
        except ValueError:
            return make_response({ "errors": ["validation errors"] }, 400)

Notice that we excluded the same data when we setup our serialize rules so we don't need to use serialize rules if we don't want to. One note though, if you have relationships that are more complex you will still have to use some serialize rules or your will run into recursion errors.

Just like that we have learned to tackle the complexity of ForeignKeys, Relationships, Serialize Rules and SerializerMixin. Refer to this guide if you need a refresher or have syntax questions. It's not that difficult once you know what everything is doing and how the components interact.