21

I'm trying to build SQLAlchemy models that can be used in Flask and in other non-Flask services. I know that in order to use these objects in Flask I can use the Flask-SQLAlchemy module and build the models like this:

app_builder.py

def create_app(config):

    # Create a Flask app from the passed in settings
    app = Flask('web_service')
    app.config.from_object(config)

    # Attach SQLAlchemy to the application
    from database import db

    db.init_app(app)

database.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Job(db.Model):
    __tablename__ = 'job'

    job_id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String(256))

    def __init__(self, description):
        self.description = description

However it looks like doing this ties the models to using flask_sqlalchemy. I have another service that I would like to use these models in that don't use flask. Is there a way I can reuse these class definitions (maybe by changing db.Model) within a non-Flask specific context?

Sergey Shubin
  • 3,040
  • 4
  • 24
  • 36
bean2Dr
  • 213
  • 2
  • 5

1 Answers1

35

flask_sqlalchemy doesn`t allow you to use it outside of a Flask context. However, you can create models via SQLAlchemy itself. So your database.py file would look like this:

from sqlalchemy import MetaData, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

metadata = MetaData()
Base = declarative_base(metadata=metadata)

class Job(Base):
    __tablename__ = 'job'
    
    job_id = Column(Integer, primary_key=True)
    description = Column(String(256))
    
    def __init__(self, description):
        self.description = description

You can initialize a flask_sqlalchemy object using produced metadata (flaskdb.py):

from flask_sqlalchemy import SQLAlchemy

from database import metadata

db = SQLAlchemy(metadata=metadata)

And you initialize your Flask app like this:

from flask import Flask

from flaskdb import db

def create_app(config):
    app = Flask('web_service')
    app.config.from_object(config)
    
    db.init_app(app)

Created models can be used outside of the Flask context via a Session. For example:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

from database import metadata, Job

engine = create_engine('your://database@configuration/here')
session = Session(engine)
jobs = session.query(Job).all()
session.close()

As a downside of this approach, you can't use direct access to database objects through models. Instead, you are forced to use Sessions:

from database import Job
from flaskdb import db

Job.query.all() # Does not work
db.session.query(Job).all() # Works
Sergey Shubin
  • 3,040
  • 4
  • 24
  • 36
  • This solves my problem perfectly. Thank you so much! – bean2Dr Dec 07 '16 at 18:06
  • 1
    small note in your last example: I had to use something like `db.session.query(Job).all()` for querying, but this has been very helpful in the rest of my setup, thanks! – Matt Oct 01 '17 at 17:26
  • Would there be a way to make this work in the case where you Flask App uses Blueprints. I am initializing my app in __init__.py in the project folder, and then I also have multiple models.py files, one for each blueprint, and then im not sure how to do the: from database import metadata db = SQLAlchemy(metadata=metadata) part. – Siesta Oct 24 '22 at 09:08
  • @Siesta It could be done regardless of the number of model files. You need to create a single `metadata` object and a single `Base` parent class in a separate module. All the model classes need to be subclassed from `Base`, also make sure they are imported on application start. If you do it your `metadata` object will contain all your models and it can be used to create `SQLAlchemy` object. If it doesn't work perhaps you should create a new question and describe your project structure in it. – Sergey Shubin Oct 25 '22 at 06:09
  • Perfect answer!! Thanks a lot for this! – Vikram Baliga Jun 08 '23 at 12:13