miguelgrinberg Flask-Migrate: SQLAlchemy database migrations for Flask applications using Alembic

The job of the ORM is to translate the high-level operations into database commands. With this command, the migration repository will be set up to track migrations on your main database, and on any additional databases defined in the SQLALCHEMY_BINDS configuration option. As I mentioned above, the contents of the migrations directory needs to be under source control. Remember to add the new migration script, which you will find in the migrations/versions directory. In a new project the models are going to be compared against a brand new (empty) database, so the migration script will include all the model definitions.

• Alembic is a lightweight and fast migration engine that supports multiple database backends, making it ideal for use in Flask applications.
• The flask db command provides a convenient way to create, apply, and roll back migrations without manually running Alembic commands.
• The contents of this folder must be created in order to version control along with your other source files.
• These scripts are run in order on a database in order to bring it up to the latest version.

In addition, let’s modify the existing web route to fetch posts from the database. Now when we create a new migration, we can see that a new column was automatically detected, and thus alembic created a migration file for us. In case you use SQLite and run into errors either when downgrading or upgrading your database, you should be aware that SQLite doesn’t have implementation for all operations and commands. I suggest reading this blog post by Miguel Grinberg for more information about SQLite limitations, and how to overcome them.

Project details

As I’m sure you have heard already, Flask does not support databases natively. You can also use Python’s faker library which may be a bit quicker as you don’t need to come up with any data yourself. One way of configuring it would be to put a method in a class that you wanted to generate data for as shown below.

Note that the id fields were automatically set to 1 and 2 when those users were added. This happens because SQLAlchemy configures integer primary key columns to be auto-incrementing. Note that Flask-SQLAlchemy uses a “snake case” naming convention for database tables by default. For the User model above, the corresponding table in the database will be named user. For a AddressAndPhone model class, the table would be named address_and_phone.

Creating The Migration Repository

Finally, you can run the flask db upgrade command, which applies the generated migration script to the database, ensuring that the changes are executed effectively. Each time the database models are altered, repeat the migrate and upgrade commands. The migrate command will create new scripts based on the alterations, and the upgrade command will execute them. In order for all of the following commands to work, the application script must be set in the FLASK_APP environment variable. The migration workflow typically starts with running the flask db init command.

• Finally, you can run the flask db upgrade command, which applies the generated migration script to the database, ensuring that the changes are executed effectively.
• This is not an actual database field, but a high-level view of the relationship between users and posts, and for that reason it isn’t in the database diagram.
• I assign an author to a post using this author field instead of having to deal with user IDs.
• Are you wondering how do all these database operations know what database to use?
• The database session, which above was used to define and commit changes, is also used to execute queries.
• Multiple changes can be accumulated in a session and once all the changes have been registered you can issue a single db.session.commit(), which writes all the changes atomically.

SQLAlchemy uses a so.mapped_column() function call assigned to each column to provide this additional configuration. In the case of id above, the column is configured as the primary key. For string columns many databases require a length to be given, so this is also included. I have included other optional arguments that allow me to indicate which fields are unique and indexed, which is important so that database is consistent and searches are efficient.

Downgrading to an older revision

Since the application does not have any database logic yet, let’s play with the database in the Python interpreter to familiarize with it. Make sure your virtual environment is activated before you start the interpreter. Once a link between a user and a post is established, the database can answer queries about this link.

The most trivial one is when you have a blog post and need to know what user wrote it. If you have a user, you may want to know all the posts that this user wrote. The application is in its infancy at this point, but it does not hurt to discuss what is going to be the database migration strategy going flask developer forward. Imagine that you have your application on your development machine, and also have a copy deployed to a production server that is online and in use. The application configuration is specified in its own file config.py, where our Flask application, database, and Flask-Migrate are configured.

Flask, CSS and Cache walked into a bar…

The project makes use of postgresql and SQLAlchemy form flask_sqlalchemy Modules. While you work on your application, you will need to test things out in a Python shell very often, so having to repeat the above statements every time is going to get tedious. Multiple configuration callbacks can be defined simply by decorating multiple functions. The order in which multiple callbacks are invoked is undetermined.

• In terms of the actual database migrations, everything is handled by Alembic so you get exactly the same functionality.
• Flask-Migrate is an extension that configures Alembic in the proper way to work with your Flask and Flask-SQLAlchemy application.
• To accomplish this seemingly difficult task, Alembic maintains a migration repository, which is a directory in which it stores its migration scripts.
• To apply the migrations to a database, these migration scripts are executed in the sequence they were created.

I will not go deeper into that topic here, since readers of this post use all kinds of databases. My personal recommendation is to use Postgres for Flask applications. Another benefit of using Flask-Migrate is its ability to facilitate collaboration among team members working on the same project. This helps maintain consistency and ensures that everyone can work with the same database schema. The nice thing about flask shell is not only that it pre-imports app, but that you can also configure a “shell context”, which is a list of other symbols to pre-import.