How to save sessions in a Postgres database?

Question

In one of our new projects, we want to store the session data in to a PostgreSQL database.

I have found several code snippet on the internet to do this, but none were specific for PostgreSQL.

Answer 1

Use the Flask-Session project; it offers a Flask session implementation that can store data in any SQLAlchemy supported database, including PostgreSQL.

1. Install one of the supported PostgreSQL client libraries; most people use psycopg2.

2. Install Flask-SQLAlchemy; it'll pull in SQLAlchemy when you do. This library integrates SQLAlchemy with Flask.

3. Install Flask-Session and configure it to use the SQLAlchemy session type and a PostgreSQL connection URI; do so by adding the following configuration options to your Flask configuration:

    SESSION_TYPE = "sqlalchemy"
    # URI to connect to the database. postgresql:// uses psycopg2, see the documentation
    SESSION_SQLALCHEMY = "postgresql://<user>:<password>@hostname:port/database"
    # What table in the database to use, default is "sessions"
    SESSION_SQLALCHEMY_TABLE = "sessions"
   

4. Use the Flask-Session extension in your Flask app; the following code assumes you have a app variable that is the Flask() object, already configured with the above configuration:

   from flask_session import Session
   
   # app has been set and configured
   Session(app)
   
   # alternatively, create a `Session()` object without passing in app
   # and then when ready, use `.init_app(app)`:
   # sess = Session()
   # sess.init_app(app)
   

To emphasise: because Flask-Session uses SQLAlchemy, it works with all database engines supported by SQLAlchemy, not just PostgreSQL. So the above would work for SQLite, MySQL, Oracle, Microsoft SQL Server, Firebird, Sybase, and any number of other databases with third-party SQLAlchemy dialect packages available.