23

I could create tables using the command alembic revision -m 'table_name' and then defining the versions and migrate using alembic upgrade head.

Also, I could create tables in a database by defining a class in models.py (SQLAlchemy).

What is the difference between the two? I'm very confused. Have I messed up the concept?

Also, when I migrate the database using Alembic, why doesn't it form a new class in my models.py? I know the tables have been created because I checked them using a SQLite browser.

I have done all the configurations already. The target for Alembic's database and SQLALCHEMY_DATABASE-URI in config.py are the same .db file.

iled
  • 2,142
  • 3
  • 31
  • 43
chanchal karn
  • 517
  • 2
  • 7
  • 11

2 Answers2

65

Yes, you are thinking about it in the wrong way.

Let's say you don't use Alembic or any other migration framework. In that case you create a new database for your application with the following steps:

  1. Write your model classes
  2. Create and configure a brand new database
  3. Run db.create_all(), which looks at your models and creates the corresponding tables in your database.

So now consider the case of an upgrade. For example, let's say you release version 1.0 of your application and now start working on version 2.0, which requires some changes to your database. How can you achieve that? The limitation here is that db.create_all() does not modify tables, it can only create them from scratch. So it goes like this:

  1. Make the necessary changes to your model classes

  2. Now you have two options to transfer those changes to the database:

    5.1 Destroy the database so that you can run db.create_all() again to get the updated tables, maybe backing up and restoring the data so that you don't lose it. Unfortunately SQLAlchemy does not help with the data, you'll have to use database tools for that.

    5.2 Apply the changes manually, directly to the database. This is error prone, and it would be tedious if the change set is large.

Now consider that you have development and production databases, that means the work needs to be done twice. Also think about how tedious would it be when you have several releases of your application, each with a different database schema and you need to investigate a bug in one of the older releases, for which you need to recreate the database as it was in that release.

See what the problem is when you don't have a migration network?

Using Alembic, you have a little bit of extra work when you start, but it pays off because it simplifies your workflow for your upgrades. The creation phase goes like this:

  1. Write your model classes
  2. Create and configure a brand new database
  3. Generate an initial Alembic migration, either manually or automatically. If you go with automatic migrations, Alembic looks at your models and generates the code that applies those to the database.
  4. Run the upgrade command, which runs the migration script, effectively creating the tables in your database.

Then when you reach the point of doing an upgrade, you do the following:

  1. Make the necessary changes to your model classes
  2. Generate another Alembic migration. If you let Alembic generate this for you, then it compares your models classes against the current schema in your database, and generates the code necessary to make the database match the models.
  3. Run the upgrade command. This applies the changes to the database, without the need to destroy any tables or back up data. You can run this upgrade on all your databases (production, development, etc.).

Important things to consider when using Alembic:

  • The migration scripts become part of your source code, so they need to be committed to source control along with your own files.
  • If you use the automatic migration generation, you always have to review the generated migrations. Alembic is not always able to determine the exact changes, so it is possible that the generated script needs some manual fine tuning.
  • Migration scripts have upgrade and downgrade functions. That means that they not only simplify upgrades, but also downgrades. If you need to sync the database to an old release, the downgrade command does it for you without any additional work on your part!
Miguel Grinberg
  • 65,299
  • 14
  • 133
  • 152
  • Thank you very very much. This pretty much cleared all for me.I still have some question's though. By doing manual migrations you mean that ,after I add new classes to models or modify existing classes in models, i need to make new migration in alembic using "alembic revision -m 'tablename' " . Then in the versions folder i have to edit(define) the newly created .py file to match the model classes that i have just defined? Is it good practice to do it manually? – chanchal karn May 24 '15 at 16:28
  • 3
    Right. Manual migrations require you to write the `upgrade` and `downgrade` functions yourself, without Alembic's help. The auto-migration generation is fairly good, so I prefer to generate a script automatically, then I review it and make any corrections if necessary, obviously before I run the `upgrade`. – Miguel Grinberg May 24 '15 at 16:56
  • @Miguel Thanks for the summary. This helped me understand the general development flow better. One of the upsides of the upgrade, as you point out, is that it preserves data but what happens when I run downgrade and then upgrade to reverse, this will destroy data right? – Usagi Mar 09 '19 at 01:33
  • Yes, a downgrade destroys the data in tables that need to be deleted. If you then upgrade again you get brand new and empty tables. – Miguel Grinberg Mar 09 '19 at 17:58
  • I don't understand the correct workflow to add a new table. If I add it to models.py then when I run the upgrade against an existing database it's not created. If I also manually add it in upgrade() then when I create a new db instance from scratch the upgrade errors out because it already exists in model.py. I need it in model.py so my db session code can reference it. – Robert Sim Dec 20 '22 at 19:26
  • @RobertSim the correct workflow is to write a new model class, then generate a migration that creates this table, and finally upgrade your database so that the migration script runs and creates the new table. If your migration isn't picking up the new table, my guess is that you have a `db.create_all()` call somewhere that is interfering with Flask-Migrate. – Miguel Grinberg Dec 21 '22 at 18:26
  • Thank @MiguelGrinberg. This works for the case where I'm only doing a migration of an existing database. Now if I try to create a new one from scratch, the table is created from the model class via db.create_all(), and then the migration errors out because there's a redundant create_table. Maybe if I auto-generate the migration it will include code that doesn't create the table if it already exists? – Robert Sim Dec 22 '22 at 15:44
  • @RobertSim Remove the create_all(). You should never use create_all() when working with Flask-Migrate or Alembic. These are competing methods of creating the database, you should use one or the other, never both. – Miguel Grinberg Dec 23 '22 at 18:37
0

I can add that for Django there are two commands

makemigrations (which creates migrations files) 
migrate (which translates migrations into sql and executes them on database)

I found its great for somebody's understanding to switch between batteries included framework(Django) and other frameworks like Flask/ Falcon.

So using alembic migrations is very convenient, and makes easy to track database changes.

kpazik
  • 175
  • 2
  • 11