SQL databases: fix creds for sql_table()

[AstrakhantsevaAA]

dlt version

0.4.12

Source name

sql database

Describe the problem

@dlt.sources.config.with_config(
    sections=("sources", "sql_database"),
    spec=SqlTableResourceConfiguration,
    sections_merge_style=ConfigSectionContext.resource_merge_style,
)

sections is hardcoded in with_config . when we trying to set credentials for multiple sql_table() in secrets.toml it doesn't work.

Slack thread: https://dlthub-community.slack.com/archives/C04DQA7JJN6/p1716972810349559

Expected behavior

No response

Steps to reproduce

I'm seeking advice on best practices for managing credentials when dealing with multiple database sources. When I tried to use the secrets.toml, I tried to add the module and pipeline names to the sections (without it the sections - source.function_name.credentials - are identical). I tried both options: [sources.my_module_name.sql_database.credentials] and [my_pipeline_name.sources.sql_database.credentials] Unfortunately, both configurations resulted in the dlt.common.configuration.exceptions.ConfigFieldMissingException error. It appears that the sections with module and pipeline names are not being recognized during the search. Any insights regarding the configuration of the secrets.toml file or suggestions on other best practices for managing credentials across multiple database sources would be greatly appreciated.

I understand the problem now. When using sql_database() from the init file, it works fine with [my_pipeline_name.sources.sql_database.credentials]. However, when using sql_table(), it does not work. I assume this is due to the with_config decorator defining the section specifically there. I tried to override this with another with_config decorator in my module and also attempted to change the init file to remove the section definition, but without success yet.

How you are using the source?

I run this source in production.

Operating system

Linux

Runtime environment

Local

Python version

3.9

dlt destination

No response

Additional information

No response

[rudolfix]

@AstrakhantsevaAA there's a big PR waiting for next dlt release which fixes all configuration problems I could find https://github.com/dlt-hub/verified-sources/pull/478