SQLAlchemy generic relationship mixin

Question

I'm working on a SQLAlchemy defining a bunch of mixin classes that applications should be able to import and extend their model.

When looking at the documentation, mixin classes are create knowing the final table name however, in the case of a generic library, the final table name that will be used by the application is not known.

Take the following mixin classes:

import sqlalchemy as sa

class UserMixin(object):
    id = sa.Column(sa.Integer(), primary_key=True)
    first_name = sa.Column(sa.Unicode(255))
    last_name  = sa.Column(sa.Unicode(255))

class ItemMixin(object):
    id = sa.Column(sa.Integer(), primary_key=True)
    name = sa.Column(sa.Unicode(255))
    short_description = sa.Column(sa.Unicode(255))

class OrdersMixin(object):
    id = sa.Column(sa.Integer(), primary_key=True)
    user_id = sa.Column(sa.Integer(), sa.ForeignKey('???'))
    item_id = sa.Column(sa.Integer(), sa.ForeignKey('???'))

Then an application defining its models:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyUser(UserMixin, Base):
    __tablename__ = 'myuser'

class MyItem(ItemMixin, Base):
    __tablename__ = 'myitem'
    total = sa.Column(sa.Integer())

class MyOrders(OrdersMixin, Base):
    __tablename__ = 'myorders'

I have two issues with this model:

1. Except from redefining the relationship columns in the extending models, how can the mixin class build the relationship on its own.
2. Type of the foreign key is assumed by the mixin class, but the id of the table may come from the application itself or from another mixin class.

Is the model I'm trying to implement correct? What would be the right way to tackle this problem?

Answer 1

A Mixin is a class that copies its data into a table, but for SQL it matters if you're the owner of that data (the table) vs being a reference (the foreign key).

It looks like you're attempting to create Mixins that are both sources of truth and references. Which isn't possible in SQL.

Taking your example one step further and defining OrdersMixin like this will make the issues more obvious I think.

class OrdersMixin(UserMixin, ItemMixin):
    id = sa.Column(sa.Integer(), primary_key=True)

For example MyOrders would end up like this once things are resolved.

class MyOrders(Base):
    __tablename__ = 'myorders'
    # This is from UserMixin
    id = sa.Column(sa.Integer(), primary_key=True)
    first_name = sa.Column(sa.Unicode(255))
    last_name  = sa.Column(sa.Unicode(255))

    # This is from ItemMixin
    id = sa.Column(sa.Integer(), primary_key=True)
    name = sa.Column(sa.Unicode(255))
    short_description = sa.Column(sa.Unicode(255))

    # From OrdersMixin
    id = sa.Column(sa.Integer(), primary_key=True)  # This is defined last so it overrides all others with the same name.
    user_id = sa.Column(sa.Integer(), sa.ForeignKey('???'))
    item_id = sa.Column(sa.Integer(), sa.ForeignKey('???'))

With how you have the Mixin defined any table that used that Mixin would have primary_keys for the id column, which would conflict. Additionally you are duplicating every column in the Mixin, which in general you want to avoid in SQL (see Database normal form).

The final result would be something like this. Which is a whole bunch of columns meaning you wouldn't need to refer to any other tables and all of the references id you had were overwritten, meaning you wouldn't be able to join them anyway.

class MyOrders(Base):
    __tablename__ = 'myorders'
    first_name = sa.Column(sa.Unicode(255))
    last_name  = sa.Column(sa.Unicode(255))
    name = sa.Column(sa.Unicode(255))
    short_description = sa.Column(sa.Unicode(255))
    id = sa.Column(sa.Integer(), primary_key=True)  # This is defined last so it overrides all others with the same name.
    user_id = sa.Column(sa.Integer(), sa.ForeignKey('???'))
    item_id = sa.Column(sa.Integer(), sa.ForeignKey('???'))

To avoid that I keep my Mixins separate from initial table definition. I.e. I use a Mixin for when I want another table to refer to that table.

The following is close to what I think you were hoping to achieve.

import sqlalchemy as sa
from sqlalchemy import orm

class UserMixin(object):
    user_id = sa.Column(sa.Integer(), ForeignKey("myuser.id"), index=True)
    user = orm.relationship("MyUser")

class ItemMixin(object):
    item_id = sa.Column(sa.Integer(), ForeignKey("myitem.id"), index=True)
    item = orm.relationship("MyItem")

class OrdersMixin(UserMixin, ItemMixin):
    order_id = sa.Column(sa.Integer(), sa.ForeignKey('myorders.id'))
    user_id = sa.Column(sa.Integer(), sa.ForeignKey('myorders.user_id'))
    item_id = sa.Column(sa.Integer(), sa.ForeignKey('myorders.item_id'))

Note in the Mixins I gave every column a unique name so that there aren't conflicts and in OrdersMixin even though I'm using UserMixin and ItemMixin I'm overriding the user_id and item_id columns because otherwise anything using the OrdersMixin would have foreign keys pointing to three different tables which would confuse the automatic query builder. But it will still add the user and item relations (and since they are defined as foreign keys to the original tables in MyOrders table I think the relationship will just work).

Then I would change your tables to look like this.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyUser(Base):
    __tablename__ = "myuser"
    id = sa.Column(sa.Integer(),primary_key=True)
    first_name = sa.Column(sa.Unicode(255))
    last_name  = sa.Column(sa.Unicode(255))

class MyItem(Base):
    __tablename__ = "myitem"
    id = sa.Column(sa.Integer(),primary_key=True)
    name = sa.Column(sa.Unicode(255))
    short_description = sa.Column(sa.Unicode(255))

class MyOrders(Base, UserMixin, OrdersMixin):
    __tablename__ = "myorders"
    id = sa.Column(sa.Integer(),primary_key=True)

The original table definition owns the columns (source of truth) defining them individually and Mixins (of this kind) are good to define references so subsequent references don't need define each of them individually. A Mixin can't be defined to be both a reference and a source of truth. In light of that instead of overriding the column each time like OrdersMixin it's better to just define it once canonically (the table) and once as a reference (the Mixin).