[Docs]: Default SQL Connection String example

[radbrt]

Documentation type

Reference

Description

The default SDK and the cookiecutter templates for sql taps and targets have different example implementations of get_sqlalchemy_url. The tap suggests fairly explicitly to return a simple connection string, and the cookiecutter template returns an example f-string.

These most simple connection strings usually work nicely, except when there is an @ in the passord (or username, I suppose). In those cases, the connection string needs to be constructed some other way. I gravitate towards the sqlalchemy.engine.url.URL method.

I suspect that this issue is easy to miss for people building a new tap/target, and we should change the default example to use/suggest the sqlalchemy URL method or some other safe way of generating a connection string.

An example that seems to work with my current sink-in-progress, with @ in the password:

        connection_url = sqlalchemy.engine.url.URL(
            drivername="mssql+pymssql",
            username=config["user"],
            password=config["password"],
            host=config["host"],
            port=config["port"],
            database=config["database"]
        )
        return str(connection_url)

[radbrt]

Slight update due to deprecation warning, we should not invoke URL directly but use connect method:

connection_url = sqlalchemy.engine.url.URL.create(
    drivername="mssql+pymssql",
    username=config["user"],
    password=config["password"],
    host=config["host"],
    port=config["port"],
    database=config["database"]
)

Or, of course, construct the connection url some other way.

[edgarrmondragon]

@radbrt It seems an URL object can be used instead of a string in create_engine, so I think it'd be a good recommendation and default to return the result of URL.create in SQLConnector.get_sqlalchemy_url:

+ from sqlalchemy.engine import URL

- def get_sqlalchemy_url(self, config: dict[str, Any]) -> str:
+ def get_sqlalchemy_url(self, config: dict[str, Any]) -> str | URL:

I'm adding https://github.com/meltano/sdk/labels/Accepting%20Pull%20Requests to this.

[visch]

Here's an example implementation https://github.com/MeltanoLabs/target-postgres/pull/32/files