Query verifymail.io using SQL statements

This topic explains how to use Invantive SQL (Structured Query Language) to interact with the API of the verifymail.io service to verify email addresses and domains to exclude temporary/one-time email addresses, domains without MX record and proxy email domains. A check helps ensure that an email address can be traced to a physical person in environments with high integrity requirements.

Verifymail.io does not assess whether an email domain is for business or consumer use. For this purpose, https://www.ipqualityscore.com might be more suitable (5000 credits monthly in Free Plan, paid plans start at USD 499 monthly).

Verifymail.io API provides a simple API using key-based authentication. Documentation on the API can be found on https://verifymail.io/api-documentation. A free plan is available for at most 3 calls per day (paid plans start at USD 25 monthly).

Basis for the explanation is an Excel spreadsheet ‘domains.xlsx’ with a sheet ‘Sheet1’ with the following contents:

1 Domain
2 nu.nl
3 nos.nl
4 facebook.com
5 … etc …

The following query first retrieves the contents of the Excel-sheet starting at row 2, and the queries the verifymail.io API service for each domain, parses the JSON and stores the results as table DomainScan@InMemoryStorage:

create or replace table DomainScan@InMemoryStorage
select jte.*
from   exceltable
       ( worksheet 'Sheet1'
         passing file 'c:\PATH\domains.xlsx'     
         skip first 1 rows    
         columns domein varchar2 position next
       ) ete
join   httpdownload@DataDictionary
       ( url => 'https://verifymail.io/api/' || ete.domein || '?key=SECRETAPIKEY'
       , diskCache => true
       , diskCacheMaxAgeSec => 86400 * 7
       , ignoreWebError => true
       ) htp
on     htp.http_status_code = 200
join   jsontable
       ( ''
         passing htp.contents_char
         columns block             boolean  path 'block'
         ,       disposable        boolean  path 'disposable'
         ,       deliverable_email boolean  path 'deliverable_email'
         ,       catch_all         boolean  path 'catch_all'
         ,       privacy           boolean  path 'privacy'
         ,       domain            varchar2 path 'domain'
         ,       email_address     varchar2 path 'email_address'
         ,       related_domains   varchar2 path 'related_domains'
         ,       mx_priority       varchar2 path 'mx_priority'
         ,       mx_ip             varchar2 path 'mx_ip'
         ,       mx_host           varchar2 path 'mx_host'
         ,       mx_fallback       boolean  path 'mx_fallback'
         ,       mx                boolean  path 'mx'
         ,       email_provider    varchar2 path 'email_provider'
       ) jte

The results can be queried using:

select *
from   DomainScan@InMemoryStorage

with results:


Please note that verifymail.io allows a limited number of API calls per month, depending on the plan. For performance and cost reasons, it is wise to cache the outcome of the verifymail.io API-calls depending on your use case.