What is the difference between creating db tables using alembic and defining models in SQLAlchemy?

Question

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.

Answer 1

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:

4. Make the necessary changes to your model classes

5. 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:

5. Make the necessary changes to your model classes
6. 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.
7. 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!