Tech vortex

How to Configure the n8n Database

Introduction

n8n is an open-source workflow automation tool designed to help developers, businesses, and hobbyists automate repetitive tasks by connecting different apps and services with ease. Unlike other automation tools, n8n is highly flexible, extensible, and free to use. It empowers users to create complex workflows with custom logic, making it a powerful addition to any automation stack.

At the heart of n8n is its database — where all workflows, credentials, executions, and settings are stored. By default, n8n uses SQLite, a simple file-based database system. This default works well for experimentation, small projects, or development environments. However, when you move to production or want to scale, this default setup has several limitations.

In production, your workflows need a more reliable, scalable, and performant database. Using a persistent database like PostgreSQL or MySQL ensures your automation system can handle growth, multiple users, and complex workflows without slowing down or risking data loss.

This comprehensive guide will walk you through why and how to configure n8n’s database for production use. We’ll cover:

  • Why to move away from SQLite in production
  • Supported database options
  • Step-by-step setup of PostgreSQL and MySQL
  • Configuring environment variables and Docker integration
  • Starting n8n with the new database
  • Migrating existing workflows
  • Troubleshooting common issues
  • Best practices for running n8n in production

By the end, you will have a clear understanding of how to configure n8n with a robust database that suits your production needs.

Why Use a Custom Database with n8n?

The Default SQLite Setup: What’s Good and What’s Not?

When you first install n8n, it uses SQLite as its default database. SQLite stores all data in a single file on your disk, which makes it incredibly easy to get started—no database server to install or configure.

Advantages of SQLite for n8n:

  • Zero setup: The database file is created automatically without user intervention.
  • File-based: No need for a separate database service.
  • Great for testing and development: Lightweight and fast for small projects.
  • Portable: You can move the database file between machines easily.

However, SQLite has important limitations that become critical in production:

  • Concurrency limitations: SQLite allows only one write operation at a time. If multiple workflows or users try to write simultaneously, operations queue up, causing delays.
  • Scaling constraints: Because it stores everything in a single file, large volumes of data or many concurrent users can slow down the system.
  • Lack of high availability features: SQLite does not support replication, clustering, or failover.
  • Difficult to backup and migrate: Managing backups can be error-prone since it relies on file copies.
  • Not designed for multi-user systems: If your n8n instance has multiple users, SQLite’s locking mechanism can cause problems.

Why Use PostgreSQL or MySQL in Production?

In contrast, PostgreSQL and MySQL are full-fledged relational database management systems (RDBMS) designed for scalability, concurrency, and reliability.

Benefits of using PostgreSQL or MySQL with n8n:

  • Better concurrency: These systems support multiple simultaneous reads and writes without blocking each other.
  • Performance: Optimized for complex queries and large datasets, which improves n8n’s responsiveness as workflows grow.
  • Scalability: Handle high volumes of workflows, executions, and users without degradation.
  • Advanced features: Support for transactions, indexing, data integrity, and stored procedures.
  • Robust backup and recovery tools: Enable scheduled backups, point-in-time recovery, and easy migrations.
  • Security: Offer advanced authentication, SSL/TLS encryption, and role-based access control.
  • Cloud-managed options: Easily deploy and maintain databases on managed cloud services, reducing operational overhead.

In short, moving to PostgreSQL or MySQL prepares your n8n instance to grow and meet production-grade requirements.

Supported Databases

n8n officially supports the following databases:

PostgreSQL

PostgreSQL is an open-source, object-relational database system known for its reliability, rich features, and standards compliance. It is highly extensible and widely used in enterprise environments.

Why PostgreSQL?

  • ACID-compliant transactions ensure data integrity.
  • Advanced indexing and query optimization.
  • Extensive support for JSON and other data types.
  • Mature community and strong ecosystem.

MySQL / MariaDB

MySQL is a popular open-source relational database, widely used in web applications. MariaDB is a community-driven fork of MySQL, fully compatible and often preferred for its additional features.

Why MySQL/MariaDB?

  • Broad adoption and tooling support.
  • Easy to manage and well-documented.
  • Suitable for many production workloads.
  • MariaDB offers extra storage engines and features.

SQLite (default)

SQLite is great for local development or simple use cases but not recommended beyond that.

Prerequisites

Before configuring your database with n8n, ensure the following:

  • n8n Installed: You should have n8n installed on your machine or server. Installation options include Docker, npm, or a standalone binary.
  • Database Instance Running: Your PostgreSQL or MySQL database should be installed and running. This can be on your local machine, a dedicated server, or a managed cloud service.
  • Basic Terminal Skills: You’ll need access to the terminal or command line interface to run commands.
  • Familiarity with Environment Variables: Configuration involves setting environment variables or editing .env files.

Database Setup

Here’s how to prepare your PostgreSQL or MySQL database for n8n.

a. PostgreSQL Setup

First, log in to your PostgreSQL instance. If you are on a Linux server, you might use:

Bash
sudo -u postgres psql

Then, create a dedicated database and user:

SQL
CREATE DATABASE n8n;
CREATE USER n8n_user WITH PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON DATABASE n8n TO n8n_user;

Explanation:

  • CREATE DATABASE n8n; creates the database named n8n.
  • CREATE USER n8n_user WITH PASSWORD 'securepassword'; creates a user with a password.
  • GRANT ALL PRIVILEGES ON DATABASE n8n TO n8n_user; gives full access to the n8n database for that user.

Exit the psql shell by typing \q.

You can adjust the username, password, and database name to fit your security policies.

b. MySQL / MariaDB Setup

Log in to your MySQL or MariaDB instance:

Bash
mysql -u root -p

Once logged in, create the database and user:

SQL
CREATE DATABASE n8n;
CREATE USER 'n8n_user'@'%' IDENTIFIED BY 'securepassword';
GRANT ALL PRIVILEGES ON n8n.* TO 'n8n_user'@'%';
FLUSH PRIVILEGES;

Notes:

  • The '%' wildcard allows connections from any host. For tighter security, replace it with a specific IP or hostname.
  • FLUSH PRIVILEGES; reloads the grant tables to apply the changes.

Exit the MySQL shell with exit.

Configure Environment Variables

n8n uses environment variables to configure its database connection. You can set these variables in a .env file in your n8n project directory or pass them directly to your environment.

For PostgreSQL

Create or edit .env:

Env
DB_TYPE=postgresdb
DB_POSTGRESDB_HOST=localhost
DB_POSTGRESDB_PORT=5432
DB_POSTGRESDB_DATABASE=n8n
DB_POSTGRESDB_USER=n8n_user
DB_POSTGRESDB_PASSWORD=securepassword

For MySQL

Env
DB_TYPE=mysqldb
DB_MYSQLDB_HOST=localhost
DB_MYSQLDB_PORT=3306
DB_MYSQLDB_DATABASE=n8n
DB_MYSQLDB_USER=n8n_user
DB_MYSQLDB_PASSWORD=securepassword

Key Points:

  • Adjust DB_POSTGRESDB_HOST or DB_MYSQLDB_HOST to point to your database host.
  • Set the correct port if you are using non-default ports.
  • Keep your credentials secure and avoid committing .env files with passwords to public repositories.

Docker Configuration (Optional)

If you run n8n with Docker, you can configure the database connection via your docker-compose.yml file.

Here’s an example snippet for PostgreSQL:

YAML
version: '3'

services:
  n8n:
    image: n8nio/n8n
    ports:
      - "5678:5678"
    environment:
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=db
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=n8n
      - DB_POSTGRESDB_USER=n8n_user
      - DB_POSTGRESDB_PASSWORD=securepassword
    depends_on:
      - db

  db:
    image: postgres:14
    environment:
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n_user
      POSTGRES_PASSWORD: securepassword
    volumes:
      - n8n-db-data:/var/lib/postgresql/data

volumes:
  n8n-db-data:

Notes:

  • The db service runs PostgreSQL inside Docker.
  • The n8n service uses environment variables to connect to the db container.
  • Use Docker volumes to persist database data.
  • Restart your Docker stack after making changes:
Bash
docker-compose down && docker-compose up -d

Start n8n

After configuring your database and environment variables, start or restart n8n.

  • Docker:
Bash
docker-compose up -d
  • npm:
Bash
n8n start

Watch the logs to ensure n8n successfully connects to the database. You should see output similar to:

Plaintext
[n8n] Database is connected

If there are errors, review your database credentials, host, and port settings.

Migrating Existing Workflows

If you already have workflows saved in SQLite and want to move to PostgreSQL or MySQL:

Backup Your SQLite Data

You can export workflows using the n8n UI or CLI:

  • UI Export:

    Go to Settings > Workflows, select all, and export as JSON.

  • CLI Export:

    Use the n8n export:workflow command to export workflows.

Import into New Database

After switching to PostgreSQL or MySQL, import workflows through the UI or CLI.

Migrating workflows ensures you don’t lose any work during the database transition.

Troubleshooting Tips

Even with clear instructions, you may encounter issues. Here are some common problems and how to fix them:

Connection Refused

  • Confirm the database service is running.
  • Verify the host and port are correct.
  • Ensure firewalls or security groups allow connections.

Invalid Credentials

  • Double-check username and password.
  • Ensure the user has the necessary privileges on the database.

Missing Database Drivers

  • n8n requires database client libraries:
    • For PostgreSQL: pg package.
    • For MySQL: mysql2 package.

When running from npm, ensure these packages are installed:

Bash
npm install pg mysql2

If using Docker, the official n8n images include these drivers.

Logs Are Your Friend

Check n8n logs for detailed error messages:

  • Docker logs:
Bash
docker logs <container_id>
  • npm logs:
Bash
n8n start

Use these logs to pinpoint issues.

Best Practices for Production

To keep your n8n instance stable and secure in production, follow these best practices:

Use Managed Database Services

Managed services like AWS RDS, Google Cloud SQL, or Azure Database simplify administration, backups, scaling, and security.

Enable SSL/TLS Connections

Encrypt traffic between n8n and your database to protect data in transit.

Automate Backups

Set up automated backup schedules to avoid data loss. Use built-in tools or third-party services.

Monitor Performance

Regularly check database metrics such as query latency, connection count, CPU usage, and disk I/O. Use tools like:

  • PostgreSQL: pgAdmin, Datadog, or New Relic.
  • MySQL: MySQL Workbench, Percona Monitoring.

Secure Your Database

  • Use strong passwords.
  • Limit database user permissions.
  • Restrict network access with firewalls and VPCs.

Keep Software Updated

Apply security patches and version updates regularly for both n8n and your database.

Conclusion

Configuring a persistent database like PostgreSQL or MySQL for your n8n instance is a critical step for production readiness. It unlocks improved performance, reliability, and scalability, allowing you to automate complex workflows safely and efficiently.

This guide covered the full process—from understanding why to move away from SQLite, to setting up your database, configuring environment variables, integrating with Docker, migrating existing workflows, troubleshooting, and following production best practices.

With these steps, your n8n deployment will be well-positioned to handle the demands of real-world automation tasks.