Log to databases via DBI

Log to a database table with any DBI compatible backend. Please be aware that AppenderDbi does not support case sensitive / quoted column names, and you advised to only use all-lowercase names for custom fields (see ... argument of LogEvent). When appending to a database table all LogEvent values for which a column exists in the target table will be appended, all others are ignored.

NOTE: AppenderDbi works reliable for most databases, but is still considered experimental, especially because the configuration is excessively complicated. Expect breaking changes to AppenderDbi in the future.

Value

The $new() method returns an R6::R6 that inherits from lgr::Appender and can be uses as an appender by a lgr::Logger.

Buffered Logging

By default, AppenderDbi writes each LogEvent directly to the target database which can be relatively slow. To improve performance it is possible to tell AppenderDbi to buffer db writes by setting buffer_size to something greater than 0. This buffer is written to the database whenever it is full (buffer_size), whenever a LogEvent with a level of fatal or error is encountered (flush_threshold), or when the Appender is garbage collected (flush_on_exit), i.e. when you close the R session or shortly after you remove the Appender object via rm().

Creating a New Appender

An AppenderDbi is linked to a database table via its table argument. If the table does not exist it is created either when the Appender is first instantiated or (more likely) when the first LogEvent would be written to that table. Rather than to rely on this feature, it is recommended that you create the target table first using an SQL CREATE TABLE statement as this is safer and more flexible. See also LayoutDbi.

Choosing the correct DBI Layout

Layouts for relational database tables are tricky as they have very strict column types and further restrictions. On top of that implementation details vary between database backends.

To make setting up AppenderDbi as painless as possible, the helper function select_dbi_layout() tries to automatically determine sensible LayoutDbi settings based on conn and - if it exists in the database already - table. If table does not exist in the database and you start logging, a new table will be created with the col_types from layout.

See also

Other Appenders: AppenderDt, AppenderElasticSearch, AppenderGmail, AppenderPushbullet, AppenderSendmail, AppenderSyslog

Super classes

lgr::Filterable -> lgr::Appender -> lgr::AppenderMemory -> AppenderDbi

Active bindings

conn

a DBI connection

close_on_exit

TRUE or FALSE. Close the Database connection when the Logger is removed?

col_types

a named character vector providing information about the column types in the database. How the column types are reported depends on the database driver. For example, SQLite returns human readable data types (character, double, ...) while IBM DB2 returns numeric codes representing the data type.

table

a character scalar or a DBI::Id specifying the target database table

table_name

character scalar. Like $table, but always returns a character scalar

table_id

DBI::Id. Like $table, but always returns a DBI::Id

Methods

Public methods

• AppenderDbi$new()

• AppenderDbi$set_close_on_exit()

• AppenderDbi$set_conn()

• AppenderDbi$show()

• AppenderDbi$flush()

Inherited methods

• lgr::Filterable$add_filter()
• lgr::Filterable$filter()
• lgr::Filterable$remove_filter()
• lgr::Filterable$set_filters()
• lgr::Appender$set_layout()
• lgr::Appender$set_threshold()
• lgr::AppenderMemory$append()
• lgr::AppenderMemory$clear()
• lgr::AppenderMemory$format()
• lgr::AppenderMemory$set_buffer_size()
• lgr::AppenderMemory$set_flush_on_exit()
• lgr::AppenderMemory$set_flush_on_rotate()
• lgr::AppenderMemory$set_flush_threshold()
• lgr::AppenderMemory$set_should_flush()

---

Method new()

Usage

AppenderDbi$new(
  conn,
  table,
  threshold = NA_integer_,
  layout = select_dbi_layout(conn, table),
  close_on_exit = TRUE,
  buffer_size = 0,
  flush_threshold = "error",
  flush_on_exit = TRUE,
  flush_on_rotate = TRUE,
  should_flush = NULL,
  filters = NULL
)

Arguments

conn, table

see section Fields

threshold, flush_threshold, layout, buffer_size

see AppenderBuffer

---

Method set_close_on_exit()

Usage

AppenderDbi$set_close_on_exit(x)

---

Method set_conn()

Usage

AppenderDbi$set_conn(conn)

---

Method show()

Usage

AppenderDbi$show(threshold = NA_integer_, n = 20)

---

Method flush()

Usage

AppenderDbi$flush()

Examples

if (requireNamespace("RSQLite")){
  app <- AppenderDbi$new(
    conn = DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:"),
    table = "log"
   )

  lg <- lgr::get_logger("test/dbi")$
    add_appender(app, "db")$
    set_propagate(FALSE)
  lg$info("test")
  print(lg$appenders[[1]]$data)

  invisible(lg$config(NULL))  # cleanup
}
#> Loading required namespace: RSQLite
#> creating 'log' with columns: level integer, timestamp TEXT, logger TEXT, caller TEXT, msg TEXT
#>   level           timestamp   logger caller  msg
#> 1   400 2023-01-04 14:24:55 test/dbi   eval test