sqlite use in shiny apps on posit connect

shiny
, ,
1

Sqlite use in shiny apps deployed on posit connect

We are developing a large multi user application in shiny that uses duckdb and rsqlite databases. We are experiencing some issue that we believe are coming from multiple connections to the RSQLite database coming from the same process.

The issue

The symptoms are the freezing if it is in use, or a white screen on start up. We don't have admin access on posit connect so we can't debug and inspect the logs. Testing suggests that simultaneous writes to the RSQLite database are issue and setting max processes =1 in the application settings resolves this.
image

Question

Doing some research[1], it appears it is due to multiple connections to the same SQLite database.

  • It is possible to have multiple connections (max connections >1) to in the posit app?
  • If so, how can this be achieved?
    • Use IMMEDIATE TRANSACTION etc [2]
    • Using a database pool [3]

Footnotes

  1. Sqlite issues with multiple connections
    • Background on the issue
  2. RSQLite concurrency issue & solutions
    • A number of solutions here including
      • Increasing timeout PRAGMA busy_timeout =10000
      • using BEGIN IMMEDIATE inplace of BEGIN TRANSACTION
  3. database pool
    • Setup a pool object to manage the SQLite connections
    • Use the poolWithTransaction to peform the transaction
2

Hi,

Welcome to the RStudio community!

I use SQLite a lot myself in app development and have used it successfully in hosted apps before, but there are indeed several caveats.

First as you know SQLite / DuckDB are file-based databases and not designed for efficient concurrent writing as there is no sophisticated DB management system monitoring anything (just file locking when writing). The use of the pool package is definitely something I would recommend as this will try and manage multiple connections to the database. Alternatively, databases like SQLite also allow you to increase the busy timeout to wait before erroring out using
sqliteSetBusyHandler(). In the end this is still only useful if your app has a limited amount of users who need to write to the database. Otherwise you'll have to implement postgres or another management system.

Also a reminder that it's not trivial to getting data back from your database as a developer once it's on Posit Connect as you cannot easily access the files once they have been deployed.

Finally, are you sure you can't see the logs? Normally they are visible at the top of the screen when accessing an app the way you show in your screenshot.
image

Hope this helps,
PJ

3

Any thoughts on enabling Write-Ahead Logging?

I wasn’t sure how this might interact with pool if there’s a long held connection in the pool this might prevent the flush of the cached writes.

4

Hi,

To my understanding pool will take care of this. It will create a WAL for every transaction that writes to the database (or if you want to run several transactions before committing). During that time indeed other connections can't write to the database, but will after it has completed. So it depends on your code if this could cause delays for the user or not. Reading should not be impeded.

I'm not a real expert on the topic so keep that in mind when following my advice :stuck_out_tongue: In general I think you can be creative with file-based databases but there is a point where you'll have to switch to a DMS is your app gets many connections or needs advances processing.

Hope this helps,
PJ