Introduction

ensembldb uses by default, similar to other annotation packages in Bioconductor, a SQLite database backend, i.e. annotations are retrieved from file-based SQLite databases that are provided via packages, such as the EnsDb.Hsapiens.v86 package. In addition, ensembldb allows to switch the backend from SQLite to MariaDB/MySQL and thus to retrieve annotations from a MySQL server instead. Such a setup might be useful for a lab running a well-configured MySQL server that would require installation of EnsDb databases only on the database server and not on the individual clients.

Note the code in this document is not executed during vignette generation as this would require access to a MySQL server.

Using `ensembldb` with a MySQL server

Installation of EnsDb databases in a MySQL server is straight forward - given that the user has write access to the server:

library(ensembldb)
## Load the EnsDb package that should be installed on the MySQL server
library(EnsDb.Hsapiens.v86)

## Call the useMySQL method providing the required credentials to create
## databases and inserting data on the MySQL server
edb_mysql <- useMySQL(EnsDb.Hsapiens.v86, host = "localhost",
                      user = "userwrite", pass = "userpass")

## Use this EnsDb object
genes(edb_mysql)

To use an EnsDb in a MySQL server without the need to install the corresponding R-package, the connection to the database can be passed to the EnsDb constructor function. With the resulting EnsDb object annotations can be retrieved from the MySQL database.

library(ensembldb)
library(RMariaDB)

## Connect to the MySQL database to list the databases.
dbcon <- dbConnect(MariaDB(), host = "localhost", user = "readonly",
                   pass = "readonly")

## List the available databases
listEnsDbs(dbcon)

## Connect to one of the databases and use that one.
dbcon <- dbConnect(MariaDB(), host = "localhost", user = "readonly",
                   pass = "readonly", dbname = "ensdb_hsapiens_v75")
edb <- EnsDb(dbcon)
edb

Session information

sessionInfo()

## R version 4.4.1 (2024-06-14)
## Platform: x86_64-pc-linux-gnu
## Running under: Ubuntu 24.04.1 LTS
## 
## Matrix products: default
## BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3 
## LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.26.so;  LAPACK version 3.12.0
## 
## locale:
##  [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
##  [3] LC_TIME=en_US.UTF-8        LC_COLLATE=C              
##  [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
##  [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
##  [9] LC_ADDRESS=C               LC_TELEPHONE=C            
## [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       
## 
## time zone: Etc/UTC
## tzcode source: system (glibc)
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] BiocStyle_2.35.0
## 
## loaded via a namespace (and not attached):
##  [1] digest_0.6.37       R6_2.5.1            fastmap_1.2.0      
##  [4] xfun_0.49           maketools_1.3.1     cachem_1.1.0       
##  [7] knitr_1.48          htmltools_0.5.8.1   rmarkdown_2.28     
## [10] buildtools_1.0.0    lifecycle_1.0.4     cli_3.6.3          
## [13] sass_0.4.9          jquerylib_0.1.4     compiler_4.4.1     
## [16] sys_3.4.3           tools_4.4.1         evaluate_1.0.1     
## [19] bslib_0.8.0         yaml_2.3.10         BiocManager_1.30.25
## [22] jsonlite_1.8.9      rlang_1.1.4

Using a MySQL server backend

Introduction

Using ensembldb with a MySQL server

Session information

Using `ensembldb` with a MySQL server