duckdb: Connect to a DuckDB database instance

In duckdb: DBI Package for the DuckDB Database Management System

Connect to a DuckDB database instance

Description

duckdb() creates or reuses a database instance.

duckdb_shutdown() shuts down a database instance.

Return an adbcdrivermanager::adbc_driver() for use with Arrow Database Connectivity via the adbcdrivermanager package.

dbConnect() connects to a database instance.

dbDisconnect() closes a DuckDB database connection. The associated DuckDB database instance is shut down automatically, it is no longer necessary to set shutdown = TRUE or to call duckdb_shutdown().

Usage

duckdb(
  dbdir = DBDIR_MEMORY,
  read_only = FALSE,
  bigint = "numeric",
  config = list(),
  ...,
  home = NULL,
  shared_home = NULL,
  allow_extensions = NULL,
  environment_scan = FALSE
)

duckdb_shutdown(drv)

duckdb_adbc()

## S4 method for signature 'duckdb_driver'
dbConnect(
  drv,
  dbdir = DBDIR_MEMORY,
  ...,
  debug = getOption("duckdb.debug", FALSE),
  read_only = FALSE,
  timezone_out = "UTC",
  tz_out_convert = c("with", "force"),
  config = list(),
  bigint = "numeric",
  array = "none",
  geometry = "blob",
  map = "data.frame"
)

## S4 method for signature 'duckdb_connection'
dbDisconnect(conn, ..., shutdown = TRUE)

Arguments

dbdir	Location for database files. Should be a path to an existing directory in the file system. With the default (or ""), all data is kept in RAM.
read_only	Set to TRUE for read-only operation. For file-based databases, this is only applied when the database file is opened for the first time. Subsequent connections (via the same drv object or a drv object pointing to the same path) cannot apply it, and fail rather than ignoring it.
bigint	How 64-bit integers should be returned. There are two options: "numeric" and "integer64". If "numeric" is selected, bigint integers will be treated as double/numeric. If "integer64" is selected, bigint integers will be set to bit64 encoding.
config	Named list with DuckDB configuration flags, see https://duckdb.org/docs/configuration/overview#configuration-reference for the possible options. These flags are only applied when the database object is instantiated. Subsequent connections cannot apply them, and fail rather than ignoring them.
...	These dots are for future extensions and must be empty.
home	Root directory for DuckDB's downloaded extensions and stored secrets. NULL (the default) resolves the location as described in duckdb_storage: an existing ⁠~/.duckdb⁠, else a per-session temporary directory (with an offer to create ⁠~/.duckdb⁠ in interactive sessions). Pass a path to use it as the root explicitly, creating it if needed. Cannot be combined with shared_home. Applied only when the database instance is created; see the ‘Database instances and driver reuse’ section.
shared_home	Opt in or out of the shared ⁠~/.duckdb⁠ location, overriding the automatic resolution. One of: NULL (the default) – resolve automatically (see duckdb_storage). This is the safe default. TRUE – store extensions and secrets under ⁠~/.duckdb⁠, creating that directory if it does not exist. This is a good setting for permanent deployments (Posit Connect, Shiny, APIs). Do not use on CRAN or on other infrastructure where you don't own ⁠~/.duckdb⁠. The setting is a durable, machine-level side effect that is not scoped to the current session: the directory persists after R exits, is reused by every future R session (and by the DuckDB CLI, Python and other clients that share ⁠~/.duckdb⁠), and any secrets written there outlive this process. Applying this setting repeatedly is a fast no-op. FALSE – use a per-session temporary directory even if ⁠~/.duckdb⁠ already exists. Nothing persists beyond the session. Cannot be combined with home. Applied only when the database instance is created; see the ‘Database instances and driver reuse’ section.
allow_extensions	Whether this driver may load DuckDB extensions (INSTALL / LOAD). One of: NULL (the default) – decide automatically. Extensions are enabled, except on an affected Linux build (one not compiled with ⁠libstdc++⁠), where they are disabled and a throttled advisory message is shown. See the ‘DuckDB extensions on Linux’ section. TRUE – force-enable extensions, attempting to load them even on an affected build (which may crash R). No message. FALSE – disable extensions and silence the advisory message. The argument takes precedence over the duckdb.allow_extensions option (a scalar logical) and the DUCKDB_R_ALLOW_EXTENSIONS environment variable (a value R reads as TRUE enables extensions and FALSE disables them; unset, empty, or any other value is undecided). Applied only when the database instance is created; see the ‘Database instances and driver reuse’ section.
environment_scan	Set to TRUE to treat data frames from the calling environment as tables. If a database table with the same name exists, it takes precedence. The default of this setting may change in a future version.
drv	Object returned by duckdb()
debug	Print additional debug information, such as queries.
timezone_out	The time zone in which plain TIMESTAMP columns (without time zone) are returned to R, defaults to "UTC". If you want to display datetime values in the local timezone, set to Sys.timezone() or "". TIMESTAMPTZ columns follow the session's TimeZone setting instead.
tz_out_convert	How to convert timestamp columns to the timezone specified in timezone_out. There are two options: "with", and "force". If "with" is chosen, the timestamp will be returned as it would appear in the specified time zone. If "force" is chosen, the timestamp will have the same clock time as the timestamp in the database, but with the new time zone.
array	How arrays should be returned. There are two options: "none" and "matrix". If "none" is selected, arrays are not returned. Instead an error is generated. If "matrix" is selected, arrays are returned as a column matrix. Each array is one row in the matrix.
geometry	How geometry columns should be returned. There are two options: "blob" and "wk". If "blob" is selected, geometry columns are returned as a list of raw vectors containing WKB data. If "wk" is selected, geometry columns are returned as wk wk_wkb vectors. Use wk::wk_handle() or sf::st_as_sfc() to convert to other geometry formats.
map	How MAP columns should be returned. There are two options: "data.frame" and "list_of". If "data.frame" is selected (the default), MAP columns are returned as a list of data frames with key and value columns. If "list_of" is selected, MAP columns are returned as a vctrs::list_of() whose ptype is a ⁠data.frame(key = <K>, value = <V>)⁠ that records the SQL key/value types. This enables MAP columns to round-trip through dbWriteTable() / dbCreateTable() without specifying field.types, and lets scans accept named-list cells as MAP entries.
conn	A duckdb_connection object
shutdown	Unused. The database instance is shut down automatically.

Details

The behavior of with = "force" at DST transitions depends on how R handles translation from the underlying time representation to a human-readable format. If the timestamp is invalid in the target timezone, the resulting value may be NA or an adjusted time.

Value

duckdb() returns an object of class duckdb_driver.

dbDisconnect() and duckdb_shutdown() are called for their side effect.

An object of class "adbc_driver"

dbConnect() returns an object of class duckdb_connection.

Database instances and driver reuse

duckdb() returns a driver object that owns a DuckDB database instance. dbConnect() opens connections to that instance, and many connections can share one instance.

For a file-based dbdir, the instance is cached, keyed by the (normalized) path: calling duckdb() again with the same dbdir returns the same driver and instance while it is still alive. This is deliberate. DuckDB allows only a single read-write handle to a database file at a time, so opening a second instance of the same file fails with a lock error in another process, and, on Linux and macOS, is not prevented at all within the same one. Reusing one instance instead lets any number of dbConnect(duckdb(dbdir = "my.db")) calls share it. An in-memory database (⁠:memory:⁠, the default) has no file to lock and is never cached: every duckdb() call creates a fresh, isolated instance.

The key is the path as the engine resolves it, not as normalizePath() does. DuckDB canonicalizes the longest part of the path that exists and appends the rest, so a database that does not exist yet gets the key it will keep once created, and two spellings of one database (a relative path, a symlink, a different separator) share an instance instead of each opening their own. A path that resolves no further is used as it stands rather than refused. Symbolic links are not supported on Windows, where creating one takes administrator rights or Developer Mode: there, a symlink to a database that does not exist yet fails to open.

Because the instance is created once per database file, config, read_only, home, and shared_home take effect only at creation. A call that reuses an existing instance cannot apply them, and fails rather than dropping them. Passing dbdir to dbConnect() fails too when the driver owns a database file of its own, because the connection would go to dbdir while the driver kept its own database open. To apply different values to a file-based database – for example to reopen it read-only, or to send extensions and secrets elsewhere – first release the instance with duckdb_shutdown(), which also drops it from the cache, then create it again. dbDisconnect() closes one connection, and its shutdown argument is unused. Connections keep the instance alive, so it is released once the last connection to it closes, unless a result not yet cleared with dbClearResult() or an Arrow stream not yet released still uses it. The cache does not find an instance that only such a result or stream keeps open, so duckdb() with the same dbdir then opens a second instance of the file in the same session. A driver that was never connected to releases its instance when the driver is garbage-collected or the session ends. dbIsValid() reports whether a driver still holds an instance.

DuckDB extensions on Linux

DuckDB's prebuilt extensions for Linux are compiled with the GNU C++ standard library (⁠libstdc++⁠). Loading one into a duckdb package that was itself built with a different C++ standard library – most commonly ⁠libc++⁠ (clang's ⁠-stdlib=libc++⁠) – is an ABI mismatch that crashes R (https://github.com/duckdb/duckdb-r/issues/1107). Almost all Linux builds (CRAN binaries and most source installs) use ⁠libstdc++⁠ and are unaffected; macOS and Windows are unaffected.

Each duckdb() call decides whether the driver it returns may load extensions, via the allow_extensions argument, the duckdb.allow_extensions option, the DUCKDB_R_ALLOW_EXTENSIONS environment variable, or automatic detection. On the automatic path a build that was not compiled with ⁠libstdc++⁠ on Linux disables extensions: INSTALL / LOAD raise a clear error instead of crashing, automatic extension install/load is turned off, and a throttled advisory message is shown when duckdb() is called. Pass allow_extensions = FALSE to disable extensions and silence that message, or allow_extensions = TRUE to attempt loading anyway (which may still crash R).

The decision is carried on the returned driver as the experimental allow_extensions slot (see duckdb_driver).

Examples

library(adbcdrivermanager)
with_adbc(db <- adbc_database_init(duckdb_adbc()), {
  as.data.frame(read_adbc(db, "SELECT 1 as one;"))
})


drv <- duckdb()
con <- dbConnect(drv)

dbGetQuery(con, "SELECT 'Hello, world!'")

dbDisconnect(con)
duckdb_shutdown(drv)

# Shorter:
con <- dbConnect(duckdb())
dbGetQuery(con, "SELECT 'Hello, world!'")
dbDisconnect(con, shutdown = TRUE)

duckdb documentation built on Oct. 23, 2026, 5:06 p.m.