PostgreSQL Connection URI Incorrectly Formatted in BaseHook.get_connection('conn_id').get_uri()

[Dong-yeong0]

Apache Airflow version

2.10.5

What happened?

When creating a new PostgreSQL connection in the Airflow UI, the conn_type is stored as "postgres". However, when calling BaseHook.get_connection(conn_id) and using get_uri() to generate an SQLAlchemy engine, an error occurs because the generated URI is postgres://..., which is not a valid scheme for SQLAlchemy.

SQLAlchemy expects the scheme postgresql:// instead. This mismatch leads to connection failures.

Steps to Reproduce

1. Go to the Airflow UI → Admin → Connections.

2. Create a new connection with:

   • Connection Id: test
   • Conn Type: Postgres
   • Host: localhost
   • Database: postgres
   • Login: test
   • Password: ********
   • Port: 5432

3. Save this connection and attempt to use it in the Airflow webserver CLI.

   python3

   >>> from airflow.hooks.base import BaseHook
   >>> from sqlalchemy import create_engine
   >>> uri = BaseHook.get_connection('test').get_uri()
   [2025-03-10T07:35:01.944+0000] {base.py:84} INFO - Retrieving connection 'test'
   >>> create_engine(uri)
   

4. The generated URI will be "postgres://user:password@host:port/dbname", which will cause SQLAlchemy to raise an error.

   Traceback (most recent call last):
     File "<stdin>", line 1, in <module>
     File "<string>", line 2, in create_engine
     File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
       return fn(*args, **kwargs)
              ^^^^^^^^^^^^^^^^^^^
     File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 518, in 
   create_engine
       entrypoint = u._get_entrypoint()
                    ^^^^^^^^^^^^^^^^^^^
     File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/url.py", line 662, in 
   _get_entrypoint
           cls = registry.load(name)
                 ^^^^^^^^^^^^^^^^^^^
         File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 343, in load
   raise exc.NoSuchModuleError(sqlalchemy.exc.NoSuchModuleError: Can't load plugin: 
   sqlalchemy.dialects:postgres

What you think should happen instead?

The generated URI must follow the correct format: postgresql://user:password@host:port/dbname.

However, the URI is now generated incorrectly as postgres://user:password@host:port/dbname and is not recognized by SQLAlchemy.

How to reproduce

Modify the get_uri() method in airflow/models/connection.py to convert "postgres" to "postgresql" before returning the URI.

Operating System

Windows 11 + WSL2

Deployment

Docker compose

Deployment details

I refered Running Airflow in Docker

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.