n8n-io/n8n · error · TypeORMError

Only select queries are supported in CTEs in ${this.connecti

Error message

Only select queries are supported in CTEs in ${this.connection.options.type} (CTE: ${cte.alias})

What it means

createCteExpression() throws when the driver's cteCapabilities.writable is false AND the CTE body is not a SelectQueryBuilder. Writable CTEs (WITH ... INSERT/UPDATE/DELETE) are Postgres-specific; on SQLite/MySQL/Oracle a CTE must be a SELECT. Passing a non-writable CTE body (insert/update/delete builder) to such a driver is rejected before SQL generation.

Source

Thrown at packages/@n8n/typeorm/src/query-builder/QueryBuilder.ts:1019

	protected createCteExpression(): string {
		if (!this.hasCommonTableExpressions()) {
			return '';
		}
		const databaseRequireRecusiveHint =
			this.connection.driver.cteCapabilities.requiresRecursiveHint;

		const cteStrings = this.expressionMap.commonTableExpressions.map((cte) => {
			const cteBodyExpression =
				typeof cte.queryBuilder === 'string' ? cte.queryBuilder : cte.queryBuilder.getQuery();
			if (typeof cte.queryBuilder !== 'string') {
				if (cte.queryBuilder.hasCommonTableExpressions()) {
					throw new TypeORMError(`Nested CTEs aren't supported (CTE: ${cte.alias})`);
				}
				if (
					!this.connection.driver.cteCapabilities.writable &&
					!InstanceChecker.isSelectQueryBuilder(cte.queryBuilder)
				) {
					throw new TypeORMError(
						`Only select queries are supported in CTEs in ${this.connection.options.type} (CTE: ${cte.alias})`,
					);
				}
				this.setParameters(cte.queryBuilder.getParameters());
			}
			let cteHeader = this.escape(cte.alias);
			if (cte.options.columnNames) {
				const escapedColumnNames = cte.options.columnNames.map((column) => this.escape(column));
				if (InstanceChecker.isSelectQueryBuilder(cte.queryBuilder)) {
					if (
						cte.queryBuilder.expressionMap.selects.length &&
						cte.options.columnNames.length !== cte.queryBuilder.expressionMap.selects.length
					) {
						throw new TypeORMError(
							`cte.options.columnNames length (${cte.options.columnNames.length}) doesn't match subquery select list length ${cte.queryBuilder.expressionMap.selects.length} (CTE: ${cte.alias})`,
						);
					}
				}

View on GitHub (pinned to 5ac6606e81)

Solutions

Restrict writable CTEs to Postgres connections; branch on driver.cteCapabilities.writable.
On non-Postgres drivers, perform the DML directly (insert/update/delete) instead of via a CTE.
Use a SELECT CTE to compute rows, then run a separate DML consuming those rows.
Switch to Postgres if writable CTEs are essential to the design.

Example fix

// before
qb.addCommonTableExpression(
  dataSource.createQueryBuilder().insert().into(Log).values(rows),
  'ins',
); // throws on SQLite

// after
if (dataSource.driver.cteCapabilities.writable) {
  qb.addCommonTableExpression(insertQb, 'ins');
} else {
  await dataSource.createQueryBuilder().insert().into(Log).values(rows).execute();
}

Defensive patterns

Strategy: validation

Validate before calling

function canUseWritableCte(ds: DataSource): boolean {
  return ds.driver.cteCapabilities?.writable === true;
}
if (!canUseWritableCte(dataSource)) throw new Error('driver does not support writable CTEs; use direct DML');

Type guard

function supportsWritableCte(driver: import('../driver/Driver').Driver): boolean {
  return driver.cteCapabilities?.writable === true;
}

Prevention

Branch on driver.cteCapabilities.writable before passing DML builders as CTE bodies.
Run writable-CTE paths only on Postgres.
Provide a non-CTE DML fallback for SQLite/MySQL.
Test CTE features across every supported driver in CI.

When it happens

Trigger: Calling .addCommonTableExpression(insertQb, 'w') on a SQLite or MySQL connection (cteCapabilities.writable = false) where insertQb is an InsertQueryBuilder. Same for update/delete builders as CTE bodies on non-Postgres drivers.

Common situations: Developing writable-CTE upsert patterns on Postgres and running the same path on SQLite in tests. Expecting MySQL 8 CTE support to include writable CTEs (it does not for DML bodies here).

Related errors

AI-assisted analysis of n8n-io/n8n@5ac6606e81 (2026-08-12). Data as JSON: /api/errors/8f9349f2b47fce4f. Report an issue: GitHub.