Environment variable overrides only work with YAML config and not Python config.py

[martinburch]

Given a config.py like...

 from sqlmesh.core.config import (
     Config,
     GatewayConfig,
     MSSQLConnectionConfig
 )

 config = Config(
     gateways={
         "local": GatewayConfig(
             connection=MSSQLConnectionConfig(
                 host='localhost',
                 user='sa',
                 password='placeholder',
                 database='db',
             ),
         ),
     },
 )

Attempting to override the password using

export SQLMESH__GATEWAYS__LOCAL__CONNECTION__PASSWORD=real_pw

as shown in https://sqlmesh.readthedocs.io/en/stable/guides/configuration/?h=override#overrides

fails with the error

Error: Missing connection type.

when running a command like sqlmesh --gateway local info

Exporting the whole set of environment variables for the local gateway connection, including SQLMESH__GATEWAYS__LOCAL__CONNECTION__TYPE allows the sqlmesh CLI to run as expected.

When the same config is given as config.yaml instead...

gateways:
  local:
    connection:
      type: mssql
      host: localhost
      user: sa
      password: placeholder
      database: db

...the override works as expected with only the single environment variable SQLMESH__GATEWAYS__LOCAL__CONNECTION__PASSWORD set.

[izeigerman]

Yes, this is an existing limitation of the Python config. I might suggest using os.environ directly with a different environment variable name.

[martinburch]

I tried using something like password=os.environ['SQLMESH_LOCAL_PASSWORD'] however the Python file then fails to parse unless all the passwords are defined (with something like KeyError: 'SQLMESH_LOCAL_PASSWORD') This error could be handled in Python, yes, but it's far easier to just switch to using YAML. This limitation could be clearer in the docs or, alternatively, it would be great to fix this bug so a Python config offers feature parity with YAML.