https://flyte.org logo
#ask-the-community
Title
# ask-the-community
a

arun tom

11/23/2023, 12:41 PM
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?
g

Greg Linklater

11/23/2023, 1:25 PM
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:
Copy code
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.
a

arun tom

11/23/2023, 1:30 PM
Thank you for the response. This should really help me!
> 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. 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?
g

Greg Linklater

11/23/2023, 1:47 PM
Maybe, but that’s where my experience ends I’m afraid
a

arun tom

11/23/2023, 2:00 PM
No worries. Thanks for the help!
s

Samhita Alla

11/24/2023, 5:01 AM
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?