Automatically pulling in SQLite database update & showing it in the app

[sborms]

Hi all,

I developed a Streamlit app that relies on a SQLite db. I want the data in the database to be updated once a week so I set up a GitHub Actions workflow that triggers a scraping script and pushes the changes back to the repository. I see the push being noticed on Community Cloud, but the database itself doesn’t change on the app… It only changes when I manually reboot the Streamlit app.

When I check the webhook, I see that both the .db file and another .txt file are effectively modified. Only the second file’s changes (to show the last refresh date on my home page) are carried forward to the app though.

Does anyone know what’s happening? Is it because the .db is a binary file? Should I remove and add it instead of modifying it?

I already added a ttl in the cache functions, moved the database in and out of my root folder, patiently waited for a few weeks, but no luck.

Information

App: https://futsalfriend.streamlit.app
GitHub repo: GitHub - sborms/futsalfriend: Your digital futsal companion: find a friendly opponent, look for a team to join, analyze vanity metrics, and receive jolly tactical advice
Versions: Python 3.9.13, Streamlit 1.27.0

[sborms]

A further overview of the things I tried:

• Added a TTL in the cache functions of 0 or 1800 seconds
• Used st.connection() with a TTL of None, 0 or 1800 seconds
• Added the SQLite .db file in a separate folder (database/) and in the main app folder (webapp/)
• Waited for a few hours/days up to one week
• Added some calls to st.cache_data.clear(), based on Cached query results are not scoped to the connection on which that query was run · Issue #7709 · streamlit/streamlit · GitHub

Only the last attempt seems to have helped (I’ll wait for a next update to be sure).

It still feels suboptimal, because it doesn’t permit proper caching and it doesn’t explain why a git committed change in the database file is ignored on Streamlit Community Cloud. I know SQLite is a semi-persistent storage so I assume it’s held on to somewhere on the server side, but not sure if that explains it.