Repository: meltano/sdk
Pull request #1865 (2023-07-20 22:04:49)

fix(targets): Handle missing record properties in SQL sinks
Closes https://github.com/meltano/sdk/issues/1134


codecov[bot]: ## [Codecov](https://app.codecov.io/gh/meltano/sdk/pull/1865?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) Report
> Merging [#1865](https://app.codecov.io/gh/meltano/sdk/pull/1865?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) (db5cb7e) into [main](https://app.codecov.io/gh/meltano/sdk/commit/384cff7526b2ac08413c9d1f4633404c7f7be481?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) (384cff7) will **increase** coverage by `0.00%`.
> The diff coverage is `100.00%`.

```diff
@@           Coverage Diff           @@
##             main    #1865   +/-   ##
=======================================
  Coverage   86.68%   86.68%           
=======================================
  Files          59       59           
  Lines        5000     5002    +2     
  Branches      811      812    +1     
=======================================
+ Hits         4334     4336    +2     
  Misses        470      470           
  Partials      196      196           
```


| [Impacted Files](https://app.codecov.io/gh/meltano/sdk/pull/1865?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) | Coverage Δ | |
|---|---|---|
| [singer\_sdk/sinks/sql.py](https://app.codecov.io/gh/meltano/sdk/pull/1865?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano#diff-c2luZ2VyX3Nkay9zaW5rcy9zcWwucHk=) | `90.35% <100.00%> (+0.17%)` | :arrow_up: |

:mega: We’re building smart automated test selection to slash your CI/CD build times. [Learn more](https://about.codecov.io/iterative-testing/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano)


Patch:
diff --git a/singer_sdk/sinks/sql.py b/singer_sdk/sinks/sql.py
index 02fd307b..9dac99b1 100644
--- a/singer_sdk/sinks/sql.py
+++ b/singer_sdk/sinks/sql.py
@@ -323,10 +323,20 @@ class SQLSink(BatchSink):
             insert_sql = sqlalchemy.text(insert_sql)
 
         conformed_records = [self.conform_record(record) for record in records]
+        property_names = list(self.conform_schema(schema)["properties"].keys())
+
+        # Create new record dicts with missing properties filled in with None
+        new_records = [
+            {name: record.get(name) for name in property_names}
+            for record in conformed_records
+        ]
+
         self.logger.info("Inserting with SQL: %s", insert_sql)
+
         with self.connector._connect() as conn, conn.begin():
-            conn.execute(insert_sql, conformed_records)
-        return len(conformed_records) if isinstance(conformed_records, list) else None
+            result = conn.execute(insert_sql, new_records)
+
+        return result.rowcount
 
     def merge_upsert_from_table(
         self,


Test patch:
diff --git a/singer_sdk/testing/suites.py b/singer_sdk/testing/suites.py
index 0f8a9fab..d795cf15 100644
--- a/singer_sdk/testing/suites.py
+++ b/singer_sdk/testing/suites.py
@@ -37,6 +37,7 @@ from .target_tests import (
     TargetOptionalAttributes,
     TargetRecordBeforeSchemaTest,
     TargetRecordMissingKeyProperty,
+    TargetRecordMissingOptionalFields,
     TargetSchemaNoProperties,
     TargetSchemaUpdates,
     TargetSpecialCharsInAttributes,
@@ -103,6 +104,7 @@ target_tests = TestSuite(
         TargetOptionalAttributes,
         TargetRecordBeforeSchemaTest,
         TargetRecordMissingKeyProperty,
+        TargetRecordMissingOptionalFields,
         TargetSchemaNoProperties,
         TargetSchemaUpdates,
         TargetSpecialCharsInAttributes,
diff --git a/singer_sdk/testing/target_test_streams/record_missing_fields.singer b/singer_sdk/testing/target_test_streams/record_missing_fields.singer
new file mode 100644
index 00000000..a398f6bd
--- /dev/null
+++ b/singer_sdk/testing/target_test_streams/record_missing_fields.singer
@@ -0,0 +1,4 @@
+{"type": "SCHEMA", "stream": "record_missing_fields", "key_properties": ["id"], "schema": {"type": "object", "properties": {"id": {"type": "integer"}, "optional": {"type": "string"}}, "required": ["id"]}}
+{"type": "RECORD", "stream": "record_missing_fields", "record": {"id": 1, "optional": "now you see me"}}
+{"type": "RECORD", "stream": "record_missing_fields", "record": {"id": 2}}
+{"type": "STATE", "value": {}}
diff --git a/singer_sdk/testing/target_tests.py b/singer_sdk/testing/target_tests.py
index 8412329c..96e0b0d5 100644
--- a/singer_sdk/testing/target_tests.py
+++ b/singer_sdk/testing/target_tests.py
@@ -139,3 +139,9 @@ class TargetSpecialCharsInAttributes(TargetFileTestTemplate):
     """Test Target handles special chars in attributes."""
 
     name = "special_chars_in_attributes"
+
+
+class TargetRecordMissingOptionalFields(TargetFileTestTemplate):
+    """Test Target handles record missing optional fields."""
+
+    name = "record_missing_fields"
diff --git a/tests/samples/test_target_sqlite.py b/tests/samples/test_target_sqlite.py
index a7ca3b3c..abf1bcca 100644
--- a/tests/samples/test_target_sqlite.py
+++ b/tests/samples/test_target_sqlite.py
@@ -396,6 +396,35 @@ def test_sqlite_column_no_morph(sqlite_sample_target: SQLTarget):
     target_sync_test(sqlite_sample_target, input=StringIO(tap_output_b), finalize=True)
 
 
+def test_record_with_missing_properties(
+    sqlite_sample_target: SQLTarget,
+):
+    """Test handling of records with missing properties."""
+    tap_output = "\n".join(
+        json.dumps(msg)
+        for msg in [
+            {
+                "type": "SCHEMA",
+                "stream": "test_stream",
+                "schema": {
+                    "type": "object",
+                    "properties": {
+                        "id": {"type": "integer"},
+                        "name": {"type": "string"},
+                    },
+                },
+                "key_properties": ["id"],
+            },
+            {
+                "type": "RECORD",
+                "stream": "test_stream",
+                "record": {"id": 1},
+            },
+        ]
+    )
+    target_sync_test(sqlite_sample_target, input=StringIO(tap_output), finalize=True)
+
+
 @pytest.mark.parametrize(
     "stream_name,schema,key_properties,expected_dml",
     [