Skip to contents

db_table() describes a database table for data_edit(). You can usually skip it and pass a dbplyr lazy table directly, e.g. data_edit(tbl(con, "artists")); use db_table() when the key cannot be detected automatically or the query is not a simple table reference.

Usage

db_table(
  x,
  table = NULL,
  key = NULL,
  limit = NULL,
  check = TRUE,
  readonly = NULL
)

Arguments

x

a dbplyr lazy table (e.g. dplyr::tbl(con, "artists"), optionally with filter(), select() or arrange() applied) or a DBI connection.

table

name of the table to write to, a character string or a DBI::Id(). Required when x is a connection, otherwise detected from the lazy table.

key

name(s) of the column(s) that uniquely identify each row. When NULL the primary key is detected for SQLite, DuckDB and PostgreSQL databases.

limit

maximum number of rows to load into the editor at a time, set to getOption("DataEditR.db_rows", 100000) by default. Use dplyr::filter() on the lazy table, or Rows in the editor, to choose which rows to load.

check

logical indicating whether updates and deletes should verify that rows have not changed in the database since they were loaded (optimistic concurrency), set to TRUE by default.

readonly

TRUE to open the table read-only, FALSE to stop with an error when it cannot be written to. Set to NULL by default to open queries that cannot be written to (SQL, joins, tables without a primary key) read-only.

Value

an object of class editr_db_table to pass to data_edit() or dataEditServer().

Details

Rows are matched to the database through the key columns. Edits are staged in the editor and only written when you press Done and confirm the commit. All changes are written in a single transaction: if any row has been changed by someone else since it was loaded (or no longer exists), the whole commit is rolled back and nothing is written. Adding, removing and renaming columns is disabled for database tables.

Tables do not have to fit in memory: at most limit rows are loaded at a time, and Rows in the toolbar loads other rows (those matching a filter, in a chosen order), with the filter and sorting done by the database. Queries that cannot be written back (SQL, joins, or tables without a primary key, e.g. read_parquet() with DuckDB) are opened read-only, so large files can still be explored.

Author

Dillon Hammill, dillon.hammill21@gmail.com

Examples

if (interactive() && requireNamespace("RSQLite", quietly = TRUE)) {
  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  DBI::dbExecute(con, "CREATE TABLE cars (id INTEGER PRIMARY KEY,
    model TEXT, mpg REAL)")
  DBI::dbAppendTable(con, "cars", data.frame(
    model = rownames(mtcars), mpg = mtcars$mpg))

  # key detected automatically
  data_edit(dplyr::tbl(con, "cars"))

  # explicit table description
  data_edit(db_table(con, table = "cars", key = "id"))
}