Issue: Scheme resolution for database urls
Author: mguillau
State: closed
Created: 2018-02-07 14:01:39

Following the feedback on #1359, here's a minimal patch to allow extensions for resolving database urls.

Below is an example of how one would create a simple standalone extension for reading database URLs stored in environment variables:

db_url_env_ext.py :
```
import os
from urlparse import urlparse
from playhouse.db_url import register_resolver

def parse_from_env(parsed):
    env_var = parsed.fragment # using the fragment component as it is case sensitive
    return urlparse(os.environ[env_var])

register_resolver(parse_from_env, 'env')
```

which could be used like this:
```
$ export DBURL="sqlite:///:memory:"
$ python
>>> import playhouse.db_url as db_url
>>> import db_url_env_ext
>>> d = db_url.connect('env://#DBURL')
```

Compared to #1359, I added recursive resolution without allowing two resolutions of the same scheme. This prevents infinite loops such as:
```
$ export DBURL="env://#DBURL"
$ python
>>> import playhouse.db_url as db_url
>>> import db_url_env_ext
>>> d = db_url.connect('env://#DBURL')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "playhouse/db_url.py", line 106, in connect
    parsed = resolve(urlparse(url))
  File "playhouse/db_url.py", line 49, in resolve
    '{}'.format(parsed.scheme, seen_schemes))
playhouse.db_url.ResolutionLoop: Scheme env has already been used: set(['env'])
```

Looking forward to your feedback!

> I added recursive resolution without allowing two resolutions of the same scheme.

I'm not sure I understand the need for this. So your overall db-url would be something like `env://sqlite:///:memory:`?

The idea is to support the chaining of resolution schemes. Because of the scheme loop constraint, this would only work if at least two resolution schemes are available.

So, imagine we have added support for two or more resolution schemes, say, the `env://` resolution scheme as above (which replaces an url of the form `env://#ENVVAR` with the url contained _inside_ the "$ENVVAR" environment variable) and also an `rds://` resolution scheme as in the PR #1359 (which provides the endpoint, port, login, db engine, etc. from an AWS RDS cluster id provided as `rds://rds-db-cluster-id`).

Without recursive resolution, one would not be able to use an environment variable and the `env://` scheme to store an `rds://` db-url to obtain the real db connection information. One would only be able to store urls that are already supported by playhouse. With recursive resolution, instead, a chain like this is possible:

`env://#ENVVAR` -> (reads the content of `$ENVVAR`) -> `rds://rds-db-cluster-id` -> (uses boto3 to get endpoint connection information for `rds-db-cluster-id`) -> `mysql://dbmaster@dummy-endpoint.amazonaws.com:1234/dbname` -> (resolution is finished since `mysql://` is not a resolution scheme) -> the corresponding `MysqlDatabase` is instantiated.

One could imagine further resolution schemes, such as parsing a config file or obtaining database connection information from a credential management service. The `env://` scheme is just the simplest possible example I could think of to demonstrate the feature.

I think this conflates the idea of url scheme handling with configuration.

To my thinking, the proper way to handle environment variables is:

```python
def configure_database():
    connection_string = os.environ['DATABASE']
    return db_url.connect(connection_string)
```

I'm not 100% sure if by "this" you mean (1) env:// as a proposed scheme, (2) the chaining of url resolutions or (3) the entire idea of url resolution.

(1) Handling environment variables was just an illustration here, it's not part of what I propose for playhouse. I agree with you that what you suggest is a simpler way to handle this.

(2) It's true that the idea of chaining came to me while considering the env:// case, but I also see the generality behind it. You might have a credential/configuration manager providing you an url that you have to resolve again (e.g. the rds:// example) to get the actual endpoint.

(3) To be brief, my team has a couple of use cases at work where we retrieve database connection information via an API by providing an identifier string. It is only after the API request that we know, what is the database engine type (and thus the right peewee Database subclass to use), the username, the password, etc. This "dynamic" mapping from id string to Database subclass and other connection information seemed to fit the whole db-url spirit quite well, but we need more than just urlparse to deduce the class and also a way to inject the login/password obtained from the API response into the `connect_kwargs` dict. The simple idea of generating a new (parsed) url from another url seemed like a reasonably elegant solution. We're open to suggestions, though.

Having given this some additional thought, I'm going to pass on the proposed change.