As I find little/ no content on data access layers with R I would like to discuss this topic and hear about your practises and experiences.
Primarily I would like to discuss the best way(s) to decouple the business/ ui-logic from the database layer using a data access layer with R.
Here are two approaches I´m already familiar with or think about using.
1. Abstracting the database CRUD operations with R-Functions
Every DB ressource (table, view, stored proceedure, ...) gets CRUD R-Function partners:
insert_db_table(...)
read_db_table()
update_db_table(id, ...)
delete_db_table(id)
This approach fits well with the functional paradigm of R and takes us a step further in the direction of an "data access layer". What I don´t like about it, is that there is an uncoupling between the data and operations performed on it.
Let´s take the function delete_db_table(id) as an example.
First, this function can only be executed on one particular data table and for no other use case. This does not follow the functional programming principal, that functions should be generalized. The function should be written in that manner: delete(table, id) which in turn would lead to an giant delete-function knowing about all different tables that are out there.
Second, the name of the datasource, that should be accessed is contained in the function name. That does not feel right to me.
2. Using R6 classes to abstract DB ressources
I was searching in vain to find anything on this topic (connected with R). The idea is that every DB ressource is hidden within an R6 class that provides data access functions. Let´s say, we want to access the table "DataTable" in our DB:
data_table <- DataTable$new() # init data table class object
data_table$delete(1) # perform operations
data_table$get()
This again provides us with an separation of the data access logic and also solves some of the issues I mentioned above. The data and accessing functions are bundled together in the right scope. We no longer have to encode the ressource-names in our functions as the class systems solves this problem for us.
There may be issues thou that I don´t see right now.
Now I´m really interested to hear about your opinions and experiences about this topic!