I am working on an S4 class that contains a DBIConnection to a database in one of its slots. When the S4 object is initiated, I pass the constructor an open connection, which is stored in the connection slot.

setClass(
  "MyClass", 
  slots = c(
    connection = "DBIConnection"
  ), 
  prototype = list(
    connection = NULL,
  )
)

Now I am wondering how I should handle closing it when the object is removed. For now, I have defined a disconnect method for the class that calls DBI::dbDiscconnect on the connection. But that requires a user to call it explicitly. (Note: I _have_ getters & setters, but omitted them here for brevity.)

setGeneric("disconnect", function(x) {
  standardGeneric("disconnect")
})

setMethod("disconnect", signature = "MyClass", function(x) {
  DBI::dbDisconnect(x@connection)
})

Is there a way to call DBI::dbDisconnect automatically when an instance of MyClass is removed? I have been going through the documentation of the withr package, which allows the definition of deferred functions that are executed e.g. when the parent frame / environment is closed (much in the spirit of the on.exit() base R function).

But I don't understand whether a function (e.g. dbDisconnect) can be triggered when an S4 object is removed (either during an analysis workflow or at the end of a session). __Any pointers would be great!__

Or maybe I am overthinking this? E.g. the RPostgres documentation states:

Manually disconnecting a connection is not necessary with RPostgres, but still recommended; if you delete the object containing the connection, it will be automatically disconnected during the next GC with a warning.

Perhaps I should just rely on connections being cleaned up automatically? (That could be ok for SQLite and Postgres, but other database engines might complain...)

Hi Thomas,

Yes it's possible and I will explain but first I want to warn you about storing a DBIConnection object inside your objects.

Main reason for this is that they tend to break on the workers in the context of parallelization.

Another reason is that they don't survive a serialize/unserialize cycle.

And finally: it's a little tedious to implement automatic closing when your objects get removed :wink:

So my recommendation is to avoid them if you can e.g. by storing the path to the db instead (easy to do with a local SQLite db) and opening the connection each time you need to access the db. For MySQL or Postgres dbs you would need to store a few more things like username and dbname.

However I can see that this approach wouldn't necessarily play well in the context of remote MySQL or Postgres dbs where opening the connection for each access could be expensive (e.g. take a few seconds). But also note that some remote servers (e.g. Ensembl or UCSC or both, can't remember) will automatically close the connection after a few seconds of inactivity so the DBIConnection object stored in your object will die anyways.

So if you're still interested:

I don't think there's a way to automatically trigger some action when an object is _removed_. I think the best we can hope is to do this at _garbage-collection time_. The way to do this is to wrap the particular resource you want to act on (the DBIConnection object in your case) in an object that has a pass-by-reference semantic (e.g. environment or external pointer) and to register a finalizer on the pass-by-reference object with reg.finalizer(). The easiest pass-by-reference object to use for that is an environment. See reg.finalizer() for the details.

Hope this helps,

H.

P.S.: If your db is local and you decide to store the path to it instead of the DBIConnection object, make sure to store the _absolute path_ so your objects won't break when the user changes the working directory (e.g. with setwd()). See for example here.