using Flask-Migrate together with Flask-Security

Question

I'm trying to get a basic Flask-Security app working with Flask-Migrate. I have two main py files: app.py and db_migrate.py

app.py:

from flask import Flask, render_template, request, session
from flask.ext.babel import Babel
from flask.ext.mail import Mail
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.security import Security, SQLAlchemyUserDatastore, UserMixin, RoleMixin    
import os
basedir = os.path.abspath(os.path.dirname(__file__)) #should be __ file __ with no spaces

# Create app
app = Flask(__name__)
app.config['DEBUG'] = True
app.config['SECRET_KEY'] = 'super-secret'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'app.db')
app.config['DEFAULT_MAIL_SENDER'] = 'info@site.com'
app.config['SECURITY_REGISTERABLE'] = True
app.config['SECURITY_CONFIRMABLE'] = True
app.config['SECURITY_RECOVERABLE'] = True
app.config.from_object('config.email')

# Setup mail extension
mail = Mail(app)

# Setup babel
babel = Babel(app)

@babel.localeselector
def get_locale():
    override = request.args.get('lang')

    if override:
        session['lang'] = override

    rv = session.get('lang', 'en')
    return rv

# Create database connection object
db = SQLAlchemy(app)

# Setup Flask-Security
from db_manager import User, Role #THIS IS PROBABLY WRONG!

user_datastore = SQLAlchemyUserDatastore(db, User, Role)
security = Security(app, user_datastore)

#db.create_all()

# Views
@app.route('/')
def home():
    return render_template('index.html')

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

db_migrate.py:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.security import Security, SQLAlchemyUserDatastore, UserMixin, RoleMixin

import os
basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'app.db')

db = SQLAlchemy(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

# Define models
roles_users = db.Table('roles_users',
        db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
        db.Column('role_id', db.Integer(), db.ForeignKey('role.id')))

class Role(db.Model, RoleMixin):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(255))

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    password = db.Column(db.String(255))
    active = db.Column(db.Boolean())
    confirmed_at = db.Column(db.DateTime())
    favcolor = db.Column(db.String(255))
    roles = db.relationship('Role', secondary=roles_users,
                            backref=db.backref('users', lazy='dynamic'))

    def __str__(self):
        return '<User id=%s email=%s>' % (self.id, self.email)

if __name__ == '__main__':
    manager.run()

I have run the migration tool to initialize and migrate the db once, to create a new db, and it worked:

python db_manager.py db init
python db_manager.py db migrate

I tried to run app.py. It serves correctly on localhost, but then when I try to log a user in, I get the following OperationalError:

OperationalError: (OperationalError) no such table: user u'SELECT user.id AS user_id, user.email AS user_email, user.password AS user_password, user.active AS user_active, user.confirmed_at AS user_confirmed_at, user.favcolor AS user_favcolor \nFROM user \nWHERE lower(user.email) LIKE lower(?)\n LIMIT ? OFFSET ?' (u'xxx@xxx.com', 1, 0)

Basically, I doubt that I'm creating user_datastore and security correctly, as I probably shouldn't be importing User and Role in that way -- but I'm not sure how to access them properly.

EDIT:

I added this final command, thanks to suggestion:

python db_manager.py db ugrade

But, now I get this error when I try to confirm a user registration via email:

(InvalidRequestError: Object '' is already attached to session '1' (this is '3')

Answer 1

The workflow with Flask-Migrate/Alembic is as follows:

1. db init

   This you do once when you create the migration repository and never again.

2. db migrate

   You run this to generate a migration script. The output of the command tells you where the migration script was created, and shows a summary of what was put in it. Your database has not been modified at this stage.

3. review the migration script

   This is very important. Automatic migrations are not perfect, you have to review the generated script and make any corrections that are necessary.

4. db upgrade

   This applies the migration to your database, effectively making the necessary schema changes.

5. You can now use your database. When you make more changes to your models return to Step 2 and repeat the cycle.

From your description you maybe missed step 4, the upgrade call.

As a side note, you have some duplication between your two scripts, you should try to consolidate them. Take a look at how people build Flask applications split across multiple modules or packages.