Connection pooling at the Tap level (esp. Database-type streams)

[MeltyBot]

Migrated from GitLab: https://gitlab.com/meltano/sdk/-/issues/21

Originally created by @aaronsteers on 2021-01-14 00:14:00

---

• @DouweM started a discussion: (+1 comment)

  What do you think about moving this to the tap, so that multiple streams could use a connection pool managed by the tap?

---

Following from the discussion on !1, as noted above, there are some complexities with moving connections to the tap level, and I thought those may be better discussed here as their own topic. Note: As of now, only database-type streams inherit these methods.

1. Currently streams can initialize themselves using class factory methods cls.from_input_catalog() and cls.from_discovery().
2. Currently taps don't need to inherit from specialized base classes (unlike streams) and all custom handling of specific stream use cases happens in the Stream class itself.
3. It will be difficult to preserve the above behavior while moving more logic to the tap, specifically execute_query() and open_connection().
4. Previously this logic lived in a "connection" class but it was proven much simpler to have the stream class fully self-sufficient in its access to its underlying data. Refactoring back to a dedicated connection class or back to the Tap class will have a similar outcome of splitting systems logic across more than one class, which could make some design choices more difficult.
5. Since execute_query() and open_connection() are already defined as class methods, we should be able to implement a class-level connection-pooling and class-level max concurrency. Without much change in design structure this would allow global limits on how many times a class (or perhaps even a set of derived subclasses) could instantiate a new connection.
6. As a counterpoint, the downside of using class members is that we do have to pass instance variables explicitly. So far, this hasn't been a problem though, since in most cases we only need to pass the query (the sql string) and the config dict.

Following from bullet (5) above, I'm inclined to try implementing a class-level connection pool as first preference, and see if we still can get good and intuitive usability from a design/dev perspective.

Opening this thread to support expanded discussion and exploring of various options.

[MeltyBot]

View 2 previous comments from the original issue on GitLab

[stale]

This has been marked as stale because it is unassigned, and has not had recent activity. It will be closed after 21 days if no further activity occurs. If this should never go stale, please add the evergreen label, or request that it be added.

[edgarrmondragon]

Still relevant

[BuzzCutNorman]

@edgarrmondragon would the work qbatten did in his pr listed above and my work in pr #1861 be considered a solution to this issue?