n8n-data-tables

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

n8n Data Tables

Data Tables are n8n's built-in tabular storage: real tables inside the n8n instance with columns, types, rows, and CRUD via the

dataTable

node and data-table MCP tools.

Use them for local persistent state: lookup tables, recent events, per-session inventories, counters, idempotency tracking, dedup state when there's row-level logic or external visibility (plain "have I seen this value?" dedup belongs in the

Remove Duplicates

node). Small-to-moderate volume (tens of thousands of rows fine, millions belong in a real DB).

Data Tables是n8n的内置表格存储：n8n实例中的真实表格，包含列、类型、行，可通过

dataTable

节点和data-table MCP工具进行CRUD操作。

可将其用于本地持久化状态：查找表、近期事件、会话级库存、计数器、幂等性追踪、当存在行级逻辑或外部可见性时的去重状态（单纯的「是否见过该值」去重应使用

Remove Duplicates

节点）。适用于中小数据量（数万行没问题，数百万行建议使用真实数据库）。

Non-negotiables

必须遵守的准则

System-managed columns + external IDs. Three columns auto-exist on every table:
```
id
```
(bigserial),
```
createdAt
```
,
```
updatedAt
```
. Don't declare them in
```
create_data_table
```
(errors or shadows the system column). Don't write them on insert. For domain identifiers from outside (arxivId, stripeCustomerId, requestId), add a separate column and key dedup/lookup on that.
Only primitives in columns, nested data uses
string
+
_object
postfix. No JSON/object/array column types exist. For nested data (arrays, parsed objects), use a
```
string
```
column with
```
JSON.stringify(...)
```
on write and
```
JSON.parse(...)
```
on read. Mark the column with
```
_object
```
(e.g.,
```
keyInsights_object
```
). The postfix is the contract that tells readers to parse. See
```
references/SCHEMA_DESIGN.md
```
.

系统管理列 + 外部ID。每个表格自动存在三列：
```
id
```
（bigserial类型）、
```
createdAt
```
、
```
updatedAt
```
。不要在
```
create_data_table
```
中声明这些列（会报错或覆盖系统列）。插入时不要写入这些列。对于外部领域标识符（如arxivId、stripeCustomerId、requestId），添加单独的列，并基于该列进行去重/查找。
列中仅存储基础类型，嵌套数据使用
string
类型 +
_object
后缀。不存在JSON/对象/数组列类型。对于嵌套数据（数组、解析后的对象），写入时使用
```
JSON.stringify(...)
```
转换为字符串，读取时使用
```
JSON.parse(...)
```
解析。列名需添加
```
_object
```
后缀（例如
```
keyInsights_object
```
）。该后缀是约定，用于提示读者需要解析该列。详情见
```
references/SCHEMA_DESIGN.md
```
。

Strong defaults

最佳默认做法

Don't add a Set node before a Data Table node to modify fields. The Data Table node's per-column expression slots are just as powerful as Set fields, so the Set node is doing zero work the Data Table node can't do itself. (Same Set-node antipattern called out in
```
n8n-expressions
```
.)
Match n8n's column casing: camelCase. The auto-managed columns are camelCase (
```
createdAt
```
,
```
updatedAt
```
), so user columns read more cleanly when they match:
```
arxivId
```
,
```
paperId
```
,
```
taxRate
```
. Mixed casing in the same query (
```
createdAt >= ... AND arxiv_id eq ...
```
) reads as a typo. Keep the
```
_object
```
postfix on stringified-blob columns regardless (
```
keyInsights_object
```
), the underscore is a contract marker, not casing.

Verify the
columns
parameter via
get_workflow_details
after create/update. The UI has a display quirk in manual mapping mode ("Currently no items exist" with no actual data loss). Checking the JSON confirms what's persisted.
Relational design works when the shape calls for it. For genuine parent-child data (papers → summaries, customers → orders), reference parents by
```
id
```
, name columns explicitly (
```
paperId
```
,
```
customerId
```
), and enforce integrity in workflow logic. Don't force it on flat use cases (dedup, lookup, audit) where there's no relationship to model.
Storage format is not interface format. Parse
```
_object
```
fields before returning them from a sub-workflow. Callers should never receive stringified shells they have to parse themselves. See
```
references/SCHEMA_DESIGN.md
```
"Storage format ≠ interface format".

不要在Data Table节点前添加Set节点来修改字段。Data Table节点的每列表达式槽与Set字段功能同样强大，因此Set节点无法完成任何Data Table节点本身做不到的工作（
```
n8n-expressions
```
中也提到了这种Set节点反模式）。
匹配n8n的列命名规范：camelCase。系统自动管理的列采用camelCase格式（
```
createdAt
```
、
```
updatedAt
```
），因此用户自定义列使用相同格式时可读性更强：
```
arxivId
```
、
```
paperId
```
、
```
taxRate
```
。同一查询中混用大小写（如
```
createdAt >= ... AND arxiv_id eq ...
```
）会看起来像拼写错误。无论如何，字符串化二进制大对象列需保留
```
_object
```
后缀（如
```
keyInsights_object
```
），下划线是约定标记，不属于命名规范。

创建/更新后通过
get_workflow_details
验证
columns
参数。手动映射模式下UI存在显示异常（「当前无数据项」但实际无数据丢失）。查看JSON可确认持久化的内容。
当数据结构需要时使用关系型设计。对于真正的父子数据（论文→摘要、客户→订单），通过
```
id
```
引用父级，明确命名列（
```
paperId
```
、
```
customerId
```
），并在工作流逻辑中保证数据完整性。不要在无关系可建模的扁平场景（去重、查找、审计）中强行使用。
存储格式≠接口格式。从子工作流返回数据前，需解析
```
_object
```
字段。调用方不应收到需要自行解析的字符串化数据壳。详情见
```
references/SCHEMA_DESIGN.md
```
中的「存储格式≠接口格式」部分。

The default columns

默认列

Every Data Table has these whether you declare them or not:

Column	Type	Behavior
`id`	bigserial / number	Auto-incrementing primary key. n8n assigns on insert, and you can't write to it. Returned in the insert response.
`createdAt`	timestamp	Set automatically on insert.
`updatedAt`	timestamp	Refreshed automatically on each update.

In practice:

Don't declare them in
```
create_data_table
```
. Already there.
Use them in queries without your own timestamp columns. "Created today":
```
createdAt >= '<today ISO>'
```
. "Updated since last sync":
```
updatedAt >= $('Last Sync').item.json.timestamp
```
.
Don't use them as cross-system identifiers. Auto-
```
id
```
is internal, and resets on table recreate or instance migration. For domain identifiers, use your own column.

无论是否声明，每个Data Table都包含以下列：

列名	类型	行为
`id`	bigserial / 数字	自增主键。n8n在插入时自动分配，无法手动写入。会在插入响应中返回。
`createdAt`	时间戳	插入时自动设置。
`updatedAt`	时间戳	每次更新时自动刷新。

实际使用注意事项：

不要在
create_data_table
中声明这些列，它们已经存在。
查询时使用这些列，无需自行创建时间戳列。例如「今日创建」：
```
createdAt >= '<今日ISO格式>'
```
；「上次同步后更新」：
```
updatedAt >= $('Last Sync').item.json.timestamp
```
。
不要将这些列用作跨系统标识符。自动生成的
```
id
```
是内部标识，在表格重建或实例迁移时会重置。对于领域标识符，使用自定义列。

Relational design when the shape calls for it

数据结构需要时的关系型设计

Data Tables don't enforce foreign keys, but you can still model parent-child data across tables when the data genuinely has that shape. The catch: integrity is your responsibility, not n8n's.

Reference parent rows by
id
. A child table holds the parent's
```
id
```
in a column.
Document references in column names.
```
paperId
```
,
```
customerId
```
,
```
eventId
```
make the relationship obvious.
Enforce integrity in workflow logic. Before inserting a child, look up the parent. Before deleting a parent, decide what happens to children (delete, orphan, archive). n8n won't cascade.
Watch for stale references. Children pointing at deleted parents are silent bugs. Soft-delete, or run cleanup workflows.

For complex relational structure (3+ tables with joins, transactional writes), reach for an actual SQL DB.

Data Tables不支持外键约束，但当数据确实存在父子结构时，仍可跨表建模父子数据。注意：数据完整性需由您负责，而非n8n。

通过
id
引用父行。子表中用一列存储父级的
```
id
```
。
在列名中明确标注引用关系。
```
paperId
```
、
```
customerId
```
、
```
eventId
```
可让关系一目了然。
在工作流逻辑中保证数据完整性。插入子数据前，先查找父数据。删除父数据前，确定子数据的处理方式（删除、保留、归档）。n8n不会自动级联操作。
注意过时引用。指向已删除父数据的子数据会导致隐性bug。可使用软删除，或运行清理工作流。

对于复杂的关系结构（3个及以上表的关联、事务性写入），建议使用真实的SQL数据库。

Operations: which one for what

操作场景匹配

Operation	When
`insert`	Always-add. New row, n8n assigns `id` .
`upsert`	"Add if new, update if exists." Needs a `matchType` and filter to decide existence.
`update`	"Modify rows matching this filter." No insert if no match.
`get`	Fetch rows matching a filter (returns 0+). Supports `orderBy` , `limit` , `returnAll` .
`deleteRows`	Remove rows matching a filter.
`rowExists` / `rowNotExists`	Boolean-style filter against incoming items. Common for dedup branching.

For the full operation surface (filter syntax, matchType, sort patterns), see

references/OPERATIONS.md

操作	使用场景
`insert`	仅新增。生成新行，n8n自动分配 `id` 。
`upsert`	「不存在则新增，存在则更新」。需要 `matchType` 和过滤器来判断数据是否存在。
`update`	「修改匹配过滤器的行」。无匹配项时不会插入。
`get`	获取匹配过滤器的行（返回0行及以上）。支持 `orderBy` 、 `limit` 、 `returnAll` 参数。
`deleteRows`	删除匹配过滤器的行。
`rowExists` / `rowNotExists`	对输入项进行布尔值过滤。常用于去重分支判断。

完整操作说明（过滤器语法、matchType、排序模式）见

references/OPERATIONS.md

。

The "Currently no items exist" UI quirk

「当前无数据项」UI显示异常

When the SDK saves manual-mode column mappings (

mappingMode: 'defineBelow'

), the n8n UI's "Values to insert" pane can render empty ("Currently no items exist") even though runtime persists data correctly. If the user reports the Insert node "looks broken" or "has no fields," tell them: it's a UI display issue, press the reload (refresh) button on the columns parameter, and it repopulates the schema and the mappings render. No data loss, safe to do anytime.

当SDK保存手动模式列映射（

mappingMode: 'defineBelow'

）时，n8n UI的「要插入的值」面板可能显示为空（「当前无数据项」），但运行时实际已正确持久化数据。如果用户反馈Insert节点「看起来坏了」或「没有字段」，请告知：这是UI显示问题，点击columns参数上的刷新按钮，即可重新加载schema并显示映射。无数据丢失，任何时候操作都安全。

Common patterns

常见模式

Dedup by external ID

基于外部ID去重

Default to the

Remove Duplicates

node ("items seen in previous executions" mode) for plain "have I seen this value?" dedup. It's a one-node solution with an internal store, no schema to maintain. Data Tables only earn the slot when there's a reason for the dedup state to live in a real table:

You'll query or inspect the dedup state. Dashboards, audit, "what have we processed in the last week?"
Row-level logic on hits. Per-category TTL ("expire after 30 days for category A, 7 days for category B"), conditional re-process based on stored state, branching on a status column.
Per-tenant or per-user namespacing that the
```
Remove Duplicates
```
history-store can't express.

When that bar is met:

[Source: { arxivId, ... }]
   ↓
[Data Table Get: filter arxivId eq $json.arxivId, limit 1]
   ↓
[IF: result has items?]
   ├── Yes → [Skip, or apply row-level logic from the stored row]
   └── No  → [Process] → [Data Table Insert: { arxivId, ...rest }]

For the full pattern surface (upsert, rowNotExists, Get+IF, idempotency keys), see

references/DEDUP_PATTERNS.md

对于单纯的「是否见过该值」去重，默认使用

Remove Duplicates

节点（「之前执行中见过的项」模式）。这是无需维护schema的单节点解决方案，内部自带存储。只有当去重状态需要存储在真实表格中时，才使用Data Tables：

需要查询或检查去重状态：如仪表盘、审计、「过去一周处理过哪些内容？」
命中时需执行行级逻辑：按类别设置TTL（「A类30天后过期，B类7天后过期」）、基于存储状态有条件地重新处理、基于状态列分支。
需要按租户或用户隔离，而
```
Remove Duplicates
```
的历史存储无法实现该需求。

满足上述条件时，使用以下流程：

[数据源: { arxivId, ... }]
   ↓
[Data Table Get: 过滤器 arxivId eq $json.arxivId, limit 1]
   ↓
[条件判断: 是否有结果？]
   ├── 是 → [跳过，或基于存储行执行行级逻辑]
   └── 否 → [处理] → [Data Table Insert: { arxivId, ...rest }]

完整模式说明（upsert、rowNotExists、Get+条件判断、幂等键）见

references/DEDUP_PATTERNS.md

。

Lookup tables

查找表

Stable reference data (country → tax rate, plan → feature flags). Edited via n8n UI, and workflows read at execution:

[Data Table Get: filter country eq $json.country, limit 1]
   ↓
[Use the looked-up row's taxRate, etc.]

稳定的参考数据（国家→税率、套餐→功能标志）。可通过n8n UI编辑，工作流执行时读取：

[Data Table Get: 过滤器 country eq $json.country, limit 1]
   ↓
[使用查找行的taxRate等数据]

Recent events / audit trail

近期事件/审计追踪

Append-only insert, queried later:

[Workflow event] → [Data Table Insert: { userId, eventType, payloadSummary }]

createdAt

makes "recent events in the last hour" trivial without your own timestamp.

仅追加插入，后续查询：

[工作流事件] → [Data Table Insert: { userId, eventType, payloadSummary }]

createdAt

列可轻松实现「过去一小时内的近期事件」查询，无需自行创建时间戳列。

Reference files

参考文档

File	Read when
`references/SCHEMA_DESIGN.md`	Designing columns/types, the no-FK relational pattern, mapping mode ( `defineBelow` vs `autoMapInputData` ), when Data Tables are the wrong tool
`references/OPERATIONS.md`	Operation surface (insert/upsert/update/get/delete/rowExists), filter syntax, matchType, orderBy
`references/DEDUP_PATTERNS.md`	Idempotency keys, RemoveDuplicates node vs Data Table dedup, search-then-insert vs upsert

For expression discipline (

$json

$('Node Name').item.json

, the Set-node antipattern), see

n8n-expressions

. For Merge convergence and same-shape branches, see

n8n-connections

文件	阅读场景
`references/SCHEMA_DESIGN.md`	设计列/类型、无外键的关系型模式、映射模式（ `defineBelow` vs `autoMapInputData` ）、判断何时不适合使用Data Tables
`references/OPERATIONS.md`	操作说明（insert/upsert/update/get/delete/rowExists）、过滤器语法、matchType、orderBy
`references/DEDUP_PATTERNS.md`	幂等键、RemoveDuplicates节点vs Data Table去重、先搜索后插入vs upsert

表达式规范（

$json

$('Node Name').item.json

、Set节点反模式）见

n8n-expressions

。合并收敛和同结构分支见

n8n-connections

。

Anti-patterns

反模式

Anti-pattern	What goes wrong	Fix
Set node upstream of Insert "to shape the input"	Extra node for nothing, classic Set antipattern, field shape drifts when you add columns	Map directly in the Insert node's per-column slots, OR rename upstream fields to enable auto-map
Declaring `id` , `createdAt` , `updatedAt` in `create_data_table`	Errors, or shadows the system column with a user column that doesn't auto-update	Don't declare them, they're already there
Storing application-critical data in Data Tables	If n8n breaks, you lose access	Use a real DB for data you can't lose
Cross-app system-of-record in Data Tables	Hard to share with non-n8n consumers, awkward query surface	Use a real DB
Treating auto- `id` as a stable cross-instance identifier	Resets if the table is recreated, not portable	Use a domain ID column ( `arxivId` , `requestId` ) for cross-system references
Foreign-key cascade assumptions	n8n doesn't cascade, deleted parents leave orphan children	Soft-delete, or run cleanup workflows that maintain referential integrity
Referencing an immediately-prior node when an intermediate stripped json	Insert silently writes NULLs for fields that "should be there"	Reference a stable upstream node by name, or use a NoOp/Merge convergence anchor (see `n8n-expressions` and `n8n-connections` )
Manual-map mode + Set node to fix "Currently no items exist"	Doesn't fix anything, that's a UI quirk, you've added a useless Set node	Verify via `get_workflow_details` that `columns.value` has your mappings, runtime is fine. Tell the user to press the reload button on the columns parameter to make the UI render the fields.

反模式	问题	修复方案
Insert节点上游使用Set节点「调整输入格式」	多余节点，典型的Set反模式，添加列时字段格式会不一致	直接在Insert节点的每列槽中映射，或重命名上游字段以启用自动映射
在 `create_data_table` 中声明 `id` 、 `createdAt` 、 `updatedAt`	报错，或用用户列覆盖系统列且无法自动更新	不要声明这些列，它们已存在
在Data Tables中存储应用核心数据	n8n故障时会丢失数据访问权限	对于不可丢失的数据，使用真实数据库
将Data Tables作为跨应用的记录系统	非n8n使用者难以共享，查询体验不佳	使用真实数据库
将自动生成的 `id` 作为跨实例的稳定标识符	表格重建时会重置，不具备可移植性	使用领域ID列（ `arxivId` 、 `requestId` ）作为跨系统引用
假设外键会自动级联	n8n不支持级联操作，删除父数据会留下孤立子数据	使用软删除，或运行维护引用完整性的清理工作流
中间节点剥离JSON时引用紧邻的前一个节点	Insert会静默写入「应该存在」的字段为NULL	通过名称引用稳定的上游节点，或使用NoOp/Merge收敛锚点（见 `n8n-expressions` 和 `n8n-connections` ）
手动映射模式下使用Set节点修复「当前无数据项」问题	无任何作用，这是UI显示异常，添加了无用的Set节点	通过 `get_workflow_details` 验证 `columns.value` 包含映射，运行时正常。告知用户点击columns参数的刷新按钮即可让UI显示字段。

Verification before publishing

发布前验证

After creating or updating a workflow that uses Data Tables:

```
validate_workflow
```
passes.
```
get_workflow_details
```
and inspect each Data Table node's
```
columns
```
. Both
```
value
```
and (for manual map)
```
schema
```
populated.
```
test_workflow
```
with pinned data. Insert response should include
```
id
```
,
```
createdAt
```
,
```
updatedAt
```
.
Inspect actual Data Table contents via UI or follow-up Get to confirm columns aren't silently NULL.

Step 4 especially the first time you wire a new Insert. Context-stripping intermediates + manual map + UI quirk silently produce NULL columns.

创建或更新使用Data Tables的工作流后：

```
validate_workflow
```
验证通过。
调用
```
get_workflow_details
```
并检查每个Data Table节点的
```
columns
```
。
```
value
```
和（手动映射模式下的）
```
schema
```
均已填充。
使用固定数据运行
```
test_workflow
```
。Insert响应应包含
```
id
```
、
```
createdAt
```
、
```
updatedAt
```
。
通过UI或后续Get操作检查Data Table实际内容，确认列未被静默设置为NULL。

首次配置新Insert时，步骤4尤为重要。上下文剥离的中间节点+手动映射+UI异常可能会静默生成NULL列。