数据库数据变更时自动更新DataTable的实现方案咨询

优化后的代码 #

# 全局初始化数据库连接，避免每次触发都新建连接
db <- dbConnect(SQLite(), dbname = "db.sqlite")

restock <- reactive({
  # 监听数据库文件的修改时间，只在文件变化时更新数据
  db_file_info <- file.info("db.sqlite")
  invalidateLater(1000, session)
  req(db_file_info$mtime)
  
  isolate({
    # 全局存储上次数据库修改时间和缓存数据
    last_mtime <- get0("last_db_mtime", envir = .GlobalEnv, inherits = FALSE)
    cached_data <- get0("cached_restock_data", envir = .GlobalEnv, inherits = FALSE)
    
    if (is.null(last_mtime) || db_file_info$mtime > last_mtime) {
      # 数据库有更新，重新读取并处理数据
      data1 <- dbReadTable(db, "restock_df")
      
      data2 <- data1 %>% 
        select(-row_id, -brand, -location, -comment, -date) %>%
        group_by(product) %>%
        summarise(across(everything(), sum), .groups = "drop") # 替代过时的summarise_all
      
      # 更新缓存和时间标记
      assign("last_db_mtime", db_file_info$mtime, envir = .GlobalEnv)
      assign("cached_restock_data", data2, envir = .GlobalEnv)
      data2
    } else {
      # 数据库没变化，直接返回缓存数据
      cached_data
    }
  })
})

# 应用关闭时自动断开数据库连接
onStop(function() {
  dbDisconnect(db)
})

核心优化点 #

• 杜绝连接泄漏：原代码每次触发reactive都新建数据库连接，会导致大量闲置连接。现在全局只初始化一次连接，应用关闭时统一断开。
• 智能触发更新：原代码固定2秒强制轮询，不管数据库有没有变化都重复读取。现在通过监听数据库文件的修改时间，只有当数据真的更新时才重新计算，大幅减少资源浪费。
• 缓存计算结果：数据库未更新时直接返回缓存的处理后数据，避免重复执行分组求和的逻辑。
• 替换过时语法：用summarise(across(everything(), sum))替代已经弃用的summarise_all(sum)，同时添加.groups = "drop"避免分组状态残留导致的后续问题。
• 调整轮询间隔：把轮询间隔改成1秒（可根据需求调整），在实时性和资源占用之间找平衡。

多用户部署补充 #

如果是多用户使用的Shiny服务器，建议用pool包做连接池，避免并发连接问题：

library(pool)
# 创建连接池
db_pool <- dbPool(SQLite(), dbname = "db.sqlite")

restock <- reactive({
  # 逻辑同上，只是把db换成db_pool
  db_file_info <- file.info("db.sqlite")
  invalidateLater(1000, session)
  req(db_file_info$mtime)
  
  isolate({
    last_mtime <- get0("last_db_mtime", envir = .GlobalEnv, inherits = FALSE)
    cached_data <- get0("cached_restock_data", envir = .GlobalEnv, inherits = FALSE)
    
    if (is.null(last_mtime) || db_file_info$mtime > last_mtime) {
      data1 <- dbReadTable(db_pool, "restock_df")
      
      data2 <- data1 %>% 
        select(-row_id, -brand, -location, -comment, -date) %>%
        group_by(product) %>%
        summarise(across(everything(), sum), .groups = "drop")
      
      assign("last_db_mtime", db_file_info$mtime, envir = .GlobalEnv)
      assign("cached_restock_data", data2, envir = .GlobalEnv)
      data2
    } else {
      cached_data
    }
  })
})

# 应用关闭时关闭连接池
onStop(function() {
  poolClose(db_pool)
})

内容的提问来源于stack exchange，提问作者Levis Ogoro