Flask Migrate - Multi-tenant issues
By hientd, at: May 16, 2023, 5:53 p.m.
Recently, I encountered an unexpected issue with Flask-Migrate while using multi-tenant with PostgreSQL schemas. Let me provide a detailed explanation of the problem and how I resolved it.
The exception I encountered while running the flask db upgrade command was:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "192.9.2.1", port 5432 failed: FATAL: password authentication failed for user "server_username"
connection to server at "192.9.2.1", port 5432 failed: FATAL: no pg_hba.conf entry for host "local", user "server_username", database "proddb", no encryption
While everything worked perfectly on my local machine, Flask-Migrate failed to work with the server database.
1. The Problem
Initially, I installed virtualenv, pip, and all the required packages:
psycopg2==2.9.6
Flask==2.3.2
Flask-Migrate==2.5.3
Flask-SQLAlchemy==3.0.3
I set up a local.json file for the Flask application:
{
"SECRET_KEY": "my secret",
"SQLALCHEMY_DATABASE_URI": "postgresql://joe:password@localhost:5432/testdb",
"DATABASE_SCHEMA": "public"
}
The app.py file contained the following code:
import json
from os import environ
from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from blueprints import api_blueprint
db = SQLAlchemy()
def create_app():
environment = environ.get("ENVIRONMENT", "local")
app = Flask(__name__)
app.config.from_file(f"{environment}.json", load=json.load)
db.metadata.schema = app.config["DATABASE_SCHEMA"]
db.init_app(app)
migrate = Migrate(app, db)
app.register_blueprint(api_blueprint)
return app
app = create_app()
Additionally, I had a main.py file with the following content:
from app import create_app
if __name__ == "__main__":
app = create_app()
app.run(host="127.0.0.1", port=5000, debug=True)
And finally, the models.py file defined a Task model:
from app import db
class Task(db.Model):
__tablename__ = "task"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.Text, nullable=False)
After running python main.py, the website was successfully running on 127.0.0.1:5000
Following the installation of Flask-Migrate==2.5.3, I ran the command flask db init, which created a new migrations directory.
I updated the content of migrations/env.py by adding my model class:
from models import Task
Next, I ran the command flask db migrate in the terminal, which generated a new migration file under migrations/versions, such as migrations/versions/48xw123sdfasf_initial_migration.py.
To apply this change to the testdb database I had created earlier, I ran the following command:
flask db upgrade
Everything worked perfectly on my local machine. However, when deploying to the server, where the database resided on a different server with the IP 192.9.2.1 and port 5432, and I encountered an issue
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "192.9.2.1", port 5432 failed: FATAL: password authentication failed for user "server_username"
connection to server at "192.9.2.1", port 5432 failed: FATAL: no pg_hba.conf entry for host "local", user "server_username", database "proddb", no encryption
Surprisingly, I was able to connect to the database using the command line and access the schema using set schema 'internal';
2. Problem Solving Strategy
To investigate and debug the problem, I followed these steps:
1. Verification of Database Connection URI
I inserted a breakpoint() statement in both app.py and migrations/env.py files to verify if the connection URI was correct. The connection URI seemed correct, with the format postgresql://server_username:***@localhost:5432/proddb. The hidden password (`***`) appeared to be a security measure to protect sensitive information.
2. Upgrading Flask-Migrate
Considering it might be a Flask-Migrate version issue, I decided to upgrade it using the command line:
pip install Flask-Migrate==4.0.4 # latest version
However, even after the upgrade, the issue persisted.
3. In-depth Debugging:
I placed breakpoint() statements in various functions within the Flask-Migrate, Alembic, and SQLAlchemy libraries, as indicated in the Python traceback errors. Unfortunately, these attempts didn't provide any useful insights or solutions. The connection issue persisted.
At this point, I took a short break to clear my mind and regain focus.
Returning with a fresh perspective, I continued my investigation into the migrations/env.py file, which plays a crucial role in the flask db upgrade command. Again, I inspected the connection URI and found it to be correct, with the hidden password.
However, one particular line in the migrations/env.py file caught my attention:
with connectable.connect() as connection:
Investigating deeper into the libraries (Alembic, SQLAlchemy, and Flask-Migrate), I couldn't pinpoint any apparent solutions or fixes for the issue.
Taking another short break, this time lasting 15 minutes, I returned with renewed determination.
Upon reflection, I realized that the URI with the hidden password (`***`) might not be appropriate. Something seemed off about it. It reminded me of my experiences with Django, where the password was not concealed in a similar manner. Django's approach seemed more straightforward.
With this in mind, I decided to make a change. I updated the `sqlalchemy.url` from `'postgresql://server_username:***@:5432/proddb'` to `'postgresql://server_username:server_password@:5432/proddb'`. To my surprise, this modification resolved the issue, and the migration process worked successfully.
So the problem is the password hidden issue, the password is changed to `***`, which is totally wrong.
I pondered why the owners of Flask-Migrate had not identified and addressed this problem earlier. Or...It's possible that I made a mistake, or perhaps the issue was specific to my environment.
Upon reflection, I realized another mistake I made during step 2.2, where I installed the latest version of Flask-Migrate without deleting the existing `migrations/env.py` file and initializing it again. Consequently, the old `env.py` content remained intact.
3. Conclusion
In conclusion, Flask-Migrate is an excellent tool for Flask database migration. To ensure a smooth experience with Flask-Migrate and avoid encountering similar issues, it is important to follow these best practices:
- Double-Check the Database Connection URI: When deploying your Flask application to a different server, verify that the database connection URI is configured correctly. Pay close attention to the server IP, port, username, password, and schema. In some cases, concealing the password with `***` in the URI may cause authentication issues, so ensure the password is provided explicitly.
- Upgrade Packages Properly: When upgrading Flask-Migrate or any other packages, it is crucial to follow the appropriate upgrade process. Ensure that the installation is successful and that any necessary configurations or updates are applied accordingly. Also, make sure to delete the existing `migrations` directory and initialize it again after upgrading Flask-Migrate to ensure compatibility with the latest changes.
- Thorough Debugging: In case you encounter an issue with Flask-Migrate, perform thorough debugging. Use `breakpoint()` statements strategically within your codebase to trace the execution flow and inspect variables, configurations, and connection information. Review relevant libraries' documentation and seek help from the community if needed.
- Learn from Similar Frameworks: Drawing from experiences with other frameworks, such as Django, can provide insights into common patterns and best practices for database migration. Django's approach to database connections and password handling might offer alternative perspectives that could aid in resolving issues.
By adhering to these guidelines and adopting a systematic debugging approach, you can maximize the benefits of Flask-Migrate and overcome any obstacles that arise during the database migration process. Remember to stay proactive, stay informed, and leverage the vibrant Flask community for support and guidance. Happy Flask development!
