Repository: meltano/sdk
Pull request #1898 (2023-08-07 19:18:43)

fix(targets): Correctly serialize `decimal.Decimal` in JSON fields of SQL targets
Closes https://github.com/meltano/sdk/issues/1897


<!-- readthedocs-preview meltano-sdk start -->
----
:books: Documentation preview :books:: https://meltano-sdk--1898.org.readthedocs.build/en/1898/

<!-- readthedocs-preview meltano-sdk end -->

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

```diff
@@            Coverage Diff             @@
##             main    #1898      +/-   ##
==========================================
+ Coverage   86.93%   86.94%   +0.01%     
==========================================
  Files          59       59              
  Lines        5073     5080       +7     
  Branches      822      822              
==========================================
+ Hits         4410     4417       +7     
  Misses        466      466              
  Partials      197      197              
```


| [Files Changed](https://app.codecov.io/gh/meltano/sdk/pull/1898?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) | Coverage Δ | |
|---|---|---|
| [singer\_sdk/connectors/sql.py](https://app.codecov.io/gh/meltano/sdk/pull/1898?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano#diff-c2luZ2VyX3Nkay9jb25uZWN0b3JzL3NxbC5weQ==) | `81.91% <100.00%> (+0.44%)` | :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/pyproject.toml b/pyproject.toml
index bceced1d..a795820d 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -2,8 +2,8 @@
 name = "singer-sdk"
 version = "0.30.0"
 description = "A framework for building Singer taps"
-authors = ["Meltano Team and Contributors"]
-maintainers = ["Meltano Team and Contributors"]
+authors = ["Meltano Team and Contributors <hello@meltano.com>"]
+maintainers = ["Meltano Team and Contributors <hello@meltano.com>"]
 readme = "README.md"
 homepage = "https://sdk.meltano.com/en/latest/"
 repository = "https://github.com/meltano/sdk"
@@ -144,7 +144,7 @@ name = "cz_version_bump"
 version = "0.30.0"
 tag_format = "v$major.$minor.$patch$prerelease"
 version_files = [
-    "docs/conf.py",
+    "docs/conf.py:^release =",
     "pyproject.toml:^version =",
     "cookiecutter/tap-template/{{cookiecutter.tap_id}}/pyproject.toml:singer-sdk",
     "cookiecutter/target-template/{{cookiecutter.target_id}}/pyproject.toml:singer-sdk",
diff --git a/singer_sdk/connectors/sql.py b/singer_sdk/connectors/sql.py
index e9a65cf8..e05e359d 100644
--- a/singer_sdk/connectors/sql.py
+++ b/singer_sdk/connectors/sql.py
@@ -2,6 +2,8 @@
 
 from __future__ import annotations
 
+import decimal
+import json
 import logging
 import typing as t
 import warnings
@@ -9,6 +11,7 @@ from contextlib import contextmanager
 from datetime import datetime
 from functools import lru_cache
 
+import simplejson
 import sqlalchemy
 from sqlalchemy.engine import Engine
 
@@ -316,7 +319,12 @@ class SQLConnector:
         Returns:
             A new SQLAlchemy Engine.
         """
-        return sqlalchemy.create_engine(self.sqlalchemy_url, echo=False)
+        return sqlalchemy.create_engine(
+            self.sqlalchemy_url,
+            echo=False,
+            json_serializer=self.serialize_json,
+            json_deserializer=self.deserialize_json,
+        )
 
     def quote(self, name: str) -> str:
         """Quote a name if it needs quoting, using '.' as a name-part delimiter.
@@ -1124,3 +1132,35 @@ class SQLConnector:
         )
         with self._connect() as conn:
             conn.execute(alter_column_ddl)
+
+    def serialize_json(self, obj: object) -> str:
+        """Serialize an object to a JSON string.
+
+        Target connectors may override this method to provide custom serialization logic
+        for JSON types.
+
+        Args:
+            obj: The object to serialize.
+
+        Returns:
+            The JSON string.
+
+        .. versionadded:: 0.31.0
+        """
+        return simplejson.dumps(obj, use_decimal=True)
+
+    def deserialize_json(self, json_str: str) -> object:
+        """Deserialize a JSON string to an object.
+
+        Tap connectors may override this method to provide custom deserialization
+        logic for JSON types.
+
+        Args:
+            json_str: The JSON string to deserialize.
+
+        Returns:
+            The deserialized object.
+
+        .. versionadded:: 0.31.0
+        """
+        return json.loads(json_str, parse_float=decimal.Decimal)


Test patch:
diff --git a/tests/core/test_connector_sql.py b/tests/core/test_connector_sql.py
index 1c04dbcd..58ba59ec 100644
--- a/tests/core/test_connector_sql.py
+++ b/tests/core/test_connector_sql.py
@@ -1,5 +1,6 @@
 from __future__ import annotations
 
+from decimal import Decimal
 from unittest import mock
 
 import pytest
@@ -258,3 +259,27 @@ class TestConnectorSQL:
     ):
         merged_type = connector.merge_sql_types(types)
         assert isinstance(merged_type, expected_type)
+
+    def test_engine_json_serialization(self, connector: SQLConnector):
+        engine = connector._engine
+        meta = sqlalchemy.MetaData()
+        table = sqlalchemy.Table(
+            "test_table",
+            meta,
+            sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
+            sqlalchemy.Column("attrs", sqlalchemy.JSON),
+        )
+        meta.create_all(engine)
+        with engine.connect() as conn:
+            conn.execute(
+                table.insert(),
+                [
+                    {"attrs": {"x": Decimal("1.0")}},
+                    {"attrs": {"x": Decimal("2.0"), "y": [1, 2, 3]}},
+                ],
+            )
+            result = conn.execute(table.select())
+            assert result.fetchall() == [
+                (1, {"x": Decimal("1.0")}),
+                (2, {"x": Decimal("2.0"), "y": [1, 2, 3]}),
+            ]