How To Connect R Shiny to Postgres Database - The Definitive Guide

Estimated time:

time

min

Managing database connections can be messy at times. It’s always easier to read and write to local CSV files. That doesn’t mean it’s the right thing to do, as most production environments have data stored in one or multiple databases. As a data professional, you must know how to connect to different databases through different programming languages. Today you’ll learn how to connect R and R Shiny to the Postgres database - one of the most well-known open-source databases. There’s no better way to learn than through a hands-on example, so you’ll also create a fully-working interactive dashboard with R Shiny. We assume you already have the <a href="https://www.postgresql.org/" target="_blank" rel="noopener noreferrer">Postgres database</a> installed, as well as a GUI management tool like <a href="https://www.pgadmin.org/" target="_blank" rel="noopener noreferrer">pgAdmin</a>. Installation is OS-specific, so we won’t go through that today. You can Google the installation steps - it shouldn’t take more than a couple of minutes. <blockquote><strong>Looking for a career switch? Here’s how to <a href="https://appsilon.com/how-to-start-a-career-as-an-r-shiny-developer/" target="_blank" rel="noopener noreferrer">start a career as an R Shiny developer</a>.</strong></blockquote> Table of contents: <ul><li><a href="#data-loading">Load Data Into Postgres Database</a></li><li><a href="#connect-r">Connect R to Postgres Database</a></li><li><a href="#connect-r-shiny">Connect R Shiny to Postgres and Create a Dashboard</a></li><li><a href="#conclusion">Conclusion</a></li></ul> <hr /> <h2 id="data-loading">Load Data Into Postgres Database</h2> As our data source, we’ll use the <a href="https://www.kaggle.com/mathurinache/quakes" target="_blank" rel="noopener noreferrer">Quakes dataset</a> from Kaggle. Download it as a CSV file - we’ll load it into the Postgres database shortly: <img class="size-full wp-image-11930" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f3e9dc2dda17fc10c17_quakes-dataset-from-kaggle.webp" alt="Image 1 - Quakes dataset from Kaggle" width="3238" height="2046" /> Image 1 - Quakes dataset from Kaggle We’ve chosen this dataset deliberately because it has only four columns. This way, the time spent importing it into the Postgres database is minimal. To start, create a new table using the following SQL query. We’ve named ours <code>earthquakes</code>: <script src="https://gist.github.com/darioappsilon/fa3b8d5705b231de6639babeee21f4f7.js"></script> Once created, right-click on the table name and select the <em>Import/Export</em> option: <img class="size-full wp-image-11926" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f3f7af0917dece601e4_import-csv-file-to-postgres-database.webp" alt="Image 2 - Importing CSV file into Postgres database (1)" width="2906" height="2110" /> Image 2 - Importing CSV file into Postgres database (1) A new modal window will open. There are a couple of things to do here: <ul><li>Toggle the <em>Import</em> switch</li><li>Specify the path to the CSV file</li><li>Make sure that the <em>Header</em> is toggled</li><li>Specify comma as a delimiter</li></ul> Here’s what the window should look like: <img class="size-full wp-image-11928" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f41fd01060a37df6eff_import-csv-file-to-postgress-2.webp" alt="Image 3 - Importing CSV file into Postgres database (2)" width="2906" height="2110" /> Image 3 - Importing CSV file into Postgres database (2) We’re not quite done yet. Switch to the <em>Columns</em> tab to ensure all four are adequately listed: <img class="size-full wp-image-11922" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f418585d1bcdb862c34_import-csv-file-to-posgres-database-3.webp" alt="Image 4 - Importing CSV file into Postgres database (3)" width="2906" height="2110" /> Image 4 - Importing CSV file into Postgres database (3) If everything looks as in the image above, you can click on the <em>OK</em> button. You should see a message telling you the dataset was successfully imported after a couple of seconds: <img class="size-full wp-image-11924" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f4286bb0c58597b01b8_import-csv-file-to-postgres-database-4.webp" alt="Image 5 - Importing CSV file into Postgres database (4)" width="2906" height="2110" /> Image 5 - Importing CSV file into Postgres database (4) Execute the following SQL command from the query editor to double-check: <script src="https://gist.github.com/darioappsilon/915fa16a3bf8a46a596c4d189b4873b7.js"></script> You should see the table populated with data: <img class="size-full wp-image-11918" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f43afc0ff7c657a13ac_check-import-was-successful.webp" alt="Image 6 - Checking if import to Postgres database was successful" width="2906" height="2110" /> Image 6 - Checking if import to Postgres database was successful To conclude - we now have the dataset loaded into the Postgres database. But how can we connect to it from R and R Shiny? That’s what we’ll answer next. Are you a bit rusty with SQL? Use the following articles as a refresher: <ul><li><a href="https://appsilon.com/introduction-to-sql/" target="_blank" rel="noopener noreferrer">Introduction to SQL: 5 Key Concepts Every Data Professional Must Know</a></li><li><a href="https://appsilon.com/intermediate-sql/" target="_blank" rel="noopener noreferrer">Intermediate SQL for Data Science - Analytical Functions Deep Dive</a></li></ul> <h2 id="connect-r">Connect R to Postgres Database</h2> You’ll need two packages to get started, so install them before doing anything else: <script src="https://gist.github.com/darioappsilon/9bdda90a7ba23de21de30d0257428544.js"></script> You also need to know a couple of things to connect R to the Postgres database: <ul><li><strong>Database name</strong>: <code>postgres</code> by default, but check by listing the databases in pgAdmin.</li><li><strong>Host</strong>: It’s <code>localhost</code>, since we’re using the locally-installed database.</li><li><strong>Port</strong>: <code>5432</code> in our case - check yours by right-clicking the database server and checking the value under <em>Properties</em> - <em>Connection</em> - <em>Port</em>.</li><li><strong>Username and password</strong> - Credentials for your user.</li></ul> Once you know these, connecting to the Postgres database is as simple as a function call: <script src="https://gist.github.com/darioappsilon/8228c789960765e8e0b424e7dfc448d8.js"></script> You won’t see any output, so how can you know the connection was established? Simple, just list the available tables: <script src="https://gist.github.com/darioappsilon/c8d9c34727d1d68670163895772e3657.js"></script> <img class="size-full wp-image-11916" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f44c99b38c07a087a37_available-tables-in-postgres-with-r.webp" alt="Image 7 - Listing available tables in the Postgres database through R" width="392" height="90" /> Image 7 - Listing available tables in the Postgres database through R The <code>earthquakes</code> table is the only one visible, so we’re on the right track. If you want to fetch data from it, use the <code>dbGetQuery</code> function as shown below: <script src="https://gist.github.com/darioappsilon/379c4ab27a1b19f5f7ba8e34a98ddac2.js"></script> <img class="size-full wp-image-11920" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f46ee976c4ea9919cf6_fetch-data-from-postgres-through-r.webp" alt="Image 8 - Fetching data from the Postgres database through R" width="1008" height="290" /> Image 8 - Fetching data from the Postgres database through R And there you have it - concrete proof we have successfully established a connection to the Postgres database. Let’s see how to integrate it with R Shiny next. <h2 id="connect-r-shiny">Connect R Shiny to Postgres and Create a Dashboard</h2> We’ll create a simple <a href="https://appsilon.com/shiny/">R Shiny</a> dashboard that renders a map of earthquakes of the specified magnitude. The dashboard will have a slider that controls the minimum magnitude required for the earthquake to be displayed on the map. When the slider value changes, R Shiny connects to the Postgres database and fetches earthquakes with the currently specified magnitude and above. The dashboard uses the <a href="https://rstudio.github.io/leaflet/" target="_blank" rel="noopener noreferrer">Leaflet</a> package for rendering the map and shows a <a href="https://www.flaticon.com/free-icon/earthquake_1684394?term=earthquake&page=1&position=41&page=1&position=41&related_id=1684394&origin=tag#" target="_blank" rel="noopener noreferrer">custom icon</a> as a marker. <script src="https://gist.github.com/darioappsilon/a024eeff3f9298a5b2bf2c28d728f5e0.js"></script> <img class="size-full wp-image-11932" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01f476bd9d78607d494a8_r-shiny-dashboard-based-on-postgres-database.gif" alt="Image 9 - R Shiny dashboard based on a Postgres database" width="972" height="608" /> Image 9 - R Shiny dashboard based on a Postgres database You have to admit - developing PoC dashboards like the one above requires almost no effort. R Shiny does all the heavy lifting for you, and probably the most challenging part is maintaining the database connection. You could extract the entire table outside the <code>server</code> function, and that would make the dashboard faster. We’ve decided to establish a new connection every time the input changes to show you how to open and close database connections, and how to use the <code>glue</code> package for better string interpolation. New to interactive maps? We have you covered: <ul><li><a href="https://appsilon.com/interactive-google-maps-with-r-shiny/" target="_blank" rel="noopener noreferrer">Build Interactive Google Maps With R Shiny</a> - A Complete Guide</li><li><a href="https://appsilon.com/leaflet-vs-tmap-build-interactive-maps-with-r-shiny/" target="_blank" rel="noopener noreferrer">Leaflet vs. Tmap</a> - Which Should You Use to Build Interactive Maps with R and R Shiny</li></ul> <hr /> <h2 id="conclusion">Conclusion</h2> Managing Postgres database connections through R is simpler than it sounds at first. You only need to know the connection parameters and remember to close the connection once you’re done working with the database. R handles everything else. You’ve created a functional but pretty-basic dashboard today, so there’s definitely room for improvement. Here are a couple of challenges you can do next: <ul><li>Load in the entire <code>earthquakes</code> table outside the <code>server</code> function and use <code>dplyr</code> to filter the results. Is the dashboard significantly faster?</li><li>Render a table showing earthquake data alongside the map.</li></ul> Feel free to share results with us on Twitter – <a href="https://twitter.com/appsilon" target="_blank" rel="noopener noreferrer">@appsilon</a>. We’d love to see what you came up with. If you’re looking for a faster way to showcase your map online, check out our <a href="https://templates.appsilon.com/" target="_blank" rel="noopener noreferrer">Shiny Dashboard Templates</a>. They’re free to use and a great launching point for your project.