IBMi_sql_access for n8n

@nicolaech/n8n-nodes-ibmi-sql-access is a new n8n community node derived from the working @nicolaech/n8n-nodes-ibmi-db2-mapepire 0.2.4 implementation. It keeps the Mapepire connection/pool/execution model while adapting runtime behavior and SQL policy for n8n Cloud verification constraints.

Operations

• Select — schema-qualified table/view reads from the configured read allowlist. Ordinary parenthesized SQL functions are accepted without putting each function in Allowed SQL Functions. Data-changing SQL keywords remain blocked.
• Insert — INSERT INTO ... VALUES only, to any configured write schema.
• Update — to any configured write schema; a top-level WHERE remains mandatory unless explicitly overridden.
• Create Table — restricted explicit base-table definitions only, in any configured write schema.
• QSYS2 Procedure — CALL QSYS2.PROCEDURE(...) only.

QSYS2 exception

Schema-qualified SQL routines are rejected by default, with one explicit exception: routines in QSYS2 are allowed. This applies to QSYS2 scalar/table functions used by SELECT and to the dedicated QSYS2 Procedure operation.

This is intentionally broader than the 0.2.4 policy. Some QSYS2 routines can have side effects. In particular, IBM documents QSYS2.QCMDEXC as capable of executing CL commands. The IBM i user profile used by the Mapepire connection therefore remains the final authority boundary and should be least-privilege. The dedicated procedure operation uses the JDBC read call access level rather than unrestricted all when executing the CALL.

Cloud-oriented changes

• No runtime filesystem access.
• No MAPEPIRE_CA_PATH; an optional CA certificate is stored inline in the encrypted n8n credential.
• No runtime environment-variable reads.
• No runtime npm dependency on @ibm/mapepire-js or ws in the published package. The build pins @ibm/mapepire-js 0.6.1 as a development input and vendors its upstream CommonJS bundle into dist.
• Static relative import of the vendored Mapepire runtime; no dynamic node:module loader.
• n8n.strict is enabled.
• Pool state is process-local and ephemeral only; no file/database storage is introduced by the node.

n8n Cloud only allows community nodes that have passed n8n's verification/publishing process. Making the package compatible with the technical constraints does not itself make an unpublished package installable on n8n Cloud.

Credential fields

Required connection values: host, port, IBM i user/password, database, and at least one read schema.

Important policy fields:

• Allowed Read Schemas (SQL_ALLOWED_READ_SCHEMAS) — comma/space separated.
• Allowed Write Schemas (SQL_ALLOWED_WRITE_SCHEMAS) — comma/space separated; blank disables normal INSERT/UPDATE/CREATE TABLE.
• Allowed SQL Functions (SQL_ALLOWED_FUNCTIONS) — retained for INSERT/UPDATE expressions. SELECT no longer needs each ordinary function listed here.
• CA Certificate PEM — optional inline CA certificate. No local path is supported.

For migration, the runtime parser still accepts the old single allowedWriteSchema credential value if imported programmatically, but the new UI exposes only allowedWriteSchemas.

Build

npm install
npm test
npm run verify:types
npm run verify:source
npm run build
npm pack

The normal build runs tools/vendor-mapepire.mjs after TypeScript compilation and copies the pinned official Mapepire bundle plus its license/manifest into dist.

Validation without an IBM i endpoint

The source contains unit tests for the SQL policy, configuration mapping, pool lifecycle, retry behavior, paging, batch writes, and the new QSYS2/multiple-write-schema rules. An actual IBM i/Mapepire endpoint is still required for end-to-end connectivity validation.

See docs/SECURITY-RULES.md, docs/CLOUD-COMPATIBILITY.md, and docs/MIGRATION-FROM-0.2.4.md.