Testing and Troubleshooting Database Connections: Using Public Resources
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:
- Rfam MySQL Database: https://docs.rfam.org/en/latest/database.html A public read-only MySQL DB, good for testing MySQL connections from Domino.
- 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
Field Engineer @ Domino