Help with accessing SQLite database in Github Repo

Using Streamlit
1

Hi All,

I’m working on a little project to automate the setup of a board game to share with my nerdy board game friends while also giving me experience with Streamlit. I have the data in two tables in a SQLite database that I’ve uploaded to my Github. There is nothing secret or sensitive in this database whatsoever. For some reason, I’m getting an error on my deployed app:

OperationalError: no such table: games

This leads me to believe the app is accessing the database, but not finding the tables. My secrets.toml are:
[sqlite]
database = "gamessqlite.db"

There are two tables: games and components.

I get the same error when running it locally, however, when I adjust the db_path in my code from st.secrets['sqlite'] to the absolute path of the database on my computer, I can run the app just fine. Am I setting my secrets up incorrectly? There was no guide and very little found in my searches for deploying a streamlit app using a SQLite database. Does anybody know what might be happening? I really appreciate the help!

App: https://share.streamlit.io/hgcno2/game-projects/Boardgames/auto-setup/everdell.py
Github repo: game-projects/Boardgames/auto-setup at master · HgCNO2/game-projects · GitHub

2

Alright, I figured out the problem on the local side. I was using a relative path to my local database. As such, in my command prompt, I changed directory to the directory that actually had the database, and boom, it loaded just fine. I’m sure I could have added an absolute path to secrets.toml and it would have worked all the same.

Now that I figured that out, I’m still running into an issue with my shared app. How do I reference the database in Github. Is it even possible? I tried copying the link to the file in Github and put that in as my database in my secrets. That didn’t work.

3

I GOT IT!

So, here’s the trick for anybody else looking to use a SQLite database on Github:
Pay attention to your console. When I rebooted my app once more, I watched it spin up in the console. I noticed it cloned the repository over to Streamlit (it’s not just referencing the code from Github). Where it said main module, I got the idea that I could use that as my relative path. That did the trick.

Again, if you’re wanting to use a SQLite database that you host on your Github, your secrets should look something like this:
[sqlite]
path/to/streamlit-cloned-repository/sqlite.db

I have no problem sharing my secrets in this case. Mine are:
[sqlite]
Boardgames/auto-setup/gamessqlite.db

I hope this helps some other newbies out there.

1 Like
4

@randyzwitch perhaps something to add to the docs here

2 Likes
5

I pondered this a little more after I got it working. The reason the first secrets didn’t work is because my app isn’t in the root of my Github repo. My repo is game-projects. If my database was in there, just using the database name is sufficient. Because the app (and database) is actually two folders deep from the repo (game-projects/Boardgames/auto-setup/), I needed more to the path.

It’s all so obvious now that I figured it out.

2 Likes
6

So if you wanted to, instead of “game-projects/Boardgames/auto-setup/” you could have “game-projects/Boardgames/auto-setup/Modules/” and store Functions in the Secret Managment? What are your thoughts? This is what I would like to do.

7

I’m not sure what you’re really trying to accomplish with this. The data still has to live somewhere, in this case the SQLite database.

My repo has evolved a bit since the original post. Now it’s at https://share.streamlit.io/hgcno2/game-projects/Boardgames/auto-setup/game_setup.py. It pulls the series’s function in when it is called in the Choose Your Universe selector. This selector calls both the series_name.py and the series data from the SQLite database.

Pulling from an additional /Modules is fine if you want to organize it a little better, but not necessary since it’s still publicly accessible from your repo. Functions generally aren’t secret as where databases and their credentials generally are.

8

Thanks for your feedback, it was very helpful.

P.S. Hiding code from the public was the objective.