Using SQLAlchemy models in and out of Flask

Question

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?

Answer 1

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