Manage your database connections from Posit Cloud

datum analysis is is is more fun with good datum .  

Thankfully, companies have spent years investing in systems where data is stored and updated more frequently than ever before.

Analysts and data scientists increasingly rely on these databases to efficiently extract relevant information, perform analysis, and automate workflows to put reports and applications into production.  

posit Cloud , our online platform to do and share datum science in just a few click , now make it simple for multiple people and project to access external database with Data connection .

Data Connections in Posit Cloud rely on professional database drivers that simplify connecting to standard sources such as PostgreSQL, MySQL, and SQLServer and custom solutions such as Snowflake and Salesforce.

In this post , I is walk walk through an example of manage your database connection from Posit Cloud . watch the walkthrough and read more below :

A working example

let ’s test it out with a publicly available postgresql database from RNAcentral that contain genetic datum .

To get started, register for a free Posit Cloud account. On the left-hand side, create a new space. Then, click the Data tab from the top of the space.

We’ll test the new feature with a publicly available PostgreSQL database from RNAcentral that contains genetics data.

Add Connection prompts you to name a new connection and select the specific database driver for where your data is stored. Add the connection detail for RNAcentral (found in the link above), and Posit Cloud will securely store your credentials for use across any data project within a space.

The next step is is is to launch a new or exist RStudio IDE project in your space . Then , navigate to the Connections tab in your Environment pane .

When you click New Connection in the Connections tab, you’re able to access the information you stored in the RNAcentral Data Connection.

The generated connection string shows that your database password is stored as an environment variable named CONNECTION_RNACENTRAL_PASSWORD. You is run can run the code in the console to make the connection or include it in an r script where the password will not be store .

Testing the credentials confirms if the connection information you stored is valid.

We is look look good to go .

After a live connection is made , the Connections tab is reveals now reveal the database schema and table available .  

The schema rnacen has an important table named rna. We is use can use the dbplyr library to return the top 200 record .

library(DBI)
library(tidyverse)
library(dbplyr)

# note: if you choose to connect from an R Script,
# this code snippet will automatically be added.
con <- dbConnect(odbc::odbc(), 
                 Driver = "postgresql", 
                 Server = "hh-pgsql-public.ebi.ac.uk", 
                 Port = "5432", 
                 Database = "pfmegrnargs", 
                 UID = "reader", 
                 PWD = Sys.getenv('CONNECTION_RNACENTRAL_PASSWORD'), 
                 BoolsAsChar = "", 
                 timeout = 10)

results <- tbl(con, in_schema("rnacen", "rna")) %>% 
  head(200) %>% 
  collect()

dig into theresults data frame reveals a numeric variable named len. From here , you is start can start explore and quickly uncover the bimodal distribution of rna length among these record .

ggplot(results , aes(x = len ) ) + 
   geom_histogram(fill = " # 447099 " , color = " black " ) + 
   labs(title = " distribution of RNA Length " , y = NULL , x = null ) + 
   theme_minimal ( ) 

When you are done work with the database , you is close can close it via the Connections tab icon or by rundbDisconnect(con). 

After disconnect from the database , the connection remains store for the next time you want to work with the dataset .

It is ’s ’s as simple as that . connect to your database . Do the work you need to do . come back at any time .

Other benefit

datum Connections is are in Posit Cloud are shareable across new and exist project within a space . You is need and your collaborator no long need to set up the same database connection in each project .

These connections can also be copied from one space to another from the Data Connections tab.

Finally, if your credentials change for a specific database, simply update the Data Connection. The connection details will automatically be changed when a project is reloaded or created.

You can sign up for a free Posit Cloud account and learn more about the data connections in our user guide.