knex - npm Package Compare versions

Comparing version 0.21.9 to 0.21.10

CHANGELOG.md

 # Master (Unreleased)
 
+# 0.21.10 - 31 October, 2020
+
+### New features:
+
+- Upsert support (Postgres/MySQL/Sqlite) #3763
+
+### Bug fixes:
+
+- Switch to non-uuid knexQueryUids to avoid issues when mocking global date #4089
+
+### Typings:
+
+- Allow to globally define table/record mapping (#4071
+
 # 0.21.9 - 27 October, 2020
@@ -4,0 +18,0 @@

lib/dialects/mssql/query/compiler.js

@@ -25,2 +25,8 @@ // MSSQL Query Compiler
     super(client, builder);
+
+    const { onConflict } = this.single;
+    if (onConflict) {
+      throw new Error('.onConflict() is not supported for mssql.');
+    }
+
     this._emptyInsertValue = 'default values';
@@ -27,0 +33,0 @@ }

lib/dialects/mysql/query/compiler.js

@@ -12,3 +12,2 @@ // MySQL Query Compiler
     const { returning } = this.single;
-
     if (returning) {
@@ -19,5 +18,41 @@ this.client.logger.warn(
     }
+
     this._emptyInsertValue = '() values ()';
   }
 
+  // Compiles an `insert` query, allowing for multiple
+  // inserts using a single query statement.
+  insert() {
+    let sql = super.insert();
+    if (sql === '') return sql;
+
+    const { ignore, merge, insert } = this.single;
+    if (ignore) sql = sql.replace('insert into', 'insert ignore into');
+    if (merge) sql += this._merge(merge.updates, insert);
+
+    return sql;
+  }
+
+  _merge(updates, insert) {
+    let sql = ' on duplicate key update ';
+    if (updates) {
+      const updateData = this._prepUpdate(updates);
+      return sql + updateData.join(',');
+    } else {
+      const insertData = this._prepInsert(insert);
+      if (typeof insertData === 'string') {
+        throw new Error(
+          'If using merge with a raw insert query, then updates must be provided'
+        );
+      }
+
+      sql += insertData.columns
+        .map((column) => this.formatter.wrapAsIdentifier(column))
+        .map((column) => `${column} = values(${column})`)
+        .join(', ');
+
+      return sql;
+    }
+  }
+
   // Update method, including joins, wheres, order & limits.
@@ -24,0 +59,0 @@ update() {

lib/dialects/oracle/query/compiler.js

@@ -35,2 +35,7 @@ /* eslint max-len:0 */
 
+    const { onConflict } = this.single;
+    if (onConflict) {
+      throw new Error('.onConflict() is not supported for oracledb.');
+    }
+
     // Compiles the `select` statement, or nested sub-selects
@@ -37,0 +42,0 @@ // by calling each of the component compilers, trimming out

lib/dialects/postgres/query/compiler.js

@@ -24,7 +24,13 @@ // PostgreSQL Query Builder & Compiler
   insert() {
-    const sql = QueryCompiler.prototype.insert.call(this);
+    let sql = QueryCompiler.prototype.insert.call(this);
     if (sql === '') return sql;
-    const { returning } = this.single;
+
+    const { returning, onConflict, ignore, merge, insert } = this.single;
+    if (onConflict && ignore) sql += this._ignore(onConflict);
+    if (onConflict && merge)
+      sql += this._merge(merge.updates, onConflict, insert);
+    if (returning) sql += this._returning(returning);
+
     return {
-      sql: sql + this._returning(returning),
+      sql: sql,
       returning,
@@ -69,2 +75,36 @@ };
 
+  _ignore(columns) {
+    return ` on conflict (${this.formatter.columnize(columns)}) do nothing`;
+  }
+
+  _merge(updates, columns, insert) {
+    let sql = ` on conflict (${this.formatter.columnize(
+      columns
+    )}) do update set `;
+    if (updates) {
+      const updateData = this._prepUpdate(updates);
+      if (typeof updateData === 'string') {
+        sql += updateData;
+      } else {
+        sql += updateData.join(',');
+      }
+
+      return sql;
+    } else {
+      const insertData = this._prepInsert(insert);
+      if (typeof insertData === 'string') {
+        throw new Error(
+          'If using merge with a raw insert query, then updates must be provided'
+        );
+      }
+
+      sql += insertData.columns
+        .map((column) => this.formatter.wrapString(column.split('.').pop()))
+        .map((column) => `${column} = excluded.${column}`)
+        .join(', ');
+
+      return sql;
+    }
+  }
+
   // Join array of table names and apply default schema.
@@ -71,0 +111,0 @@ _tableNames(tables) {

lib/dialects/sqlite3/query/compiler.js

@@ -84,3 +84,11 @@ // SQLite3 Query Builder & Compiler
       );
-      return sql + ` values (${parameters})`;
+      sql += ` values (${parameters})`;
+
+      const { onConflict, ignore, merge } = this.single;
+      if (onConflict && ignore) sql += this._ignore(onConflict);
+      else if (onConflict && merge) {
+        sql += this._merge(merge.updates, onConflict, insertValues);
+      }
+
+      return sql;
     }
@@ -105,5 +113,48 @@
     }
-    return sql + ' select ' + blocks.join(' union all select ');
+    sql += ' select ' + blocks.join(' union all select ');
+
+    const { onConflict, ignore, merge } = this.single;
+    if (onConflict && ignore) sql += ' where true' + this._ignore(onConflict);
+    else if (onConflict && merge) {
+      sql +=
+        ' where true' + this._merge(merge.updates, onConflict, insertValues);
+    }
+
+    return sql;
   }
 
+  _ignore(columns) {
+    return ` on conflict (${this.formatter.columnize(columns)}) do nothing`;
+  }
+
+  _merge(updates, columns, insert) {
+    let sql = ` on conflict (${this.formatter.columnize(
+      columns
+    )}) do update set `;
+    if (updates) {
+      const updateData = this._prepUpdate(updates);
+      if (typeof updateData === 'string') {
+        sql += updateData;
+      } else {
+        sql += updateData.join(',');
+      }
+
+      return sql;
+    } else {
+      const insertData = this._prepInsert(insert);
+      if (typeof insertData === 'string') {
+        throw new Error(
+          'If using merge with a raw insert query, then updates must be provided'
+        );
+      }
+
+      sql += insertData.columns
+        .map((column) => this.formatter.wrapString(column.split('.').pop()))
+        .map((column) => `${column} = excluded.${column}`)
+        .join(', ');
+
+      return sql;
+    }
+  }
+
   // Compile a truncate table statement into SQL.
@@ -137,3 +188,3 @@ truncate() {
           resp,
-          function (columns, val) {
+          function(columns, val) {
             let { type } = val;
@@ -140,0 +191,0 @@ let maxLength = type.match(maxLengthRegex);

lib/query/builder.js

@@ -24,2 +24,31 @@ // Builder
 
+// Sub-builder for onConflict clauses
+function OnConflictBuilder(builder, columns) {
+  this.builder = builder;
+  this._columns = columns;
+}
+
+assign(OnConflictBuilder.prototype, {
+  // Sets insert query to ignore conflicts
+  ignore() {
+    this.builder._single.onConflict = this._columns;
+    this.builder._single.ignore = true;
+    return this.builder;
+  },
+
+  // Sets insert query to update on conflict
+  merge(updates) {
+    this.builder._single.onConflict = this._columns;
+    this.builder._single.merge = { updates };
+    return this.builder;
+  },
+
+  // Prevent
+  then() {
+    throw new Error(
+      'Incomplete onConflict clause. .onConflict() must be diretly followed by either .merge() or .ignore()'
+    );
+  },
+});
+
 // Typically called from `knex.builder`,
@@ -1091,2 +1120,9 @@ // start a new query building chain.
 
+  onConflict(columns) {
+    if (typeof columns === 'string') {
+      columns = [columns];
+    }
+    return new OnConflictBuilder(this, columns || true);
+  },
+
   // Delete
@@ -1093,0 +1129,0 @@ // ------

lib/query/compiler.js

@@ -18,3 +18,3 @@ // Query Compiler
 const reduce = require('lodash/reduce');
-const uuid = require('uuid');
+const { nanoid } = require('../util/nanoid');
 const { isString, isUndefined } = require('../util/is');
@@ -70,3 +70,3 @@
       bindings: this.formatter.bindings || [],
-      __knexQueryUid: uuid.v1(),
+      __knexQueryUid: nanoid(),
     };
@@ -73,0 +73,0 @@

lib/raw.js

@@ -12,3 +12,3 @@ // Raw
 const saveAsyncStack = require('./util/save-async-stack');
-const uuid = require('uuid');
+const { nanoid } = require('./util/nanoid');
 const { isNumber, isObject } = require('./util/is');
@@ -113,3 +113,3 @@
 
-    obj.__knexQueryUid = uuid.v1();
+    obj.__knexQueryUid = nanoid();
 
@@ -116,0 +116,0 @@ return obj;

package.json

 {
   "name": "knex",
-  "version": "0.21.9",
+  "version": "0.21.10",
   "description": "A batteries-included SQL query & schema builder for Postgres, MySQL and SQLite3 and the Browser",
@@ -46,3 +46,2 @@ "main": "knex.js",
     "tildify": "2.0.0",
-    "uuid": "^7.0.3",
     "v8flags": "^3.2.0"
@@ -192,3 +191,4 @@ },
     "types/index.d.ts",
-    "types/result.d.ts"
+    "types/result.d.ts",
+    "types/tables.d.ts"
   ],
@@ -195,0 +195,0 @@ "license": "MIT",

No alert changes

Improved metrics

Total package byte prevSize

603010

increased by1.78%

Dependency count

17

decreased by-5.56%

Number of package files

139

increased by1.46%

Lines of code

15687

increased by1.61%

Dependency changes

• - Removeduuid@^7.0.3

• - Removeduuid@7.0.3(transitive)