Repository: knex/knex
Pull request #5386 (2022-11-17 09:48:30)

feat(pg): implement QueryBuilder#updateFrom
Documentation PR: https://github.com/knex/documentation/pull/476

Introduces the `updateFrom` querybuilder method for PostgreSQL. This allows the creation of `UPDATE <table> SET <data> FROM <name>` queries, which may reference relations or CTEs outside of the target table.

I opted for creating a new QB method (`updateFrom`) to avoid conflict or weird interactions or unexpected changes to behaviour with the target table already being inferred from `knex(table)`, `knex.table(table)`, etc.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/54321900/badge)](https://coveralls.io/builds/54321900)

Coverage increased (+0.003%) to 92.338% when pulling **0d7b256db924f9e93a484c15d67590c6bc14fccc on wms:pg-update-from** into **4fc939a17640fd1abf1a94d106b7836ae20c1d1e on knex:master**.

wms: Sorry, looks like I've not fully understood how the testsuite works; its attempting to run pg-specific QB methods against other DB engines.

Is there documentation on how to correctly place my tests so that they only run against the correct DB engine(s)?
wms: > BY the way, we are sure only PG support update from ? Cannot be added for other DB?

I've not done an exhaustive search, but I know that (and am in need of support for) PostgreSQL handles this.

Patch:
diff --git a/.github/workflows/coverage.yml b/.github/workflows/coverage.yml
index f5ff4003..41bcff96 100644
--- a/.github/workflows/coverage.yml
+++ b/.github/workflows/coverage.yml
@@ -28,7 +28,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.6.0
+        uses: actions/setup-node@v3.7.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/.github/workflows/linting.yml b/.github/workflows/linting.yml
index 2e451413..31304736 100644
--- a/.github/workflows/linting.yml
+++ b/.github/workflows/linting.yml
@@ -29,7 +29,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.6.0
+        uses: actions/setup-node@v3.7.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/lib/dialects/postgres/query/pg-querybuilder.js b/lib/dialects/postgres/query/pg-querybuilder.js
index 2d4868dc..b5bb5b75 100644
--- a/lib/dialects/postgres/query/pg-querybuilder.js
+++ b/lib/dialects/postgres/query/pg-querybuilder.js
@@ -1,6 +1,11 @@
 const QueryBuilder = require('../../../query/querybuilder.js');
 
 module.exports = class QueryBuilder_PostgreSQL extends QueryBuilder {
+  updateFrom(name) {
+    this._single.updateFrom = name;
+    return this;
+  }
+
   using(tables) {
     this._single.using = tables;
     return this;
diff --git a/lib/dialects/postgres/query/pg-querycompiler.js b/lib/dialects/postgres/query/pg-querycompiler.js
index e4dcff4e..137486f7 100644
--- a/lib/dialects/postgres/query/pg-querycompiler.js
+++ b/lib/dialects/postgres/query/pg-querycompiler.js
@@ -50,12 +50,13 @@ class QueryCompiler_PG extends QueryCompiler {
     const withSQL = this.with();
     const updateData = this._prepUpdate(this.single.update);
     const wheres = this.where();
-    const { returning } = this.single;
+    const { returning, updateFrom } = this.single;
     return {
       sql:
         withSQL +
         `update ${this.single.only ? 'only ' : ''}${this.tableName} ` +
         `set ${updateData.join(', ')}` +
+        this._updateFrom(updateFrom) +
         (wheres ? ` ${wheres}` : '') +
         this._returning(returning),
       returning,
@@ -141,6 +142,10 @@ class QueryCompiler_PG extends QueryCompiler {
     return value ? ` returning ${this.formatter.columnize(value)}` : '';
   }
 
+  _updateFrom(name) {
+    return name ? ` from ${this.formatter.wrap(name)}` : '';
+  }
+
   _ignore(columns) {
     if (columns === true) {
       return ' on conflict do nothing';
diff --git a/types/index.d.ts b/types/index.d.ts
index 3f1b78d2..5f72e3fe 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -1151,6 +1151,8 @@ export declare namespace Knex {
 
     onConflict(): OnConflictQueryBuilder<TRecord, TResult>;
 
+    updateFrom: Table<TRecord, TResult>;
+
     del(
       returning: '*',
       options?: DMLOptions


Test patch:
diff --git a/.github/workflows/integration-tests.yml b/.github/workflows/integration-tests.yml
index 72a865f7..1fa73d0f 100644
--- a/.github/workflows/integration-tests.yml
+++ b/.github/workflows/integration-tests.yml
@@ -31,7 +31,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.6.0
+        uses: actions/setup-node@v3.7.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
@@ -108,7 +108,7 @@ jobs:
         with: { fetch-depth: 1 }
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.6.0
+        uses: actions/setup-node@v3.7.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/.github/workflows/unit-tests.yml b/.github/workflows/unit-tests.yml
index a57dfd69..0a215bd4 100644
--- a/.github/workflows/unit-tests.yml
+++ b/.github/workflows/unit-tests.yml
@@ -29,7 +29,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.6.0
+        uses: actions/setup-node@v3.7.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/test/integration2/query/update/updates.spec.js b/test/integration2/query/update/updates.spec.js
index 55e61ef6..e699870c 100644
--- a/test/integration2/query/update/updates.spec.js
+++ b/test/integration2/query/update/updates.spec.js
@@ -3,6 +3,7 @@
 const { expect } = require('chai');
 
 const { TEST_TIMESTAMP } = require('../../../util/constants');
+const { isPostgreSQL } = require('../../../util/db-helpers');
 const {
   getAllDbs,
   getKnexForDb,
@@ -534,6 +535,30 @@ describe('Updates', function () {
         expect(results[0].last_name).to.equal('olivier');
       });
 
+      it('should allow explicit from', async function () {
+        if (!isPostgreSQL(knex)) {
+          return this.skip();
+        }
+
+        await knex('accounts')
+          .update({ last_name: 'olivier' })
+          .with('withClause', function () {
+            this.select('id', 'last_name')
+              .from('accounts')
+              .where('email', '=', 'test1@example.com');
+          })
+          .updateFrom('withClause')
+          .where('withClause.id', '=', knex.ref('accounts.id'))
+          .testSql(function (tester) {
+            tester(
+              'pg',
+              'with "withClause" as (select "id", "last_name" from "accounts" where "email" = ?) update "accounts" set "last_name" = ? from "withClause" where "withClause"."id" = "accounts"."id"',
+              ['test1@example.com', 'olivier'],
+              1
+            );
+          });
+      });
+
       it('should escaped json objects when update value #5059', async function () {
         await knex.schema.dropTableIfExists('testing');
         await knex.schema.createTable('testing', (t) => {
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index 00aff178..3a2d11d7 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -6080,6 +6080,22 @@ describe('QueryBuilder', () => {
     );
   });
 
+  it('update from table', () => {
+    testsql(
+      qb()
+        .update({ email: 'foo', name: 'bar' })
+        .table('users')
+        .updateFrom('others')
+        .where('id', '=', 1),
+      {
+        pg: {
+          sql: 'update "users" set "email" = ?, "name" = ? from "others" where "id" = ?',
+          bindings: ['foo', 'bar', 1],
+        },
+      }
+    );
+  });
+
   it('should not update columns undefined values', () => {
     testsql(
       qb()