aerich cannot generate migrations

[b-rowan]

Attempting to generate DB migrations for #132 fails to find any difference. I am starting to wonder if aerich is even worth using, or if there are any better options. Looking at the github page, it is not in a good state, the top issue currently is users wondering if the project is even being maintained anymore.

Would like to start discussion here about other possible options or workarounds for this.

[b-rowan]

I think there are 2 main options here if we decide to move away from tortoise-ORM due to lack of migration support.

1. Piccolo - https://piccolo-orm.readthedocs.io/en/latest/piccolo/getting_started/index.html
2. SQLAlchemy - https://docs.sqlalchemy.org/en/20/index.html

I am more familiar with SQLAlchemy personally, but piccolo also looks good (with the added benefit of built in migrations). My only concern with SQLAlchemy is I am very unfamiliar with the async capabilities of it.

[easybe]

The following works for me:

git switch master
git clean -f goosebit/db/migrations/models
rm -f db.sqlite3
poetry run aerich upgrade

git switch dev_rauc_support
poetry run aerich migrate --name add_image_format
Success migrate 1_20240911082808_add_image_format.py

cat goosebit/db/migrations/models/1_20240911082808_add_image_format.py
from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        ALTER TABLE "software" ADD "image_format" SMALLINT NOT NULL  DEFAULT 0 /* SWU: 0\nRAUC: 1 */;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        ALTER TABLE "software" DROP COLUMN "image_format";"""

aerich seems fine to me. I actually like Tortoise ORM because I like the Django ORM 🤷‍♂️

[tsagadar]

@easybe was two minutes faster than I was... The following Aerich issue hinted for this approach: tortoise/aerich#270

Annoying - but not sure if other frameworks are that much better. I'd propose to gain some more experience with this first.

[easybe]

@easybe was two minutes faster than I was... The following Aerich issue hinted for this approach: tortoise/aerich#270

Annoying - but not sure if other frameworks are that much better. I'd propose to gain some more experience with this first.

To me this is completely natural 🤷‍♂️

[b-rowan]

aerich seems fine to me. I actually like Tortoise ORM because I like the Django ORM 🤷‍♂️

I also really like Tortoise ORM, but the apparent lack of maintenance or fixes on Aerich makes it a bit annoying to do the DB migrations. At least this works, maybe should add a bit to the documentation about it.

[b-rowan]

Re-opening this to bring discussion to a couple other options I've come across recently. It is still relatively early with this project, so swapping ORM's now is much easier than later.

Option 1: SQLModel
Pros:

• Written by the creator of FastAPI, so support directly with FastAPI is very good.
• Similar API to tortoise, with a few small differences.
• SQLAlchemy under the hood, so could be used with alembic, which is much more stable and supported.
• FastAPI has a template for using this as part of a stack - https://github.com/fastapi/full-stack-fastapi-template
• Very good type checking, uses pydantic, and will allow for a lot of flexibility directly in models.
  Cons:
• No async documentation

Option 2: ormar
Pros:

• Designed for FastAPI
• SQLAlchemy under the hood, so could be used with alembic, which is much more stable and supported.
• Similar API to tortoise, with a few small differences.
• Very good type checking, uses pydantic, and will allow for a lot of flexibility directly in models.
• Decent documentation, designed with async in mind.

Overall I think both of these might be better options than tortoiseORM, and I lean towards SQLModel. If we decide we want to look into either of these, I could create a branch illustrating the differences.

[easybe]

Overall I think both of these might be better options than tortoiseORM, and I lean towards SQLModel. If we decide we want to look into either of these, I could create a branch illustrating the differences.

I only had a quick glance, but, SQLModel seems like a good option to me. Switching to something that has a wider user base, is based on proven technology, and fits nicely into the FastAPI ecosystems sounds reasonable to me.