Skip to contents

Query a database

Source: R/query.R
db_query.Rd

  • db_query() runs a SQL query and returns a data.table. Given a path instead of a connection, it opens the database read-only (with the snapshot fallback of db_connect_read_only()), runs the query and always closes the connection, even if the query fails: this is how a downstream project should read an upstream database, without ever holding its lock.

  • db_scalar() returns the first value of the first row (a count, a maximum).

  • db_quote() quotes strings as SQL literals, for the rare cases where a value cannot be passed as a parameter (e.g. inside a COPY statement).

Usage

db_query(con, sql, params = NULL, register = NULL)

db_scalar(con, sql, params = NULL)

db_quote(con, x)

Arguments

con

A DBI connection, or the path of a .duckdb file.

sql

Character. One SQL statement; may contain ? placeholders.

params

List or NULL. Values bound to the ? placeholders, in order. Binding values instead of pasting them into the SQL avoids quoting problems (titles with apostrophes) and SQL injection.

register

Named list of data frames, or NULL. Each one is exposed to the query as a virtual table named after its list element, and removed afterwards. This is how to filter on thousands of identifiers: join on a registered table rather than building a huge IN (...) string.

x

Character vector of values to quote.

Value

db_query(): a data.table. db_scalar(): a vector of length one (NA if the query returns no row). db_quote(): a character vector.

Details

Filter and aggregate in SQL before loading into R: a text table of a few tens of gigabytes does not fit in memory, but its counts by year do.

See also

Other queries: db_tables()

Examples

path <- tempfile(fileext = ".duckdb")
con <- db_connect(path)
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "document", data.frame(
  doc_id = c("ecb:1", "ecb:2", "boe:1"),
  year = c(2001L, 2005L, 2005L)
))

db_query(con, "SELECT * FROM document WHERE year = ?", params = list(2005L))
db_query(
  con,
  "SELECT d.* FROM document d JOIN wanted w USING (doc_id)",
  register = list(wanted = data.frame(doc_id = c("ecb:2", "boe:1")))
)
db_scalar(con, "SELECT count(*) FROM document")
#> [1] 3
db_disconnect(con)

# A downstream project reads with the path: no connection is left open.
db_query(path, "SELECT count(*) AS n FROM document")
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.