Keywords: databases, R, shiny, SQL, DBI, dplyr, pool
Being able to interact with an external relational database (like MySQL) is an increasingly important skill in data science. While this process may be a lot slower than dealing with in-memory data in R, it is infinitely more scalable. In particular, it's important to know how to establish a connection to a database, how to execute safe queries using SQL (goodbye SQL injections!) and how to close the connection. These skills allow you to read and write from a remote database. In this talk, I'll go over how to do these in R, using the DBI, dplyr and pool packages. I'll place special importance on these best practices when applied to the interactive context of a Shiny app. Interacting with databases through a Shiny app allows you to build an app that lets users modify data on a database, without knowing anything about SQL or R!
This talk is meant for a wide audience. Those in the audience already familiar with databases, R and Shiny will be able to see best practices in action. Those in the audience who are generally unfamiliar with the topic will be able to see how much is currently possible and have references to all the best practices through my publicly available slides.