Python Flask SQLalchemy Mixins at the end of existing columns

A mixin can add specific fields to another model in Flask,

Example model

# User Class
class User(db.Model):

    __tablename__ = 'User'

    id = db.Column(Integer, primary_key=True)
    username = db.Column(String(32), unique=True)
    email = db.Column(String(32), unique=True)
    password = db.Column(Binary)
    role = db.Column(String(32), default="Member")

    # Define the relationship to Role via UserRoles

    def __init__(self, **kwargs):
        for property, value in kwargs.items():
            # depending on whether value is an iterable or not, we must
            # unpack it's value (when **kwargs is request.form, some values
            # will be a 1-element list)
            if hasattr(value, '__iter__') and not isinstance(value, str):
                # the ,= unpack of a singleton fails PEP8 (travis flake8 test)
                value = value[0]
            if property == 'password':
                value = hashpw(value.encode('utf8'), gensalt())
            setattr(self, property, value)

    def __repr__(self):
        return str(self.username)

This is an ORM approach to creating and then being able to interface with a table called User which will have an id,username,email,password and role column in place.

I also have similar tables for clients and companies.

One nice feature of Laravel and its Eleqoent ORM is that it always adds in an updated_at and created_at column into every table.

This is possible using the concept of a mixin when using SQLalchemy in Python.

a basic mixin might be as follows

class TimeMixin(object):
    #Keep track when records are created and updated.
    created_on = db.Column(DateTime, nullable=False, default=datetime.utcnow)
    updated_on = db.Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)

If i were to then change the main User class to use this by adding it to first line

class User(TimeMixin, db.Model):

Then it would add these two database columns when you use either the command line interface to create_all() or use flask migrate to db init, migrate and upgrade

However it adds these columns to the start of the table and not the end.

To add a new column from a mixin to the end of the table you have to format it slightly differently and add an additional import

add

from sqlalchemy.ext.declarative import declared_attr

to your imports and then format like

class TimeMixin(object):
    @declared_attr
    def created_on(cls):
        return db.Column(
        db.DateTime, nullable=False, default=datetime.utcnow
        )

    @declared_attr
    def updated_on(cls):
        return db.Column(db.DateTime, onupdate=datetime.utcnow, default=datetime.utcnow)

This then adds the new columns to the end of the table.

I am then free to just add TimeMixin to my client and company models and it will create the same columns for me on those tables also allowing me to keep track of db modifications and see when an entry was first created. somehting very useful and that I used extensively in Laravel.

Mixins allow much more functionality than just adding columns feel free to read more about sqlalchemy mixins here https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html