Support per-database configuration in sql_database and sql_table sources

[trymzet]

Feature description

Currently, you must either have one sql_database config, or per-pipeline configs. This is impractical if ingesting from multiple databases and having possibly many (eg. if creating a pipeline per table or per schema) pipelines, which AFAIK is a common use case.

(BTW I think there's the same issue with eg. the filesystem destination, which also only works if you only load data to one filesystem.)

Are you a dlt user?

None

Use case

No response

Proposed solution

Would love to be able to do something like

# secrets.toml
[sources.sql_database.my_db.credentials]
...

[sources.sql_database.my_other_db.credentials]
...

And then in the code you have sth like

source = sql_table(database="my_db", schema="my_schema", table="my_table")
pipeline = dlt.pipeline()
pipeline.run(source)

Related issues

No response

[rudolfix]

@trymzet you should be able to get what you need by giving each sql_database instance a name that is ie. your database name. for example

source = sql_database(...).with_args(name="my_database", section="my_database")

and then

[sources.my_database.credentials]

you could also tryi

source = sql_database(...).with_args(name="my_database")

and

[sources.sql_database.my_database.credentials]

https://dlthub.com/docs/api_reference/extract/source#with_args

[trymzet]

@rudolfix it's still looking under sql_database in the config, it's probably because the config is being resolved when sql_database() is called (so before with_args())

db = sql_database(schema="my_schema", backend="pyarrow").with_args(
    name="mssql", section="mssql"
)

dlt.common.configuration.exceptions.ConfigFieldMissingException: Following fields are missing: ['drivername'] in configuration with spec ConnectionStringCredentials
        for field "drivername" config providers and keys were tried in following order:
                In Environment Variables key SOURCES__SQL_DATABASE__SQL_DATABASE__CREDENTIALS__DRIVERNAME was not found.
                In Environment Variables key SOURCES__SQL_DATABASE__CREDENTIALS__DRIVERNAME was not found.
                In Environment Variables key SOURCES__CREDENTIALS__DRIVERNAME was not found.
                In Environment Variables key CREDENTIALS__DRIVERNAME was not found.
                In secrets.toml key sources.sql_database.sql_database.credentials.drivername was not found.
                In secrets.toml key sources.sql_database.credentials.drivername was not found.
                In secrets.toml key sources.credentials.drivername was not found.
                In secrets.toml key credentials.drivername was not found.
                In config.toml key sources.sql_database.sql_database.credentials.drivername was not found.
                In config.toml key sources.sql_database.credentials.drivername was not found.
                In config.toml key sources.credentials.drivername was not found.
                In config.toml key credentials.drivername was not found.

Also, is there a way to pass these when using sql_table()?

[rudolfix]

@trymzet OK I gave you a wrong example :) as you say - you need to rename the source before it is called. this one works:

from dlt.sources import sql_database

renamed = sql_database.sql_database.with_args(name="my_db", section="my_db")(table_names=["table_1"])

in case of standalone sql_table: the resource name is set to table name and configured as such. this could be indeed improved by implementing similar mechanism we have for sources... currently the config sections for sql_table is
[sources.sql_database.<table name>]. so for your case it is better to go with sql_database