Do you want to be able to build web applications that feature impressive data visualizations? Do you want more to happen when users are interacting with a web application than to have data display on a screen? With most web-based software development languages, it’s very difficult to create data visualizations. However, Shiny makes creating robust and dynamic visualizations from backend data super easy to do.
Using SQL with Shiny
It’s possible to build Shiny apps with a SQL backend. This allows you to benefit from the powerful data visualization features of R along with the dynamic backend that SQL allows for. Building a database-powered application in Shiny is no more difficult than building a application using Ruby on Rails, PHP, or any other software development environment which you are familiar with. However, there can be a slight learning curve to using Shiny if you are generally not familiar with web development
Connecting to a Database
The first step in your code is to connect to the database software which you are using on your backend. R has a DBI package that you are able to use in order to connect to your database software. Before you use the DBI function in your code, include a library function which references the DBI library: library(DBI)
This DBI package supports the following database tools: MySQL, MariaDB, Postgres, and SQLite and more. You may find a complete list of databases here.
The function that you would use with this package is the DBI::dbConnect function.
The DBI::dbConnect function takes a few parameters in order to connect to the database. The first one is the DB Connection object depending on the type of database that you are using with your R code. Aside from that, you should also include the server location, port that MySQL runs on, username, password, and database name (host, port, username, password, and dbname).
Secure Password Storage
Storing your database password in your connection code is typically not regarded as being a very secure choice among most web developers. If you are looking to securely store your password without having it stored in the code, you should use the keyring function. Keyring allows you to store the credentials in your system and run a query against it to retrieve the password. To use this, set the password = keyring::key_get() in your connector function and use the server address and username as the two parameters to access it.
Creating a Table
You can do nearly anything with SQL in Shiny that you can do with any other language. That also includes creating database tables with DBI using the dbCreateTable() function. The dbCreateTable function accepts the connection, the name of the table, and fields you are adding to it as a data frame. The minimum function requires 3 parameters: connection, table name in double quotes, and column names (either as a single column or a vector).
Running SQL queries is a very simple task with Shiny. You are able to use the dbGetQuery function with your connection (con) and SQL code by passing the two into the function. The connection is a variable, and the SQL query would be held in quotation marks – much like the following:
query <- dbGetQuery(connection_variable, “SQL Query”)
If you are using character strings as a part of your SQL code, make sure that you are using single quotes to hold the text that you are using in order to write your query.
You may also choose to limit the number of rows that you are outputting from the results of a query. If that is so, use the head() function with the query variable and the number of rows that you would like to limit the query to as parameters.
In order to insert data, it’s best to use the dbWriteTable() function to perform this operation. This function takes your connection, table name, data frame, and append = TRUE as parameters. You could use the dbGetQuery function with the insert command, however it’s easier for the dbWriteTable function to insert an entire data frame into the table.
Using a data frame with the dbWriteTable command is straightforward. The data frame uses both the column names from the database and sets these equal to the data that you are inserting into each column.
As an example, if you wanted to insert a new person into the “People” database with First_Name, Last_Name, and Birth_Year columns, you might use the following data frame:
person <- data.frame(“First_Name” = ‘Jack’, “Last_name” = ‘Smith’, “Birth_Year” = 1970)
In order to delete data from your database, you will need to use the Delete SQL command with the dbExecute command.
Disconnecting From The Database
Whenever you are done using the database in your code, you will always want to close your database session. This can prevent many other errors from happening. The function for disconnecting from a database in Shiny is dbDisconnect(connection variable).
Fortunately, Shiny makes it very simple for you to create database-powered apps. However, there is a slight learning curve involved if you are not very experienced in working with SQL or Shiny.
If you are fairly new to working with databases and/or Shiny, you may want to consider working with SQLite. You won’t have to work with data types, which can be something that might be slightly challenging for you. You may not necessarily want to create a high-volume application using this method, but you can certainly create a few great web-based tools using Shiny and SQLite databases.
As you gain in your experience with working with Shiny, you will be able to build more robust and scalable database-backed applications. Combined with the impressive visualization tools of R, you can create applications which are significantly more robust and feature more impressive user interfaces with Shiny than you could with any other web development tool.