Thomas Sandmann’s blog - SQL and noSQL approaches to creating & querying databases (using R)

Creating, polulating and querying SQL and noSQL databases with R

The first step of any data analysis is to obtain and explore the available data, often by accessing and querying a database. There many great introductions on how to read data into an R session. But I found it harder to find tutorials on how to create and populate a new database from scratch.

In this document, I explore both noSQL and SQL approaches to data management. As an example use case, we store a collection of gene sets, specifically the mouse MSigDb hallmark gene sets (MH), either as unstructured documents or in relational tables.

Motivation

Bioconductor offers well designed S4 Classes to store gene set collections, including e.g. in a list-like GSEABase::GeneSetCollection or a set of three tibbles within a BiocSet::BiocSet object. So why could we be interested in storing this information in a database?

A database (e.g. SQLite, Postgres, etc) offers a standardized way to store, manage and access information in a language-agnostic way. E.g. some of my colleagues use python for their analyses and are comfortable retrieving gene set information from a database, but not necessarily from an R S4 object.
Gene sets capture knowledge from multiple experiments, studies and sources. If you are part of a larger organization a single source of truth, available in a central location, is very useful.
Collaborators might not be interested / able to access information programmatically, e.g. they may prefer a web application to search, share and edit gene sets. Many tools to build web applications have built-in capabilities to interact with a database.
As the number of gene sets grows, sharing them in the form of one or more files might become cumbersome. A hosted database (e.g. Postgres or MariaDB ) allows users to retrieve only the information they need.

In this tutorial, I am using the SQLite engine to explore both relational and non-relational ways to manage gene sets. SQLite can be embedded into applications, and does not require a central server, making it ideal for experimentation. (But as you move into a scenario where multiple users need to access a central, it is time to switch to a hosted database instead; my favorite is Postgres.)

library(BiocSet)
library(dm)
library(dplyr)
library(jsonlite)
library(nodbi)
library(org.Mm.eg.db)
library(purrr)
library(DiagrammeR)
library(RSQLite)
library(tibble)
library(tidyr)

The Mouse Hallmarks MSigDB collection

At the time of writing, Mouse Molecular Signatures Database (MSigDB) contains 15918 gene sets, organized into numerous different collections. For example, the 50 hallmark gene sets (MH) summarize and represent specific well-defined biological states or processes ( Liberzon et al, Cell Systems, 2015 ).

Gene symbols

Each of the 50 sets in the collection contains between 32 and 200 official gene symbols, specifying the members of the gene set.

Here, I will use the hallmarks collection as an example but the overall approach can be applied to other gene set collection in a similar way. (You might need additional / different annotation fields, though.)

The mouse hallmarks collection is available in different formats, including as a JSON file. Let’s start by reading it into an R session as nested list mh.

json_file <- paste0(
  "https://raw.githubusercontent.com/tomsing1/blog/main/posts/",
  "geneset-sqlite-db/mh.all.v2022.1.Mm.json")
mh <- jsonlite::read_json(json_file, simplifyVector = TRUE)

Each of the 50 elements in the JSON file corresponds to a different gene set,

head(names(mh))

[1] "HALLMARK_ADIPOGENESIS"        "HALLMARK_ALLOGRAFT_REJECTION"
[3] "HALLMARK_ANDROGEN_RESPONSE"   "HALLMARK_ANGIOGENESIS"       
[5] "HALLMARK_APICAL_JUNCTION"     "HALLMARK_APICAL_SURFACE"

each gene set is a nested list with the following elements,

lengths(mh[[1]])

              systematicName                         pmid 
                           1                            1 
                 exactSource                  geneSymbols 
                           1                          200 
                   msigdbURL           externalDetailsURL 
                           1                            1 
        filteredBySimilarity externalNamesForSimilarTerms 
                           0                            0 
                  collection 
                           1

and the gene symbols that make up the set are listed in the geneSymbols vector:

head(mh[[1]]$geneSymbols)

[1] "Abca1" "Abcb8" "Acaa2" "Acadl" "Acadm" "Acads"

noSQL: storing gene sets as unstructured documents

Each gene set is represented as a list - so why not store it in the same way? A noSQL database is designed to store unstructed information, e.g. data models that are not organized in tables, making them flexible and scalable. Examples of noSQL databases include e.g. Mongodb, CouchDB or AWS dynamodb.

In addition, traditional relational database engines - including SQLite and Postgres - can also store unstructured data in dedicated JSON fields.

The nodbi R package provides a unified interface to multiple noSQL implementations, including SQLite. (If you are interested in a deeper look at how to create & query a JSON field in SQLite with raw SQL, check out this gist ).

Creating & populating a noSQL database with the `nodbi` R package

To experiment with its noSQL mode, we create a temporary SQLite database in memory. (For real data, you definitely want to provide a file path as the dbname instead!)

src <- nodbi::src_sqlite(dbname = ":memory:")

Right now, the names of the gene sets are only stored as the names() of the list elements, e.g. not in a field within each sub-list itself. To make sure they are included in each database record, we add them to each sub-list in a new name field.

mh2 <- lapply(names(mh), \(gs) c("name" = gs, mh[[gs]]))

Unique identifiers

The docdb_create() function accepts either a data.frame, a JSON string or a list as its value argument.

If you include a field _id in your list, it will be used as the primary key for each element. If no _id field is found, then the _id field is created automatically with a call to the uuid::UUIDgenerate() function.

If you provide a data.frames() with row.names, they will be used to populate the _id field.

Now we are ready to create a new SQLite table hallmarks and populate it with the 50 gene sets.

docdb_create(src, key = "hallmarks", value = mh2)

[1] 50

We can retrieve the full set of records as a data.frame with the docdb_get() function. (Here we select a subset of the returned columns due to space constraints.) Because each gene set contains multiple geneSymbols, this field is a list-column.

docdb_get(src, "hallmarks")[1:4, c("name", "geneSymbols", "pmid")]

                          name  geneSymbols     pmid
1        HALLMARK_ADIPOGENESIS Abca1, A.... 30224793
2 HALLMARK_ALLOGRAFT_REJECTION Aars, Ab.... 30224793
3   HALLMARK_ANDROGEN_RESPONSE Abcc4, A.... 30224793
4        HALLMARK_ANGIOGENESIS Apoh, Ap.... 30224793

Querying with JSON filters

More commonly, users might want to retrieve one or more gene sets by name. The docdb_query() function accepts a query argument specifying the desired filter criteria (as MongoDB JSON ).

results <- nodbi::docdb_query(
  src = src, key = "hallmarks",
  query = '{"name": "HALLMARK_ADIPOGENESIS"}')
results[, c("name", "geneSymbols", "pmid")]

                   name  geneSymbols     pmid
1 HALLMARK_ADIPOGENESIS Abca1, A.... 30224793

The fields argument allows us to return only specific columns. (Specifying a field as 1 or 0 will include or exclude it, respectively.)

nodbi::docdb_query(
  src = src, key = "hallmarks",
  query = '{"name": "HALLMARK_ADIPOGENESIS"}',
  fields = '{"name": 1, "geneSymbols": 1}'
)

                   name  geneSymbols
1 HALLMARK_ADIPOGENESIS Abca1, A....

We can also identify gene sets containing at least one of the given gene symbols:

results <- nodbi::docdb_query(
  src = src, key = "hallmarks",
  query = paste0('{"$or":[',
                 '{"geneSymbols": "Abca1"},', 
                 '{"geneSymbols": "Gapdh"}',
                 ']}'),
  fields = '{"name": 1, "geneSymbols": 1}'
)

Unnesting columns

Because the set contains more than one geneSymbol, we obtain a nested data.frame. We can unnest it e.g. with the tidyr R package

tidyr::unnest(results, cols = c(geneSymbols))

# A tibble: 798 × 2
   name                  geneSymbols
   <chr>                 <chr>      
 1 HALLMARK_ADIPOGENESIS Abca1      
 2 HALLMARK_ADIPOGENESIS Abcb8      
 3 HALLMARK_ADIPOGENESIS Acaa2      
 4 HALLMARK_ADIPOGENESIS Acadl      
 5 HALLMARK_ADIPOGENESIS Acadm      
 6 HALLMARK_ADIPOGENESIS Acads      
 7 HALLMARK_ADIPOGENESIS Acly       
 8 HALLMARK_ADIPOGENESIS Aco2       
 9 HALLMARK_ADIPOGENESIS Acox1      
10 HALLMARK_ADIPOGENESIS Adcy6      
# ℹ 788 more rows

Querying using SQL

Formulating the queries as JSON strings is tedious, though. Alternatively, SQLite also supports querying JSON columns using SQL (muddying the border between noSQL and SQL). For example, we can use SQLite’s -> and ->> operators and the json_each() SQL function to create a query that returns the names of all gene sets that include e.g. the Abca1 gene:

SELECT hallmarks.json->>'name' as name
FROM hallmarks, json_each(hallmarks.json, '$.geneSymbols')
WHERE json_each.value LIKE '%Abca1%'

5 records
name
HALLMARK_ADIPOGENESIS
HALLMARK_BILE_ACID_METABOLISM
HALLMARK_INFLAMMATORY_RESPONSE
HALLMARK_PROTEIN_SECRETION
HALLMARK_TNFA_SIGNALING_VIA_NFKB

Depending on comfortable you are reading / writing SQL, this might be a nicer approach.

Limitations

SQLite’s JSON operators are somewhat limited, e.g. there is no straightforward way to ask whether a column contains one or more gene identifiers (e.g. the query we performed above using a query JSON string). Indexing a SQLite JSON column also comes with limitations.

The Postgres database engine supports JSON and binary JSONB fields) with indexing & additional operators like the @> contains operator.

noSQL summary

This example highlights some of the advantages of a noSQL solution:

Rapid ingestion of data without the need for a rigid schema.
Simple retrieval of individual object identified by their primary key.

But also some of the disadvantages:

Queries that descend into the (potentially nested) objects must be carefully constructed.
Increasing database performance with indices is more complicated than for relational databases (see below.)

Next, we will try another approach: reshaping the gene set collection into a set of tables and modeling the relationship between them.s

SQL: storing gene sets in a relational database

R has excellent support for interacting with relational database, e.g. via the foundational ‘Common Database Interface’ (DBI) package and the numerous database-specific packages built on top of it, including the RSQLite, RPostgres and many others.

To take advantage of a relational database we have perform a little more work up-front. But this effort is amply repaid by simplifying subsequent queries.

Learning from Bioconductor: BiocSet’s three tables

The BiocSet Class from the eponymous Bioconductor package represents a collection of gene sets in three tibbles. Let’s create a simple BiocSet with two gene sets for illustration:

set_names <- purrr::map_chr(mh2[1:2], "name")
gene_ids <- purrr::map(mh2[1:2], "geneSymbols")
es <- BiocSet(setNames(gene_ids, set_names))

The first two tibbles represent genes (called elements) and sets, respectively:

es_element: one row per gene

head(es_element(es))

# A tibble: 6 × 1
  element
  <chr>  
1 Abca1  
2 Abcb8  
3 Acaa2  
4 Acadl  
5 Acadm  
6 Acads

es_set: one row per gene set

es_set(es)

# A tibble: 2 × 1
  set                         
  <chr>                       
1 HALLMARK_ADIPOGENESIS       
2 HALLMARK_ALLOGRAFT_REJECTION

The third table establishes the many-to-many relationship between genes and sets, e.g. it tracks which gene is a member of each set.

es_elementset: gene x set combination

# we are showing 10 random rows
set.seed(42)
es_elementset(es)[sample(nrow(es_elementset(es)), size = 10), ]

# A tibble: 10 × 2
   element set                         
   <chr>   <chr>                       
 1 Coq5    HALLMARK_ADIPOGENESIS       
 2 Irf7    HALLMARK_ALLOGRAFT_REJECTION
 3 Qdpr    HALLMARK_ADIPOGENESIS       
 4 Elovl6  HALLMARK_ADIPOGENESIS       
 5 Cd28    HALLMARK_ALLOGRAFT_REJECTION
 6 Ppm1b   HALLMARK_ADIPOGENESIS       
 7 Mtarc2  HALLMARK_ADIPOGENESIS       
 8 Zap70   HALLMARK_ALLOGRAFT_REJECTION
 9 Ndufb7  HALLMARK_ADIPOGENESIS       
10 Il15    HALLMARK_ALLOGRAFT_REJECTION

Each of these tables can be augmented with additional metadata, e.g. we could add Entrez gene identifiers to the es_element (see below), or long-form descriptions for each set to the es_set tibble.

These three tables can easily be represented in a relational database, using the element and set columns as primary keys.

Creating and populating a relational database

Let’s start with a fresh SQLite database.

con <- dbConnect(RSQLite::SQLite(), ":memory:")

First, we create the geneset data.frame that lists all gene sets, and we also include their MSigDb URLs as metadata:

geneset <- data.frame(
  geneset = purrr::map_chr(mh2, "name"),
  url = purrr::map_chr(mh2, "msigdbURL"))
head(geneset)

                       geneset
1        HALLMARK_ADIPOGENESIS
2 HALLMARK_ALLOGRAFT_REJECTION
3   HALLMARK_ANDROGEN_RESPONSE
4        HALLMARK_ANGIOGENESIS
5     HALLMARK_APICAL_JUNCTION
6      HALLMARK_APICAL_SURFACE
                                                                                 url
1        https://www.gsea-msigdb.org/gsea/msigdb/mouse/geneset/HALLMARK_ADIPOGENESIS
2 https://www.gsea-msigdb.org/gsea/msigdb/mouse/geneset/HALLMARK_ALLOGRAFT_REJECTION
3   https://www.gsea-msigdb.org/gsea/msigdb/mouse/geneset/HALLMARK_ANDROGEN_RESPONSE
4        https://www.gsea-msigdb.org/gsea/msigdb/mouse/geneset/HALLMARK_ANGIOGENESIS
5     https://www.gsea-msigdb.org/gsea/msigdb/mouse/geneset/HALLMARK_APICAL_JUNCTION
6      https://www.gsea-msigdb.org/gsea/msigdb/mouse/geneset/HALLMARK_APICAL_SURFACE

Next, we identify all unique gene symbols, annotate them with their Entrez ids (using the org.Mm.eg.db Bioconductor annotation package), and store both identifier types in the element data.frame.

gene_symbols <- unique(unlist(purrr::map(mh2, "geneSymbols")))
element <- data.frame(
  element = gene_symbols,
  entrezid = mapIds(org.Mm.eg.db, keys = gene_symbols, keytype = "SYMBOL", 
                    column = "ENTREZID")
  )
head(element)

      element entrezid
Abca1   Abca1    11303
Abcb8   Abcb8    74610
Acaa2   Acaa2    52538
Acadl   Acadl    11363
Acadm   Acadm    11364
Acads   Acads    11409

Finally, we create the element_set join table, connecting gene sets to their constituent genes:

elementset <- purrr::map_df(mh2, \(gs) {
  with(gs, 
       data.frame(
         element = geneSymbols,
         geneset = name
       )
  )
})
head(elementset)

  element               geneset
1   Abca1 HALLMARK_ADIPOGENESIS
2   Abcb8 HALLMARK_ADIPOGENESIS
3   Acaa2 HALLMARK_ADIPOGENESIS
4   Acadl HALLMARK_ADIPOGENESIS
5   Acadm HALLMARK_ADIPOGENESIS
6   Acads HALLMARK_ADIPOGENESIS

Next, we write each data.frame into a separate table in our SQLite database.

Verifying foreign keys

By default, SQLite does not verify that foreign keys actually exist in the referenced table. To make this a requirement, we can enable checking with the following command:

dbExecute(con, 'PRAGMA foreign_keys = 1;')

[1] 0

dbExecute(con, 
          "CREATE TABLE tbl_geneset (geneset TEXT PRIMARY KEY, url TEXT)")

[1] 0

dbWriteTable(con, name = "tbl_geneset", value = geneset, overwrite = TRUE)

dbExecute(con, 
          "CREATE TABLE tbl_element (element TEXT PRIMARY KEY, entrezid TEXT)")

[1] 0

dbWriteTable(con, name = "tbl_element", value = element, overwrite = TRUE)

dbExecute(con, paste(
  "CREATE TABLE tbl_elementset (",
  "element TEXT,", 
  "geneset TEXT,",
  "FOREIGN KEY(geneset) REFERENCES tbl_geneset(geneset),",
  "FOREIGN KEY(element) REFERENCES tbl_element(element)",
  ")")
  )

[1] 0

dbWriteTable(con, name = "tbl_elementset", value = elementset, overwrite = TRUE)

dbListTables(con)

[1] "tbl_element"    "tbl_elementset" "tbl_geneset"

Plotting relationships

As we create and need to keep track of multiple tables, it is useful to visualize their contents (fields, columns) and relationships in a model diagram. The awesome dm R package, designed to bring an existing relational data model into your R session, can be used to generate diagrams like the one shown below. (dm can identify the keys in postgres and SQL server database engines automatically, but for SQLite we need to specify them ourselves with the dm_add_pk() and dm_add_fk() functions.)

dm_from_con(con, learn_keys = FALSE) %>%
  dm_add_pk(tbl_element, element) %>%
  dm_add_pk(tbl_geneset, geneset) %>%
  dm_add_fk(tbl_elementset, element, tbl_element) %>%
  dm_add_fk(tbl_elementset, geneset, tbl_geneset) %>%
  dm_draw(view_type = "all")

Model diagram

Querying the database

Great! Now we are ready to query our database. To make our lives easier, we will use the dplyr package to translate our R syntax into SQL. (But we could just as well use plain SQL instead.)

First we define the remote tables by connecting to our brand new database:

tbl_geneset <- tbl(con, "tbl_geneset")
tbl_element <- tbl(con, "tbl_element")
tbl_elementset <- tbl(con, "tbl_elementset")

Let’s return the gene symbols and entrez identifiers that make up the HALLMARK_APOPTOSIS gene set and display the first 5 (in alphabetical order of the gene symbols).

result <- tbl_elementset %>% 
  dplyr::filter(geneset == "HALLMARK_ADIPOGENESIS") %>%
  dplyr::inner_join(tbl_element, by = "element") %>%
  dplyr::slice_min(n = 5, order_by = element)
result

# Source:   SQL [5 x 3]
# Database: sqlite 3.41.2 [:memory:]
  element geneset               entrezid
  <chr>   <chr>                 <chr>   
1 Abca1   HALLMARK_ADIPOGENESIS 11303   
2 Abcb8   HALLMARK_ADIPOGENESIS 74610   
3 Acaa2   HALLMARK_ADIPOGENESIS 52538   
4 Acadl   HALLMARK_ADIPOGENESIS 11363   
5 Acadm   HALLMARK_ADIPOGENESIS 11364

And now let’s add the gene set’s URL as well:

result %>%
  dplyr::left_join(tbl_geneset, by = "geneset")

# Source:   SQL [5 x 4]
# Database: sqlite 3.41.2 [:memory:]
  element geneset               entrezid url                                    
  <chr>   <chr>                 <chr>    <chr>                                  
1 Abca1   HALLMARK_ADIPOGENESIS 11303    https://www.gsea-msigdb.org/gsea/msigd…
2 Abcb8   HALLMARK_ADIPOGENESIS 74610    https://www.gsea-msigdb.org/gsea/msigd…
3 Acaa2   HALLMARK_ADIPOGENESIS 52538    https://www.gsea-msigdb.org/gsea/msigd…
4 Acadl   HALLMARK_ADIPOGENESIS 11363    https://www.gsea-msigdb.org/gsea/msigd…
5 Acadm   HALLMARK_ADIPOGENESIS 11364    https://www.gsea-msigdb.org/gsea/msigd…

Pulling data into a BiocSet

Finally, we can easily pull selected (or even all) gene sets into a Bioconductor BiocSet object for analysis in R. Importantly, the database does not require us to use R: e.g. python users can connect to the same SQLite database (e.g. using sqlalchemy ) and retrieve the information in whatever form is most useful to them.

For example, let’s retrieve all gene sets whose name ends in the letter N, store them in a list and create a BiocSet object.

gene_set_list <- with(
  tbl_elementset %>% 
    dplyr::filter(geneset %like% '%N') %>%
    collect(), 
  split(element, geneset)
)
es <- BiocSet(gene_set_list)

Next, we add gene set metadata to the es_set tibble, by joining it with the (richer) information in the database. This will add the url column.

es <- left_join_set(es, 
  tbl_geneset, by = c(set = "geneset"), 
  copy = TRUE
)
es_set(es)

# A tibble: 8 × 2
  set                                        url                                
  <chr>                                      <chr>                              
1 HALLMARK_ALLOGRAFT_REJECTION               https://www.gsea-msigdb.org/gsea/m…
2 HALLMARK_APICAL_JUNCTION                   https://www.gsea-msigdb.org/gsea/m…
3 HALLMARK_COAGULATION                       https://www.gsea-msigdb.org/gsea/m…
4 HALLMARK_EPITHELIAL_MESENCHYMAL_TRANSITION https://www.gsea-msigdb.org/gsea/m…
5 HALLMARK_KRAS_SIGNALING_DN                 https://www.gsea-msigdb.org/gsea/m…
6 HALLMARK_OXIDATIVE_PHOSPHORYLATION         https://www.gsea-msigdb.org/gsea/m…
7 HALLMARK_PROTEIN_SECRETION                 https://www.gsea-msigdb.org/gsea/m…
8 HALLMARK_UV_RESPONSE_DN                    https://www.gsea-msigdb.org/gsea/m…

And finally, let’s also add the entrezid column from out database to the es_element table:

es <- left_join_element(es, 
  tbl_element, by = "element", 
  copy = TRUE
)
es_element(es)

# A tibble: 1,233 × 2
   element entrezid
   <chr>   <chr>   
 1 Aars    234734  
 2 Abce1   24015   
 3 Abi1    11308   
 4 Ache    11423   
 5 Acvr2a  11480   
 6 Akt1    11651   
 7 Apbb1   11785   
 8 B2m     12010   
 9 Bcat1   12035   
10 Bcl10   12042   
# ℹ 1,223 more rows

SQL summary

For this example the effort required to transform the dataset into a set of three tables - the starting point for import into a relational database - was minimal.
Given the use case, e.g. management of a gene set collections, the number of times that data is added to the database is likely much smaller than the number of times it is queried. That makes it worth the effort to transform it once - and benefit from this upfront cost ever after.
Because we knew exactly which properties / annotations we wanted to capture in the database, defining the database tables and their relationships (e.g. the schema ) was not an obstacle, either.
Enabling users to query the data using simple SQL or via a higher level abstraction like dplyr makes it accessible to a broader audience.

Defining a schema is much harder when we deal with datasets that are less standardized, deeply nested, changing over time, etc.

References

If you are new to working with databases, then you might find these two great books useful:

SQL for Data Scientists: A Beginner’s Guide for Building Datasets for Analysis by Renee M. P. Teate is a great starting place to learn SQL. It mainly focusses on accessing existing databases.
Practical SQL: A Beginner’s Guide to Storytelling with Data by Anthony DeBarros teaches readers how to create & populate a Postgres database, and how to index and search it effectively.

SessionInfo

─ Session info ───────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.3.1 (2023-06-16)
 os       macOS Ventura 13.5.1
 system   aarch64, darwin20
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       America/Los_Angeles
 date     2023-08-30
 pandoc   3.1.1 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/ (via rmarkdown)

─ Packages ───────────────────────────────────────────────────────────────────
 ! package          * version   date (UTC) lib source
 P AnnotationDbi    * 1.62.2    2023-07-02 [?] Bioconductor
 P askpass            1.1       2019-01-13 [?] CRAN (R 4.3.0)
 P backports          1.4.1     2021-12-13 [?] CRAN (R 4.3.0)
 P Biobase          * 2.60.0    2023-05-08 [?] Bioconductor
 P BiocGenerics     * 0.46.0    2023-06-04 [?] Bioconductor
 P BiocIO             1.10.0    2023-05-08 [?] Bioconductor
 P BiocManager        1.30.22   2023-08-08 [?] CRAN (R 4.3.0)
 P BiocSet          * 1.14.0    2023-05-08 [?] Bioconductor
 P Biostrings         2.68.1    2023-05-21 [?] Bioconductor
 P bit                4.0.5     2022-11-15 [?] CRAN (R 4.3.0)
 P bit64              4.0.5     2020-08-30 [?] CRAN (R 4.3.0)
 P bitops             1.0-7     2021-04-24 [?] CRAN (R 4.3.0)
 P blob               1.2.4     2023-03-17 [?] CRAN (R 4.3.0)
 P cachem             1.0.8     2023-05-01 [?] CRAN (R 4.3.0)
 P cli                3.6.1     2023-03-23 [?] CRAN (R 4.3.0)
 P crayon             1.5.2     2022-09-29 [?] CRAN (R 4.3.0)
 R credentials        1.3.2     <NA>       [?] <NA>
 P DBI                1.1.3     2022-06-18 [?] CRAN (R 4.3.0)
 P dbplyr             2.3.3     2023-07-07 [?] CRAN (R 4.3.0)
 P DiagrammeR       * 1.0.10    2023-05-18 [?] CRAN (R 4.3.0)
 P digest             0.6.33    2023-07-07 [?] CRAN (R 4.3.0)
 P dm               * 1.0.6     2023-07-21 [?] CRAN (R 4.3.0)
 P dplyr            * 1.1.2     2023-04-20 [?] CRAN (R 4.3.0)
 P ellipsis           0.3.2     2021-04-29 [?] CRAN (R 4.3.0)
 P evaluate           0.21      2023-05-05 [?] CRAN (R 4.3.0)
 P fansi              1.0.4     2023-01-22 [?] CRAN (R 4.3.0)
 P fastmap            1.1.1     2023-02-24 [?] CRAN (R 4.3.0)
 P generics           0.1.3     2022-07-05 [?] CRAN (R 4.3.0)
 P GenomeInfoDb       1.36.1    2023-07-02 [?] Bioconductor
 P GenomeInfoDbData   1.2.10    2023-08-23 [?] Bioconductor
 P glue               1.6.2     2022-02-24 [?] CRAN (R 4.3.0)
 P htmltools          0.5.6     2023-08-10 [?] CRAN (R 4.3.0)
 P htmlwidgets        1.6.2     2023-03-17 [?] CRAN (R 4.3.0)
 P httpuv             1.6.11    2023-05-11 [?] CRAN (R 4.3.0)
 P httr               1.4.7     2023-08-15 [?] CRAN (R 4.3.0)
 P igraph             1.5.1     2023-08-10 [?] CRAN (R 4.3.0)
 P IRanges          * 2.34.1    2023-07-02 [?] Bioconductor
 P jsonlite         * 1.8.7     2023-06-29 [?] CRAN (R 4.3.0)
 P KEGGREST           1.40.0    2023-05-08 [?] Bioconductor
 P knitr              1.43      2023-05-25 [?] CRAN (R 4.3.0)
 P later              1.3.1     2023-05-02 [?] CRAN (R 4.3.0)
 P lifecycle          1.0.3     2022-10-07 [?] CRAN (R 4.3.0)
 P magrittr           2.0.3     2022-03-30 [?] CRAN (R 4.3.0)
 P memoise            2.0.1     2021-11-26 [?] CRAN (R 4.3.0)
 P mime               0.12      2021-09-28 [?] CRAN (R 4.3.0)
 P nodbi            * 0.9.6     2023-08-07 [?] CRAN (R 4.3.0)
 P ontologyIndex      2.11      2023-05-30 [?] CRAN (R 4.3.0)
 P openssl            2.1.0     2023-07-15 [?] CRAN (R 4.3.0)
 P org.Mm.eg.db     * 3.17.0    2023-08-23 [?] Bioconductor
 P pillar             1.9.0     2023-03-22 [?] CRAN (R 4.3.0)
 P pkgconfig          2.0.3     2019-09-22 [?] CRAN (R 4.3.0)
 P plyr               1.8.8     2022-11-11 [?] CRAN (R 4.3.0)
 P png                0.1-8     2022-11-29 [?] CRAN (R 4.3.0)
 P promises           1.2.1     2023-08-10 [?] CRAN (R 4.3.0)
 P purrr            * 1.0.2     2023-08-10 [?] CRAN (R 4.3.0)
 P R6                 2.5.1     2021-08-19 [?] CRAN (R 4.3.0)
 P RColorBrewer       1.1-3     2022-04-03 [?] CRAN (R 4.3.0)
 P Rcpp               1.0.11    2023-07-06 [?] CRAN (R 4.3.0)
 P RCurl              1.98-1.12 2023-03-27 [?] CRAN (R 4.3.0)
   renv               1.0.2     2023-08-15 [1] CRAN (R 4.3.0)
 P rlang              1.1.1     2023-04-28 [?] CRAN (R 4.3.0)
 P rmarkdown          2.24      2023-08-14 [?] CRAN (R 4.3.0)
 P RSQLite          * 2.3.1     2023-04-03 [?] CRAN (R 4.3.0)
 P rstudioapi         0.15.0    2023-07-07 [?] CRAN (R 4.3.0)
 P S4Vectors        * 0.38.1    2023-05-08 [?] Bioconductor
 P sessioninfo        1.2.2     2021-12-06 [?] CRAN (R 4.3.0)
 P shiny              1.7.5     2023-08-12 [?] CRAN (R 4.3.0)
 P stringi            1.7.12    2023-01-11 [?] CRAN (R 4.3.0)
 P sys                3.4.2     2023-05-23 [?] CRAN (R 4.3.0)
 P tibble           * 3.2.1     2023-03-20 [?] CRAN (R 4.3.0)
 P tidyr            * 1.3.0     2023-01-24 [?] CRAN (R 4.3.0)
 P tidyselect         1.2.0     2022-10-10 [?] CRAN (R 4.3.0)
 P utf8               1.2.3     2023-01-31 [?] CRAN (R 4.3.0)
 P uuid               1.1-1     2023-08-17 [?] CRAN (R 4.3.0)
 P vctrs              0.6.3     2023-06-14 [?] CRAN (R 4.3.0)
 P visNetwork         2.1.2     2022-09-29 [?] CRAN (R 4.3.0)
 P withr              2.5.0     2022-03-03 [?] CRAN (R 4.3.0)
 P xfun               0.40      2023-08-09 [?] CRAN (R 4.3.0)
 P xtable             1.8-4     2019-04-21 [?] CRAN (R 4.3.0)
 P XVector            0.40.0    2023-05-08 [?] Bioconductor
 P yaml               2.3.7     2023-01-23 [?] CRAN (R 4.3.0)
 P zlibbioc           1.46.0    2023-05-08 [?] Bioconductor

 [1] /Users/sandmann/repositories/blog/renv/library/R-4.3/aarch64-apple-darwin20
 [2] /Users/sandmann/Library/Caches/org.R-project.R/R/renv/sandbox/R-4.3/aarch64-apple-darwin20/ac5c2659

 P ── Loaded and on-disk path mismatch.
 R ── Package was removed from disk.

──────────────────────────────────────────────────────────────────────────────

This work is licensed under a Creative Commons Attribution 4.0 International License.

Creating, polulating and querying SQL and noSQL databases with R

Motivation

The Mouse Hallmarks MSigDB collection

noSQL: storing gene sets as unstructured documents

Creating & populating a noSQL database with the nodbi R package

Querying with JSON filters

Querying using SQL

noSQL summary

SQL: storing gene sets in a relational database

Learning from Bioconductor: BiocSet’s three tables

Creating and populating a relational database

Plotting relationships

Querying the database

Pulling data into a BiocSet

SQL summary

References

Creating & populating a noSQL database with the `nodbi` R package