README.Rmd

<!-- README.md is generated from README.Rmd. Please edit that file -->

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%",
  error = FALSE
)
```

# dbcooper

<!-- badges: start -->
![R-CMD-check](https://github.com/pipeline-tools/dbcooper/workflows/R-CMD-check/badge.svg)
<!-- badges: end -->

The dbcooper package turns a database connection into a collection of functions, handling logic for keeping track of connections and letting you take advantage of autocompletion when exploring a database.

It's especially helpful to use when authoring database-specific R packages, for instance in an internal company package or one wrapping a public data source.

The package's name is a reference to the bandit [D.B. Cooper](https://en.wikipedia.org/wiki/D._B._Cooper).

* For the Python version of the package, see [machow/dbcooper-py](https://github.com/machow/dbcooper-py).
* For an example of database packages created with dbcooper, see [stackbigquery](https://github.com/dgrtwo/stackbigquery/) or [lahmancooper](https://github.com/pipeline-tools/lahmancooper)
* For some slides about the package, see [here](http://varianceexplained.org/files/dbcooper-rstudio-conf-2022.pdf)

## Installation

You can install the development version from [GitHub](https://github.com/) with:

``` r
# install.packages("devtools")
devtools::install_github("pipeline-tools/dbcooper")
```

## Example

### Initializing the functions

The dbcooper package asks you to create the connection first. As an example, we'll use the Lahman baseball database packaged with dbplyr.

```{r message = FALSE}
library(dplyr)

lahman_db <- dbplyr::lahman_sqlite()
lahman_db
```

You set up dbcooper with the `dbc_init` function, passing it a prefix `lahman` that will apply to all the functions it creates.

```{r}
library(dbcooper)
dbc_init(lahman_db, "lahman")
```

`dbc_init` then creates user-friendly accessor functions in your global environment. (You could also pass it an environment in which the functions will be created).

### Using database functions

`dbc_init` adds several functions when it initializes a database source. In this case, each will start with the `lahman_` prefix.

* `_list`: Get a list of tables
* `_tbl`: Access a table that can be worked with in dbplyr
* `_query`: Perform of a SQL query and work with the result
* `_execute`: Execute a query (such as a `CREATE` or `DROP`)
* `_src`: Retrieve a `dbi_src` for the database

For instance, we could start by finding the names of the tables in the Lahman database.

```{r}
lahman_list()
```

We can access one of these tables with `lahman_tbl()`, then put it through any kind of dplyr operation.

```{r}
lahman_tbl("Batting")

lahman_tbl("Batting") %>%
  count(teamID, sort = TRUE)
```

If we'd rather write SQL than dplyr, we could also run `lahman_query()` (which can also take a filename).

```{r}
lahman_query("SELECT
                playerID,
                sum(AB) as AB
              FROM Batting
              GROUP BY playerID")
```

Finally, `lahman_execute()` is for commands like `CREATE` and `DROP` that don't return a table, but rather execute a command on the database.

```{r}
lahman_execute("CREATE TABLE Players AS
                  SELECT playerID, SUM(AB) AS AB
                  FROM Batting
                  GROUP BY playerID")

lahman_tbl("Players")

lahman_execute("DROP TABLE Players")
```

### Autocompleted tables

Besides the `_list`, `_tbl`, `_query`, and `_execute` functions, the package also creates auto-completed table accessors.

```{r}
# Same result as lahman_tbl("Batting")
lahman_batting()

# Same result as lahman_tbl("Managers") %>% count()
lahman_managers() %>%
  count()
```

These are useful because they let you use auto-complete to complete table names as you're exploring a data source.

## Code of Conduct

Please note that the 'dbcooper' project is released with a
[Contributor Code of Conduct](https://github.com/pipeline-tools/dbcooper/blob/main/CODE_OF_CONDUCT.md).
By contributing to this project, you agree to abide by its terms.