Database Connection Library

[sylwiabr]

Summary

In industry it is incredibly common to store data for later analysis in various kinds of database. To that end it is incredibly important for Enso to be able to connect to databases as part of workflows, using them as sources for data that is then processed and visualised.

Value

Enso will be able to connect to a variety of commonly-used databases.

Specification

• Create a new library included as part of the standard library set called something akin to Database.
• Determine how to build an API that is consistent with the Enso dataframes API. It should allow use of a database seamlessly as the backing store (no need to load the contents into memory where possible) for a dataframe.
• Determine whether the library should build SQL queries itself, or whether it should be backed by JDBC. As part of this, determine whether the PostgreSQL JDBC driver generates SQL code as an output.
• The API should abstract the operations on the database as a DSL on the Enso node.
• The API should be designed in a fluent fashion, such that a query is built through use of an in-Enso DSL, and then executed on demand (e.g. when a visualisation is shown or a result requested). The query should be executed lazily instead of eagerly.
• The API should be native to Enso, and not expose implementation details that have an impedance mismatch with Enso's semantics.
• The library should be designed in an extensible manner, allowing it to function with multiple databases. Initially we only intend to support PostgreSQL, but the more easily it can be extended to other databases (e.g. Snowflake, MySQL, SQLite), the better.

Acceptance Criteria & Test Cases

• TBC. Need a scenario from @sylwiabr.

[radeusgd]

Writing here to not forget to discuss this:

I've noticed that we are currently not handling an edge case for join both in Table and Database libraries:

we use suffixes to disambugate duplicate column names when joining two tables, but what if both tables contain a column named A, our suffixes are _left and _right, but one of these tables already contains A_left too? I think we will get an inconsistent state with the table containing two columns with equal names.

@kustosz how do you think we should handle this? The most straightforward for me is to just detect these situations and issue an error asking to rename the columns. Any other semi-automatic solutions is in my opinion likely to confuse users.