Database Schema & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• packages/@n8n/db/src/entities/workflow-entity.ts
• packages/@n8n/db/src/entities/execution-entity.ts
• packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts
• packages/@n8n/api-types/src/schemas/tests/project.schema.test.ts
• packages/cli/src/services/tests/import.service.test.ts
• packages/cli/src/services/tests/export.service.test.ts
• CLAUDE.md
• docker/images/n8n/README.md

The n8n platform relies on a relational database to persist critical application data, including workflows, executions, credentials, and user information. The database layer is built using the TypeORM framework, which provides an object-relational mapping (ORM) for Node.js. This allows for a flexible architecture that supports SQLite, PostgreSQL, and MySQL databases.

The schema is defined through TypeORM entities, and its evolution is managed by a series of migration files. This system ensures data integrity and consistency across different n8n versions and database backends. Additionally, n8n provides CLI services for exporting and importing database entities, facilitating instance migration and backups.

Technology Stack

n8n's backend is built on a technology stack that supports multiple database systems.

• ORM: TypeORM is used for object-relational mapping, defining database entities as TypeScript classes.
• Supported Databases:
  • SQLite (default for local development)
  • PostgreSQL
  • MySQL

This flexibility is configured via environment variables, allowing users to choose a database that fits their infrastructure and scaling needs.

Sources: CLAUDE.md, docker/images/n8n/README.md

Core Entities & Relationships

The database schema is composed of several core entities that represent the main objects within n8n. Key entities include Project, WorkflowEntity, and ExecutionEntity.

Entity Relationship Diagram

The following diagram illustrates the relationships between the Project, WorkflowEntity, and ExecutionEntity tables.

This diagram shows that a Project can have multiple WorkflowEntity records, and each WorkflowEntity can have multiple ExecutionEntity records.

Sources: packages/@n8n/db/src/entities/workflow-entity.ts, packages/@n8n/db/src/entities/execution-entity.ts, packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts

WorkflowEntity

The WorkflowEntity represents a single n8n workflow. It stores the workflow's structure, settings, and metadata.

Column	Type	Description
id	string	Primary key, a unique identifier for the workflow.
name	string	The user-defined name of the workflow.
active	boolean	A flag indicating if the workflow is active.
nodes	json	A JSON object containing the definition of all nodes in the workflow.
connections	json	A JSON object defining the connections between nodes.
settings	json	Workflow-specific settings.
projectId	string	Foreign key linking to the Project entity.
createdAt	datetime	Timestamp of when the workflow was created.
updatedAt	datetime	Timestamp of the last update.

Sources: packages/@n8n/db/src/entities/workflow-entity.ts

ExecutionEntity

The ExecutionEntity stores the result and status of a single workflow run.

Column	Type	Description
id	string	Primary key, a unique identifier for the execution.
workflowId	string	Foreign key linking to the WorkflowEntity that was executed.
status	string	The final status of the execution (e.g., 'success', 'failed').
startedAt	datetime	Timestamp of when the execution began.
finishedAt	datetime	Timestamp of when the execution finished.
data	binary	Stores the execution data, including input, output, and parameters for each node.
mode	string	The execution mode (e.g., 'manual', 'webhook').

Sources: packages/@n8n/db/src/entities/execution-entity.ts

Project Entity

The Project entity is a container for organizing resources like workflows and credentials. It was introduced in a database migration.

Column	Type	Description
id	varchar	Primary key, a unique identifier for the project.
name	varchar(255)	The name of the project.
type	varchar	The type of project (e.g., 'personal', 'team').
description	varchar(512)	An optional description for the project.
createdAt	datetime	Timestamp of when the project was created.
`updatedAt``	datetime	Timestamp of the last update.

Sources: packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts:20-39

Data Validation

Before data is persisted, it is validated using Zod schemas. This ensures that only well-formed data enters the database. The project.schema.test.ts file demonstrates the validation rules for project-related fields.

Field	Rule	Example
name	Must be a string between 1 and 255 characters.	My Project (valid), '' (invalid)
type	Must be either 'personal' or 'team'.	'personal' (valid), 'invalid' (invalid)
description	Must be a string with a maximum length of 512 characters.	'Nice Description' (valid)
icon	Must be an object with type ('emoji' or 'icon') and a non-empty value.	{ type: 'emoji', value: '🚀' } (valid)
relation	Must be an object with a userId (string) and a valid role (e.g., 'project:admin').	{ userId: 'user-123', role: 'project:admin' } (valid)

Sources: packages/@n8n/api-types/src/schemas/tests/project.schema.test.ts

Database Migrations

Database schema changes are managed through TypeORM migrations. Each migration is a TypeScript class with up and down methods.

Migration Process

The up method applies new changes (e.g., creating tables, adding columns), while the down method reverts them. This ensures the database schema can be moved between different versions of the application reliably.

Sources: packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts

Example: CreateProject Migration

The 1714133768519-CreateProject.ts migration file is responsible for creating the project table and establishing its relationship with the workflow_entity table.

up() method

The up method executes SQL queries to create the project table and add a projectId foreign key to the workflow_entity table.

// packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts:18-46
public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
        new Table({
            name: 'project',
            columns: [
                // ... column definitions
            ],
        }),
    );

    await queryRunner.addColumn(
        'workflow_entity',
        new TableColumn({
            name: 'projectId',
            type: 'varchar',
            isNullable: true,
        }),
    );

    await queryRunner.createForeignKey(
        'workflow_entity',
        new TableForeignKey({
            columnNames: ['projectId'],
            referencedColumnNames: ['id'],
            referencedTableName: 'project',
            onDelete: 'SET NULL',
        }),
    );
}

down() method

The down method reverses the changes made by up, dropping the foreign key, the column, and finally the table.

// packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts:48-58
public async down(queryRunner: QueryRunner): Promise<void> {
    const foreignKey = (await queryRunner.getTable('workflow_entity'))?.foreignKeys.find(
        (fk) => fk.columnNames.indexOf('projectId') !== -1,
    );
    if (foreignKey) {
        await queryRunner.dropForeignKey('workflow_entity', foreignKey);
    }
    await queryRunner.dropColumn('workflow_entity', 'projectId');
    await queryRunner.dropTable('project');
}

Sources: packages/@n8n/db/src/migrations/common/1714133768519-CreateProject.ts

Database Import & Export

n8n provides CLI services to export data from one instance and import it into another. This is essential for backups, and migrating from self-hosted to cloud environments (or vice-versa).

Export Process

The ExportService queries the database for all entities, paginates through the results, and writes them to .jsonl files.

Sources: packages/cli/src/services/tests/export.service.test.ts

Import Process

The ImportService handles the process of populating a database from exported .jsonl files. It includes steps to ensure data integrity during the import.

This sequence shows that the import process is wrapped in a database transaction. It can optionally truncate existing tables and temporarily disables foreign key constraints to handle inter-table dependencies during data insertion.

A key step in the import process is migration validation. The validateMigrations method checks the migrations.jsonl file from the export against the target database's migrations table to ensure the source and target instances are at the same schema version. If they do not match, the import is aborted to prevent data corruption.

Sources: packages/cli/src/services/tests/import.service.test.ts