Is it possible to use a json file to seed a supabase postgres database?
I want to use a json file as single source of truth for my database seeds and my end-to-end tests.
The following code works:
WITH
notification_json (doc) AS (
VALUES
('[
{
"sender": "42e58ca1-2eb8-4651-93c2-cefba2e32f42",
"receiver": "f8b028b8-231b-4c80-abf2-7ca787fe686f",
"type_of_notification": "follow_from_user",
"read_by_receiver": "FALSE"
}
]'::json)
)
INSERT
INTO
authenticated_access.notifications_by_user (sender,
receiver,
type_of_notification,
read_by_receiver)
SELECT
n.sender,
n.receiver,
n.type_of_notification,
n.read_by_receiver
FROM
notification_json l
CROSS JOIN LATERAL JSON_POPULATE_RECORDSET(
NULL::authenticated_access.notifications_by_user,
doc) AS n;
But if I try to load the JSON from a separate file with
ERROR: could not open file "notifications.json" for reading: No such file or directory (SQLSTATE 58P01)
While this means, that the file does not exist, I checked it and the file does exist and the directory structure looks like this:
supabase
WITH
notification_json (doc) AS (
VALUES
((
SELECT PG_READ_FILE('./notifications.json')
)::json)
)
What is wrong here? Is it possible to use a json file in the supabase seed process?
The following code works:
WITH
notification_json (doc) AS (
VALUES
('[
{
"sender": "42e58ca1-2eb8-4651-93c2-cefba2e32f42",
"receiver": "f8b028b8-231b-4c80-abf2-7ca787fe686f",
"type_of_notification": "follow_from_user",
"read_by_receiver": "FALSE"
}
]'::json)
)
INSERT
INTO
authenticated_access.notifications_by_user (sender,
receiver,
type_of_notification,
read_by_receiver)
SELECT
n.sender,
n.receiver,
n.type_of_notification,
n.read_by_receiver
FROM
notification_json l
CROSS JOIN LATERAL JSON_POPULATE_RECORDSET(
NULL::authenticated_access.notifications_by_user,
doc) AS n;
But if I try to load the JSON from a separate file with
PG_READ_FILE('./notifications.json') then the code fails with the error: ERROR: could not open file "notifications.json" for reading: No such file or directory (SQLSTATE 58P01)
While this means, that the file does not exist, I checked it and the file does exist and the directory structure looks like this:
supabase
- migrations
- seed.sql
- notifications.json
WITH
notification_json (doc) AS (
VALUES
((
SELECT PG_READ_FILE('./notifications.json')
)::json)
)
What is wrong here? Is it possible to use a json file in the supabase seed process?
