supabase-postgres.git / summary / log / commit / refs

commit    431fb732e1da504fe675f8eb0a17b1eabafbc628
Author:   Oliver Rice <github@oliverrice.com>
Commit:   Div Arora <darora@users.noreply.github.com>
Date:     Wed Aug 21 18:16:27 2024 +0000

    add pg_jsonschema tests


 nix/tests/expected/pg_jsonschema.out | 73 ++++++++++++++++++++++++++++++++++++
 nix/tests/sql/pg_jsonschema.sql      | 68 +++++++++++++++++++++++++++++++++
 2 files changed, 141 insertions(+)

diff --git a/nix/tests/expected/pg_jsonschema.out b/nix/tests/expected/pg_jsonschema.out
new file mode 100644
index 00000000..c291141a
--- /dev/null
+++ b/nix/tests/expected/pg_jsonschema.out
@@ -0,0 +1,73 @@
+begin;
+-- Test json_matches_schema
+create table customer(
+    id serial primary key,
+    metadata json,
+    check (
+        json_matches_schema(
+            '{
+                "type": "object",
+                "properties": {
+                    "tags": {
+                        "type": "array",
+                        "items": {
+                            "type": "string",
+                            "maxLength": 16
+                        }
+                    }
+                }
+            }',
+            metadata
+        )
+    )
+);
+insert into customer(metadata)
+values ('{"tags": ["vip", "darkmode-ui"]}');
+-- Test jsonb_matches_schema
+select
+  jsonb_matches_schema(
+  '{
+    "type": "object",
+    "properties": {
+	  "tags": {
+        "type": "array",
+        "items": {
+          "type": "string",
+          "maxLength": 16
+        }
+      }
+      }
+  }',
+  '{"tags": ["vip", "darkmode-ui"]}'::jsonb
+);
+ jsonb_matches_schema 
+----------------------
+ t
+(1 row)
+
+-- Test jsonschema_is_valid
+select
+  jsonschema_is_valid(
+  '{
+    "type": "object",
+    "properties": {
+	  "tags": {
+        "type": "array",
+        "items": {
+          "type": "string",
+          "maxLength": 16
+        }
+      }
+    }
+  }');
+ jsonschema_is_valid 
+---------------------
+ t
+(1 row)
+
+-- Test invalid payload
+insert into customer(metadata)
+values ('{"tags": [1, 3]}');
+ERROR:  new row for relation "customer" violates check constraint "customer_metadata_check"
+DETAIL:  Failing row contains (2, {"tags": [1, 3]}).
+rollback;
diff --git a/nix/tests/sql/pg_jsonschema.sql b/nix/tests/sql/pg_jsonschema.sql
new file mode 100644
index 00000000..f5d7c8c7
--- /dev/null
+++ b/nix/tests/sql/pg_jsonschema.sql
@@ -0,0 +1,68 @@
+begin;
+
+-- Test json_matches_schema
+create table customer(
+    id serial primary key,
+    metadata json,
+
+    check (
+        json_matches_schema(
+            '{
+                "type": "object",
+                "properties": {
+                    "tags": {
+                        "type": "array",
+                        "items": {
+                            "type": "string",
+                            "maxLength": 16
+                        }
+                    }
+                }
+            }',
+            metadata
+        )
+    )
+);
+
+insert into customer(metadata)
+values ('{"tags": ["vip", "darkmode-ui"]}');
+
+-- Test jsonb_matches_schema
+select
+  jsonb_matches_schema(
+  '{
+    "type": "object",
+    "properties": {
+	  "tags": {
+        "type": "array",
+        "items": {
+          "type": "string",
+          "maxLength": 16
+        }
+      }
+      }
+  }',
+  '{"tags": ["vip", "darkmode-ui"]}'::jsonb
+);
+
+-- Test jsonschema_is_valid
+select
+  jsonschema_is_valid(
+  '{
+    "type": "object",
+    "properties": {
+	  "tags": {
+        "type": "array",
+        "items": {
+          "type": "string",
+          "maxLength": 16
+        }
+      }
+    }
+  }');
+
+-- Test invalid payload
+insert into customer(metadata)
+values ('{"tags": [1, 3]}');
+
+rollback;


[parent: 7c1f76948003]