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.
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## R Under development (unstable) (2023-02-16 r83857)
## Platform: x86_64-pc-linux-gnu (64-bit)
## Running under: Ubuntu 22.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.20.so; LAPACK version 3.10.0
##
## locale:
## [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
## [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
## [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: UTC
## tzcode source: system (glibc)
##
## attached base packages:
## [1] stats graphics grDevices utils datasets methods base
##
## other attached packages:
## [1] BiocStyle_2.27.1
##
## loaded via a namespace (and not attached):
## [1] vctrs_0.5.2 cli_3.6.0 knitr_1.42
## [4] rlang_1.0.6 xfun_0.37 stringi_1.7.12
## [7] purrr_1.0.1 textshaping_0.3.6 jsonlite_1.8.4
## [10] glue_1.6.2 rprojroot_2.0.3 htmltools_0.5.4
## [13] ragg_1.2.5 sass_0.4.5 rmarkdown_2.20
## [16] evaluate_0.20 jquerylib_0.1.4 fastmap_1.1.0
## [19] lifecycle_1.0.3 yaml_2.3.7 memoise_2.0.1
## [22] bookdown_0.32 BiocManager_1.30.19 stringr_1.5.0
## [25] compiler_4.3.0 fs_1.6.1 systemfonts_1.0.4
## [28] digest_0.6.31 R6_2.5.1 magrittr_2.0.3
## [31] bslib_0.4.2 tools_4.3.0 pkgdown_2.0.7.9000
## [34] cachem_1.0.6 desc_1.4.2