This document describes how to add a new SQL backend to dbplyr. To begin:

  • Ensure that you have a DBI compliant database backend. If not, you’ll need to first create it by following the instructions in vignette("backend", package = "DBI").

  • You’ll need a working knowledge of S3. Make sure that you’re familiar with the basics before you start.

This document is still a work in progress, but it will hopefully get you started. I’d also strongly recommending the bundled source code for SQLite, MySQL, and PostgreSQL.

First steps

For interactive exploation, attach dplyr and DBI. If you’re creating a package, you’ll need to import dplyr and DBI.

library(dplyr)
library(DBI)

Check that you can create a tbl from a connection, like:

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
DBI::dbWriteTable(con, "mtcars", mtcars)
#> [1] TRUE

tbl(con, "mtcars")
#> Source:     table<mtcars> [?? x 11]
#> Database:   sqlite 3.11.1 []
#> 
#> # S3: tbl_dbi
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21.0     6   160   110  3.90 2.620 16.46     0     1     4     4
#> 2  21.0     6   160   110  3.90 2.875 17.02     0     1     4     4
#> 3  22.8     4   108    93  3.85 2.320 18.61     1     1     4     1
#> 4  21.4     6   258   110  3.08 3.215 19.44     1     0     3     1
#> # ... with more rows

If you can’t, this likely indicates some problem with the DBI methods. Use DBItest to narrow down the problem.

Now is a good time to implement a method for db_desc(). This should briefly describe the connection, typically formatting the information returned form dbGetInfo(). This is what dbplyr does for Postgres connections:

#' @export
db_desc.PostgreSQLConnection <- function(x) {
  info <- dbGetInfo(x)
  host <- if (info$host == "") "localhost" else info$host

  paste0("postgres ", info$serverVersion, " [", info$user, "@",
    host, ":", info$port, "/", info$dbname, "]")
}

Coping, computing, collecting and collapsing

Next, check that copy_to(), collapse(), compute(), and collect() work.

  • If copy_to() fails, it’s likely you need a method for db_write_table(), db_create_indexes() or db_analyze().

  • If collapse() fails, your database has a non-standard way of constructing subqueries. Add a method for sql_subquery().

  • If compute() fails, your database has a non-standard way of saving queries in temporary tables. Add a method for db_save_query().

SQL translation

Make sure you’ve read vignette("sql-translation") so you have the lay of the land.

Verbs

Check that SQL translation for the key verbs work:

  • summarise(), mutate(), filter() etc: powered by sql_select()
  • left_join(), inner_join(): powered by sql_join()
  • semi_join(), anti_join(): powered by sql_semi_join()
  • union(), intersect(), setdiff(): powered by sql_set_op()

Vectors

Finially, you may way to provide custom R -> SQL translation at the vector level by providing a method for src_translate_env(). This function should return an object created by sql_variant(). See existing methods for examples.