Hi, I’m looking for some guidance / resources on how to properly link a fly.io DB with my Flask python app - on local it’s working due to local mysql database - but it does not work properly when deployed as-is to fly (even though no errors) - I suspect due to lack of DB.
Would appreciate some tips, don’t see anything easily referencable on google or search here 
Thx!
I am looking for the same thing, I think with an interactive python console we can execute db.create_all() to generate a DB from the app model
I’ve been able to run the app on local, using the fly.io postrgres database by proxying to localhost. So I assume that my app is working correctly (at local).
So it works fine when using this link on local:
SQLALCHEMY_DATABASE_URI = ‘postgresql://user:pwd@localhost:5432’
However, when I change it back to the original
SQLALCHEMY_DATABASE_URI = ‘postgresql://user:pwd@ht-new-db.internal:5432’
and deploy, the app does not work.
Not sure if that is app-db problem, or is it a problem with building the various requirement libraries I want to have. E.g. psycopg2 library is often problematic to build.
Would using docker help here somehow? Can someone suggest a solution?
Project structure:
fly.toml
app = "ht-pl-youth"
kill_signal = "SIGINT"
kill_timeout = 5
processes = []
[build]
builder = "paketobuildpacks/builder:base"
[env]
PORT = "8080"
[experimental]
allowed_public_ports = []
auto_rollback = true
[[services]]
http_checks = []
internal_port = 8080
processes = ["app"]
protocol = "tcp"
script_checks = []
[services.concurrency]
hard_limit = 25
soft_limit = 20
type = "connections"
[[services.ports]]
force_https = true
handlers = ["http"]
port = 80
[[services.ports]]
handlers = ["tls", "http"]
port = 443
[[services.tcp_checks]]
grace_period = "1s"
interval = "15s"
restart_limit = 0
timeout = "2s"
Procfile:
# Modify this Procfile to fit your needs
web: gunicorn run:app
requirements.txt (libraries are duplicated, because some of them change the version of others):
pychpp
flask==2.0.2
Werkzeug==2.0.3
jinja2==3.0.2
flask_login==0.5.0
flask_migrate==3.1.0
WTForms==3.0.0
flask_wtf==1.0.0
flask_sqlalchemy==2.5.1
sqlalchemy==1.4.29
email_validator==1.1.3
gunicorn==20.1.0
flask-restx==0.5.1
python-dotenv==0.19.2
Flask-Minify==0.37
pychpp
flask==2.0.2
Werkzeug==2.0.3
psycopg2-binary
This happens when i deploy my app and try to open it:
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info]Traceback (most recent call last):
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask/app.py", line 2073, in wsgi_app
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] response = self.full_dispatch_request()
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask/app.py", line 1511, in full_dispatch_request
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] self.try_trigger_before_first_request_functions()
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask/app.py", line 1563, in try_trigger_before_first_request_functions
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] self.ensure_sync(func)()
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/workspace/apps/__init__.py", line 39, in initialize_database
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] db.create_all()
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask_sqlalchemy/__init__.py", line 1094, in create_all
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] self._execute_for_all_tables(app, bind, 'create_all')
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask_sqlalchemy/__init__.py", line 1086, in _execute_for_all_tables
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] op(bind=self.get_engine(app, bind), **extra)
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask_sqlalchemy/__init__.py", line 1017, in get_engine
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] return connector.get_engine()
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask_sqlalchemy/__init__.py", line 594, in get_engine
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] self._engine = rv = self._sa.create_engine(sa_url, options)
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/flask_sqlalchemy/__init__.py", line 1027, in create_engine
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] return sqlalchemy.create_engine(sa_url, **engine_opts)
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "<string>", line 2, in create_engine
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] return fn(*args, **kwargs)
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 560, in create_engine
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] dbapi = dialect_cls.dbapi(**dbapi_args)
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] File "/layers/paketo-buildpacks_pip-install/packages/lib/python3.10/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 163, in dbapi
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info] return __import__("MySQLdb")
2022-11-06T09:18:22Z app[ffa9c4ba] waw [info]ModuleNotFoundError: No module named 'MySQLdb'
I don’t get any mySqldb error on local, it’s also not referenced directly anywhere in my code…
This is the source of your current error. Are you sure you don’t have an attempted MySQL connection somewhere in your setup? Perhaps in apps/__init__.py? It looks like SQLAlchemy is trying to use it somewhere (Maybe try searching your repo. Are you using migrations? Were you using MySQL previously?
The brute-force solution is simply to add the most recent version of mysqlclient to your requirements, just to see whether you get further in the deploy process, but you really shouldn’t need it.
Somehow I resolved that, apparently this error happens when the link to the db is incorrect. I was placing my link in debug mode and the fly.io deploy was using prod variables, which had the db link missing.