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

Question

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

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

What is the difference between the two? I am very confused. Am I messing 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 that tables were created because I checked them using SQLite browser.

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

Answer 1

Yes, you do not think so.

Say you are not using Alembic or any other migration structure. In this case, you create a new database for your application with the following steps:

• Write Model Classes
• Create and configure a new database
• Run db.create_all() , which scans your models and creates the appropriate tables in your database.

So, consider the update case. For example, let's say you released version 1.0 of your application and now start working with version 2.0, which requires some changes in your database. How can you achieve this? The limitation here is that db.create_all() does not alter the tables, it can only create them from scratch. So this happens as follows:

4. Make the necessary changes to the model classes.

5. You now have two options for transferring these changes to the database:

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

   5.2. Make changes manually, directly to the database. This is a mistake, and it would be tiring if the set of changes is large.

Now think that you have development and production databases, which means that the work needs to be done twice. Also think about how tedious it would be when you have several editions of your application, each of which has a different database schema, and you need to investigate the error in one of the old versions, for which you need to recreate the database, as it were in release.

See what is the problem when you do not have a migration network?

Using Alembic, you have a little extra work when you start, but it pays off because it simplifies your workflow for your updates. The creation phase is performed as follows:

• Write Model Classes
• Create and configure a new database
• Generate initial Alembic migration manually or automatically. If you migrate with automatic migration, Alembic scans your models and generates code that applies them to the database.
• Run the upgrade command, which starts the script migration, effectively creating tables in your database.

Then, when you reach the point where the update is complete, follow these steps:

5. Make the necessary changes to the model classes.
6. Generate another Alembic migration. If you let Alembic generate this for you, then it compares your model classes with the current schema in your database and generates the code needed to make the database fit the models.
7. Run the upgrade command. This applies to changes to the database without the need to destroy tables or data backups. You can run this update in all of your databases (production, development, etc.).

Important things to consider when using Alembic:

• Migration scripts become part of your source code, so they must be tied to the source control along with your own files.
• If you use automatic generation generation, you always need to look at the generated migrations. Alembic cannot always determine the exact changes, so it is possible that the generated script needs to be manually configured manually.
• Migration scripts have upgrade and downgrade functions. This means that they not only simplify modernization, but also lower it. If you need to synchronize the database with the old version, the downgrade command will do this for you without any additional work on your part!

Answer 2

I can add that there are two commands for Django

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

I found this to be great for someone who wants to switch between batteries powered by the chassis (Django) and other frames like Flask / Falcon.

Thus, using alembic migration is very convenient and makes it easy to track database changes.