Programmatically sending email from SQL using AWS SES (Simple Email Service)

AWS Simple Email Service for Templated Transactional Emails

Invantive SQL has enabled sending email through SMTP since years as described on Send email from Invantive SQL using SMTP.

However, to monthly handle millions of templated transactional emails SMTP careful design and setting up your own template engine is required with the SMTP-connector.

As an alternative, AWS provides the Simple Email Service (“SES”). AWS SES combines low cost, high performance and templating while considered a non-spammy mail transfer agent by recipients. AWS SES is accessible through an API and additionally supports the SMTP-protocol (with limitations in functionality) through the Amazon SES SMTP interface.

This topic explains how to send email directly through the AWS SES APIs instead of using the SMTP-protocol to connect to AWS SES using familiar Invantive SQL and PSQL-statements.

The associated package aws_ses is available for use in all free and premium subscriptions starting release 22.0.339.

Configure AWS SES

The first step is to configure the AWS SES credentials using aws_ses.configure:

declare
  l_client aws_ses_client;
begin
  l_client := aws_ses.configure
  ( 'eu-west-1' /* Or another region. */
  , 'AK...token to identify...NS'
  , 'wj9...some secret token...UW'
  ); 
...

The AWS SES configuration can also be taken from system-wide settings unless Invantive SQL runs on a infrastructure shared with other companies. In that case, the configuration is enforced to have be done using aws_ses.configure.

Sending an Email from SQL through AWS SES

To send an email through AWS SES with SQL-statements the following code can be executed:

... preceding configuration code
  aws_ses.send_email
  ( l_client
  , 'sender@acme.com'
  , 'recipient@acme.com'
  , 'My First Subject'
  , 'The body of the email in <b>HTML</b> format.'
  , 'The body of the email in text format.'
  );
end;

Sending an Templated Email from SQL through AWS SES

An email can also be sent using an email template. Use aws_ses.create_template to define an email template in AWS SES, while it can be maintained using aws_ses.delete_template and aws_ses.update_template.

A template can contain place holders and iterators to print all rows of the template data. In this sample, a simple template is used (see Using templates to send personalized email with the Amazon SES API - Amazon Simple Email Service for more samples):

... preceding configuration code
  aws_ses.create_template
  ( l_client
  , 'templatesample'
  , 'subject 2 of templated mail for {{text.name}}'
  , 'HTML: <b>bold 2</b> created ' || to_char(sysdateutc) || ' for {{text.name}}'
  , '**bold 2** created ' || to_char(sysdateutc) || ' for {{text.name}}'
  );
end;

The template can be filled with template data and sent as an email. The template data must be provided in JSON-format using code such as:

... preceding configuration code
  aws_ses.send_templated_email
  ( l_client
  , 'sender@acme.com'
  , 'recipient@acme.com'
  , 'templatesample'
  , '{"text": {"name": "Some name"}}'
  );
end;

The email received by the recipient with template data inserted by AWS SES is:

image