Database schema with sqlalchemy

sob 25 kwietnia 2020
Programming
#sqlalchemy, #database design, #orm, #python

I decided to learn fastAPI and a new frontend framework, finally chose Vue.js, which is actually pretty fortunate because there is a fastAPI cookiecutter which generates Vue.js frontend. I decide I won't use that for now because it seemed pretty complex, probably I will use it later on and write here about it as well.

Anyway, my mini-project is about a game Age of Empires 2: Definite Edition. The purpose of the website is to see what's the best counter against a specific civilization or a unit. Users can submit new strategies and other users can then upvote or downvote them.

The first thing I did was designing a database.

Here is a diagram of my db generated with eralchemy:

entity relation diagram

GameEntity is many-to-many-related with itself via a Tip table. GameEntity can be a specific civilization or a specific unit, not a perfect name but I couldn't come up with a better one. To mark it as a civilization, unit, or any other type (like infantry, archer), there is a GameEntityType table.

Tip is a tip/strategy that users can submit for a specific unit vs unit or civilization vs civilization (or even unit vs civilization) match up.

Here is the implementation in SQLAlchemy (I only pasted relevant parts of the classes):

from datetime import datetime
from sqlalchemy import Column, Integer, ForeignKey, String, Text, Table,\
 UniqueConstraint, DateTime, Boolean, Unicode
from sqlalchemy.orm import relationship, Session, validates
from sqlalchemy.ext.declarative import declared_attr, as_declarative
from sqlalchemy_utils import PasswordType, EmailType


@as_declarative()
class Base:
    id = Column(Integer, primary_key=True, index=True)
    active = Column(Boolean, default=True)
    created = Column(DateTime, default=datetime.utcnow)
    updated = Column(DateTime, onupdate=datetime.utcnow)


class User(Base):
    __tablename__ = 'user'
    name = Column(Unicode(length=255))
    email = Column(EmailType, unique=True, index=True)
    password = Column(PasswordType(
        schemes=[
            'pbkdf2_sha512',
        ]),
        unique=False,
        nullable=False,
    )
    votes = relationship('Vote', back_populates='user')


association_table = Table(
    'game_entity_type_assoc',
    Base.metadata,
    Column('game_entity_id', Integer, ForeignKey('gameentity.id')),
    Column('type_id', Integer, ForeignKey('gameentitytype.id'))
)


class GameEntity(Base):
    name = Column(String(length=255), unique=True, index=True)
    types = relationship(
        'GameEntityType',
        secondary='game_entity_type_assoc',
        back_populates="game_entities"
    )


class GameEntityType(Base):
    name = Column(String(length=255), unique=True, index=True)
    game_entities = relationship(
        "GameEntity",
        secondary='game_entity_type_assoc',
        back_populates="types",
    )


class Tip(Base):
    left_entity_id = Column(Integer, ForeignKey('gameentity.id'))
    right_entity_id = Column(Integer, ForeignKey('gameentity.id'))
    left_entity = relationship("GameEntity", foreign_keys=[left_entity_id])
    right_entity = relationship("GameEntity", foreign_keys=[right_entity_id])
    description = Column(Text(length=500), nullable=False)
    votes = relationship('Vote', back_populates="tip")


class Vote(Base):
    tip = relationship("Tip", back_populates='votes')
    tip_id = Column(Integer, ForeignKey('tip.id'), nullable=False)
    user = relationship("User", back_populates='votes')
    user_id = Column(Integer, ForeignKey('user.id'))
    value = Column(Integer, default=1)

    __table_args__ = (
        UniqueConstraint('tip_id', 'user_id', name='_one_vote_per_user'),
    )

One more thing about the db design is that every vote is a separate row, which may lead to ineffective queries when selecting votes count for a particular Tip. For now I leave it be, and will try to find out some way to improve that without denormalizing the db by adding an aggregate field to Tip table. I think I read it in Two Scoops of Django, that you should always start normalized, and denormalize only as a last resort and I will stick to that.

Some maybe more interesting takeaways from the implementation are:

We can use Base class if we want all tables to have some set of fields.
We can use as_declarative decorator from sqlalchemy if we want to have IDE support for our Base fields.
We can use Association Object if we want to store more data in the association table for many-to-many relationship (GameEntity-Tip-GameEntity).
We can use sqlalchemy_utils to ease some common things like keeping hashed passwords in the db, instead of reinventing the wheel and doing it by ourselves.

That would be all for now, next post will be about a fastAPI backend utilizing this schema.