How to setup Flask-Migrate with the correct database reference?

Question

I am building a flask app and am trying to set up database migration using Flask-Migrate. I have gotten it working kind of, but have a weird issue of the app/migrate not looking in the same place for the .db file and need to know how to how to get them to look in the same place.

My app works perfectly and I am not getting any errors. The Flask-Migrate works perfectly and I am not getting errors. This is the simplified layout of my app currently:

app.py
app/
|- __init__.py
|- config.py

When I run 'flask run' it thinks the database is here:

app.py
app/
|- __init__.py
|- config.py
|- app.db  <--(In the app folder)

But when I run 'flask db upgrade' it thinks that the database is here:

app.py
app.db  <--(In the main folder)
app/
|- __init__.py
|- config.py

I have the SQLALCHEMY_DATABASE_URI set to 'sqlite:///app.db' and I can actually get them both to work properly if I constantly change the SQLALCHEMY_DATABASE_URI between 'sqlite:///app.db' and 'sqlite:///../app.db' between running the two commands.

app.py file

from app import create_app

app = create_app()

if __name__ == '__main__':
    app.run(debug=True)

simplified __init__.py file:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from app.config import Config
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(Config)

    db.init_app(app)
    migrate.init_app(app, db)

    from app.routes import main
    app.register_blueprint(main)

    return app

simplified config.py file

import os

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY')
    SQLALCHEMY_DATABASE_URI = os.environ.get('SQLALCHEMY_DATABASE_URI')

I would expect flask run and flask db upgrade to see the .db file in the same place, but they do not. Instead they see them in the child folder or parent folder respectively.

Miguel Miguel · Accepted Answer · 2019-08-05T14:41:05

The problem is that you are using a URL to your SQLite database, so any time the application changes the current working directory the location of your database will be calculated again relative to the new current directory.

The solution is simple, use an absolute URL. This is done when you set the SQLALCHEMY_DATABASE_URI environment variable, the value should be something like this:

export SQLALCHEMY_DATABASE_URI="sqlite:////home/yourname/yourapp/app.db"

Note that this is sqlite: followed by four slashes, not three. The first two slashes are the separator between the URL scheme and the rest of the URL. The 3rd slash separates the host/port from the path (host and port are empty on SQLite URLs). The 4th slash is the start of the absolute path to your database file.

How to setup Flask-Migrate with the correct database reference?

2 Answers