if I have an unstable network or very long running R scripts, I am confronted with the problem, that I may lose the database connection. Usually I initialise the connection at the start and pass it as an argument to all procedures that need the connection so that I don't have to create a new connection every time (slow)

library(DBI)
library(odbc)
dbcon <- DBI::dbConnect(odbc::odbc(),...)
procedure1(dbcon)
procedure2(dbcon)
procedure3(dbcon)

Is there a best practice on how to automatically reconnect if the connection is lost?

In a shiny app I once did a workaround: defined the DB-connection as a reactive and to look every 30s if the connection is still active and if not, reconnect... but I bet there is a better way, especially in non-interactive scripts.

Maybe something that DBI already implements or maybe another pacakge?