connections only available if entered via UI

[longbowww]

I am currently developing within a local devcontainer that uses airflow:2.6.3 production source image.

I can see that the _CONN environment variable was correctly injected:

airflow@bbe914fbb436:~/dags$ printenv | grep DATA
AIRFLOW_CONN_MY_DB="postgresql://user:xxx@myhost:5444?Test-DB"

when trying to modify the postgres tutorial

# DAG setup goes here.....

get_all_testme = PostgresOperator(
    task_id="select_test",
    postgres_conn_id="my_db",
    sql="""
        SELECT * from schema.testme
      """,
)

...
I will always get the following error:

[2023-07-24, 09:40:31 UTC] {sql.py:265} INFO - Executing: 
            SELECT * from cts.testme
          
[2023-07-24, 09:40:31 UTC] {crypto.py:83} WARNING - empty cryptography key - values will not be stored encrypted.
[2023-07-24, 09:40:31 UTC] {base.py:73} INFO - Using connection ID 'my_db' for task execution.
[2023-07-24, 09:40:32 UTC] {taskinstance.py:1824} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/operators/sql.py", line 266, in execute
    hook = self.get_db_hook()
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/operators/sql.py", line 181, in get_db_hook
    return self._hook
  File "/home/airflow/.local/lib/python3.7/site-packages/cached_property.py", line 36, in __get__
    value = obj.__dict__[self.func.__name__] = self.func(obj)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/operators/sql.py", line 144, in _hook
    hook = conn.get_hook(hook_params=self.hook_params)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/models/connection.py", line 359, in get_hook
    raise AirflowException(f'Unknown hook type "{self.conn_type}"')
airflow.exceptions.AirflowException: Unknown hook type ""

This happens for the inline style as well as the json object style described in the documentation. The only way that I can use connections correctly is by entering them manually via UI -> Admin -> Connections.

[potiuk]

I think you should use postgres not postgresql. postgresql:// is sqlalchemy connection, but Airflow's connection type is postgres. Check the type created in the db via airflow connections command for example.