Skip to content

Development: Database Changes

This document is open to improvement; please share any insights you have/develop.

Overview

When modifying the database, you will most likely need to change the files under /mealie/db/models/. How exactly you need to modify it is of course highly contextual to the change you're making.

Using Alembic to generate upgrade script

In your dev container you can run something like (change the message) task py:migrate "Add creation tag to group preferences" to have Alembic generate an upgrade script for you.

The script Alembic generates, will be limited! (Perhaps there's a way to resolve that? Haven't looked into it yet) For example, Alembic generated a script similar to this (it has been modified already to have accurate foreign key names, for instance):

"""Add creation tag to group preferences

Revision ID: 0ea6eb8eaa44
Revises: ba1e4a6cfe99
Create Date: 2024-01-04 12:40:03.062671

"""
import sqlalchemy as sa

import mealie.db.migration_types
from alembic import op

# revision identifiers, used by Alembic.
revision = "0ea6eb8eaa44"
down_revision = "ba1e4a6cfe99"
branch_labels = None
depends_on = None


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "group_preferences", sa.Column("recipe_creation_tag", mealie.db.migration_types.GUID(), nullable=True)
    )
    op.create_foreign_key("fk_groupprefs_tags", "group_preferences", "tags", ["recipe_creation_tag"], ["id"])
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint("fk_groupprefs_tags", "group_preferences", type_="foreignkey")
    op.drop_column("group_preferences", "recipe_creation_tag")
    ### end Alembic commands ###

But when trying to actually use that upgrade script, it becomes clear that our SQLite database doesn't like them. The minor modification needed looks like:

"""Add creation tag to group preferences

Revision ID: 0ea6eb8eaa44
Revises: ba1e4a6cfe99
Create Date: 2024-01-04 12:40:03.062671

"""
import sqlalchemy as sa

import mealie.db.migration_types
from alembic import op

# revision identifiers, used by Alembic.
revision = "0ea6eb8eaa44"
down_revision = "ba1e4a6cfe99"
branch_labels = None
depends_on = None


def upgrade():
    with op.batch_alter_table("group_preferences", schema=None) as batch_op:
        batch_op.add_column(sa.Column("recipe_creation_tag", mealie.db.migration_types.GUID(), nullable=True))
        batch_op.create_foreign_key("fk_groupprefs_tags", "tags", ["recipe_creation_tag"], ["id"])


def downgrade():
    with op.batch_alter_table("group_preferences", schema=None) as batch_op:
        batch_op.drop_constraint("fk_groupprefs_tags", type_="foreignkey")
        batch_op.drop_column("recipe_creation_tag")