IOLEBA

IOLEBA N8N Textbook – Table of Contents

Chapter 7: Database Integrations

Introduction

Most business applications rely on databases to store and retrieve data. In this chapter, you'll learn to connect n8n to various database systems, perform CRUD operations (Create, Read, Update, Delete), and build data-driven workflows that interact seamlessly with your data stores.

Understanding Database Types

Databases fall into two main categories, each with different strengths:

SQL (Relational) Databases

Characteristics:

Structured data in tables with rows and columns
Relationships between tables
ACID compliance (reliable transactions)
Uses SQL query language

Popular SQL Databases:

MySQL: Most common, open-source
PostgreSQL: Advanced features, highly reliable
Microsoft SQL Server: Enterprise Windows environments
SQLite: Lightweight, file-based

NoSQL (Non-Relational) Databases

Characteristics:

Flexible schemas (no fixed structure)
Horizontal scalability
Fast read/write operations
Various data models (document, key-value, graph)

Popular NoSQL Databases:

MongoDB: Document-based (JSON-like)
Redis: In-memory key-value store
Firebase: Real-time cloud database
DynamoDB: AWS managed NoSQL

Connecting to MySQL/PostgreSQL

Let's start with SQL databases, the most common type for business applications.

Setting Up Database Credentials

Go to Credentials in n8n sidebar
Click "Add Credential"
Select database type (MySQL or PostgreSQL)
Configure connection details

Required Connection Information:

Host: Server address (e.g., localhost, db.example.com)
Database: Database name
User: Username with access permissions
Password: User password
Port: 3306 (MySQL) or 5432 (PostgreSQL)

⚠️ Security Best Practices:

Never use root database account
Create dedicated user with minimal required permissions
Use SSL/TLS connections when available
Restrict database access by IP address
Store credentials securely in n8n credential manager

CRUD Operations

CRUD represents the four basic database operations you'll use most often.

Create (INSERT)

Add new records to database tables.

MySQL Node Configuration:

Operation: Insert
Table: customers
Columns: name, email, created_at

Example SQL Generated:

INSERT INTO customers (name, email, created_at) 
VALUES ('John Smith', '[email protected]', NOW())

Read (SELECT)

Retrieve data from database tables.

Common SELECT Patterns:

Get all records:

SELECT * FROM customers

Get specific columns:

SELECT name, email FROM customers

Filter with WHERE:

SELECT * FROM customers 
WHERE created_at > '2025-01-01'

Order results:

SELECT * FROM customers 
ORDER BY created_at DESC 
LIMIT 10

Update (UPDATE)

Modify existing records.

Update Example:

UPDATE customers 
SET status = 'active', updated_at = NOW() 
WHERE email = '[email protected]'

⚠️ Critical Warning: Always use WHERE clause in UPDATE statements! Without it, you'll update ALL records in the table.

Delete (DELETE)

Remove records from tables.

⚠️ Deletion Warning:

DELETE FROM customers 
WHERE id = 123

NEVER run DELETE without WHERE clause! This would delete ALL records.

Best Practice: Consider "soft deletes" - mark records as deleted rather than actually removing them.

Building Data-Driven Workflows

Pattern 1: Database-Triggered Actions

Scenario: Daily Report Generation

Schedule Trigger: Every day at 8 AM
MySQL Node: Query yesterday's sales
Aggregate Node: Calculate totals
Set Node: Format report data
Email Node: Send report to management

Pattern 2: Two-Way Data Sync

Scenario: CRM to Database Sync

Webhook Trigger: CRM sends new contact
MySQL Node: Check if contact exists
IF Node: Exists or new?
True: Update existing record
False: Insert new record

Working with MongoDB

MongoDB is the most popular NoSQL database, storing data as flexible JSON-like documents.

MongoDB Operations

Operation	SQL Equivalent	Purpose
insertOne/insertMany	INSERT	Add documents
find	SELECT	Query documents
updateOne/updateMany	UPDATE	Modify documents
deleteOne/deleteMany	DELETE	Remove documents

MongoDB Query Examples

Find all active users:

{ "status": "active" }

Find users created this year:

{ "createdAt": { "$gte": "2025-01-01" } }

Find by multiple conditions:

{ 
  "status": "active",
  "subscription": "premium",
  "credits": { "$gt": 0 }
}

Database Performance Optimization

Query Optimization

Best Practices:

Use indexes: Speed up frequently queried fields
Limit results: Use LIMIT/TOP to reduce data transfer
Select specific columns: Don't use SELECT * in production
Filter at database: Use WHERE instead of filtering in workflow
Batch operations: Insert/update multiple records at once

Connection Management

Connection Best Practices:

Reuse connections across nodes
Set appropriate timeout values
Handle connection errors gracefully
Close connections when workflow ends
Monitor connection pool usage

Common Database Workflow Patterns

Pattern 1: Upsert (Insert or Update)

Problem: Don't know if record exists

Solution:

Try to find record by unique identifier
IF found: Update existing record
IF not found: Insert new record

Pattern 2: Bulk Data Import

Importing CSV to Database:

Read Binary File: Load CSV
Split In Batches: Process 100 rows at a time
MySQL Node: Insert batch
Wait: Brief pause between batches
Loop: Continue until all data imported

Pattern 3: Scheduled Database Cleanup

Maintain Database Health:

Schedule Trigger: Weekly
MySQL Node: Delete old records (e.g., logs older than 90 days)
MySQL Node: Optimize tables
Slack Node: Report cleanup results

Error Handling for Database Operations

Common Database Errors:

Connection timeout: Database unreachable
Duplicate key: Trying to insert existing unique value
Constraint violation: Data doesn't meet requirements
Permission denied: User lacks necessary privileges
Table doesn't exist: Wrong database or table name

Error Handling Strategy:

Enable "Continue On Fail" for database nodes
Use IF node to check for errors
Log errors to external system
Send notifications for critical failures
Implement retry logic for temporary issues

Practice Exercises

Exercise 1: Customer Database Manager

Build a workflow that:

Receives customer data via webhook
Validates email format
Checks if customer exists in database
Updates if exists, inserts if new
Logs all operations to audit table

Exercise 2: Daily Sales Report

Create automated reporting:

Schedule trigger (daily at 8 AM)
Query database for yesterday's sales
Calculate: total revenue, number of orders, average order value
Identify top 5 selling products
Format as HTML email
Send to management team

Exercise 3: Data Sync Between Systems

Synchronize data across platforms:

Schedule trigger (every hour)
Get new records from Source Database A
Transform data to match Target Database B schema
Insert into Target Database B
Update sync timestamp in Source Database A

Advanced Database Techniques

Transactions

Ensure multiple operations complete successfully together or none at all.

Use Case: Transferring money between accounts

Must deduct from Account A AND add to Account B
If either fails, both should be rolled back
Prevents data inconsistency

Stored Procedures

Call pre-written database procedures for complex operations.

Benefits:

Faster execution (pre-compiled)
Reduced network traffic
Centralized business logic
Enhanced security

Key Takeaways

Databases store structured (SQL) or flexible (NoSQL) data
CRUD operations are the foundation: Create, Read, Update, Delete
Always use WHERE clauses in UPDATE and DELETE statements
Optimize queries with indexes, limits, and specific column selection
Handle database errors gracefully with retry logic
Batch operations improve performance for large datasets
Security best practices protect your data

Looking Forward

In Chapter 8, we'll explore API authentication methods. You'll learn OAuth, API keys, JWT tokens, and how to securely connect n8n to protected APIs, enabling integration with virtually any modern web service.