Hi folks! I'm new to Flyte and data engineering in general. I see that SQLAlchemyTask needs to be passed database configuration (connection string etc) when the task is defined. Is there a way to pass it to the task dynamically during run time. Also is there a way to share database connections between tasks (like a pool of db connections) instead of creating one each time the task is run?

Is there a way to pass it to the task dynamically during run time?

I’ve struggled with this for a while, it is tricky and depends on the database you are using. This is my example for Postgres:

secrets = {
    "user": Secret(
        group=CREDS_GROUP,
        key=USER_KEY,
    ),
    "password": Secret(
        group=CREDS_GROUP,
        key=PASSWORD_KEY,
    ),
    "host": Secret(
        group=ENV_GROUP,
        key=HOST_KEY,
    ),
    "port": Secret(
        group=ENV_GROUP,
        key=PORT_KEY,
    ),
    "dbname": Secret(
        group=ENV_GROUP,
        key=DB_KEY,
    ),
}

sql_task = SQLAlchemyTask(
    "my_exmaple_postgresql_task",
    query_template="""
        select version()
    """,
    output_schema_type=DataSchema,
    task_config=SQLAlchemyConfig(
        uri="postgresql://",
        connect_args={
            "sslmode": "require",
        },
        secret_connect_args=secrets,
    ),
    secret_requests=[*secrets.values()],
)

# ...

Also is there a way to share database connections between tasks (like a pool of db connections) instead of creating one each time the task is run?

Each task is run as a separate container, whether they are sequential or parallel, so they won’t be able to share a connection pool.

Thank you for the response. This should really help me!

Maybe, but that’s where my experience ends I’m afraid

No worries. Thanks for the help!

So would it be appropriate to use an agent to write to a database. From the docs an agent is long lived service. So maybe the agent can implement the connection pool and write to the database?

@Kevin Su wdyt?