Multiple (simultaneous) users in R Shiny with access to MySQL database

Question

I have a Shiny application (hosted on shinyapps.io) that records a user's click of certain actionButtons to a MySQL database. I'd love some advice on a few things:

• where to put the dbConnect code (i.e. inside or outside the shinyServer function)
• when to close the connection (as I was running into the problem of too many open connections)

Each addition to the database just adds a new row, so users aren't accessing and modifying the same elements. The reason I ask this is I was running into problem of multiple users not being able to use the app at the same time (with the error "Disconnected from server") and I wasn't sure if it was from the MySQL connections.

Thank you!

Answer 1

Someone in the comments posted about the pool package, which serves this exact purpose! Here's the relevant parts of my server.R code:

library(shiny)
library(RMySQL)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  user='username', 
  password='password', 
  dbname='words', 
  host='blahblahblah')

shinyServer(function(input, output) {
  ## function to write to databse
  writeToDB <- function(word, vote){
    query <- paste("INSERT INTO word_votes (vote, word) VALUES (", vote, ", '", word, "');", sep="")
    conn <- poolCheckout(pool) 
    dbSendQuery(conn, query) 
    conn <- poolReturn(conn)

  ## rest of code
  }

I added the poolCheckout and poolReturn to run successfully and prevent leaks.