Sending Email From Postgres

This is the sixth article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover how to send email from a PostgreSQL function. The examples are tailored for Sendgrid, but the concepts will work with any API-based email provider.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

The other articles in this series have so far covered writing and testing Row-Level Security (RLS) policies for these tables and how to write functions, but they aren't required reading for this article.

Why send email from a database?

For my current project I need to notify users when certain situations occur. In particular, when a user is signed up for an in-person event but there isn't enough room, they are put on a wait list. When another attendee indicates they can't go, the first person on the wait list will now be able to attend and needs to be notified.

This is a purely backend action. User A triggers the action, but I want to send the email to User B. I can't send the email from the frontend.

How to send an email

Step 1. Choose a provider

There are several (maybe dozens?) companies out there that will send email for you, AWS SES, Mailgun, Sendgrid, Sendinblue, Mailjet, Mailersend. I've used Sendgrid in the past and like their free trial of 100 emails/month forever, among other features they provide.

Step 2. Set up the provider

I haven't used all of the services, but they usually follow a similar approach. You need to create an account, verify a "from" email address, and probably provide some more info to verify you aren't trying to spam the world about your get-rich-quick Bitcoin scheme.

Note that if you're sending test messages to a Gmail account, that platform is particularly picky about spam. You may need to set up extra email authentication before you receive messages there.

Step 3. Create a Postgres trigger function

The mechanism for sending an email based on a database event is to write a "trigger function." For example in my project, whenever a user changes their "attendee" record it may trigger sending an email to another user. Here's the SQL that creates the function and the trigger:

create function attendees_promote_trigger()
  returns trigger as $$
  begin
    perform private.promote_attendees(NEW.event_id);
    return NULL;
  end;
  $$ language plpgsql;

CREATE TRIGGER attendees_promote_trigger
  AFTER INSERT OR UPDATE OR DELETE ON attendees
  -- To make recovery easier, skip trigger when using service role
  FOR EACH ROW WHEN (auth.uid() IS NOT NULL)
  EXECUTE FUNCTION attendees_promote_trigger();

Note that it skips the trigger when there is no user ID associated with the request. That will help when inserting records as part of a database recovery, or setting up dummy data for testing.

The trigger function is simply calling another function. I did this primarily because for my project there are two separate triggers for sending this email, insert/update attendee row AND update group row. To avoid repeating the logic, I created a separate function.

Step 4. Call the API

Next step is to use the provider's API to send an email. You send an HTTP request to the API with a secret key, the recipient(s) info, and the message. You might send the entire message (text and HTML) in the API call, or you might be able to pre-define a template email and just send values that will be filled in. Sort of like a mail merge.

I was excited to find the supabase-mailer project, but unfortunately it seems that only the Mailgun provider is fully supported and working. Mailgun only has a 30 day trial, which didn't work for my needs.

Calling the API directly is not difficult. Look at the API docs for your email service. Sendgrid's docs recommend using a client library, but scroll past that to the details of the API request including the exact JSON format to send.

From here you'll write a send_email function in Postgres that calls the API. I initially wrote the entire function in PLpgSQL and spent an entire day to get it debugged and working. And even then I wasn't happy with it. In my opinion that language is unintuitive and difficult to learn and debug. I switched over to PLV8, an extension for Postgres that supports writing functions in Javascript. It takes one click in the Supabase UI to enable this extension, and it will save hours and hours of time.

Borrowing some ideas from supabase-mailer, I decided to store my Sendgrid secret API key in a private schema (which is not accessible from the API) of the database. Create the schema and table with:

CREATE SCHEMA IF NOT EXISTS private;
CREATE TABLE IF NOT EXISTS private.keys (
    key text primary key not null,
    value text
);

And then add the key SENDGRID_API_KEY and value via the Supabase UI console.

Here's the send_grid function I ended up with:

create or replace function private.send_email(payload jsonb)
returns void AS $$
    let apiKey = '';
    let debugMail = false;
    try {
        apiKey = plv8.execute(
            `select value from private.keys
                where key = 'SENDGRID_API_KEY'`)[0].value;
        const debugValue = plv8.execute(
            `select value from private.keys
                where key = 'SENDGRID_DEBUG'`)[0].value;
        debugMail = debugValue === 'true';
    }
    catch {
        plv8.elog(ERROR, 'Missing entry in private.keys');
    }
    if (debugMail) {
        plv8.elog(LOG, `Debug email: ${JSON.stringify(payload)}`);
    }
    else {
        const result = plv8.execute(`select * from http((
        'POST',
        'https://api.sendgrid.com/v3/mail/send',
        ARRAY[http_header('Authorization', $1)],
        'application/json',
        $2
      )::http_request);`, ['Bearer ' + apiKey, JSON.stringify(payload)]);
        const { status, content } = result[0];
        if (status !== 202) {
            plv8.elog(ERROR, `Sending email failed, status: ${status}, content: ${content}`);
        }
    }
$$ language plv8;

The function first retrieves my secret Sendgrid API key from the private.keys table. It then retrieves another value from the table that indicates whether to write messages to the database log or actually send them to Sendgrid. The former is handy for debugging since it takes a while for messages to flow through an email provider.

The next step is to either write the info to the log, or use the HTTP extension (which is also enabled via the Supabase UI) to send an API request. If the status code from that request is something other than 202, something went wrong and an error message is logged.

Step 5. The update function

The only thing missing at this point is the promote_attendee function that the trigger calls. Most of the logic isn't relevant for this article, so I'll include just the part that calls send_email. Again this function is written in PLV8:

const user_data_rows = plv8.execute(
    `select au.email, p.full_name
        from auth.users as au
        join profiles as p on au.id = p.id
        where au.id = any($1)`,
        [user_ids]);
// Get template ID from the private.keys table.
const template_id = plv8.execute(
    `select value from private.keys
        where key = 'EMAIL_TEMPLATE_PROMOTE_ATTENDEE'`)[0].value;
// Create the data structure that SendGrid expects.
const personalizations = user_data_rows.map(({ email, full_name }) => ({
    to: [
        {
            email,
            name: full_name,
        },
    ],
    dynamic_template_data: {
        event_name,
        event_location,
        event_date_time: localizedDateTime,
    },
}));
const payload = {
    from: {
        email: 'admin@example.com',
        name: 'Admin',
    },
    template_id,
    personalizations,
};
plv8.execute('select private.send_email($1)', [payload]);

This code first retrieves the email address and name for each user that is listed in the user_ids variable. It next retrieves the template ID from the database. This is a value I also stored in the private.keys table. The value was provided by Sendgrid when I created an email template in their UI.

The next step is to generate the JSON blob that Sendgrid expects. This follows their documentation for generating an array of email addresses and names, along with "from" data and the template ID.

Last step is to finally send the JSON blob to the send_email function from above.

Future work

Sendgrid (and probably the other providers as well) have a feature where an HTTP endpoint can be called whenever the status of an email changes. I'll cover setting this up in a future article.

I also set up an environment for writing PLV8 functions in Typescript which simplifies the process of writing the wrapper create function SQL and provides type checking, linting, prettier(ing), and testing. That will be a future article as well.