Connecting Shiny Apps to Databases with Posit Team

Hi everyone, my name is Sarah Altman and I'm on the developer relations team at Posit. Thanks for coming to this workflow demo. At the end of the session, there will be a live Q&A, at which point you can ask any questions.

Today I'm going to talk about connecting Shiny apps to databases with Posit Team. I'll show you an example app that can read from and write back to a DuckDB database, and I'll show you that app in both R and Python. I'm going to focus on how the app connects to and reads from the database, allows users to edit a table, and then writes those changes back to the database. I'll also talk about considerations for deploying your app, and show you how to deploy to Posit Connect.

Why connect a Shiny app to a database?

So before we take a look at the app, let's first talk about why you would even want to connect your Shiny app to a database. One reason is that your organization might just store your data in a database, and you want to make a Shiny app that interacts with, displays, does something with that data. A second consideration is speed. Even if your data originates elsewhere, you can use DuckDB to speed up operations, which might be important if your app allows the user to do a lot of computations on the data. A third reason is if your app's functionality relies on a shared data source. As you'll see, the app we'll build today allows users to read and write to a central data source.

So let's talk about the general workflow. We're going to create an app that reads from a DuckDB database, allows users to edit that data by flagging suspicious values, and then writes those flagged values back to the database. As I said, we're going to use a DuckDB database, but the code we'll write can be adapted to a variety of database management systems. This related blog post linked here shows the same code we'll see today adapted for Snowflake and Databricks.

Overview of the app

So let's take a look at the app. This is the app that we'll be working on today. I've written the same app in both R and Python, and the GitHub repositories are linked below. This is an image of the Python app, but the R one looks pretty similar. The general idea of this app is to allow users to flag suspicious values in a dataset. That data is displayed in the plot on the left-hand side with a few outliers highlighted, and then those outliers are displayed in that table on the right-hand side. You can edit the flag column if you think a value might be an error or is in some way suspicious, and then write those flag values back to the database, at which point the idea is maybe someone would take a closer look at those rows of data or exclude suspicious values or something like that. So we're not really changing the underlying data, but we're just adding a flag.

Now let's take a look at the deployed version of the app so we can see it in action and interact with it.

So now we're looking at the deployed version of the app. On the left-hand side, we have this plot that's displaying the data. This is error quality data, so it's showing the date and the PPM, which is the ozone level in parts per million on that date. In blue are a couple of highlighted outlying values. So you can click on these values, and the corresponding row will highlight in this table. This is an editable table, which means that you can edit this value.

The point of the flag is if you identify a point that you think is not just an outlier but is suspicious in some way, you think it's an error, something went wrong, you can set the flag to 1 just to mark that there might be a problem with that row of data. And then you can click write to database to write that flag back to the database table.

And one other thing I want to highlight, as it says down here, this example app is reading from and writing to an in-memory DuckDB database, which means that when you refresh the page, the database is going to be regenerated from scratch. So you're not going to see any changes that you have written. This is solely for the purposes of the example. Obviously, this isn't really how you would want to implement this in real life, since you would want those updated flag values to persist.

Connecting to and reading from the database (Python)

So first, we want to connect and read from the database. We're going to use Ibis to connect to the database and read from it. Ibis is an open-source DataFrame library that works with a variety of backends, including DuckDB, Snowflake, and Databricks.

So like I said, Ibis lazily evaluates commands, which means that the full query is never run on the database unless explicitly requested. If we want to preview Ozone in this interactive window, we can run ibis.options.interactive equals true. And this is going to turn on interactive mode, and now all our expressions are going to be executed immediately and then printed to the console, or when printed to the console.

And then the rest of this code that I had moved, this is Ibis code that is renaming some columns and selecting some columns in preparation for the rest of the things we're going to do in the app.

So like I said, Ibis lazily evaluates commands, and it's not going to run the full query unless requested, or it has to. If you want to force the query to execute, you can use to pandas. And what to pandas does is it evaluates the query fully and then returns the result as a pandas data frame. So here I've done this in the app because we need the entire outliers table. Outliers is what's going to become that editable table, and we want the entire thing.

So like I said, Ibis lazily evaluates commands, which means that the full query is never run on the database unless explicitly requested.

Creating an editable table (Python)

So now let's talk about creating that editable table component that displays in our Shiny app. This is a Shiny Express app, which means it doesn't have a separation between the UI elements and the server elements. So all this creates an editable table. It's a data grid where I've set editable equal to true, which makes the cells editable. You can also do this for a data table component.

And because we set editable to true, now we can change these values. Actually, all of the cells are editable. So you could change this to Pennsylvania. But I have some other code in the app that reverts it to the original because we don't want you actually changing the state or anything else. The point of the app is only to set the flag.

Writing changes back to the database (Python)

All of this code at the end is responsible for writing back to the database when the user clicks that write to database function. We use the reactive event decorator so that this function is only triggered when the user clicks that button. This code is trying to figure out which values have been changed. So we know which values we need to update. I really just want to focus on these lines of code because these are the lines of code that are actually interacting with the database. So what this code is doing is for every row that we've changed, it's updating so that the table has the new flag that had been set by the user.

So you'll notice that there's a bit of SQL here. One advantage of IBIS is that you can write familiar Python instead of writing SQL to interact with the database. But I had to write a bit of SQL here because while some IBIS backends support updating existing tables using an IBIS method, the DuckDB backend doesn't. So I had to write a little bit of SQL and then execute it using the IBIS method raw SQL. So what this SQL is doing is updating flag to the new value where it was one of those ones that was changed. And then raw SQL is executing that query.

We've already set two flag values to one. Now when we click write to database, we get this little message that two values were successfully updated.

The R version of the app

So now I'm going to open this RStudio session. So that we can take a look at the R app. And I'm going to talk about the same three elements of the app, connecting and reading from the database, creating that editable table and then writing changes back to the database.

So our app primarily is an app.R. Let's first take a look at the connection code. So this is where we're connecting to the DuckDB database. Whereas in the Python app, we used IBIS. Here we're using the DBI package and dbconnect from the DBI package allows you to connect to various databases. We also need a driver. This is the DuckDB driver from the DuckDB package. And this code looks pretty similar to the Python one. Again, we need to make a table to represent our ozone data. And now we're using table or TBL function from the dplyr package. And this is going to create a representation of our ozone data that, again, we can use in the app.

So now let's take a look at the editable table component. Because this is a Shiny for R app, we have a separation of the UI components and the server function. So here, this is where we've defined the data table output component that is going to be our editable table. And then let's scroll down to the server function. And you see that inside render DT, there's this function create editable table. So this is just a helper function that I created.

Here, we're creating a data table. And just the thing to note is that similar to the Python app, we can add an editable argument. And this time, we need to provide it a list of options. So we're saying that we want the cells to be editable and then to disable editing on columns 0 through 3. So that's all the columns except for the flag.

And now the third element is writing changes back to the database. So again, the similar concept. We need to figure out what has been changed and then update those values in the database. So here, we've used the DBI function, db execute, to write back to the database. DB execute takes a connection, so that's con, and a SQL string, and then executes that SQL. Since we want to update an existing table, we're using an update statement.

Deploying to Posit Connect

So now let's deploy this app. One way is to click this blue Publish button, and we can publish it to Connect. Okay, and here is our published app, ready to share with others.

One thing to keep in mind is that when deploying an app that interacts with the database, you'll probably have to think about managing access. There's a couple strategies to handling database permissions. So the first is that if you are deploying to Connect, you can use the Vars pane to securely set environment variables after deploying your app. And Posit Connect will automatically provide the correct values to your app during runtime.

And now when we deploy the app, it'll get that value from Connect and be able to manage access that way.

So as you just saw, one way to deploy your app and manage access involves using environment variables in Posit Connect. This strategy relies on adding one set of credentials to Posit Connect, though. What if you want each user to authenticate with their own credentials? This could be particularly important if your database includes row-level or data-level permissions. One solution is to use OAuth integration, which is now available in Posit Connect for Databricks and Snowflake, allowing users to log in with their own accounts. With OAuth integration, you don't need to specify the database credentials used in your app. Instead, users can authenticate through the OAuth integration, and then Posit Connect will automatically handle the connection. For other database management systems, you can request user credentials through your app and then pass those credentials to the connection code.

What if you want each user to authenticate with their own credentials? This could be particularly important if your database includes row-level or data-level permissions.

Summary

So now just to summarize what we talked about today, you saw how to create a Shiny app that reads from and writes to a database. We used IBIS for the Python app to build a connection to DuckDB and read in a table. For the R version of the app, we used the DBI package for the same task. Then, to allow users of the app to make changes to the data, we created an editable table that lets you set a flag value. We then wrote the changes made by the user back to our original database table. And then we deployed the app to Posit Connect and discussed two possible ways to securely deploy your app.

I showed you one particular implementation of this style of app, an app that reads in data from a database, displays it, allows someone to edit that data, and then writes the data back to the database. But you can imagine this framework extending to other use cases, such as labeling images when training an image recognition system or something similar.

I've provided a few links to additional resources if you'd like to learn more. The first is the related blog post that includes sample code for creating Shiny apps that interact with DuckDB, Snowflake, and Databricks. The second is the title of another workflow demo that covers how to use OAuth and Databricks to create a Shiny app. The third is the integration in Posit Connect. And then I've also linked to the IBIS and DBI websites. Thanks for attending this demo. I'm looking forward to your questions in the Q&A.