Creating your first model

Now we have a working website in place. It’s time to start to add some content there.

In this tutorial chapter, we’ll create a model for polling. The model describes what elements a poll contains.

Adding a model

Models are added to a models.py file, that the cookiecutter-websauna-app cookiecutter template generated for you. Websauna uses SQLAlchemy for modelling.

Open models.py and add:

from uuid import uuid4

from sqlalchemy import Column, String, Integer, ForeignKey
from websauna.system.model.columns import UUID
from sqlalchemy.orm import relationship

from websauna.system.model.meta import Base
from websauna.system.model.columns import UTCDateTime


class Question(Base):

    #: The table in the database
    __tablename__ = "question"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: Question text
    question_text = Column(String(256), default=None)

    #: When this question was published
    published_at = Column(UTCDateTime, default=None)

    #: Relationship mapping between question and choice.
    #: Each choice can have only question.
    #: Deleting question deletes its choices.
    choices = relationship("Choice",
                           back_populates="question",
                           lazy="dynamic",
                           cascade="all, delete-orphan",
                           single_parent=True)


class Choice(Base):

    #: The table in the database
    __tablename__ = "choice"

    #: Database primary key for the row (running counter)
    id = Column(Integer, autoincrement=True, primary_key=True)

    #: Publicly exposed non-guessable id
    uuid = Column(UUID(as_uuid=True), default=uuid4)

    #: What the user sees for this choice
    choice_text = Column(String(256), default=None)

    #: How many times this choice has been voted
    votes = Column(Integer, default=0)

    #: Which question this choice is part of
    question_id = Column(Integer, ForeignKey('question.id'))
    question = relationship("Question", back_populates="choices")

Some notes:

• websauna.system.model.meta.Base is the default base class for model

• We define an UUID column for randomly generated ids. We never expose the database primary key id to the world, as it might give hints for malicious actors targetting your website.

• We store time in the database explicitly using UTC timezone. This will save you from many time ambiguity issues.

Creating a migration

Having Python code in place is not enough for a new or changed model. You need to inform your database to create the corresponding structure. This is called migration.

If you try to start your development server in this point the sanity check feature aborts the start up:

ws-pserve ws://company/application/conf/development.ini --reload

...
Model <class 'myapp.models.Question'> declares table question which does not exist in database Engine(postgresql://localhost/myapp_dev)
...
websauna.system.SanityCheckFailed: The database sanity check failed. Check log for details.
...

To create a migration script for your application, run the following command in your application folder:

ws-alembic -c company/application/conf/development.ini revision --auto -m "Added choices and questions"

... a lot of output ...
.. All done

Note

Thumbs up! PostgreSQL migrations are transactional. The whole migration succeeds or not. Not all databases do have this kind of safety, thus making migration a risky operation.

You need to do this every time columns change. After the migration script is created on your local computer, you can re-use it across different computers where the database is installed (staging server, production server).

Now migrate your local database:

ws-alembic -c company/application/conf/development.ini upgrade head

... a lot of output ...
.. All done

Exploring tables

Like shown earlier, you can run the ws-db-shell command to see, that new tables appeared in the database.

More information

See models documentation.