---
title: "Using a MySQL server backend"
author: "Johannes Rainer"
package: ensembldb
output:
  BiocStyle::html_document:
    toc_float: true
vignette: >
  %\VignetteIndexEntry{Using a MariaDB/MySQL server backend}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
  %\VignetteDepends{ensembldb,EnsDb.Hsapiens.v86,BiocStyle}
---


# 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:

```{r  eval = FALSE }
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.

```{r  eval = FALSE }
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

```{r  sessionInfo }
sessionInfo() 
```