postgresql auto creation into fixtures. simple tests for db conn checks. wrapper fixture for InternalHostnam (pydantic has MultiHost without host attr annoyances)

9 files changed, 190 insertions, 136 deletions

diff --git a/generalresearch/config.py b/generalresearch/config.py
index 551f75a..af80069 100644
--- a/generalresearch/config.py
+++ b/generalresearch/config.py
@@ -5,9 +5,9 @@ from datetime import datetime, timezone
 from pathlib import Path
 
 from pydantic import DirectoryPath, Field, MariaDBDsn, PostgresDsn, RedisDsn
-from pydantic_settings import BaseSettings
+from pydantic_settings import BaseSettings, SettingsConfigDict
 
-from generalresearch.models.custom_types import DaskDsn, SentryDsn
+from generalresearch.models.custom_types import DaskDsn, InternalHostname, SentryDsn
 
 os.environ["DISABLE_PANDERA_IMPORT_WARNING"] = "True"
 
@@ -39,8 +39,22 @@ def is_debug() -> bool:
 
 
 class GRLBaseSettings(BaseSettings):
+    model_config = SettingsConfigDict(
+        env_file=(".env.test", ".env.testing", ".env.staging", ".env.prod"),
+        env_file_encoding="utf-8",
+        extra="allow",
+    )
+
     debug: bool = Field(default=True)
 
+    # --- Pytest ---
+
+    testing_postgres: InternalHostname | None = Field(default=None)
+    testing_postgres_user: str | None = Field(default=None)
+    testing_postgres_pass: str | None = Field(default=None)
+
+    # ---
+
     redis: RedisDsn | None = Field(default=None)
     redis_timeout: float = Field(default=0.10)
 
diff --git a/generalresearch/models/custom_types.py b/generalresearch/models/custom_types.py
index ea96741..2f40dbb 100644
--- a/generalresearch/models/custom_types.py
+++ b/generalresearch/models/custom_types.py
@@ -1,6 +1,9 @@
+from __future__ import annotations
+
 import json
+import re
 from datetime import datetime, timedelta, timezone
-from typing import Any, Literal, Optional, Set
+from typing import Any, Literal
 from uuid import UUID
 
 from pydantic import (
@@ -14,7 +17,7 @@ from pydantic import (
 )
 from pydantic.functional_serializers import PlainSerializer
 from pydantic.functional_validators import AfterValidator, BeforeValidator
-from pydantic.networks import UrlConstraints, IPvAnyNetwork
+from pydantic.networks import IPvAnyNetwork, UrlConstraints
 from pydantic_core import Url
 from typing_extensions import Annotated
 
@@ -24,6 +27,20 @@ from generalresearch.models import DeviceType, Source
 #     from generalresearch.models import DeviceType
 
 
+HOSTNAME_REGEX = re.compile(
+    r"^[a-zA-Z0-9]([a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(\.[a-zA-Z0-9]([a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$"
+)
+
+
+def validate_hostname(v: str) -> str:
+    if not HOSTNAME_REGEX.match(v):
+        raise ValueError("Invalid internal hostname format")
+    return v
+
+
+InternalHostname = Annotated[str, AfterValidator(validate_hostname)]
+
+
 def convert_datetime_to_iso_8601_with_z_suffix(dt: datetime) -> str:
     # By default, datetimes are serialized with the %f optional. We don't
     # want that because then the deserialization fails if the datetime
@@ -31,7 +48,7 @@ def convert_datetime_to_iso_8601_with_z_suffix(dt: datetime) -> str:
     return dt.strftime("%Y-%m-%dT%H:%M:%S.%fZ")
 
 
-def convert_str_dt(v: Any) -> Optional[AwareDatetime]:
+def convert_str_dt(v: Any) -> AwareDatetime | None:
     # By default, pydantic is unable to handle tz-aware isoformat str. Attempt
     # to parse a str that was dumped using the iso8601 format with Z suffix.
     if v is not None and type(v) is str:
@@ -158,7 +175,7 @@ from_comma_sep_str = BeforeValidator(
 
 # This is a set of DeviceType, that serializes and de-serializes into a
 # (sorted) comma-separated str
-DeviceTypes = Annotated[Set[DeviceType], enum_to_comma_sep_str, from_comma_sep_str]
+DeviceTypes = Annotated[set[DeviceType], enum_to_comma_sep_str, from_comma_sep_str]
 
 # This is a set of alphanumeric strings, that serializes and de-serializes
 # into a (sorted) comma-separated str
@@ -223,9 +240,9 @@ InfluxDsn = Annotated[
     ),
 ]
 
-AlphaNumStrSet = Annotated[Set[AlphaNumStr], to_comma_sep_str, from_comma_sep_str]
-IPLikeStrSet = Annotated[Set[IPLikeStr], to_comma_sep_str, from_comma_sep_str]
-UUIDStrSet = Annotated[Set[UUIDStr], to_comma_sep_str, from_comma_sep_str]
+AlphaNumStrSet = Annotated[set[AlphaNumStr], to_comma_sep_str, from_comma_sep_str]
+IPLikeStrSet = Annotated[set[IPLikeStr], to_comma_sep_str, from_comma_sep_str]
+UUIDStrSet = Annotated[set[UUIDStr], to_comma_sep_str, from_comma_sep_str]
 
 list_models_to_json_str = PlainSerializer(
     lambda x: json.dumps([y.model_dump(mode="json") for y in x]),
diff --git a/test_utils/conftest.py b/test_utils/conftest.py
index 232c1fc..c2c46c5 100644
--- a/test_utils/conftest.py
+++ b/test_utils/conftest.py
@@ -1,22 +1,19 @@
 import os
 import shutil
-import sys
 from datetime import datetime, timedelta, timezone
 from os.path import join as pjoin
 from pathlib import Path
 from typing import TYPE_CHECKING, Callable, Generator
 from uuid import uuid4
 
-import django
 import pytest
 import redis
 from _pytest.config import Config
-from django.conf import settings as django_settings
-from django.core.management import call_command
 from dotenv import load_dotenv
-from pydantic import MariaDBDsn, PostgresDsn
+from pydantic import MariaDBDsn, PostgresDsn, TypeAdapter
 from redis import Redis
 
+from generalresearch.models.custom_types import InternalHostname
 from generalresearch.pg_helper import PostgresConfig
 from generalresearch.redis_helper import RedisConfig
 from generalresearch.sql_helper import SqlHelper
@@ -28,7 +25,7 @@ if TYPE_CHECKING:
 
 
 @pytest.fixture(scope="session")
-def env_file_path(pytestconfig: Config) -> str:
+def env_file_path(pytestconfig: Config) -> Path:
     root_path = pytestconfig.rootpath
     env_file = ".env.test"
 
@@ -40,18 +37,16 @@ def env_file_path(pytestconfig: Config) -> str:
     for env_path in candidates:
         if os.path.exists(env_path):
             load_dotenv(dotenv_path=env_path, override=True)
-            return os.path.normpath(env_path)
+            return Path(os.path.normpath(env_path))
 
     raise AssertionError(f"No .env.test file found in: {', '.join(candidates)}")
 
 
 @pytest.fixture(scope="session")
-def settings(env_file_path: str) -> "GRLBaseSettings":
+def settings(env_file_path: Path) -> "GRLBaseSettings":
     from generalresearch.config import GRLBaseSettings
 
-    print(f"{env_file_path=}")
-
-    s = GRLBaseSettings(_env_file=env_file_path)
+    s = GRLBaseSettings()
 
     if s.thl_mkpl_rr_db is not None:
         if s.spectrum_rw_db is None:
@@ -71,14 +66,28 @@ def settings(env_file_path: str) -> "GRLBaseSettings":
 def postgres_instance(settings: "GRLBaseSettings") -> Generator[PostgresDsn]:
     """Create a ephemeral postgresql instance for us to use during pytest.
 
-    This is simplified, and only based off a single host. We don't want to
-    create multiple migrated tmp databases for each rw/rr/ro connection
+    This does not create any tables, or schema definitions within the instance.
+    What this does is simply:
+
+        1. Create a database on a known, consistent, staging or unittest
+            defined Postgres server.
+
+        2. Return the PostgresDsn of that table
+
+        3. On shutdown, go ahead and delete that database after the
+            tests have finished.
     """
 
-    assert settings.thl_web_rw_db
-    # assert settings.thl_web_rw_db.host
+    msg = "Must define Postgres test settings"
+    assert settings.testing_postgres, msg
+    assert settings.testing_postgres_user, msg
+    assert settings.testing_postgres_pass, msg
 
-    dsn: PostgresDsn = settings.thl_web_rw_db
+    db_uri, db_user, db_pass = (
+        settings.testing_postgres,
+        settings.testing_postgres_user,
+        settings.testing_postgres_pass,
+    )
 
     # Connect to default DB to create the new one
     from psycopg import connect
@@ -86,40 +95,52 @@ def postgres_instance(settings: "GRLBaseSettings") -> Generator[PostgresDsn]:
 
     now = datetime.now(timezone.utc)
     ts: str = now.strftime("%Y-%m-%d")
-
     db_name = f"unittest-{ts}-{uuid4().hex[:6]}"
-    print("XXX", str(dsn))
-    conn = connect(str(dsn))
+
+    db_path_connect = f"postgres://{db_user}:{db_pass}@{db_uri}"
+    db_path = f"{db_path_connect}/{db_name}"
+
+    # The DATABASE does NOT yet exist on the Postgres SERVER, thus
+    # we first must connect only to the SERVER (eg: default postgres path used)
+    conn = connect(f"{db_path_connect}/postgres")
     conn.autocommit = True
     cur = conn.cursor()
     cur.execute(SQL("CREATE DATABASE {}").format(Identifier(db_name)))
     cur.close()
     conn.close()
 
-    host = dsn.hosts()[0]
-    db_url = (
-        f"postgres://{host['username']}:{host['password']}@{host['host']}/{db_name}"
-    )
-
-    yield PostgresDsn(db_url)
+    yield PostgresDsn(db_path)
 
     # Teardown: drop the DB after the session
-    conn = connect(str(dsn))
+    conn = connect(f"{db_path_connect}/postgres")
     conn.autocommit = True
     cur = conn.cursor()
-    # cur.execute(SQL("DROP DATABASE {}").format(Identifier(db_name)))
+    cur.execute(SQL("DROP DATABASE {}").format(Identifier(db_name)))
     cur.close()
     conn.close()
 
 
+@pytest.fixture
+def postgres_instance_host(
+    postgres_instance: PostgresDsn,
+) -> Generator[InternalHostname]:
+    host = postgres_instance.hosts()[0]["host"]
+    assert host is not None
+
+    adapter = TypeAdapter(InternalHostname)
+    value = adapter.validate_python(host)
+    yield value
+
+
 @pytest.fixture(scope="session")
-def django_db_setup(settings: "GRLBaseSettings") -> Callable[..., None]:
+def django_db_setup(postgres_instance: PostgresDsn) -> Callable[..., None]:
 
-    def _inner():
+    import django
+    from django.apps import apps
+    from django.conf import settings as django_settings
+    from django.core.management import call_command
 
-        assert settings.thl_web_rw_db
-        dsn: PostgresDsn = settings.thl_web_rw_db
-        host = dsn.hosts()[0]
+    def _inner():
 
         # 1. Bootstrapping Django settings
         if not django_settings.configured:
@@ -128,10 +149,10 @@ def django_db_setup(settings: "GRLBaseSettings") -> Callable[..., None]:
                     "default": {
                         "ENGINE": "django.db.backends.postgresql",
                         # PostgresDsn stores path as "/dbname"
-                        "NAME": str(dsn.path).lstrip("/"),
-                        "USER": host["username"],
-                        "PASSWORD": host["password"],
-                        "HOST": host["host"],
+                        "NAME": str(postgres_instance.path).lstrip("/"),
+                        "USER": postgres_instance["username"],
+                        "PASSWORD": postgres_instance["password"],
+                        "HOST": postgres_instance["host"],
                         "PORT": "5432",
                     }
                 },
@@ -143,8 +164,6 @@ def django_db_setup(settings: "GRLBaseSettings") -> Callable[..., None]:
             )
         django.setup()
 
-        from django.apps import apps
-
         for model in apps.get_models():
             print(f"Discovered model: {model._meta.label}")
 
@@ -155,65 +174,44 @@ def django_db_setup(settings: "GRLBaseSettings") -> Callable[..., None]:
 
 
 @pytest.fixture(scope="session")
-def thl_web_rr(
-    settings: "GRLBaseSettings", postgres_instance: PostgresDsn, django_db_setup
-) -> PostgresConfig:
-    dsn = settings.thl_web_rr_db
-    assert dsn
-    assert dsn.path
-
-    if dsn.path not in ["/", "/postgres"]:
-        assert "/unittest-" in dsn.path
-
-    db_path = postgres_instance.path
-    host = dsn.hosts()[0]
-    db_url = f"postgres://{host['username']}:{host['password']}@{host['host']}{db_path}"
+def thl_web_rr(postgres_instance: PostgresDsn, django_db_setup) -> PostgresConfig:
 
     # Run Migrations now.
+    # generalresearch/thl_django
     django_db_setup()
 
     return PostgresConfig(
-        dsn=PostgresDsn(db_url),
+        dsn=postgres_instance,
         connect_timeout=1,
         statement_timeout=5,
     )
 
 
 @pytest.fixture(scope="session")
-def thl_web_rw(
-    settings: "GRLBaseSettings", postgres_instance: PostgresDsn, django_db_setup
-) -> PostgresConfig:
-    dsn = settings.thl_web_rw_db
-    assert dsn
-    assert dsn.path
+def thl_web_rw(thl_web_rr: PostgresConfig) -> PostgresConfig:
+    return thl_web_rr
 
-    if dsn.path not in ["/", "/postgres"]:
-        assert "/unittest-" in dsn.path
 
-    db_path = postgres_instance.path
-    host = dsn.hosts()[0]
-    db_url = f"postgres://{host['username']}:{host['password']}@{host['host']}{db_path}"
+@pytest.fixture(scope="session")
+def gr_db(postgres_instance: PostgresDsn) -> PostgresConfig:
 
-    # Run Migrations now.
+    # Run Migrations, somehow pull from other repo...
     django_db_setup()
-
-    return PostgresConfig(
-        dsn=PostgresDsn(db_url),
-        connect_timeout=1,
-        statement_timeout=5,
-    )
+    return PostgresConfig(dsn=postgres_instance, connect_timeout=1, statement_timeout=5)
 
 
 @pytest.fixture(scope="session")
-def gr_db(settings: "GRLBaseSettings") -> PostgresConfig:
-    dsn = settings.gr_db
-    assert dsn
-    assert dsn.path
+def grliq_db(postgres_instance: PostgresDsn) -> PostgresConfig:
 
-    if dsn.path not in ["/", "/postgres"]:
-        assert "/unittest-" in dsn.path
+    # test_words = {"localhost", "127.0.0.1", "unittest", "grliq-test"}
+    # assert any(w in str(postgres_config.dsn) for w in test_words), "check grliq postgres_config"
+    # assert "grliqdeceezpocymo" not in str(postgres_config.dsn), "check grliq postgres_config"
 
-    return PostgresConfig(dsn=settings.gr_db, connect_timeout=5, statement_timeout=2)
+    return PostgresConfig(
+        dsn=postgres_instance,
+        connect_timeout=1,
+        statement_timeout=5,
+    )
 
 
 @pytest.fixture(scope="session")
@@ -234,26 +232,6 @@ def spectrum_rw(settings: "GRLBaseSettings") -> SqlHelper:
 
 
 @pytest.fixture(scope="session")
-def grliq_db(settings: "GRLBaseSettings") -> PostgresConfig:
-    dsn = settings.grliq_db
-    assert dsn
-    assert dsn.path
-
-    if dsn.path not in ["/", "/postgres"]:
-        assert "/unittest-" in dsn.path
-
-    # test_words = {"localhost", "127.0.0.1", "unittest", "grliq-test"}
-    # assert any(w in str(postgres_config.dsn) for w in test_words), "check grliq postgres_config"
-    # assert "grliqdeceezpocymo" not in str(postgres_config.dsn), "check grliq postgres_config"
-
-    return PostgresConfig(
-        dsn=settings.grliq_db,
-        connect_timeout=2,
-        statement_timeout=2,
-    )
-
-
-@pytest.fixture(scope="session")
 def thl_redis(settings: "GRLBaseSettings") -> "Redis":
     # todo: this should get replaced with redisconfig (in most places)
     # I'm not sure where this would be? in the domain name?
diff --git a/test_utils/incite/conftest.py b/test_utils/incite/conftest.py
index 058093e..0e2f7bd 100644
--- a/test_utils/incite/conftest.py
+++ b/test_utils/incite/conftest.py
@@ -10,9 +10,6 @@ import pytest
 from _pytest.fixtures import SubRequest
 from faker import Faker
 
-# from test_utils.managers.ledger.conftest import session_with_tx_factory
-# from test_utils.models.conftest import session_factory
-
 if TYPE_CHECKING:
     from generalresearch.config import GRLBaseSettings
     from generalresearch.incite.base import GRLDatasets
diff --git a/tests/incite/collections/test_df_collection_item_thl_web.py b/tests/incite/collections/test_df_collection_item_thl_web.py
index a858fbe..8b8bcbe 100644
--- a/tests/incite/collections/test_df_collection_item_thl_web.py
+++ b/tests/incite/collections/test_df_collection_item_thl_web.py
@@ -1,3 +1,6 @@
+from __future__ import annotations
+
+from collections.abc import Generator
 from datetime import datetime, timedelta, timezone
 from itertools import product as iter_product
 from os.path import join as pjoin
@@ -12,13 +15,7 @@ from distributed import Client, Scheduler, Worker
 
 # noinspection PyUnresolvedReferences
 from distributed.utils_test import (
-    cleanup,
-    client,
-    client_no_amm,
-    cluster_fixture,
     gen_cluster,
-    loop,
-    loop_in_thread,
 )
 from faker import Faker
 from pandera.pandas import DataFrameSchema
@@ -34,7 +31,6 @@ from generalresearch.models.thl.product import Product
 from generalresearch.models.thl.user import User
 from generalresearch.pg_helper import PostgresConfig
 from generalresearch.sql_helper import PostgresDsn
-from test_utils.incite.conftest import incite_item_factory, mnt_filepath
 
 if TYPE_CHECKING:
     from generalresearch.incite.base import GRLDatasets
@@ -56,12 +52,12 @@ unsupported_mock_types = {
 }
 
 
-def combo_object():
+def combo_object() -> Generator[str, None, None]:
     for x in iter_product(
         df_collections,
         ["15min", "45min", "1H"],
     ):
-        yield x
+        yield from x
 
 
 class TestDFCollectionItemBase:
@@ -199,7 +195,7 @@ class TestDFCollectionItemMethod:
         client_no_amm,
         incite_item_factory,
         delete_df_collection,
-        mnt_filepath: "GRLDatasets",
+        mnt_filepath: GRLDatasets,
     ):
         assert 1 + 1 == 2
 
@@ -768,7 +764,7 @@ class TestDFCollectionItemFunctionalTest:
         product: Product,
         incite_item_factory,
         delete_df_collection,
-        mnt_filepath: "GRLDatasets",
+        mnt_filepath: GRLDatasets,
     ):
         from generalresearch.models.thl.user import User
 
@@ -818,7 +814,7 @@ class TestDFCollectionItemFunctionalTest:
         df_collection_data_type,
         incite_item_factory,
         delete_df_collection,
-        mnt_filepath: "GRLDatasets",
+        mnt_filepath: GRLDatasets,
     ):
         """A functional test to write some Parquet files for the
         DFCollection and then confirm that the files get written
@@ -866,7 +862,7 @@ class TestDFCollectionItemFunctionalTest:
         df_collection_data_type,
         incite_item_factory,
         delete_df_collection,
-        mnt_filepath: "GRLDatasets",
+        mnt_filepath: GRLDatasets,
     ):
         from generalresearch.models.thl.user import User
 
@@ -919,7 +915,7 @@ class TestDFCollectionItemFunctionalTest:
         product: Product,
         offset: str,
         duration: timedelta,
-        mnt_filepath: "GRLDatasets",
+        mnt_filepath: GRLDatasets,
     ):
         """Don't allow creating an archive for data that will likely be
         overwritten or updated
@@ -960,7 +956,7 @@ class TestDFCollectionItemFunctionalTest:
         user: User,
         offset: str,
         duration: timedelta,
-        mnt_filepath: "GRLDatasets",
+        mnt_filepath: GRLDatasets,
     ):
         delete_df_collection(coll=df_collection)
 
diff --git a/tests/incite/collections/test_df_collection_thl_marketplaces.py b/tests/incite/collections/test_df_collection_thl_marketplaces.py
index 8ce8acc..981f62e 100644
--- a/tests/incite/collections/test_df_collection_thl_marketplaces.py
+++ b/tests/incite/collections/test_df_collection_thl_marketplaces.py
@@ -28,7 +28,7 @@ def combo_object():
         ],
         ["5min", "6H", "30D"],
     ):
-        yield x
+        yield from x
 
 
 @pytest.mark.parametrize("df_coll, offset", combo_object())
diff --git a/tests/incite/collections/test_df_collection_thl_web.py b/tests/incite/collections/test_df_collection_thl_web.py
index c64dac8..b09d44c 100644
--- a/tests/incite/collections/test_df_collection_thl_web.py
+++ b/tests/incite/collections/test_df_collection_thl_web.py
@@ -1,3 +1,6 @@
+from __future__ import annotations
+
+from collections.abc import Generator
 from datetime import datetime
 from itertools import product
 from typing import TYPE_CHECKING
@@ -11,9 +14,13 @@ from generalresearch.incite.collections import DFCollection, DFCollectionType
 
 if TYPE_CHECKING:
     from generalresearch.incite.base import GRLDatasets
+    from generalresearch.incite.collections import (
+        DFCollectionItem,
+        DFCollectionType,
+    )
 
 
-def combo_object():
+def combo_object() -> Generator[tuple, None, None]:
     for x in product(
         [
             DFCollectionType.USER,
@@ -25,7 +32,7 @@ def combo_object():
         ],
         ["30min", "1H"],
     ):
-        yield x
+        yield from x
 
 
 @pytest.mark.parametrize(
@@ -33,7 +40,9 @@ def combo_object():
 )
 class TestDFCollection_thl_web:
 
-    def test_init(self, df_collection_data_type, offset: str, df_collection):
+    def test_init(
+        self, df_collection_data_type: DFCollectionType, offset: str, df_collection
+    ):
         assert isinstance(df_collection_data_type, DFCollectionType)
         assert isinstance(df_collection, DFCollection)
 
@@ -43,12 +52,12 @@ class TestDFCollection_thl_web:
 )
 class TestDFCollection_thl_web_Properties:
 
-    def test_items(self, df_collection_data_type, offset: str, df_collection):
+    def test_items(self, df_collection):
         assert isinstance(df_collection.items, list)
         for i in df_collection.items:
             assert i._collection == df_collection
 
-    def test__schema(self, df_collection_data_type, offset: str, df_collection):
+    def test__schema(self, df_collection):
         assert isinstance(df_collection._schema, DataFrameSchema)
 
 
@@ -58,16 +67,16 @@ class TestDFCollection_thl_web_Properties:
 class TestDFCollection_thl_web_BaseProperties:
 
     @pytest.mark.skip
-    def test__interval_range(self, df_collection_data_type, offset: str, df_collection):
+    def test__interval_range(self, df_collection):
         pass
 
-    def test_interval_start(self, df_collection_data_type, offset: str, df_collection):
+    def test_interval_start(self, df_collection):
         assert isinstance(df_collection.interval_start, datetime)
 
-    def test_interval_range(self, df_collection_data_type, offset: str, df_collection):
+    def test_interval_range(self, df_collection):
         assert isinstance(df_collection.interval_range, list)
 
-    def test_progress(self, df_collection_data_type, offset: str, df_collection):
+    def test_progress(self, df_collection):
         assert isinstance(df_collection.progress, pd.DataFrame)
 
 
diff --git a/tests/pytest.ini b/tests/pytest.ini
index d280de0..1a5c089 100644
--- a/tests/pytest.ini
+++ b/tests/pytest.ini
@@ -1,2 +1 @@
-[pytest]
-asyncio_mode = auto
\ No newline at end of file
+[pytest]
\ No newline at end of file
diff --git a/tests/test_postgres.py b/tests/test_postgres.py
new file mode 100644
index 0000000..92eed71
--- /dev/null
+++ b/tests/test_postgres.py
@@ -0,0 +1,44 @@
+import socket
+import subprocess
+
+from pydantic import PostgresDsn
+
+from generalresearch.models.custom_types import InternalHostname
+from generalresearch.pg_helper import PostgresConfig
+
+
+def is_port_open(host: InternalHostname, port: int = 5432, timeout: int = 3):
+    try:
+        with socket.create_connection((host, port), timeout=timeout):
+            return True
+    except (socket.timeout, ConnectionRefusedError, OSError):
+        return False
+
+
+def can_ping(host: InternalHostname):
+    return (
+        subprocess.call(
+            ["ping", "-c", "1", str(host)],
+            stdout=subprocess.DEVNULL,
+            stderr=subprocess.DEVNULL,
+        )
+        == 0
+    )
+
+
+class TestPostgresDSN:
+
+    def test_ping(self, postgres_instance_host: InternalHostname):
+        assert can_ping(host=postgres_instance_host)
+
+    def test_port(self, postgres_instance_host: InternalHostname):
+        assert is_port_open(host=postgres_instance_host)
+
+    def test_conn(self, postgres_instance: PostgresDsn):
+        config = PostgresConfig(
+            dsn=postgres_instance,
+            connect_timeout=1,
+            statement_timeout=1,
+        )
+        res = config.execute_sql_query(query="SELECT 1;")
+        assert len(res) == 1