SSH tunneling

[tconbeer]

Need to think through whether this is an adapter feature or a harlequin feature. Maybe it's a library that adapters can use.

Would be nice to provide CLI options for configuring an ssh tunnel, and then intercept/rewrite the host/port/etc options passed through to the adapter.

See:

https://stackoverflow.com/questions/22046708/psycopg2-access-postgresql-database-on-remote-host-without-manually-opening-ssh

[golgor]

One similar topic that would be nice is to add is support for Cloud SQL. This is used for any database hosted in google Cloud for example, maybe other providers as well.

I have some working examples, I can try to find it and add some info here. Maybe also try to implement something if I find the time.

[tconbeer]

A major hurdle for me on this one is that the packages I would use don't seem very well-maintained. e.g., https://github.com/pahaz/sshtunnel/ looks perfect, but hasn't seen a release in 4 years.

[golgor]

The package for Cloud SQL connection is well maintained by Google: https://github.com/GoogleCloudPlatform/cloud-sql-python-connector

I implemented my solution with SQL Alchemy, I haven't checked the code for Harlequin that much yet, so not sure if the same approach would work for you.

Bascially you create a "Connector" using the Google Cloud SQL package, and then pass that into the create_engine of SQL Alchemy.

The example below would give you a better idea of how it works.

@dataclass
class DatabaseConnectionSpec:
    """A dataclass to hold the specification for a database connection."""

    user: str
    password: str
    database: str
    gcloud_project_str: str


class MysqlSessionContext:
    """Class to handle a session to a MySQL database."""

    def __init__(self: Self, connection_spec: DatabaseConnectionSpec) -> None:
        """Initialize the object and setup the database connection.

        The connection is setup using cloud_sql_proxy to connect to the database. The user

        Args:
            connection_spec (DatabaseConnectionSpec): The specification for the database connection.
        """
        self.spec = connection_spec

        self.engine = create_engine("mysql+pymysql://", creator=self._get_connector, echo=False)
        self.session = Session(bind=self.engine)

    def __enter__(self: Self) -> Session:
        """Enter the context manager and return the session.

        Returns:
            Session: A session to use for database operations.
        """
        return self.session

    def __exit__(self: Self, exc_type: type | None, exc_val: Exception | None, exc_tb: TracebackType | None) -> None:
        """Exit the context manager and close the session.

        Args:
            exc_type (type): The type of the exception, if any.
            exc_val (Exception): The exception that was raised, if any.
            exc_tb (TracebackType): The traceback of the exception, if any.
        """
        if exc_type is not None:
            self.session.rollback()
            logger.exception("Exception during database operation in api_session.")
        self.session.close()

    def _get_connector(self: Self) -> pymysql.connections.Connection:
        """Get a pymysql connection using cloud_sql_proxy.

        Returns:
            pymysql.connections.Connection: A connection to the database.
        """
        connector = Connector()
        conn: pymysql.connections.Connection = connector.connect(
            self.spec.gcloud_project_str,
            "pymysql",
            user=self.spec.user,
            password=self.spec.password,
            db=self.spec.database,
        )
        return conn

if __name__ == "__main__":
    conn_spec = DatabaseConnectionSpec(
        user="username",
        password="password",
        database="database",
        gcloud_project_str="project_string"
    )
    context = MysqlSessionContext(conn_spec)

    with context as session:
        query = session.query(...)
        return query.limit(limit).all()

The big advantage with this is that if you have a database in Google Cloud, you have to run the command for the cloud_sql separately. Which is annoying and error prone. Adding it in the package itself would be amazing.

As mentioned, it might be something I can look into if I find the time.