title: How to initialize a Postgresql database in a Flask app with SQLAlchemy
body: <p>The <a href="https://flask.palletsprojects.com/en/2.0.x/patterns/sqlalchemy/" rel="nofollow noreferrer">Flask tutorial</a> (and many <a href="https://stackabuse.com/using-sqlalchemy-with-flask-and-postgresql/" rel="nofollow noreferrer">other tutorials</a> <a href="https://realpython.com/flask-by-example-part-2-postgres-sqlalchemy-and-alembic/" rel="nofollow noreferrer">out there</a>) suggests that the <code>engine</code>, the <code>db_session</code> and the <code>Base</code> (an instance of <code>declarative_metadata</code>) are all created at import-time.</p>
  <p>This creates some problems, one being, that the URI of the DB is hardcoded in the code and evaluated only once.
  One solution is to wrap these calls in functions that accept the <code>app</code> as a parameter, which is what I've done. Mind you - each call caches the result in <code>app.config</code>:</p>
  <pre><code>def get_engine(app):                                                                           
      &quot;&quot;&quot;Return the engine connected to the database URI in the config file.
      Store it in the config for later use.
      &quot;&quot;&quot;
      engine = app.config.setdefault(
          'DB_ENGINE', create_engine(app.config['DATABASE_URI'](), echo=True))
      return engine                                                                              
                                                                                                 
  def get_session(app):                                                                          
      &quot;&quot;&quot;Return the DB session for the database in use
      Store it in the config for later use.
      &quot;&quot;&quot;                          
      engine = get_engine(app)
      db_session = app.config.setdefault(
          'DB_SESSION', scoped_session(sessionmaker(
              autocommit=False, autoflush=False, bind=engine)))
      return db_session
                                                                                                 
  def get_base(app):                     
      &quot;&quot;&quot;Return the declarative base to use in DB models.
      Store it in the config for later use.
      &quot;&quot;&quot;                                                                                        
      Base = app.config.setdefault('DB_BASE', declarative_base())
      Base.query = get_session(app).query_property()
      return Base                                
  </code></pre>
  <p>In <code>init_db</code>, I call all those functions, but there's still code smell:</p>
  <pre><code>def init_db(app):
      &quot;&quot;&quot;Initialise the database&quot;&quot;&quot;
      create_db(app)
      engine = get_engine(app)
      db_session = get_session(app)
      base = get_base(app)
  
      if not app.config['TESTING']:
          import flaskr.models
      else:
          if 'flaskr.models' not in sys.modules:
              import flaskr.models
          else:
              import flaskr.models
              importlib.reload(flaskr.models)
  
      base.metadata.create_all(bind=engine)
  </code></pre>
  <p>The smell is of course the hoops I have to go through to import and create all models.
  The reason for the code above is that, when unit testing, <code>init_db</code> is called once for each test (in <code>setup()</code>, as <a href="https://flask.palletsprojects.com/en/2.0.x/tutorial/tests/" rel="nofollow noreferrer">suggested in the same tutorial</a>), but the import will only be performed the first time, and <code>create_all</code> will therefore work only that time.</p>
  <p>Not only that, now with a session shared for the duration of the app, I have problems in parametrized negative unit tests (that is, parametrized unit tests that expect some sort of failures): the first instance of the test will trigger a failure (e.g. login failure, see <a href="https://flask.palletsprojects.com/en/2.0.x/tutorial/tests/" rel="nofollow noreferrer"><code>test_login_validate_input</code> in the tutorial</a>) and exit correctly, while all subsequent will bail out early because the <code>db_session</code> should be rolled back first. Clearly there's something wrong with the DB initialization.</p>
  <p>What is the Right Way(TM) to initialize the database?</p>