SQLAlchemy: How to add column to existing table?

Question

I am trying to add a field or column to an existing table using SQLAchemy.

Below is the table class

class ReleaseVersion(Base):

    __tablename__ = 'versions'

    id = Column(Integer, primary_key=True, autoincrement=True)
    release = Column(String(128), nullable=False, unique=True)


    def __init__(self,release, id=None):

        if(id):
            self.id = id

        self.release = release

I initialized the table using following line

myDB.ReleaseVersion.__table__.create(bind=self.engine, checkfirst=True)

After using the DB for some time, I need to add a boolean field, 'is_currentversion' while keeping all the existing content of the table, but I am not so sure how to do this.

Should I manually create the field to the table an update the class? Alternatively, add the field in the table class and add column if it does not exist in the initialization function?

Solution 1

TL;DR

Install flask-migrate with pip or in your requirements.txt, then once you add your new column in your models:

flask db init
flask db migrate -m "Adding column x."
flask db upgrade

Flask-Migrate

What you’re looking for is called a database migration. Using something like Flask-Migrate allows you to change the database schema - adding or deleting columns - without losing your data. It also versions these database migrations so you can revert back if necessary.

Flask-Migrate uses the Alembic module so they both have the same functionality, Flask-Migrate is used to correctly setup alembic with your Flask and SQL-Alchemy application.

If your app is pre-production:

Here is a great video by Pretty Printed for setting up Flask-Migrate. Note that Flask-Migrate is intended for Flask applications that are using SQL-Alchemy as the ORM.

If your app is post-production:

You have two options.

1. If you only want to track database migrations going forward (easier option)

Run flask db init, to create the migration repository. Add the new column to your database model.

Run flask db migrate, to generate a migration. The migration script will only have the new column in it.

Run flask db upgrade to apply the new migration to your database.

At this point your database should have the new column, and you can continue working. Repeat the above steps any time you need to make additional changes.

Note that with this approach, you cannot recreate the entire database from scratch. You have to have a way to initialize the database to the schema you had on Day 1, and then you can apply the migration history to that to upgrade it to your current schema.

2. If you want to keep track of the entire migration history, including the schema on the day you are adding Flask-Migrate to your application.

Start with flask db init, to create the migration repository. Next, you need to trick Flask-Migrate into thinking your database is empty. You can do this by renaming your actual db, and creating a new db with the same name that has no tables in it.

In that state, run flask db migrate. This will generate a migration that contains the entire schema of your database. Once you have that initial migration, restore your database to the correct state.

Run flask db stamp head to mark the database as updated. Add the new column to your database model.

Run flask db migrate again, to generate a second migration. The migration script will only have the new column in it.

Run flask db upgrade to apply the new migration to your database.

Solution 2

You can simply add a column in your model and then run the following commands.

flask db migrate -m "Your Message."

Then Run:

flask db upgrade

Now check your database. Also, you can take reference from Here

Comments

[Kay]

Hi polymath, before I saw your answer, I tried adding the new column in the table definition class, and in the initialization function, I wrote code to check if the field exist in the database. If it does not exist, it adds the field to the table in the database with default value to make sure the table definition class matches with the database. It works fine. Now, I am wondering why migration would be better option in this case. I understand making work and doing things in proper way are two different things. I really want to learn. Thanks for your help.

[polymath]

In my opinion, database migrations are very important for a few reasons. Firstly, they standardise changes across databases (ie your development database, your production database etc) which reduces the risk of errors. Secondly, it provides versioning of your database so you can revert back if you do make mistakes (which we all do). And finally it can be automated so it's easier for you to update next time.