Skip to content

Change Database Schema

If you are a developer and want to add a feature that needs changes to the database schema, this how-to guide will show you the steps to do it.

Whenever you have planned such changes, please be sure to get into contact early as changes to the data model impact the core of the application and hence need a bit of planning. Also the database schema can only be changed in a linear history, hence there cannot be two pull requests with pending migrations present.

ORM and Migrations

Since version 0.39.0, this project uses SQL Alchemy as a database framework. This abstracts away the concrete database such that one could use various backends. At the moment we just SQLite as a default because it is stored in a local file and doesn't need any particular setup. SQL Alchemy provides an object relational mapper such that we can write Python code and it will take care of all the database interactions. There are certain drawbacks with regard to performance and flexibility, but these don't concern us in this project.

Whenever there is a change to the model, existing databases have to be upgraded accordingly. As users could potentially have any previous state of the database, it gets a bit messy. Fortunately Alembic takes care of these migrations.

Concrete example

Somebody wanted to be able to change the colors for the tags. We need to add a color field to the database table of the tags. In the file geo_activity_playground/core/datamodel.py we find the following model for the tags:

class Tag(DB.Model):
    __tablename__ = "tags"
    __table_args__ = (sa.UniqueConstraint("tag", name="tags_tag"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    tag: Mapped[str] = mapped_column(String, unique=True)

    activities: Mapped[list[Activity]] = relationship(
        secondary=activity_tag_association_table, back_populates="tags"
    )

This maps to the database table tags and has a column id as primary key and a column tag with the name of tag. We want to add a color field as a string. We can just add that field to this class Tag:

class Tag(DB.Model):
    __tablename__ = "tags"
    __table_args__ = (sa.UniqueConstraint("tag", name="tags_tag"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    tag: Mapped[str] = mapped_column(String, unique=True)
    color: Mapped[str] = mapped_column(String, nullable=True)

    activities: Mapped[list[Activity]] = relationship(
        secondary=activity_tag_association_table, back_populates="tags"
    )

This changes the model, but it doesn't upgrade the database. We need to run the following:

poetry run alembic revision --autogenerate -m 'Add Tag.color'

This then created the file geo_activity_playground/alembic/versions/0f02b92c4f94_add_tag_color.py. This contains a bit of metadata:

revision: str = "0f02b92c4f94"
down_revision: Union[str, None] = "da2cba03b71d"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

We can see that it has its own revision has and depends on a previous version. This way the system can figure out which migration builds on which ones.

Then it contains an upgrade method, which adds the new column to the table:

def upgrade() -> None:
    with op.batch_alter_table("tags", schema=None) as batch_op:
        batch_op.add_column(sa.Column("color", sa.String(), nullable=True))

And as a nice benefit, we also get a downgrade method which can let the user go back if they would want to do that:

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("tags", schema=None) as batch_op:
        batch_op.drop_column("color")

All you need to do is to add this new file to the git repository and it will become part of the program. When the program is started on any database, it will first check whether that database needs to be migrated and apply the relevant migrations in the right order.

Verifying the database status

The current state of the database is stored in the database itself in a table called alembic_version. We can query that table and see the version:

❯ sqlite3 database.sqlite 
sqlite> SELECT * FROM alembic_version;
0f02b92c4f94

So here the migration was already applied at the time of writing.

Summary

If you want to change the data model, do the following:

  1. Get in touch, let's discuss your plans.
  2. Add the necessary fields in geo_activity_playground/core/datamodel.py or potentially other files if your model has more cohesion with your code.
  3. Run poetry run alembic revision --autogenerate -m 'MESSAGE' with an appropriate message.
  4. Add your changes and the generated migration file to git.