Need odbc driver in dockerfile for my r shiny app

Question

I'm making a R shiny app, which calls the database (SQL Server) using DBI and odbc and displays a table. My app works perfectly on my local machine. However, when I tried to dockerize the shiny app, it just failed to start. No clue why that does not work.

I feel like I might need to install the odbc drive in my dockerfile. Maybe I'm also missing something else...

I'm new to docker. Appreciate it if someone can show how I can modify my dockerfile so that my shiny app also works as docker container.

below is my dockerfile:

# Base image https://hub.docker.com/u/rocker/
FROM rocker/shiny:latest

# system libraries of general use
RUN apt-get update -qq && apt-get -y --no-install-recommends install \
    libxml2-dev \
    libcairo2-dev \
    libsqlite3-dev \
    libmariadbd-dev \
    libpq-dev \
    libssh2-1-dev \
    unixodbc-dev \
    libcurl4-openssl-dev \
    libssl-dev

## update system libraries
RUN apt-get update && \
    apt-get upgrade -y && \
    apt-get clean

# Install R packages that are required
RUN R -e "install.packages(c('dplyr','DT', 'DBI', 'odbc'), repos='http://cran.rstudio.com/')"
    
# Copy configuration files into the Docker image
COPY shiny-server.conf  /etc/shiny-server/shiny-server.conf
COPY /app /srv/shiny-server/

# Make the ShinyApp available at port 80
EXPOSE 80

# Copy further configuration files into the Docker image
COPY shiny-server.sh /usr/bin/shiny-server.sh

CMD ["/usr/bin/shiny-server.sh"]

below is my shiny ui.r:

ui <- fluidPage(
    
    titlePanel("Database table"),    
    sidebarLayout(
        sidebarPanel(),
        mainPanel(
            fluidRow (
                column(12, DT::dataTableOutput('datatable')) )
        )
    )
)

my server.r

server <- function(input, output, session) {
    conn <- DBI::dbConnect(odbc::odbc(),
                           Driver = "ODBC Driver 17 for SQL Server",
                           Server = 'xxxxxx',
                           Database = "xxxxxx",
                           UID = "xxxxxx",
                           PWD = 'xxxxxxx',
                           port=1433)
    df = dbGetQuery(conn, "SELECT * FROM myTable")
    dbDisconnect(conn)    
    output$datatable <- DT::renderDataTable({
        DT::datatable(df)
    })
}

global.r:

library(shiny)
library(DBI)
library(odbc)
library(dplyr)
library(DT)

I think we're missing a piece, are you running a SQL server container, or is the R container supposed to talk to SQL server on your host machine? Also: "However, when I tried to dockerize the shiny app, it just failed to start." - can we see a screenshot of what is failing? What happens if you try and run the R program from within the container manually? Is the app not working or is docker failing to start? I would wager the issue is DB connectivity but it's hard to be certain just based on that description. Might be worth adding in error handling in the R script. Let us know how you go — mikey, Aug 31 '20 at 04:13

score 2 · Answer 1 · edited Oct 22 '20 at 03:36

2

If it throws the error like "ODBC Driver 17 for SQL Server" cannot be found, perhaps you haven't installed the ODBC driver in your Dockerfile.

I had the same issue, but it was solved for me when putting this in my Dockerfile: https://community.rstudio.com/t/odbc-drivers-docker/32714.

edited Oct 22 '20 at 03:36

Dharman

30,962
25
85
135

answered Oct 22 '20 at 03:30

dora

21
3

score 0 · Answer 2 · answered Oct 12 '20 at 03:18

I still think we don't have enough information (it isn't ideal that we can't see the specific error message confirming that it is a connectivity issue between the Docker container and the SQL Server), but I'll state my assumptions and give this a shot.

You mentioned that you have this stack running locally without issue, and it's only when you move the R application into a container that it isn't working. This leads me to think that your SQL Server instance is running locally.

Once again, it's hard to be certain as in server.r. You've censored out the database endpoint (Server = 'xxxxxx',). If it is an external endpoint it makes sense to censor it, but if it is an internal/localhost address this could be useful.

I'm not familiar with this stack, but from what I've read you've done the right thing in regards to ODBC - you installed the system packages, installed the R libraries, and included the R libraries. Looking at this article it seems like it should be correct: https://nhsrcommunity.com/blog/sql-server-database-connections-in-r/

So, my theory is that your server configuration is still pointing at localhost or 127.0.0.1 despite being in a Docker container (they no longer share the same loopback/localhost!).

So please try updating your server.r database endpoint to Server = 'host.docker.internal',

I think there's a decent chance this is the issue, please refer to this thread for a lot more information about connecting a Docker container to a host service: https://stackoverflow.com/a/24326540/3080207, host.docker.internal should work, but I think it may depend on the OS you are running and the version of Docker you are running.

If I'm way off the mark, please provide more information :)

Need odbc driver in dockerfile for my r shiny app

2 Answers2