Editing database tables

DataEditR can edit tables stored in a database as easily as data in your R session. You connect with DBI, pass a dbplyr lazy table to data_edit(), and the editor loads the rows, lets you edit them with all the usual tools, and writes your changes back to the database in a single transaction when you are done.

1. Connect to a database

Any database with a DBI backend can be used. A few common examples are shown below; see the documentation of each backend for the connection details it needs.

# SQLite - a database in a single file
con <- DBI::dbConnect(RSQLite::SQLite(), "cars.sqlite")

# DuckDB
con <- DBI::dbConnect(duckdb::duckdb(), "cars.duckdb")

# PostgreSQL
con <- DBI::dbConnect(
  RPostgres::Postgres(),
  dbname = "garage",
  host = "db.example.com",
  user = "me",
  password = Sys.getenv("PGPASSWORD")
)

# MySQL or MariaDB
con <- DBI::dbConnect(RMariaDB::MariaDB(), dbname = "garage",
                      host = "db.example.com", user = "me",
                      password = Sys.getenv("MYSQL_PASSWORD"))

# SQL Server and other databases through ODBC
con <- DBI::dbConnect(odbc::odbc(), dsn = "garage")

In this article we will use a small SQLite database created from mtcars:

library(DataEditR)
library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbExecute(con, "
  CREATE TABLE cars (
    id INTEGER PRIMARY KEY,
    model TEXT,
    mpg REAL,
    cyl INTEGER,
    hp INTEGER
  )
")
#> [1] 0
DBI::dbAppendTable(con, "cars", data.frame(
  model = rownames(mtcars),
  mpg = mtcars$mpg,
  cyl = as.integer(mtcars$cyl),
  hp = as.integer(mtcars$hp)
))
#> [1] 32

2. Edit a table

Pass a lazy table created with dplyr::tbl() to data_edit():

data_edit(tbl(con, "cars"))

Use filter(), select() and arrange() to choose the rows and columns to edit; only the matching rows are loaded into the editor, so you can work on a small part of a very large table. Columns calculated with mutate() are shown for reference but are read-only (they show a lock next to their name), because they do not exist in the table.

cars <- tbl(con, "cars") %>%
  filter(cyl >= 6) %>%
  mutate(kpl = round(mpg * 0.425, 1))

data_edit(cars)

Everything else works as it does for data frames: edit cells, paste ranges, fill down, add or delete rows, undo and redo, filter rows under the column headers, review changes and revert them. Adding, removing and renaming columns is disabled, because that would change the structure of the table.

3. How rows are matched

Rows are matched to the database through the table’s primary key. The key is detected automatically for SQLite, DuckDB and databases that describe their keys in information_schema (such as PostgreSQL). For other databases, or tables without a primary key, use db_table() to say which column(s) uniquely identify each row:

cars_db <- db_table(tbl(con, "cars"), key = "id")
cars_db
#> <editr_db_table>
#>   table: cars
#>   key:   id
#>   check: TRUE

The key columns must be part of the data you edit (don’t drop them with select()), and their values must be unique in the loaded rows.

db_table() also accepts a connection and a table name, which is useful for tables in another schema:

# a table in another schema
data_edit(db_table(con, table = DBI::Id(schema = "sales", table = "orders"),
                   key = "order_id"))

Queries that combine tables, such as joins or unions, raw SQL queries and tables without a primary key cannot be written back automatically, so they are opened read-only (you can still filter, sort and look through them). Use db_table() to say which table the edits belong to; columns from other tables are then read-only:

owners <- tbl(con, "owners")
cars_and_owners <- left_join(tbl(con, "cars"), owners, by = c("id" = "car_id"))

data_edit(db_table(cars_and_owners, table = "cars", key = "id"))

4. Tables too big to load

The table does not have to fit in memory. data_edit() loads at most 100,000 rows at a time (set limit in db_table(), or options(DataEditR.db_rows = )), and the header and the Rows button in the toolbar show how many rows are loaded out of how many there are:

flights <- tbl(con, "flights")    # 30 million rows
data_edit(flights)                # loads 100,000 of them

# or choose the rows before opening the editor
data_edit(filter(flights, carrier == "UA", dep_delay > 60))

Click Rows to load other rows: the rows matching a filter, written like dplyr::filter() (e.g. carrier == "UA" & dep_delay > 60), sorted by a column, at most a number of rows. The database does the filtering and sorting (the filter is translated to SQL by dbplyr), and the dialog shows how many rows match before you load them. If you have changed rows that are not saved yet, you can save them to the database first or discard them.

Filters can use column names, values and the functions that formulas may use (see the user guide), so users of a shiny app cannot run other code or SQL. Column summaries, the filters under the column headers, the Filters panel and Find work on the loaded rows.

Files that are too big for memory can be explored the same way through DuckDB, which reads Parquet and CSV files without loading them; such queries are read-only:

con <- DBI::dbConnect(duckdb::duckdb())
data_edit(tbl(con, sql("SELECT * FROM read_parquet('trips/*.parquet')")))

5. Committing changes

Nothing is written to the database while you edit. When you press Done, the editor shows a summary of the rows that will be updated, inserted and deleted and asks you to confirm. Choose Keep editing to go back to the editor or Commit to write the changes. The Code panel shows the rows_update(), rows_append() and rows_delete() calls that make the same changes.

All changes are written in a single transaction, so either every change is saved or none are:

  • edited rows are updated with UPDATE ... WHERE <key> = ..., only setting the columns you changed;
  • removed rows are deleted by their key;
  • new rows are inserted. If you leave the key of a new row empty the database generates it (e.g. an auto-incrementing id).

After a successful commit data_edit() returns the edited rows as a data frame. Press Cancel to close the editor without writing anything.

cars_edited <- data_edit(tbl(con, "cars"))

To keep a record of who changed which rows, and why, pass a function to log that writes the change log to a table in the same database. Rows are named by their key in the log, and the notes added in the History tab are included:

data_edit(tbl(con, "cars"),
          log = function(log) DBI::dbWriteTable(con, "cars_log", log,
                                                append = TRUE))

6. When someone else changes the data

Databases are often shared, so the rows you are editing may be changed by someone else before you press Done. To make sure you never overwrite their changes without knowing, every update checks that the values you changed are still the values that were loaded, and every delete checks that the whole row is unchanged (optimistic concurrency). If any row was changed or removed in the meantime, the whole commit is rolled back, nothing is written and you are told how many rows conflicted. Close the editor, load the data again to see the latest values and redo your edits.

If you would rather overwrite rows regardless of changes made by others, turn the check off with check = FALSE:

data_edit(db_table(tbl(con, "cars"), check = FALSE))

7. Code to reproduce the changes

Set code = TRUE (or supply the name of an R script) to get the dplyr code that makes the same changes to the database with rows_delete(), rows_update() and rows_append(). The Code tab in the editor shows the same code as you edit.

cars <- tbl(con, "cars")
data_edit(cars, code = TRUE)
library(dplyr)

cars <- tbl(con, "cars")

rows_delete(cars, tibble::tibble(id = c(4)), by = "id", unmatched = "ignore", copy = TRUE, in_place = TRUE)
rows_update(cars, tibble::tibble(id = c(1), mpg = c(21.5)), by = "id", unmatched = "ignore", copy = TRUE, in_place = TRUE)
rows_update(cars, tibble::tibble(id = c(2), model = c("Mazda RX4 Wagon")), by = "id", unmatched = "ignore", copy = TRUE, in_place = TRUE)
rows_append(cars, tibble::tibble(model = c("Ford Escort"), mpg = c(30.1), cyl = c(4)), copy = TRUE, in_place = TRUE)

Tables in other schemas are referred to with dbplyr::in_schema() in the generated code.

8. Databases in shiny apps

The dataEdit module edits database tables in your own shiny applications too. Pass the lazy table (or a db_table()) to dataEditServer() and call dataEditCommit() when the user wants to save; it writes the changes in a single transaction, raises an error of class "editr_conflict" if rows were changed by someone else, and reads the rows back from the database afterwards. dataEditChanges() summarises what will be written, e.g. to enable a Save button or ask for confirmation.

server <- function(input, output, session) {
  cars <- dataEditServer("cars", data = tbl(con, "cars"))

  observeEvent(input$save, {
    res <- tryCatch(dataEditCommit(cars), editr_conflict = function(e) e)
    if (inherits(res, "editr_conflict")) {
      showNotification(conditionMessage(res), type = "error")
    } else {
      # who changed what, next to the data
      DBI::dbWriteTable(con, "cars_log", res$log, append = TRUE)
      showNotification(sprintf("%d updated, %d added, %d removed",
                               res$updated, res$inserted, res$deleted))
    }
  })
}

dataEditChanges() counts the rows that will be updated, inserted and deleted, which makes it easy to enable a Save button only when there is something to save, or to ask the user to confirm:

See vignette("shiny-modules") for the complete app.