{"record":{"id":"8f9349f2b47fce4f","repo":"n8n-io/n8n","slug":"only-select-queries-are-supported-in-ctes-in-thi","errorCode":null,"errorMessage":"Only select queries are supported in CTEs in ${this.connection.options.type} (CTE: ${cte.alias})","messagePattern":"Only select queries are supported in CTEs in (.+?) \\(CTE: (.+?)\\)","errorType":"exception","errorClass":"TypeORMError","httpStatus":null,"severity":"error","filePath":"packages/@n8n/typeorm/src/query-builder/QueryBuilder.ts","lineNumber":1019,"sourceCode":"\tprotected createCteExpression(): string {\n\t\tif (!this.hasCommonTableExpressions()) {\n\t\t\treturn '';\n\t\t}\n\t\tconst databaseRequireRecusiveHint =\n\t\t\tthis.connection.driver.cteCapabilities.requiresRecursiveHint;\n\n\t\tconst cteStrings = this.expressionMap.commonTableExpressions.map((cte) => {\n\t\t\tconst cteBodyExpression =\n\t\t\t\ttypeof cte.queryBuilder === 'string' ? cte.queryBuilder : cte.queryBuilder.getQuery();\n\t\t\tif (typeof cte.queryBuilder !== 'string') {\n\t\t\t\tif (cte.queryBuilder.hasCommonTableExpressions()) {\n\t\t\t\t\tthrow new TypeORMError(`Nested CTEs aren't supported (CTE: ${cte.alias})`);\n\t\t\t\t}\n\t\t\t\tif (\n\t\t\t\t\t!this.connection.driver.cteCapabilities.writable &&\n\t\t\t\t\t!InstanceChecker.isSelectQueryBuilder(cte.queryBuilder)\n\t\t\t\t) {\n\t\t\t\t\tthrow new TypeORMError(\n\t\t\t\t\t\t`Only select queries are supported in CTEs in ${this.connection.options.type} (CTE: ${cte.alias})`,\n\t\t\t\t\t);\n\t\t\t\t}\n\t\t\t\tthis.setParameters(cte.queryBuilder.getParameters());\n\t\t\t}\n\t\t\tlet cteHeader = this.escape(cte.alias);\n\t\t\tif (cte.options.columnNames) {\n\t\t\t\tconst escapedColumnNames = cte.options.columnNames.map((column) => this.escape(column));\n\t\t\t\tif (InstanceChecker.isSelectQueryBuilder(cte.queryBuilder)) {\n\t\t\t\t\tif (\n\t\t\t\t\t\tcte.queryBuilder.expressionMap.selects.length &&\n\t\t\t\t\t\tcte.options.columnNames.length !== cte.queryBuilder.expressionMap.selects.length\n\t\t\t\t\t) {\n\t\t\t\t\t\tthrow new TypeORMError(\n\t\t\t\t\t\t\t`cte.options.columnNames length (${cte.options.columnNames.length}) doesn't match subquery select list length ${cte.queryBuilder.expressionMap.selects.length} (CTE: ${cte.alias})`,\n\t\t\t\t\t\t);\n\t\t\t\t\t}\n\t\t\t\t}","sourceCodeStart":1001,"sourceCodeEnd":1037,"githubUrl":"https://github.com/n8n-io/n8n/blob/5ac6606e81f67bb9534255570cd4e86fd8101eee/packages/@n8n/typeorm/src/query-builder/QueryBuilder.ts#L1001-L1037","documentation":"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.","triggerScenarios":"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.","commonSituations":"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).","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."],"exampleFix":"// before\nqb.addCommonTableExpression(\n  dataSource.createQueryBuilder().insert().into(Log).values(rows),\n  'ins',\n); // throws on SQLite\n\n// after\nif (dataSource.driver.cteCapabilities.writable) {\n  qb.addCommonTableExpression(insertQb, 'ins');\n} else {\n  await dataSource.createQueryBuilder().insert().into(Log).values(rows).execute();\n}","handlingStrategy":"validation","validationCode":"function canUseWritableCte(ds: DataSource): boolean {\n  return ds.driver.cteCapabilities?.writable === true;\n}\nif (!canUseWritableCte(dataSource)) throw new Error('driver does not support writable CTEs; use direct DML');","typeGuard":"function supportsWritableCte(driver: import('../driver/Driver').Driver): boolean {\n  return driver.cteCapabilities?.writable === true;\n}","tryCatchPattern":null,"preventionTips":["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."],"tags":["query-builder","cte","driver-capability","postgres"],"backgroundTag":null,"analyzedSha":"5ac6606e81f67bb9534255570cd4e86fd8101eee","analyzedAt":"2026-08-12T05:26:35.080Z","schemaVersion":2},"datasetVersion":"2026-08-13T19:17:28.613Z"}