Automate SQL Workflows with n8n: Scheduled Database Reviews through E mail

Picture by Creator | ChatGPT

The Hidden Price of Routine SQL Reporting

 
Knowledge groups throughout organizations face the identical recurring problem: stakeholders require common stories, however handbook SQL reporting consumes precious time that might be spent on evaluation. The method stays constant no matter firm dimension — connect with the database, execute queries, format outcomes, and distribute findings to decision-makers.

Knowledge professionals routinely deal with reporting duties that do not require superior statistical data or area experience, but they eat vital time via repetitive execution of the identical queries and formatting procedures.

This workflow addresses a elementary effectivity drawback: remodeling one-time setup into ongoing automated supply {of professional} stories on to stakeholder inboxes.

The Resolution: A 4-Node Automated Reporting Pipeline

 
Constructing on our earlier n8n exploration, this workflow tackles a distinct automation problem: scheduled SQL reporting. Whereas our first tutorial centered on information high quality evaluation, this one demonstrates how n8n handles database integration, recurring schedules, and electronic mail distribution.

In contrast to writing standalone Python scripts for reporting, n8n workflows are visible, reusable, and straightforward to change. You possibly can join databases, carry out transformations, run analyses, and ship outcomes — all with out switching between totally different instruments or environments. Every workflow consists of “nodes” that signify totally different actions, linked collectively to create an automatic pipeline.

Our automated SQL reporter consists of 4 linked nodes that rework handbook reporting right into a hands-off course of:

1. Schedule Set off – Runs each Monday at 9 AM
2. PostgreSQL Node – Executes gross sales question towards database
3. Code Node – Transforms uncooked information into formatted HTML report
4. Ship E mail Node – Delivers skilled report back to stakeholders

Constructing the Workflow: Step-by-Step Implementation

Stipulations

Step 1: Set Up Your PostgreSQL Database

We’ll create a sensible gross sales database utilizing Supabase for this tutorial. Supabase is a cloud-based PostgreSQL platform that gives managed databases with built-in APIs and authentication—making it very best for speedy prototyping and manufacturing purposes. Whereas this tutorial makes use of Supabase for comfort, the n8n workflow connects to any PostgreSQL database, together with AWS RDS, Google Cloud SQL, or your group’s current database infrastructure.

Create Supabase Account:

1. Go to supabase.com and join free
2. Create new mission – select any identify and area
3. Anticipate setup – takes about 2 minutes for database provisioning
4. View your connection particulars from the Settings > Database web page (or the “join” button on the principle web page)

Load Pattern Knowledge:

Navigate to the SQL Editor in Supabase and run this setup script to create our gross sales database tables and populate them with pattern information:

-- Create staff desk
CREATE TABLE staff (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    division VARCHAR(50)
);

-- Create gross sales desk
CREATE TABLE gross sales (
    sale_id SERIAL PRIMARY KEY,
    emp_id INTEGER REFERENCES staff(emp_id),
    sale_amount DECIMAL(10,2),
    sale_date DATE
);

-- Insert pattern staff
INSERT INTO staff (first_name, last_name, division) VALUES
('Mike', 'Johnson', 'Gross sales'),
('John', 'Doe', 'Gross sales'),
('Tom', 'Wilson', 'Gross sales'),
('Sarah', 'Chen', 'Advertising');

-- Insert current gross sales information
INSERT INTO gross sales (emp_id, sale_amount, sale_date) VALUES
(1, 2500.00, CURRENT_DATE - 2),
(1, 1550.00, CURRENT_DATE - 5),
(2, 890.00, CURRENT_DATE - 1),
(2, 1500.00, CURRENT_DATE - 4),
(3, 3200.00, CURRENT_DATE - 3),
(4, 1200.00, CURRENT_DATE - 6);

Paste this complete script into the SQL Editor and click on the “Run” button within the bottom-right nook. You must see “Success. No rows returned” confirming that your tables and pattern information have been created efficiently.

Check Your Connection:

Throughout the identical SQL Editor, run a recent question to confirm the whole lot works: SELECT COUNT(*) FROM staff;

You must see 4 staff within the outcomes.

Step 2: Configure Gmail for Automated Sending

Allow App Password:

1. Activate 2-step verification in your Google Account settings
2. Generate app password – go to Safety > App passwords
3. Choose “Mail” and “Different” – identify it “n8n reporting”
4. Copy the 16-character password – you will want this for n8n

Step 3: Import and Configure the Workflow

Import the Template:

1. Obtain the workflow file
2. Open n8n and click on “Import from File”
3. Choose the downloaded file – all 4 nodes seem robotically
4. Save the workflow as “Automated SQL Reporting”

The imported workflow accommodates 4 linked nodes with all of the complicated SQL and formatting code already configured.

Configure Database Connection:

1. Click on the PostgreSQL node
2. Get your connection particulars from Supabase by clicking the “Join” button in your principal web page. For n8n integration, use the “Transaction pooler” connection string because it’s optimized for automated workflows:

3. Create new credential along with your Supabase particulars:
   • Host: [your-project].supabase.com
   • Database: postgres
   • Person: postgres…..
   • Password: [from Supabase settings]
   • Port: 6543
   • SSL: Allow
4. Check connection – you need to see a inexperienced success message

Configure E mail Settings:

1. Click on the Ship E mail node
2. Create SMTP credential:
   • Host: smtp.gmail.com
   • Port: 587
   • Person: [email protected]
   • Password: [your app password]
   • Safe: Allow STARTTLS
3. Replace recipient within the “To E mail” subject

That is it! The evaluation logic robotically adapts to totally different database schemas, desk names, and information sorts.

Step 4: Check and Deploy

1. Click on “Execute Workflow” within the toolbar
2. Watch every node flip inexperienced because it processes
3. Verify your electronic mail – you need to obtain the formatted report
4. Toggle to “Energetic” to allow Monday morning automation

As soon as the setup is full, you will obtain automated weekly stories with none handbook intervention.

Understanding Your Automated Report

This is what your stakeholders will obtain each Monday:

E mail Topic: 📊 Weekly Gross sales Report – June 27, 2025

Report Content material:

• Clear HTML desk with correct styling and borders
• Abstract statistics calculated robotically from SQL outcomes
• Skilled formatting appropriate for government stakeholders
• Timestamp and metadata for audit trails

This is what the ultimate report appears like:

The workflow robotically handles all of the complicated formatting and calculations behind this skilled output. Discover how the report consists of correct forex formatting, calculated averages, and clear desk styling—all generated immediately from uncooked SQL outcomes with none handbook intervention. The e-mail arrives with a timestamp, making it straightforward for stakeholders to trace reporting durations and keep audit trails for decision-making processes.

Technical Deep Dive: Understanding the Implementation

 
Schedule Set off Configuration:

The workflow runs each Monday at 9:00 AM utilizing n8n’s interval scheduling. This timing ensures stories arrive earlier than weekly crew conferences.

SQL Question Logic:

The PostgreSQL node executes a classy question with JOINs, date filtering, aggregations, and correct numeric formatting. It robotically:

• Joins worker and gross sales tables for full data
• Filters information to final 7 days utilizing CURRENT_DATE - INTERVAL '7 days'
• Calculates complete gross sales, income, and averages per particular person
• Orders outcomes by income for enterprise prioritization

HTML Era Logic:

The Code node transforms SQL outcomes into skilled HTML utilizing JavaScript. It iterates via question outcomes, builds styled HTML tables with constant formatting, calculates abstract statistics, and provides skilled touches like emojis and timestamps.

E mail Supply:

The Ship E mail node makes use of Gmail’s SMTP service with correct authentication and HTML rendering help.

Testing with Totally different Situations

 
To see how the workflow handles various information patterns, attempt these modifications:

1. Totally different Time Intervals: Change INTERVAL '7 days' to INTERVAL '30 days' for month-to-month stories
2. Division Filtering: Add WHERE e.division="Gross sales" for team-specific stories
3. Totally different Metrics: Modify SELECT clause to incorporate product classes or buyer segments

Primarily based on your small business wants, you’ll be able to decide subsequent steps: weekly stories work properly for operational groups, month-to-month stories swimsuit strategic planning, quarterly stories serve government dashboards, and each day stories assist with real-time monitoring. The workflow adapts robotically to any SQL construction, permitting you to shortly create a number of reporting pipelines for various stakeholders.

Subsequent Steps

1. Multi-Database Help

Substitute the PostgreSQL node with MySQL, SQL Server, or any supported database. The workflow logic stays an identical whereas connecting to totally different information sources. This flexibility makes the answer precious throughout various expertise stacks.

2. Superior Scheduling

Modify the Schedule Set off for various frequencies. Arrange each day stories for operational metrics, month-to-month stories for strategic planning, or quarterly stories for board conferences. Every schedule can goal totally different recipient teams with tailor-made content material.

3. Enhanced Formatting

Prolong the Code node to incorporate charts and visualizations utilizing Chart.js, conditional formatting based mostly on efficiency thresholds, or government summaries with key insights. The HTML output helps wealthy formatting and embedded graphics.

4. Multi-Recipient Distribution

Add logic to ship totally different stories to totally different stakeholders. Gross sales managers obtain particular person crew stories, executives obtain high-level summaries, and finance groups obtain revenue-focused metrics. This focused strategy ensures every viewers will get related data.

Conclusion

 
This automated SQL reporting workflow demonstrates how n8n bridges the hole between information science experience and operational effectivity. By combining database integration, scheduling, and electronic mail automation, you’ll be able to get rid of routine reporting duties whereas delivering skilled outcomes to stakeholders.

The workflow’s modular design makes it significantly precious for information groups managing a number of reporting necessities. You possibly can duplicate the workflow for various databases, modify the SQL queries for varied metrics, and modify the formatting for various audiences—all with out writing customized scripts or managing server infrastructure.

In contrast to conventional ETL instruments that require in depth configuration, n8n’s visible interface makes complicated information workflows accessible to each technical and non-technical crew members. Your SQL experience stays the core worth, whereas n8n handles the automation infrastructure, scheduling reliability, and supply mechanisms.

Most significantly, this strategy scales along with your group’s wants. Begin with easy weekly stories, then develop to incorporate information visualizations, multi-database queries, or integration with enterprise intelligence platforms. The muse you construct at this time turns into the automated reporting infrastructure that helps your crew’s progress tomorrow.
 
 

Born in India and raised in Japan, Vinod brings a world perspective to information science and machine studying schooling. He bridges the hole between rising AI applied sciences and sensible implementation for working professionals. Vinod focuses on creating accessible studying pathways for complicated matters like agentic AI, efficiency optimization, and AI engineering. He focuses on sensible machine studying implementations and mentoring the following era of knowledge professionals via reside periods and personalised steerage.