0

In FastAPI the database is generated automatically and it was created based on the models I had in the moment. After the database creation I changed a model's property name from "owner_id" to "user_id" but after re-runing the API it does not upgraded the Database.

How can I trigger the database upgrade in FastAPI (with SQLAlchemy)? Is there a specific command I need to run?

I've tried generating a migration for that and it can work, but I think there is a more easy way that does not rely on a migration from Alembic.

I'd expect a CLI command to trigger the database upgrade after a model change...

Almeida Cavalcante
  • 589
  • 7
  • 10

1 Answers1

1

Upgrading databases automatically is a really bad idea. In a lot of situations it is not clear what/how something was changed. Your example of renaming the column owner_id to user_id is a prime example. There are multiple ways of changing the database to match the new schema:

  1. rename the owner_id column to user_id
  2. or drop the old owner_id column and create a new empty user_id column

And in fact some tools will prefer the second version (including Alembic: What does Autogenerate Detect (and what does it not detect?)), and you would loose the entire data of that column. The best way to change database schemas is by doing database migrations with manual migration checks. Alembic is the typical tool for it. At the beginning initialize it, and whenever something changes do:

  1. alembic revision --autogenerate -m "some message"
  2. manually check the generated revision for errors/completeness and fix/extend it if something is wrong/missing
  3. alembic upgrade head

Warning: this here is dangerous.

If you really want to apply the changes directly, you however can use Alembic also in a programmatically way: by asking it for the changes, and apply them directly. The Alembic documentation has a recipe for such a usecase: Run Alembic Operation Objects Directly (as in from autogenerate). But as I mention above, that you will very likely loose some data using that approach, probably sooner than later.

Btw FastAPI doesn't generate the tables for you. Somewhere in your code you probably have something like Base.metadata.create_all(engine) in it, which is pure SqlAlchemy code.

Jakube
  • 3,353
  • 3
  • 23
  • 40
  • `Base.metadata.create_all(engine)` - That´s right... Just occurred to me that might be another function to check for changes and upgrade the database, but I agree with you that this is dangerous. In this case would be better to remove this line `.create_all(engine)` and rely only on migrations from Alembic, is that right?. – Almeida Cavalcante Mar 25 '23 at 14:22
  • @AlmeidaCavalcante Yes – Jakube Mar 25 '23 at 17:30