Saving and restoring bookmarks from a database

Hello Shiny community!

I'm working on a large Shiny application. We are currently using Shiny's "server-side" bookmarks, but we want to save bookmarks in a database (Snowflake) to store additional information like username, bookmark description, created date, etc.

I have an implementation of "database" bookmarks working. When restoring the application state using Shiny's "server-side" bookmarking implementation, the bookmarked state is loaded first. In my implementation, the application will load its "default" state before updating all the inputs with the state fetched from the database. Ideally, the application would initially load with the state retrieved from the database.

I've included a simple example of "database" bookmarking with SQLite below (also available here: https://github.com/byollin/database-bookmarking). How might I change my code to make the Shiny application seamlessly load from a database, i.e., load the bookmarked state first instead of loading the "default" state, then the bookmarked state?

library(shiny)
library(shinyWidgets)
library(magrittr)
library(DBI)

# create/connect to database
conn = dbConnect(RSQLite::SQLite(), 'bookmarks.sqlite')

ui = fluidPage(
    titlePanel('An app with lots of inputs'),
    sidebarLayout(
        sidebarPanel(
            dateRangeInput('date_range_input', label = 'Date range input'),
            pickerInput('picker_input', label = 'Picker input',
                        choices = LETTERS, multiple = TRUE,
                        options = pickerOptions(actionsBox = TRUE, liveSearch = TRUE, size = 5)),
            prettyCheckboxGroup('checkbox_group_input', label = 'Checkbox group input',
                                choices = c('Apple', 'Orange', 'Banana', 'Pear'),
                                selected = c('Apple', 'Pear'), inline = TRUE),
            prettyRadioButtons('radio_buttons_input', label = 'Radio buttons input',
                               choices = c('Cat', 'Dog', 'Fish', 'Rock'),
                                selected = c('Rock'), inline = TRUE),
            actionLink('bookmark', 'Bookmark application', icon = icon('share-alt'))
        ),
        mainPanel(
           verbatimTextOutput('values')
        )
    )
)

server = function(input, output, session) {
    
    # print current input values
    output$values = renderPrint({
        cat('date_range_input : ', paste0(input$date_range_input, collapse = ', '),
            '\npicker_input : ', paste0(input$picker_input, collapse = ', '),
            '\ncheckbox_group_input : ', paste0(input$checkbox_group_input, collapse = ', '),
            '\nradio_buttons_input : ', input$radio_buttons_input, sep = '')
    })
    
    observeEvent(input$bookmark, {
        # snapshot application state
        snapshot_state = reactiveValuesToList(input)
        # only bookmark inputs
        snapshot_state = snapshot_state[stringr::str_detect(names(snapshot_state), '_input')]
        # add the current system time to get a unique hash
        snapshot_state[['time']] = Sys.time()
        # create a state id
        state_id = digest::digest(snapshot_state)
        # create a row to add to the database table
        bookmark_row = tibble::tibble(
            'STATE_ID' = state_id,
            'CREATED_BY' = 'TEST',
            'DESCRIPTION' = 'TEST',
            'STATE_DATA' = jsonlite::toJSON(snapshot_state),
            'CREATED_AT' = Sys.time()
        )
        # write row to database table
        dbWriteTable(conn, 'bookmarks', bookmark_row, append = TRUE)
        # update the query string for the user
        updateQueryString(paste0('?bookmark=', state_id), mode = 'push')
    })
    
    observeEvent(getQueryString(), once = TRUE, {
        # get query string
        hash = getQueryString()
        if(length(hash) > 0) {
            # get bookmark from database table
            bookmark = dbGetQuery(conn, paste0("select state_data from bookmarks where state_id = '", hash$bookmark, "'"))
            # simulate average query time
            Sys.sleep(2)
            # coerce JSON to R list
            bookmark_list = jsonlite::fromJSON(bookmark$STATE_DATA)
            for(id in names(bookmark_list)) {
                value = bookmark_list[[id]]
                if(length(value) == 0) {
                    html_value = ''
                } else {
                    html_value = value
                }
                # update inputs in UI
                session$sendInputMessage(id, list(value = html_value))
            }
        }
    })
    
    # disconnect from database on session ended
    session$onEnded(function() {
        dbDisconnect(db)
    })
    
}

shinyApp(ui = ui, server = server)
7 Likes

I believe you need need
enableBookmarking()
in your ui.R in order to call your bookmark, and then

shinyApp(ui, server, enableBookmarking = "url")

Hi Daniel! For my production application, URL bookmarking would not be feasible due to the number of inputs--it would exceed the maximum URL length. We are currently using "server-side" bookmarking with enableBookmarking(store = 'server'). However, we want to move away from Shiny's built-in bookmarking for the flexibility of a database.

The URL in my code example would appear something like this: mycoolapp.com/?bookmark=<hash>. The hash is extracted from the URL using getQueryString() and the application state is then fetched from a database where the state is stored as a JSON object. Unlike Shiny's URL bookmarking, the application state is not encoded in the query string, just the hash required to locate the state (similar to Shiny's "server-side" bookmarking).

However, the last hurdle is ensuring that the bookmarked state retrieved from the database is the state that gets loaded on initial application startup.

I hope this clears up my question!

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.