If your target table is in a database, the db_tbl() function is a handy way of accessing it. This function simplifies the process of getting a tbl_dbi object, which usually involves a combination of building a connection to a database and using the dplyr::tbl() function with the connection and the table name (or a reference to a table in a schema). You can use db_tbl() as the basis for obtaining a database table for the read_fn parameter in create_agent() or create_informant(). This can be done by using a leading ~ before the db_tbl() call (e.g,. read_fn = ~db_tbl(...)). Another great option is supplying a table-prep formula involving db_tbl() to tbl_store() so that you have access to database tables though single names via a table store.

The username and password are supplied though environment variables. If desired, these can be supplied directly by enclosing those values in I().

db_tbl(
table,
dbname,
dbtype,
host = NULL,
port = NULL,
user = NULL,
)

## Arguments

table The name of the table, or, a reference to a table in a schema (two-element vector with the names of schema and table). Alternatively, this can be supplied as a data table to copy into an in-memory database connection. This only works if: (1) the db is either "sqlite" or "duckdb", (2) the dbname was chosen as ":memory:", and (3) the data_tbl is a data frame or a tibble object. The database name. Either an appropriate driver function (e.g., RPostgres::Postgres()) or a shortname for the database type. Valid names are: "postgresql", "postgres", or "pgsql" (PostgreSQL, using the RPostgres::Postgres() driver function); "mysql" (MySQL, using RMySQL::MySQL()); "duckdb" (DuckDB, using duckdb::duckdb()); and "sqlite" (SQLite, using RSQLite::SQLite()). The database host and optional port number. The environment variables used to access the username and password for the database.

## Value

A tbl_dbi object.

## Function ID

1-6

Other Planning and Prep: action_levels(), create_agent(), create_informant(), draft_validation(), file_tbl(), scan_data(), tbl_get(), tbl_source(), tbl_store(), validate_rmd()

## Examples

# You can use an in-memory database
# table and supply an in-memory table
# to it too:
small_table_duckdb <-
db_tbl(
table = small_table,
dbname = ":memory:",
dbtype = "duckdb"
)

if (interactive()) {

# It's also possible to obtain a remote
# file and shove it into an in-memory
# database; use the all-powerful
# file_tbl() + db_tbl() combo
all_revenue_large_duckdb <-
db_tbl(
table = file_tbl(
file = from_github(
file = "all_revenue_large.rds",
repo = "rich-iannone/intendo",
subdir = "data-large"
)
),
dbname = ":memory:",
dbtype = "duckdb"
)

# For remote databases, it's much the
# same; here's an example that accesses
# the rna table (in the RNA Central
# public database) using db_tbl()
rna_db_tbl <-
db_tbl(
table = "rna",
dbname = "pfmegrnargs",
dbtype = "postgres",
host = "hh-pgsql-public.ebi.ac.uk",
port = 5432,
)

# Using I() for the user name and
# password means that you're passing in
# the actual values but, normally, you
# would want use the names of environment
# variables (envvars) to securely access
# values when connecting to a DB:
example_db_tbl <-
db_tbl(
table = "<table_name>",
dbname = "<database_name>",
dbtype = "<database_type_shortname>",
host = "<connection_url>",
port = "<connection_port>",
user = "<DB_USER_NAME>",
)

# Environment variables can be created
# by editing the user .Renviron file and
# the usethis::edit_r_environ() function
# makes this pretty easy to do

# Storing table-prep formulas in a table
# store makes it easier to work with DB
# tables in pointblank; here's how to
# generate a table store with two named
# entries for table preparations
tbls <-
tbl_store(
small_table_duck ~ db_tbl(
table = pointblank::small_table,
dbname = ":memory:",
dbtype = "duckdb"
),
small_high_duck ~ db_tbl(
table = pointblank::small_table,
dbname = ":memory:",
dbtype = "duckdb"
) %>%
dplyr::filter(f == "high")
)

# Now it's easy to access either of these
# tables (the second is a mutated version)
# via the tbl_get() function
tbl_get("small_table_duck", store = tbls)
tbl_get("small_high_duck", store = tbls)

# The table-prep formulas in tbls
# could also be used in functions with
# the read_fn argument; this is thanks
# to the tbl_source() function
agent <-
create_agent(
"small_table_duck",
store = tbls
)
)

informant <-
create_informant(