dbi3

The goal of this repository is to design a new database interface from scratch, based on the lessons learned from DBI and the previous R Consortium projects. The interface provides compatibility to DBI in both directions: the new interface supports existing DBI backends (possibly with degraded performance), and backends implemented against the new interface automatically support DBI. Backends for the new interface will be easier to implement, because they can use the full feature set provided by dbi3.

In the beginning, the issue tracker will be used to collect and categorize issues that did not fit the scope of {DBI} or related packages. Eventually, the repository will contain a working package and a design document.

Properties

• async-first
  • required for efficient web applications: Shiny and Plumber
  • simpler to implement a synchronous interface from an async interface than the other way round
• only pure functions/methods with callbacks
  • idiomatic to R
• agnostic to query language
  • concepts: context, connection, query objects, tables, query strings, parameters
• reference implementation: generic backend for DBI classic

Issues and solutions

The following list of issues, collected 2024-04-14, were categorized by how (and where) they can be solved, taking into account the new ADBC standard and the new adbi package.

Solved by ADBC: Performance

• Query cancellation

• Arrow/Flight SQL

• let dbreadtable use copy

Solved by ADBC: Data format

• Send parameter types

• libpq expects knowledge about data types for parametrized queries

• Optionally return tibbles

• Raise error if data type domain differs between R and database

• Specialised data types in R

• Think about column conversion

• Support arrays natively

Solved by ADBC: Usability

• Rethink immediate argument

• Streaming multiple statements in a single dbExecute/dbQuery/etc. call

• Add default dbWriteTable() method

To be solved by ADBC/Arrow: Usability

• Think about asynchronous operations

• Implement RStudio Connection Contract

• dbGetChunkedQuery()

Interface: wrapper around adbcdrivermanager?

• Progress bar for dbWriteTable?

• dbWithConnection() and dbWithResult()

• Specify n argument for dbReadTable()

• Default connection

Quoting: dbplyr?

• dbQuoteIdentifier should validate SQL inputs

• Reading and writing tables with dots in name

• Stronger guarantees for quoting literals

• Specify limits per connection or per driver

SQL generation: dbplyr?

• Allow creation of tables with primary key and/or index

• Do you have any intention of adding the "upsert" function?

• Support passing lists as arguments for queries like SELECT a, b WHERE a IN ($1)

• How to create an external table in Hive?

• Name collision when creating tables

Introspection: dm?

• More arguments to dbListObjects() to narrow result set

• Think about listing temporary tables

Interface: maintenance

• Use ellipsis::check_dots_used() in dbConnect

• Consider standard timezone and timezone_out arguments to dbConnect()

• Add tweak for restricting to one result set per connection

Documentation: maintenance

• Dynamically list methods in generic documentation

• dbConnect should give advice about argument names

• Should backends use "Depends: DBI"?

Testing: maintenance

• How to run tests on a user's database?

• Reenable test for dbIsReadOnly()

• Think about moving DBItest code here

• Avoid DML in tests

• Avoid hard-coded data types in queries

• Specify and test case-sensitivity for table names

• Test for formals(dbXxx) ineffective

• Specify binding of different argument types for different calls of dbBind()

• Feature Request: Virtual connection

Parameterized queries: unclear, are they necessary for data analysis?

• Provide interfaces and support for Parameterised Queries

• Think about ways to specify placeholder in prepared/parametrized query

• Used named parameters in parameterized statements

Reconnection: unclear, long-lived or serializable connections seem a specialized use case?

• Reconnection

• dbIsValid() fails in new sessions

• Connector class needs to store expression to create driver

• Feature request: add a dbResetConnection() generic

Out of scope?

• Interface to call stored procedure with various parameters and their attributes need to be supported in DBI

• Any way to know if a connection is already inside a transaction?