Reports

So, the solution I arrived at was to use reticulate.

If someone has a pure R solution that follows a similar pattern, I would still be interested in hearing it and changing the accepted solution.

reticulate::py_require("polars[database]")
reticulate::py_require("sqlalchemy")

polars     <- reticulate::import("polars")
sqlalchemy <- reticulate::import("sqlalchemy")

engine    <- sqlalchemy$create_engine("sqlite:///transactions.sqlite3", future = TRUE)
dataframe <- polars$DataFrame(data.frame(x = 1:5, y = letters[1:5]))

with(
    engine$begin() %as% conn, 
    {
        dataframe$write_database("table_a", conn, if_table_exists = "append")
        dataframe$write_database("table_b", conn, if_table_exists = "append")
        dataframe$write_database("table_c", conn, if_table_exists = "append")
        stop("OOPS :(")
    }
)

Note: there was a bug in with() which the maintainers were kind enough to fix within a day, and this now works (i.e. the whole transaction is rolled-back upon error) with the latest branch.

79775618