Is it a bad idea to

🎈 Using Streamlit
,
1

Hi folks, a general question about db connections. I am using duckdb and motherduck for data storage , is there anything that might go wrong with storing the connection as a session state object?

I understand there is st.connection, but there’s no duckdb connection out of the box, and I am not sure if the overhead for setting up a duckdb connection via st.connection is worth it? Any thoughts?

a related question about how streamlit works : storing the connection should persist the connection through re-runs, what does st.connection provide that session state doesn’t when it’s an in process database like duckdb?

thanks in advance

2

Hi @Andrew_Shih

You can use the duckdb library to connect to your duckdb database.

See this blog for example code snippet to get started in using duckdb in a Streamlit app.

To connect to your duckdb database you can use its native library:

import duckdb
con = duckdb.connect(database='duckdb_stats.db', read_only=True)

To connect to MotherDuck:

import duckdb
con = duckdb.connect('md:?motherduck_token=<token>')

Hope this helps!

3

thanks @dataprofessor. I have connected to motherduck as described before. My worry, however, is with how streamlit’s execution model works with data connections? does a connection gets re-created every time the script reloads? would that cause issues? If the db is read_only , that won’t be a problem. But I do need to write and update tables.

do you know if the ‘con’ variable will point to the same db connection and I am not just re-initiating a connection every time? will either be problematic? without explicitly closing it?

hence why I was thinking of having the db connection as a session state object, can you think of any issues with that approach? or are we pretty safe either way?

Thanks Again!

p.s. I really enjoy your youtube channel, thanks for the great videos!