Proper sqlalchemy use in flask

Question

I am making a basic API for funsies with flask and sqlalchemy. I read in the sqlalchemy documentation that you should only ever have one engine with the connection string. How is that supposed to be structured within an app> An __init__.py file? In the flask file that runs to start the flask server? Here is my github repo, since i think its easier just to see the current app structure and any flaws it may have. https://github.com/JayHelton/Geekdom_Web_App

In my query methods, i am creating a new DB engine everytime and it seems to be working, but i dont want to run into issues if multiple people try to call these methods. Thank you!

Answer 1

Of course there is already a Flask extension Flask-SQLAlchemy - also mentioned in the Flask docs SQLAlchemy in Flask. But like most Flask extensions, all it does is "just" some "plumbing" Flask and SQLAlchemy (or any other library) together. The best documentation is often times the source code :)

github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py

However, in case of Flask-SQLAlchemy it is a lot of code and also some black magic about scoped sessions intertwined with Flask contexts and modification tracking and duration debugging and some things useful in web apps and all possible corner cases and some other things. I'd say it's a bit overengineered. I'm not saying that you shouldn't use it - just that the connection between SQLAlchemy and Flask is not visible at first look from the extension code so it may take more reading time.

But if you wish to do it yourself it is very easy (well, as easy as SQLAlchemy can get) - just initialize SQLAlchemy so you get a sessionmaker, and then create a session before each request, then don't forget to close it after the request :) and just use this session in your Flask handlers (I mean the @app.route functions).

import flask
import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Item (Base):

    __tablename__ = 'items'

    id = Column(Integer, primary_key=True)
    title = Column(String)    

engine = sqlalchemy.create_engine('sqlite:///bestappever.db', echo=True)
Session = sessionmaker(bind=engine)

# create all tables if you wish...
Base.metadata.create_all(engine)

app = flask.Flask(__name__)

@app.before_request
def create_session():
    flask.g.session = Session()

@app.teardown_appcontext
def shutdown_session(response_or_exc):
    flask.g.session.commit()
    flask.g.session.remove()

@app.route('/')
    # just use the SQLAlchemy session :)
    items = flask.g.session.query(Item).all()
    return flask.render_template('index.html', items=items)

See my example Flask + SQLAlchemy app: https://github.com/messa/db-workshop-web-app/blob/sqlalchemy/anketa.py

As you see you can even have everything on one big file but splitting it into multiple files (one of which is usually named like model.py) is the best practice.

The most important thing is to have isolated sessions between individual requests - in my solution the session is created for each request in the before_request hook. The solution from Flask docs "SQLAlchemy in Flask" uses scoped_session which has basically the same effect (achieved using thread-local variable with one separate session per each thread).

Ad application architecture: for bigger applications it is good to use Larger Applications patterns and blueprints. So all my flask route handlers will be in blueprints, and then I would have one "main" function get_app() that 1) creates Flask object 2) registers the blueprints with it 3) creates SQLAlchemy engine and hooks Session() with Flask app.before_request. Roughly similar to this or this.

---

you should only ever have one engine

Why? Technically, engine is just a connection (pool) to the database. What if your app uses three separate databases? Then you need three engines, of course.

But of course a session is connected to exactly one engine. So you would need multiple sessions. And multiple declarative bases for your model classes, and - most importantly - not mix it up accidentally. That's why it's recommended to have just one engine - if possible.

You can create engine per request - it is technically correct, but it is inefficient. Keep one engine for whole application and just create session per request.