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

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 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 it.

I know that I should use code snippet somewhere to manually initiate the tables:

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

But I can't really wrap my mind around where to put this code snippet. 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.

None of these approaches work. Can someone assist me with what I'm doing wrong here, and better design approaches if any.

PS - I've omitted some code here because I thought that it wasn't relevant to the question, but I can share it if necessary.

EDIT - Additionally, if I discard my project structure, and keep everything in one file (__init__.py), then it works just fine - tables are being created perfectly, all on their own (without even the need for db.create_all()).
That's why I strongly believe that this 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 though the table metadata is not present, 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 the cause of this apparent discrepancy here.

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)