Flask-SQLAlchemy tables are defined in the schema but not getting created

Question

I am creating my first, simple RBAC login/signup page using Flask

This is my project structure

├── app.py
├── flask_api
│   ├── __init__.py
│   ├── config.py
│   ├── extensions.py
│   ├── models.py
│   └── routes.py
├── instance
└── migrations
    ├── __pycache__
    ├── env.py
    └── versions

app.py

from flask_api import create_app

app = create_app()

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

__init__.py

from flask import Flask
from flask_api.config import Config
from flask_api.extensions import db, migrate, init_extensions, user_datastore
from flask_api.routes import main

def create_app():
    app = Flask(__name__)
    app.config.from_object(Config)
    init_extensions(app) # Initializing extensions
    app.register_blueprint(main) # Registering blueprint
    return app

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

extensions.py

from flask_sqlalchemy import SQLAlchemy
from flask_security import Security, SQLAlchemyUserDatastore
from flask_migrate import Migrate
from flask_api.models import User, Role

db = SQLAlchemy()
migrate = Migrate()
user_datastore = SQLAlchemyUserDatastore(db, User, Role)

def init_extensions(app):
    global user_datastore, security
    db.init_app(app)
    migrate.init_app(app, db)
    security = Security(app, user_datastore)

models.py

from flask_sqlalchemy import SQLAlchemy
from flask_security import UserMixin, RoleMixin

db = SQLAlchemy()

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):
    ...

class User(db.Model, UserMixin):
    ...

routes.py

from flask import Blueprint, request, jsonify
from flask_api.models import db, User, Role
from flask_api.extensions import user_datastore

main = Blueprint('main', __name__)

@main.route('/user-signup', methods=['POST'])
def user_signup():
    data = request.json
    if user_datastore.find_user(email=data['email']):
        return jsonify(message="Email is already registered"), 400
    # More signup logic is there
    ...

@main.route('/user-login', methods=['POST'])
def user_login():
    # More logic
    ...

---

Now, when I run the app.py using flask run and make a POST request to the /user-signup endpoint, main.db gets created but there are no tables inside of it.

I know that I must use this line of code somewhere to manually initiate the tables:

with app.app_context():
    db.create_all()

But I can't really wrap my mind over this, that where to put this line of code. These are few approaches that I tried but they were all unsuccessful:

• Tried putting it in app.py after creating app = create_app().
• Tried putting in init.py inside the create_app() method, after initializing the extensions.
• Tried putting it in extensions.py inside the init_extensions(app) method.

Every one of them is not working

Can someone guide me with what am I doing wrong here, and better design approaches if possible.

PS - I've hidden some code because I thought it's not relevant to the question, but if you want to see it I can edit my question later!

EDIT - One more thing, if I discard my project structure, and keep everything in one file (__init__.py), then it works just fine, tables are being created perfectly, on their own (Even no need for db.create_all()).
That's why I am adamant, that it is possible with my current project structure.

EDIT 2 - I updated my __init__.py as follows:

from flask import Flask
from flask_api.config import Config
from flask_api.extensions import db, migrate, init_extensions, user_datastore
from flask_api.routes import main

def create_app():
    app = Flask(__name__)
    app.config.from_object(Config)
    with app.app_context():
        init_extensions(app) 
        app.register_blueprint(main) 
        print(db.metadata.tables.keys()) # dict_keys([])
        print(db.engine.table_names())   # ['alembic_version']
        db.create_all()
        return app

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

So, even the table metadata is not present, and doing db.create_all() here does nothing.

In my routes.py I tested with these -

from flask import Blueprint, request, jsonify
from flask_api.models import db, User, Role
from flask_api.extensions import user_datastore

main = Blueprint('main', __name__)

@main.route('/user-signup', methods=['POST'])
def user_signup():
    print(db.metadata.tables.keys()) # dict_keys(['roles_users', 'role', 'user'])
    print(db.engine.table_names())   # ['alembic_version']
    import pdb; pdb.set_trace() # To stop further code from executing
    # More signup logic is there
    ...

@main.route('/user-login', methods=['POST'])
def user_login():
    # More logic
    ...

I do not understand this discrepancy here.

Answer 1

Your problem is that you have two separate db = SQLAlchemy() that are not connected with each other. In extensions.py you should import User and Role along with db, as that db instance actually has the Role and User models declared for it, while the one defined inside extensions.py has none of that. In short, the following is a patch to what you've done that should resolve the issue where having all that in same file worked and your split version doesn't, as the split version has this flaw where there was a duplicate db defined which got used to create the database with.

--- a/extensions.py
+++ b/extensions.py
@@ -1,9 +1,8 @@
 from flask_sqlalchemy import SQLAlchemy
 from flask_security import Security, SQLAlchemyUserDatastore
 from flask_migrate import Migrate
-from flask_api.models import User, Role
+from flask_api.models import db, User, Role
 
-db = SQLAlchemy()
 migrate = Migrate()
 user_datastore = SQLAlchemyUserDatastore(db, User, Role)