Testing and Troubleshooting Database Connections: Using Public Resources

andrew.colemanandrew.coleman Member, Moderator, Domino Posts: 4 mod

When testing database connections from Domino, it's sometimes helpful to be able to connect to an existing database. For example, if you're thinking of spinning up a new postgres DB to use with Domino, you might want to try connecting to an existing postgres DB before going to the trouble of setting up your own. Here are some resources we've used for testing and troubleshooting:

  1. Rfam MySQL Database: https://docs.rfam.org/en/latest/database.html  A public read-only MySQL DB, good for testing MySQL connections from Domino.
  2. H2 Database Engine https://www.h2database.com/html/main.html A SQL DB engine written in Java, useful for testing MySQL and PostgreSQL.

To demonstrate how you can use these, here's an example R code to load the 'family' table from the Rfam MySQL database:

### Connection details: 
# host	mysql-rfam-public.ebi.ac.uk
# user	rfamro
# password	none
# port	4497
# database	Rfam

### Use the Dockerfile instructions below to add RMySQL to your environment.
#RUN sudo apt-get install -y libmariadb-client-lgpl-dev
#RUN R -e 'install.packages("RMySQL")'

# load the library
library(RMySQL)

# fetch values from environment variables and set the target database
hostname <- 'mysql-rfam-public.ebi.ac.uk'
username <- 'rfamro'
password <- ''
database <- 'Rfam'
port <- 4497

# set up a driver and use it to create a connection to your database
con <- dbConnect(RMySQL::MySQL(), host = hostname,
         user = username, password = password, dbname = database, port = port)

# run a query and load the response into a dataframe
df_mysql <- dbGetQuery(con, "SELECT * FROM family")

# close your connection when finished
dbDisconnect(con)

Note: The public connection details, including username and password, are shown here for illustration purposes only. We recommend storing your credentials in environment variables rather than hard-coding them. See our docs on environment variables for credential storage for more information: https://docs.dominodatalab.com/en/4.3/reference/runs/advanced/Environment_variables_for_secure_credential_storage.html

Andrew

Field Engineer @ Domino

Sign In or Register to comment.