Repository: meltano/sdk
Pull request #3004 (2025-04-28 17:45:51)

fix(targets): Ensure fields with an array of JSON schema types are properly handled by SQL targets
…operly handled by SQL targets

## Summary by Sourcery

Fix SQL type mapping for JSON schemas defining multiple types for a field.

Bug Fixes:
- Ensure the result of the `handle_multiple_types` method is returned when mapping JSON schema types to SQL, correctly handling cases like `type: ["object", "null"]` or `anyOf` definitions.

Tests:
- Add tests to verify correct SQL type mapping (e.g., to JSON) for fields with `anyOf` or an array of types specified in the JSON schema.
- Rename `test_complex` to `test_multiple_types` for clarity.

sourcery-ai[bot]: <!-- Generated by sourcery-ai[bot]: start review_guide -->

🧙 Sourcery is reviewing your pull request!

---

<details>
<summary>Tips and commands</summary>

#### Interacting with Sourcery

- **Trigger a new review:** Comment `@sourcery-ai review` on the pull request.
- **Continue discussions:** Reply directly to Sourcery's review comments.
- **Generate a GitHub issue from a review comment:** Ask Sourcery to create an
  issue from a review comment by replying to it. You can also reply to a
  review comment with `@sourcery-ai issue` to create an issue from it.
- **Generate a pull request title:** Write `@sourcery-ai` anywhere in the pull
  request title to generate a title at any time. You can also comment
  `@sourcery-ai title` on the pull request to (re-)generate the title at any time.
- **Generate a pull request summary:** Write `@sourcery-ai summary` anywhere in
  the pull request body to generate a PR summary at any time exactly where you
  want it. You can also comment `@sourcery-ai summary` on the pull request to
  (re-)generate the summary at any time.
- **Generate reviewer's guide:** Comment `@sourcery-ai guide` on the pull
  request to (re-)generate the reviewer's guide at any time.
- **Resolve all Sourcery comments:** Comment `@sourcery-ai resolve` on the
  pull request to resolve all Sourcery comments. Useful if you've already
  addressed all the comments and don't want to see them anymore.
- **Dismiss all Sourcery reviews:** Comment `@sourcery-ai dismiss` on the pull
  request to dismiss all existing Sourcery reviews. Especially useful if you
  want to start fresh with a new review - don't forget to comment
  `@sourcery-ai review` to trigger a new review!

#### Customizing Your Experience

Access your [dashboard](https://app.sourcery.ai) to:
- Enable or disable review features such as the Sourcery-generated pull request
  summary, the reviewer's guide, and others.
- Change the review language.
- Add, remove or edit custom review instructions.
- Adjust other review settings.

#### Getting Help

- [Contact our support team](mailto:support@sourcery.ai) for questions or feedback.
- Visit our [documentation](https://docs.sourcery.ai) for detailed guides and information.
- Keep in touch with the Sourcery team by following us on [X/Twitter](https://x.com/SourceryAI), [LinkedIn](https://www.linkedin.com/company/sourcery-ai/) or [GitHub](https://github.com/sourcery-ai).

</details>

<!-- Generated by sourcery-ai[bot]: end review_guide -->
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/meltano/sdk/pull/3004?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Project coverage is 91.52%. Comparing base [(`9fe2c5f`)](https://app.codecov.io/gh/meltano/sdk/commit/9fe2c5f9b00e8d3bbd2e694c6c0ad97dcdd9c93e?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) to head [(`c978399`)](https://app.codecov.io/gh/meltano/sdk/commit/c978399d1216e4f7d35132dcc7dffecd156b5805?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##             main    #3004      +/-   ##
==========================================
- Coverage   91.53%   91.52%   -0.02%     
==========================================
  Files          63       63              
  Lines        5319     5319              
  Branches      683      683              
==========================================
- Hits         4869     4868       -1     
  Misses        318      318              
- Partials      132      133       +1     
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/meltano/sdk/pull/3004?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).

<details><summary> :rocket: New features to boost your workflow: </summary>

- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
</details>
codspeed-hq[bot]: <!-- __CODSPEED_PERFORMANCE_REPORT_COMMENT__ -->
<!-- __CODSPEED_INSTRUMENTATION_PERFORMANCE_REPORT_COMMENT__ -->

## [CodSpeed Performance Report](https://codspeed.io/meltano/sdk/branches/fix%2Fhandle-multiple-types)

### Merging #3004 will **not alter performance**

<sub>Comparing <code>fix/handle-multiple-types</code> (c978399) with <code>main</code> (9fe2c5f)</sub>



### Summary

`✅ 7` untouched benchmarks  





Patch:
diff --git a/singer_sdk/connectors/sql.py b/singer_sdk/connectors/sql.py
index ba0d3246..99e104c1 100644
--- a/singer_sdk/connectors/sql.py
+++ b/singer_sdk/connectors/sql.py
@@ -479,7 +479,7 @@ class JSONSchemaToSQL:
 
                 # If we have multiple non-null types, use VARCHAR
                 if len(non_null_types) > 1:
-                    self.handle_multiple_types(non_null_types)
+                    return self.handle_multiple_types(non_null_types)
 
                 # If we have exactly one non-null type, use its handler
                 if len(non_null_types) == 1 and non_null_types[0] in self._type_mapping:


Test patch:
diff --git a/tests/core/test_connector_sql.py b/tests/core/test_connector_sql.py
index 0c7fa51a..478c7f72 100644
--- a/tests/core/test_connector_sql.py
+++ b/tests/core/test_connector_sql.py
@@ -645,6 +645,18 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
         result = json_schema_to_sql.to_sql_type(jsonschema_type)
         assert isinstance(result, sa.types.INTEGER)
 
+    def test_anyof_json(self):
+        json_schema_to_sql = JSONSchemaToSQL()
+        json_schema_to_sql.register_type_handler("object", sa.types.JSON)
+        jsonschema_type = {
+            "anyOf": [
+                {"type": "null"},
+                {"type": "object"},
+            ],
+        }
+        result = json_schema_to_sql.to_sql_type(jsonschema_type)
+        assert isinstance(result, sa.types.JSON)
+
     def test_anyof_unknown(self, json_schema_to_sql: JSONSchemaToSQL):
         jsonschema_type = {
             "anyOf": [
@@ -658,6 +670,11 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
     @pytest.mark.parametrize(
         "jsonschema_type,expected_type",
         [
+            pytest.param(
+                {"type": ["unknown", "null"]},
+                sa.types.VARCHAR,
+                id="unknown",
+            ),
             pytest.param(
                 {"type": ["array", "object", "boolean", "null"]},
                 sa.types.VARCHAR,
@@ -670,7 +687,7 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
             ),
         ],
     )
-    def test_complex(
+    def test_multiple_types(
         self,
         json_schema_to_sql: JSONSchemaToSQL,
         jsonschema_type: dict,
@@ -679,6 +696,22 @@ class TestJSONSchemaToSQL:  # noqa: PLR0904
         result = json_schema_to_sql.to_sql_type(jsonschema_type)
         assert isinstance(result, expected_type)
 
+    def test_multiple_types_custom_handler(self):
+        class CustomJSONSchemaToSQL(JSONSchemaToSQL):
+            def handle_multiple_types(self, types: list[str]) -> sa.types.TypeEngine:
+                if "object" in types or "array" in types:
+                    return sa.types.JSON()
+                return super().handle_multiple_types(types)
+
+        json_schema_to_sql = CustomJSONSchemaToSQL()
+        jsonschema_type = {"type": ["object", "array", "string", "null"]}
+        result = json_schema_to_sql.to_sql_type(jsonschema_type)
+        assert isinstance(result, sa.types.JSON)
+
+        jsonschema_type = {"type": ["string", "number", "null"]}
+        result = json_schema_to_sql.to_sql_type(jsonschema_type)
+        assert isinstance(result, sa.types.VARCHAR)
+
     def test_unknown_type(self, json_schema_to_sql: JSONSchemaToSQL):
         jsonschema_type = {"cannot": "compute"}
         result = json_schema_to_sql.to_sql_type(jsonschema_type)