4

I'm building a shiny app that connects to a SQLite database. It enables the user to see tables and update and insert data.

Now my question is whether it is better to connect once at the start of the app and disconnect once when closing it. Or everytime a query is executed (connect -> execute query -> disconnect).

Approach 1:

  1. App starts -> dbConnect() (only once)
  2. User does stuff. Insert, read, update...
  3. App closes -> dbDisconnect() (only once)

Approach 2:

  1. App starts -> dbConnect(), read tables, dbDisconnect()

  2. User inserts or updates data -> dbConnect(), execute SQL-Query, dbDisconnect()

  3. Open DB, execute Query, Close DB repeats for every interaction with DB...

What are the benefits of each approach? Is the connection blocked for the time the connection is held open? And what how is opening and closing the DB affecting the performance?

Johannes69
  • 41
  • 1
  • 1
    I've found approach 2 beneficial for remote server-based DBMSes when: (1) the ODBC/`DBI` layers do not always handle KeepAlive intentions correctly; and (2) I need to fork into multiple processes (`shiny` + `future`), where the connection object itself does not pass through `future(.)` correctly (not a bug). I see no need and only negligible overhead with using approach 2 with SQLite (and DuckDB) "connections". – r2evans May 06 '22 at 13:53
  • 1
    Thank you for your answer! That are quite interesting use cases for using approach 2. On top of that, it might also be recommendable to use approach 2 when many users are connected at the same time, since there may be some limit how many connections RSQLite can keep open simultaneously. – Johannes69 May 09 '22 at 12:44
  • @Johannes69 http://rstudio.github.io/pool/ is a third alternative and the easiest to manage in my experience. – s_baldur Aug 24 '23 at 14:36

0 Answers0