Shiny user input in query to PostgreSQL DB fails

Question

Long-time lurker, first time poster.

I am trying to put together a simple Shiny app that allows users to select a species (using a dropdown) and which results in a map showing the species' distribution range. To date I have mostly used Shiny in the context of pre-processed dataframes which is easy enough, but species spatial ranges are large and complicated and as such are stored in a PostgresSQL database.

I have no issues connecting with the database, either directly in the console or inside the Shiny script. I.e. connection exists, I can access tables and using e.g. dbGETQuery dbGetQuery(con, "SELECT * FROM range WHERE sp_id = 664") or st_read st_read(con, query = "SELECT * FROM range WHERE sp_id = 580"). Results are as expected.

The problem seems to arise when trying to feed the user input (species selection) into the SQL query to the PostgreSQL DB. It is no doubt something simple, but I can't for the life of me figure out what I am doing wrong.

This is the error I end up with: *Warning: Error in : Failed to prepare query : ERROR: syntax error at or near "{" LINE 1: SELECT * FROM range WHERE sp_id = {input$species_choice} *

The system seems to dislike the use of '{}' brackets but that seems a legitimate notation as far as I can tell (as shown e.g. here: text, which is an example I can emulate without issues, curly brackets and all.

Global

library(shiny)
#library(glue)
library(DBI)
library(RPostgres)
library(RPostgreSQL)
library(sf)
library(dplyr)
library(ggplot2)

library(ozmaps)
con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "db",
  host = "localhost",
  port = 5432,
  password = "password",
  user = "username")

species_list <- data.frame(sp_id  = as.integer(c(580,581)),
                            taxon_name = c("Species1","Species2")) ## example short list to populate dropdown

*

UI section:*

ui <- fluidPage(

    # Application title
    titlePanel("Range Layers"),

    # Sidebar with a slider input for number of bins 
    sidebarLayout(
        sidebarPanel(
          selectizeInput(inputId = 'species_choice', 'Select species or start typing', choices = c("Choose Species" = "",species_list$sp_id), selected = NULL, multiple = FALSE,
                         options = NULL),
        ),

        # Show a plot of the distribution layer at continental scale
        mainPanel(
           plotOutput("distPlot")
        )
    )
)

Server section:

server <- function(input, output, session) {
  data <- reactive({
    req(input$species_choice)
    
    # Get the data
    species <- st_read(con, query = "SELECT * FROM range WHERE sp_id = {input$species_choice}")

    species
})

output$distPlot <- renderPlot({ggplot(data()) +
              geom_sf(ozmap_states,mapping = aes()) +
             geom_sf(mapping = aes(fill = taxon_id_r)) + 
              ggtitle(species$taxon_name)
    
})

shinyApp(ui = ui, server = server)

No doubt the answer is something daft I have overlooked, but it is currently driving me up the wall. Any and all insight much appreciated!

Answer 1

Unlike dbGetQuery, st_read seems a bit more sensitive to quite how the SQL statement is constructed.

The below solved the issue. Simply using paste to construe the first (SQL) part of the string and secondly the user input reference:

data <- reactive({ req(input$species_choice) st_read(con, query = paste(" SELECT * FROM range WHERE sp_id = ",input$species_choice)