Mailgun Error - Failed to run sql query: invalid input syntax for type json

[Epikur86]

Hello,

trying to send an email through SQL-Editor I get the error : Failed to run sql query: invalid input syntax for type json. Not sure how to solve this issue.

select send_email_message('{"sender": "mailgun@sandbox07beb0c907ba54.mailgun.org","recipient":"info@test.com","subject": "This is a test message from my Supabase app!","html_body":"test","text_body":"test"}'); _send_emailmailgun

DECLARE
  retval json;
  MAILGUN_DOMAIN text;
  MAILGUN_API_KEY text;
BEGIN

  SELECT value::text INTO MAILGUN_DOMAIN FROM privatekeys WHERE key = 'MAILGUN_DOMAIN';
  IF NOT found THEN RAISE 'missing entry in privatekeys: MAILGUN_DOMAIN'; END IF;
  SELECT value::text INTO MAILGUN_API_KEY FROM privatekeys WHERE key = 'MAILGUN_API_KEY';
  IF NOT found THEN RAISE 'missing entry in privatekeys: MAILGUN_API_KEY'; END IF;

  SELECT
    content INTO retval
  FROM
    http (('POST', 
      'https://api.eu.mailgun.net/v3/' || MAILGUN_DOMAIN || '/messages', 
      ARRAY[http_header ('Authorization', 
      'Basic ' || encode(MAILGUN_API_KEY::bytea, 'base64'::text))], 
      'application/x-www-form-urlencoded', 
      'from=' || urlencode (message->>'sender') || 
      '&to=' || urlencode (message->>'recipient') || 
      CASE WHEN message->>'cc' IS NOT NULL THEN '&cc=' || urlencode(message->>'cc') ELSE '' END || 
      CASE WHEN message->>'bcc' IS NOT NULL THEN '&bcc=' || urlencode(message->>'bcc') ELSE '' END || 
      CASE WHEN message->>'messageid' IS NOT NULL THEN '&v:messageid=' || urlencode(message->>'messageid') ELSE '' END || 
      '&subject=' || urlencode(message->>'subject') || 
      '&text=' || urlencode(message->>'text_body') || 
      '&html=' || urlencode(message->>'html_body')));
      -- if the message table exists, 
      -- and the response from the mail server contains an id
      -- and the message from the mail server starts wtih 'Queued'
      -- mark this message as 'queued' in our message table, otherwise leave it as 'ready'
      IF  (SELECT to_regclass('public.messages')) IS NOT NULL AND 
          retval->'id' IS NOT NULL 
          AND substring(retval->>'message',1,6) = 'Queued' THEN
        UPDATE public.messages SET status = 'queued' WHERE id = (message->>'messageid')::UUID;
      END IF;

  RETURN retval;
END;

_send_emailmessage:

DECLARE
  -- variable declaration
  email_provider text := 'mailgun'; -- 'mailgun', 'sendgrid', 'sendinblue', 'mailjet', 'mailersend'
  retval json;
  messageid text;
BEGIN

  IF message->'text_body' IS NULL AND message->'html_body' IS NULL THEN RAISE 'message.text_body or message.html_body is required'; END IF;

  IF message->'text_body' IS NULL THEN     
     select message || jsonb_build_object('text_body',message->>'html_body') into message;
  END IF;

  IF message->'html_body' IS NULL THEN 
     select message || jsonb_build_object('html_body',message->>'text_body') into message;
  END IF;  

  IF message->'recipient' IS NULL THEN RAISE 'message.recipient is required'; END IF;
  IF message->'sender' IS NULL THEN RAISE 'message.sender is required'; END IF;
  IF message->'subject' IS NULL THEN RAISE 'message.subject is required'; END IF;

  IF message->'messageid' IS NULL AND (SELECT to_regclass('public.messages')) IS NOT NULL THEN
    -- messages table exists, so save this message in the messages table
    INSERT INTO public.messages(recipient, sender, cc, bcc, subject, text_body, html_body, status, log)
    VALUES (message->'recipient', message->'sender', message->'cc', message->'bcc', message->'subject', message->'text_body', message->'html_body', 'ready', '[]'::jsonb) RETURNING id INTO messageid;
    select message || jsonb_build_object('messageid',messageid) into message;
  END IF;

  EXECUTE 'SELECT send_email_' || email_provider || '($1)' INTO retval USING message;
  -- SELECT send_email_mailgun(message) INTO retval;
  -- SELECT send_email_sendgrid(message) INTO retval;

  RETURN retval;
END;

[mir0ir]

This happened to me too. Since you are using a mailgun sandbox, you have to insert the api key you have received for the specific sandbox, and you have to prepend api:. something like this: INSERT INTO private.keys (key, value) values ('MAILGUN_DOMAIN', 'sandbox07beb0c907ba54.mailgun.org'); INSERT INTO private.keys (key, value) values ('MAILGUN_API_KEY', 'api:xxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxxxx');