Flask & Alembic: how to migrate models to postgresql

Question

I'm doing a technical test for an interview I'm doing.

It's a flask application running in docker with PostgreSQL and already using SqlAlchemy. Unfortunately I don't have knowledge in flask, only Django.

I have added a new Model to my app and I need to make a migration to update the database, basically what makemigrationsand migrate do in Django.

In my docker app, I have added alembic with pip install alembic then I have execute alembic init migrations which add a new folder migrations with a default config env.py inside.

I have then try to run alembic revision --autogenerate -m "add MySuperModel" but then I got this error: sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:driver

I don't know if I'm on the right track or not.

What I want to do is running python manage.py makemigrations and python manage.py migrate in my flask app which already use SqlAlchemy.

The former is, I will admit, a pain to fit. I can rummage through my repo when I get home and try remember what I did, but it used the `command` features of `alembic` in the end. If you're making the models yourself just for the sake of an interview, I'd definitely just use `flask-sqlachemy` — roganjosh, Feb 25 '23 at 16:45
Fwiw I think the `flask` aspect of this is pretty crucial to the complexity, so I would add it to your question title. The problem is pulling in models under an application context — roganjosh, Feb 25 '23 at 16:51
Any reason why you don't just use flask-sqlalchemy? It will be much easier since there's already a very nice alembic integration available (flask-migrate). — ThiefMaster, Feb 25 '23 at 23:23

roganjosh · Answer 1 · 2023-02-25T19:24:30.340

Preamble

It's actually pretty important to be able to do this. Models developed using flask-sqlalchemy cannot be used outside of flask itself. There is flask-migrate but that requires flask-sqlalchemy be used. There are guides such as this that explain the setup for the database, and even flask itself with SQLAlchemy in Flask to avoid this restriction. When it comes to alembic for this setup, I couldn't find anything. So, this is the best I have atm and would welcome other answers.

Using `command`

This is the best I have got to date using the commands module when pulling in arbitrary SQLAlchemy models for a project called ex_machina with multiple blueprints.

database.py on the top level of the project.

import os

from alembic import command
from alembic.config import Config as AlembicConfig
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from ex_machina.config import Config


engine = create_engine(Config.CONN_STRING)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

meta = MetaData(naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_%(column_0_name)s",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s"
      })

Base = declarative_base(metadata=meta)
Base.query = db_session.query_property()


def _get_alembic_config():
    directory = os.path.dirname(__file__)
    alembic_directory = os.path.join(directory, 'alembic')
    alembic_cfg = AlembicConfig()
    alembic_cfg.set_main_option('sqlalchemy.url', 
                                Config.CONN_STRING)
    alembic_cfg.set_main_option('script_location', alembic_directory)
    return alembic_cfg


def _migrate():
    
    import ex_machina.admin.models
    import ex_machina.auth.models
    import ex_machina.departments.models
    import ex_machina.home.models
    
    alembic_cfg = _get_alembic_config()
    
    command.stamp(alembic_cfg, 'head')
    command.revision(alembic_cfg, autogenerate=True)


def _upgrade():
    alembic_cfg = _get_alembic_config()
    command.upgrade(alembic_cfg, 'head')
    
    
def _downgrade(hash_):
    alembic_cfg = _get_alembic_config()
    command.downgrade(alembic_cfg, hash_)


def init_db():
    import ex_machina.admin.models
    import ex_machina.auth.models
    import ex_machina.departments.models
    import ex_machina.home.models
    Base.metadata.create_all(bind=engine)

alembic.env.py

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
# target_metadata = None
from ex_machina import database
target_metadata = database.Base.metadata

I don't like it but you can wrap a CLI around this proof of concept. I look forward to a better guide.

Flask & Alembic: how to migrate models to postgresql

1 Answers1

Preamble

Using command

Using `command`