• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

knex / knex / 22119956886

17 Feb 2026 11:30PM UTC coverage: 93.128% (+0.003%) from 93.125%
22119956886

Pull #6380

github

web-flow
Merge 6fb42a440 into 80c1da585
Pull Request #6380: fix: prevent pre-existing WHERE clauses from leaking into upsert

3736 of 4414 branches covered (84.64%)

Branch coverage included in aggregate %.

77 of 88 new or added lines in 7 files covered. (87.5%)

9 existing lines in 1 file now uncovered.

15590 of 16338 relevant lines covered (95.42%)

2274.52 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

73.64
/test/integration/query/trigger-inserts.js
1
'use strict';
2

3
const _ = require('lodash');
1✔
4
const { expect } = require('chai');
1✔
5
const { TEST_TIMESTAMP } = require('../../util/constants');
1✔
6
const {
7
  isMssql,
8
  isRedshift,
9
  isPostgreSQL,
10
  isSQLite,
11
  isOracle,
12
  isMysql,
13
  isPgBased,
14
} = require('../../util/db-helpers');
1✔
15
const {
16
  dropTables,
17
  createTestTableTwo,
18
  createAccounts,
19
} = require('../../util/tableCreatorHelper');
1✔
20

21
module.exports = function (knex) {
1✔
22
  describe('Insert with Triggers', function () {
7✔
23
    // Trigger options
24
    const insertTriggerOptions = { includeTriggerModifications: true };
7✔
25

26
    beforeEach(async () => {
7✔
27
      await dropTables(knex);
21✔
28
      await createTestTableTwo(knex, false);
21✔
29
      await createAccounts(knex);
21✔
30
    });
31

32
    afterEach(async () => {
7✔
33
      // ToDo can remove after other tests are migrated
34
      // await dropTables(knex)
35
    });
36

37
    before(function () {
7✔
38
      if (!isMssql(knex)) {
7✔
39
        this.skip('This test is MSSQL only');
6✔
40
      }
41
    });
42

43
    describe('Trigger Specific Tests', function () {
7✔
44
      // Reused variables
45
      // Table Names
46
      const primaryTable = 'test_return_with_trigger_primary';
7✔
47
      const secondaryTable = 'test_return_with_trigger_secondary';
7✔
48

49
      // Foreign Key Column Names
50
      const primaryLink = 'foreign_id';
7✔
51
      const secondaryLink = 'looping_id';
7✔
52

53
      // Trigger Name
54
      const triggerName = 'tr_test_insert_with_trigger';
7✔
55

56
      // Create proper environment for tests
57
      before(async function () {
7✔
58
        if (!isMssql(knex)) {
1!
59
          this.skip('This test is MSSQL only');
×
60
        }
61

62
        await knex.schema.hasTable('users').then(async function () {
1✔
63
          await knex.schema.dropTableIfExists(primaryTable);
1✔
64
          await knex.schema.dropTableIfExists(secondaryTable);
1✔
65

66
          // Create tables
67
          await knex.schema.createTable(primaryTable, function (table) {
1✔
68
            table.increments().primary();
1✔
69
            table.string('data').defaultsTo('');
1✔
70
            table.integer(primaryLink).nullable();
1✔
71
          });
72

73
          await knex.schema.createTable(secondaryTable, function (table) {
1✔
74
            table.increments().primary();
1✔
75
            table.string('data').defaultsTo('');
1✔
76
            table.integer(secondaryLink).nullable();
1✔
77
          });
78

79
          await knex.raw(`
1✔
80
                    CREATE TRIGGER [${triggerName}] ON [${secondaryTable}]
81
                    AFTER INSERT
82
                    AS
83
                    BEGIN
84
                        SET NOCOUNT ON;
85

86
                        BEGIN
87
                            update pt
88
                            set pt.${primaryLink} = i.id
89
                            from Inserted as i
90
                            inner join ${primaryTable} as pt
91
                                on pt.id = i.${secondaryLink}
92
                        END
93
                    END
94
                `);
95
        });
96
      });
97

98
      // Clean-up test specific tables
99
      after(async function () {
7✔
100
        if (!isMssql(knex)) {
1!
101
          return;
×
102
        }
103

104
        // Drop table (Trigger is removed with table)
105
        await knex.schema.dropTable(primaryTable);
1✔
106
        await knex.schema.dropTable(secondaryTable);
1✔
107
      });
108

109
      // Reset tables for each test
110
      beforeEach(async function () {
7✔
111
        // "Truncate" tables instead of recreate for each test for speed gains
112
        await knex.raw(`
2✔
113
                delete from ${primaryTable} dbcc checkident('${primaryTable}', reseed, 0);
114
                delete from ${secondaryTable} dbcc checkident('${secondaryTable}', reseed, 0);
115
            `);
116
      });
117

118
      it('#4152 Should allow returns with inserts on tables with triggers', async function () {
7✔
119
        let reachedEnd = false;
1✔
120

121
        await knex.transaction(async function () {
1✔
122
          let insertResults;
123

124
          async function insertWithReturn() {
125
            const insertPrimary = {
1✔
126
              data: 'Testing Data',
127
            };
128

129
            const insertSecondary = {
1✔
130
              data: 'Test Linking',
131
            };
132

133
            const primaryId = await knex(primaryTable).insert(
1✔
134
              [insertPrimary],
135
              ['id'],
136
              insertTriggerOptions
137
            );
138
            insertSecondary[secondaryLink] = primaryId[0].id;
1✔
139

140
            // Test retrieve with trigger
141
            insertResults = (
1✔
142
              await knex(secondaryTable).insert(
143
                [insertSecondary],
144
                ['id'],
145
                insertTriggerOptions
146
              )
147
            )[0].id;
148
          }
149

150
          await insertWithReturn();
1✔
151

152
          expect(Number.parseInt(insertResults)).to.be.finite;
1✔
153

154
          reachedEnd = true;
1✔
155
        });
156

157
        expect(reachedEnd).to.be.true;
1✔
158
      });
159

160
      it('#4152 Should allow returns with inserts on tables with triggers using returning function', async function () {
7✔
161
        let reachedEnd = false;
1✔
162

163
        await knex.transaction(async function () {
1✔
164
          let insertResults;
165

166
          async function insertWithReturn() {
167
            const insertPrimary = {
1✔
168
              data: 'Testing Data',
169
            };
170

171
            const insertSecondary = {
1✔
172
              data: 'Test Linking',
173
            };
174

175
            const primaryId = await knex(primaryTable)
1✔
176
              .returning(['id'], insertTriggerOptions)
177
              .insert([insertPrimary]);
178
            insertSecondary[secondaryLink] = primaryId[0].id;
1✔
179

180
            // Test retrieve with trigger
181
            insertResults = (
1✔
182
              await knex(secondaryTable)
183
                .returning(['id'], insertTriggerOptions)
184
                .insert([insertSecondary])
185
            )[0].id;
186
          }
187

188
          await insertWithReturn();
1✔
189

190
          expect(Number.parseInt(insertResults)).to.be.finite;
1✔
191

192
          reachedEnd = true;
1✔
193
        });
194

195
        expect(reachedEnd).to.be.true;
1✔
196
      });
197
    });
198

199
    describe('Re-test all Insert Functions with trigger option and returns', function () {
7✔
200
      before(async function () {
7✔
201
        if (!isMssql(knex)) {
1!
202
          this.skip('This test is MSSQL only');
×
203
        }
204
      });
205

206
      it('should handle simple inserts', function () {
7✔
207
        return knex('accounts')
1✔
208
          .insert(
209
            {
210
              first_name: 'Test',
211
              last_name: 'User',
212
              email: 'test1@example.com',
213
              logins: 1,
214
              about: 'Lorem ipsum Dolore labore incididunt enim.',
215
              created_at: TEST_TIMESTAMP,
216
              updated_at: TEST_TIMESTAMP,
217
            },
218
            'id',
219
            insertTriggerOptions
220
          )
221
          .testSql(function (tester) {
222
            tester(
1✔
223
              'mysql',
224
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
225
              [
226
                'Lorem ipsum Dolore labore incididunt enim.',
227
                TEST_TIMESTAMP,
228
                'test1@example.com',
229
                'Test',
230
                'User',
231
                1,
232
                TEST_TIMESTAMP,
233
              ],
234
              [1]
235
            );
236
            tester(
1✔
237
              'pg',
238
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id"',
239
              [
240
                'Lorem ipsum Dolore labore incididunt enim.',
241
                TEST_TIMESTAMP,
242
                'test1@example.com',
243
                'Test',
244
                'User',
245
                1,
246
                TEST_TIMESTAMP,
247
              ],
248
              [{ id: 1 }]
249
            );
250
            tester(
1✔
251
              'pg-redshift',
252
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?)',
253
              [
254
                'Lorem ipsum Dolore labore incididunt enim.',
255
                TEST_TIMESTAMP,
256
                'test1@example.com',
257
                'Test',
258
                'User',
259
                1,
260
                TEST_TIMESTAMP,
261
              ],
262
              1
263
            );
264
            tester(
1✔
265
              'sqlite3',
266
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
267
              [
268
                'Lorem ipsum Dolore labore incididunt enim.',
269
                TEST_TIMESTAMP,
270
                'test1@example.com',
271
                'Test',
272
                'User',
273
                1,
274
                TEST_TIMESTAMP,
275
              ],
276
              [1]
277
            );
278
            tester(
1✔
279
              'oracledb',
280
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id" into ?',
281
              [
282
                'Lorem ipsum Dolore labore incididunt enim.',
283
                TEST_TIMESTAMP,
284
                'test1@example.com',
285
                'Test',
286
                'User',
287
                1,
288
                TEST_TIMESTAMP,
289
                function (v) {
290
                  return v.toString() === '[object ReturningHelper:id]';
×
291
                },
292
              ],
293
              [{ id: 1 }]
294
            );
295
            tester(
1✔
296
              'mssql',
297
              'select top(0) [t].[id] into #out from [accounts] as t left join [accounts] on 0=1;insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] into #out values (?, ?, ?, ?, ?, ?, ?); select [id] from #out; drop table #out;',
298
              [
299
                'Lorem ipsum Dolore labore incididunt enim.',
300
                TEST_TIMESTAMP,
301
                'test1@example.com',
302
                'Test',
303
                'User',
304
                1,
305
                TEST_TIMESTAMP,
306
              ],
307
              [{ id: '1' }]
308
            );
309
          });
310
      });
311

312
      it('should handle multi inserts', function () {
7✔
313
        return knex('accounts')
1✔
314
          .insert(
315
            [
316
              {
317
                first_name: 'Test',
318
                last_name: 'User',
319
                email: 'test2@example.com',
320
                logins: 1,
321
                about: 'Lorem ipsum Dolore labore incididunt enim.',
322
                created_at: TEST_TIMESTAMP,
323
                updated_at: TEST_TIMESTAMP,
324
              },
325
              {
326
                first_name: 'Test',
327
                last_name: 'User',
328
                email: 'test3@example.com',
329
                about: 'Lorem ipsum Dolore labore incididunt enim.',
330
                logins: 2,
331
                created_at: TEST_TIMESTAMP,
332
                updated_at: TEST_TIMESTAMP,
333
              },
334
            ],
335
            'id',
336
            insertTriggerOptions
337
          )
338
          .testSql(function (tester) {
339
            tester(
1✔
340
              'mysql',
341
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
342
              [
343
                'Lorem ipsum Dolore labore incididunt enim.',
344
                TEST_TIMESTAMP,
345
                'test2@example.com',
346
                'Test',
347
                'User',
348
                1,
349
                TEST_TIMESTAMP,
350
                'Lorem ipsum Dolore labore incididunt enim.',
351
                TEST_TIMESTAMP,
352
                'test3@example.com',
353
                'Test',
354
                'User',
355
                2,
356
                TEST_TIMESTAMP,
357
              ],
358
              [2]
359
            );
360
            tester(
1✔
361
              'pg',
362
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?) returning "id"',
363
              [
364
                'Lorem ipsum Dolore labore incididunt enim.',
365
                TEST_TIMESTAMP,
366
                'test2@example.com',
367
                'Test',
368
                'User',
369
                1,
370
                TEST_TIMESTAMP,
371
                'Lorem ipsum Dolore labore incididunt enim.',
372
                TEST_TIMESTAMP,
373
                'test3@example.com',
374
                'Test',
375
                'User',
376
                2,
377
                TEST_TIMESTAMP,
378
              ],
379
              ['1', '2']
380
            );
381
            tester(
1✔
382
              'pg-redshift',
383
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
384
              [
385
                'Lorem ipsum Dolore labore incididunt enim.',
386
                TEST_TIMESTAMP,
387
                'test2@example.com',
388
                'Test',
389
                'User',
390
                1,
391
                TEST_TIMESTAMP,
392
                'Lorem ipsum Dolore labore incididunt enim.',
393
                TEST_TIMESTAMP,
394
                'test3@example.com',
395
                'Test',
396
                'User',
397
                2,
398
                TEST_TIMESTAMP,
399
              ],
400
              2
401
            );
402
            tester(
1✔
403
              'sqlite3',
404
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) select ? as `about`, ? as `created_at`, ? as `email`, ? as `first_name`, ? as `last_name`, ? as `logins`, ? as `updated_at` union all select ? as `about`, ? as `created_at`, ? as `email`, ? as `first_name`, ? as `last_name`, ? as `logins`, ? as `updated_at`',
405
              [
406
                'Lorem ipsum Dolore labore incididunt enim.',
407
                TEST_TIMESTAMP,
408
                'test2@example.com',
409
                'Test',
410
                'User',
411
                1,
412
                TEST_TIMESTAMP,
413
                'Lorem ipsum Dolore labore incididunt enim.',
414
                TEST_TIMESTAMP,
415
                'test3@example.com',
416
                'Test',
417
                'User',
418
                2,
419
                TEST_TIMESTAMP,
420
              ],
421
              [3]
422
            );
423
            tester(
1✔
424
              'oracledb',
425
              'begin execute immediate \'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (:1, :2, :3, :4, :5, :6, :7) returning "id" into :8\' using ?, ?, ?, ?, ?, ?, ?, out ?; execute immediate \'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (:1, :2, :3, :4, :5, :6, :7) returning "id" into :8\' using ?, ?, ?, ?, ?, ?, ?, out ?;end;',
426
              [
427
                'Lorem ipsum Dolore labore incididunt enim.',
428
                TEST_TIMESTAMP,
429
                'test2@example.com',
430
                'Test',
431
                'User',
432
                1,
433
                TEST_TIMESTAMP,
434
                function (v) {
435
                  return v.toString() === '[object ReturningHelper:id]';
×
436
                },
437
                'Lorem ipsum Dolore labore incididunt enim.',
438
                TEST_TIMESTAMP,
439
                'test3@example.com',
440
                'Test',
441
                'User',
442
                2,
443
                TEST_TIMESTAMP,
444
                function (v) {
445
                  return v.toString() === '[object ReturningHelper:id]';
×
446
                },
447
              ],
448
              ['1', '2']
449
            );
450
            tester(
1✔
451
              'mssql',
452
              'select top(0) [t].[id] into #out from [accounts] as t left join [accounts] on 0=1;insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] into #out values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?); select [id] from #out; drop table #out;',
453
              [
454
                'Lorem ipsum Dolore labore incididunt enim.',
455
                TEST_TIMESTAMP,
456
                'test2@example.com',
457
                'Test',
458
                'User',
459
                1,
460
                TEST_TIMESTAMP,
461
                'Lorem ipsum Dolore labore incididunt enim.',
462
                TEST_TIMESTAMP,
463
                'test3@example.com',
464
                'Test',
465
                'User',
466
                2,
467
                TEST_TIMESTAMP,
468
              ],
469
              [{ id: '1' }, { id: '2' }]
470
            );
471
          });
472
      });
473

474
      it('should allow for using the `asCallback` interface', function (ok) {
7✔
475
        knex('test_table_two')
1✔
476
          .insert(
477
            [
478
              {
479
                account_id: 1,
480
                details:
481
                  'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
482
                status: 0,
483
              },
484
              {
485
                account_id: 2,
486
                details:
487
                  'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
488
                status: 1,
489
              },
490
              {
491
                account_id: 3,
492
                details: '',
493
                status: 1,
494
              },
495
            ],
496
            'id',
497
            insertTriggerOptions
498
          )
499
          .testSql(function (tester) {
500
            tester(
1✔
501
              'oracledb',
502
              'begin execute immediate \'insert into "test_table_two" ("account_id", "details", "status") values (:1, :2, :3) returning "id" into :4\' using ?, ?, ?, out ?; execute immediate \'insert into "test_table_two" ("account_id", "details", "status") values (:1, :2, :3) returning "id" into :4\' using ?, ?, ?, out ?; execute immediate \'insert into "test_table_two" ("account_id", "details", "status") values (:1, :2, :3) returning "id" into :4\' using ?, ?, ?, out ?;end;',
503
              [
504
                1,
505
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
506
                0,
507
                function (v) {
508
                  return v.toString() === '[object ReturningHelper:id]';
×
509
                },
510
                2,
511
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
512
                1,
513
                function (v) {
514
                  return v.toString() === '[object ReturningHelper:id]';
×
515
                },
516
                3,
517
                '',
518
                1,
519
                function (v) {
520
                  return v.toString() === '[object ReturningHelper:id]';
×
521
                },
522
              ],
523
              ['1', '2', '3']
524
            );
525
          })
526
          .asCallback(function (err) {
527
            if (err) return ok(err);
1!
528
            ok();
1✔
529
          });
530
      });
531

532
      it('should take hashes passed into insert and keep them in the correct order', function () {
7✔
533
        return knex('accounts')
1✔
534
          .insert(
535
            [
536
              {
537
                first_name: 'Test',
538
                last_name: 'User',
539
                email: 'test4@example.com',
540
                about: 'Lorem ipsum Dolore labore incididunt enim.',
541
                logins: 2,
542
                created_at: TEST_TIMESTAMP,
543
                updated_at: TEST_TIMESTAMP,
544
              },
545
              {
546
                first_name: 'Test',
547
                about: 'Lorem ipsum Dolore labore incididunt enim.',
548
                logins: 2,
549
                created_at: TEST_TIMESTAMP,
550
                updated_at: TEST_TIMESTAMP,
551
                last_name: 'User',
552
                email: 'test5@example.com',
553
              },
554
            ],
555
            'id',
556
            insertTriggerOptions
557
          )
558
          .testSql(function (tester) {
559
            tester(
1✔
560
              'mysql',
561
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
562
              [
563
                'Lorem ipsum Dolore labore incididunt enim.',
564
                TEST_TIMESTAMP,
565
                'test4@example.com',
566
                'Test',
567
                'User',
568
                2,
569
                TEST_TIMESTAMP,
570
                'Lorem ipsum Dolore labore incididunt enim.',
571
                TEST_TIMESTAMP,
572
                'test5@example.com',
573
                'Test',
574
                'User',
575
                2,
576
                TEST_TIMESTAMP,
577
              ],
578
              [4]
579
            );
580
            tester(
1✔
581
              'pg',
582
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?) returning "id"',
583
              [
584
                'Lorem ipsum Dolore labore incididunt enim.',
585
                TEST_TIMESTAMP,
586
                'test4@example.com',
587
                'Test',
588
                'User',
589
                2,
590
                TEST_TIMESTAMP,
591
                'Lorem ipsum Dolore labore incididunt enim.',
592
                TEST_TIMESTAMP,
593
                'test5@example.com',
594
                'Test',
595
                'User',
596
                2,
597
                TEST_TIMESTAMP,
598
              ],
599
              ['1', '2']
600
            );
601
            tester(
1✔
602
              'pg-redshift',
603
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
604
              [
605
                'Lorem ipsum Dolore labore incididunt enim.',
606
                TEST_TIMESTAMP,
607
                'test4@example.com',
608
                'Test',
609
                'User',
610
                2,
611
                TEST_TIMESTAMP,
612
                'Lorem ipsum Dolore labore incididunt enim.',
613
                TEST_TIMESTAMP,
614
                'test5@example.com',
615
                'Test',
616
                'User',
617
                2,
618
                TEST_TIMESTAMP,
619
              ],
620
              2
621
            );
622
            tester(
1✔
623
              'sqlite3',
624
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) select ? as `about`, ? as `created_at`, ? as `email`, ? as `first_name`, ? as `last_name`, ? as `logins`, ? as `updated_at` union all select ? as `about`, ? as `created_at`, ? as `email`, ? as `first_name`, ? as `last_name`, ? as `logins`, ? as `updated_at`',
625
              [
626
                'Lorem ipsum Dolore labore incididunt enim.',
627
                TEST_TIMESTAMP,
628
                'test4@example.com',
629
                'Test',
630
                'User',
631
                2,
632
                TEST_TIMESTAMP,
633
                'Lorem ipsum Dolore labore incididunt enim.',
634
                TEST_TIMESTAMP,
635
                'test5@example.com',
636
                'Test',
637
                'User',
638
                2,
639
                TEST_TIMESTAMP,
640
              ],
641
              [5]
642
            );
643
            tester(
1✔
644
              'oracledb',
645
              'begin execute immediate \'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (:1, :2, :3, :4, :5, :6, :7) returning "id" into :8\' using ?, ?, ?, ?, ?, ?, ?, out ?; execute immediate \'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (:1, :2, :3, :4, :5, :6, :7) returning "id" into :8\' using ?, ?, ?, ?, ?, ?, ?, out ?;end;',
646
              [
647
                'Lorem ipsum Dolore labore incididunt enim.',
648
                TEST_TIMESTAMP,
649
                'test4@example.com',
650
                'Test',
651
                'User',
652
                2,
653
                TEST_TIMESTAMP,
654
                function (v) {
655
                  return v.toString() === '[object ReturningHelper:id]';
×
656
                },
657
                'Lorem ipsum Dolore labore incididunt enim.',
658
                TEST_TIMESTAMP,
659
                'test5@example.com',
660
                'Test',
661
                'User',
662
                2,
663
                TEST_TIMESTAMP,
664
                function (v) {
665
                  return v.toString() === '[object ReturningHelper:id]';
×
666
                },
667
              ],
668
              ['1', '2']
669
            );
670
            tester(
1✔
671
              'mssql',
672
              'select top(0) [t].[id] into #out from [accounts] as t left join [accounts] on 0=1;insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] into #out values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?); select [id] from #out; drop table #out;',
673
              [
674
                'Lorem ipsum Dolore labore incididunt enim.',
675
                TEST_TIMESTAMP,
676
                'test4@example.com',
677
                'Test',
678
                'User',
679
                2,
680
                TEST_TIMESTAMP,
681
                'Lorem ipsum Dolore labore incididunt enim.',
682
                TEST_TIMESTAMP,
683
                'test5@example.com',
684
                'Test',
685
                'User',
686
                2,
687
                TEST_TIMESTAMP,
688
              ],
689
              [{ id: '1' }, { id: '2' }]
690
            );
691
          });
692
      });
693

694
      it('will fail when multiple inserts are made into a unique column', async function () {
7✔
695
        if (isRedshift(knex)) {
1!
696
          return this.skip();
×
697
        }
698

699
        await knex('accounts').insert(
1✔
700
          {
701
            first_name: 'Test',
702
            last_name: 'User',
703
            email: 'test5@example.com',
704
            about: 'Lorem ipsum Dolore labore incididunt enim.',
705
            logins: 2,
706
            created_at: TEST_TIMESTAMP,
707
            updated_at: TEST_TIMESTAMP,
708
          },
709
          'id',
710
          insertTriggerOptions
711
        );
712

713
        await knex('accounts')
1✔
714
          .where('id', '>', 1)
715
          .orWhere('x', 2)
716
          .insert(
717
            {
718
              first_name: 'Test',
719
              last_name: 'User',
720
              email: 'test5@example.com',
721
              about: 'Lorem ipsum Dolore labore incididunt enim.',
722
              logins: 2,
723
              created_at: TEST_TIMESTAMP,
724
              updated_at: TEST_TIMESTAMP,
725
            },
726
            'id',
727
            insertTriggerOptions
728
          )
729
          .testSql(function (tester) {
730
            tester(
1✔
731
              'mysql',
732
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
733
              [
734
                'Lorem ipsum Dolore labore incididunt enim.',
735
                TEST_TIMESTAMP,
736
                'test5@example.com',
737
                'Test',
738
                'User',
739
                2,
740
                TEST_TIMESTAMP,
741
              ]
742
            );
743
            tester(
1✔
744
              'pg',
745
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id"',
746
              [
747
                'Lorem ipsum Dolore labore incididunt enim.',
748
                TEST_TIMESTAMP,
749
                'test5@example.com',
750
                'Test',
751
                'User',
752
                2,
753
                TEST_TIMESTAMP,
754
              ]
755
            );
756
            tester(
1✔
757
              'sqlite3',
758
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
759
              [
760
                'Lorem ipsum Dolore labore incididunt enim.',
761
                TEST_TIMESTAMP,
762
                'test5@example.com',
763
                'Test',
764
                'User',
765
                2,
766
                TEST_TIMESTAMP,
767
              ]
768
            );
769
            tester(
1✔
770
              'oracledb',
771
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id" into ?',
772
              [
773
                'Lorem ipsum Dolore labore incididunt enim.',
774
                TEST_TIMESTAMP,
775
                'test5@example.com',
776
                'Test',
777
                'User',
778
                2,
779
                TEST_TIMESTAMP,
780
                function (v) {
781
                  return v.toString() === '[object ReturningHelper:id]';
×
782
                },
783
              ]
784
            );
785
            tester(
1✔
786
              'mssql',
787
              'select top(0) [t].[id] into #out from [accounts] as t left join [accounts] on 0=1;insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] into #out values (?, ?, ?, ?, ?, ?, ?); select [id] from #out; drop table #out;',
788
              [
789
                'Lorem ipsum Dolore labore incididunt enim.',
790
                TEST_TIMESTAMP,
791
                'test5@example.com',
792
                'Test',
793
                'User',
794
                2,
795
                TEST_TIMESTAMP,
796
              ]
797
            );
798
          })
799
          .then(
800
            function () {
801
              throw new Error(
×
802
                'There should be a fail when multi-insert are made in unique col.'
803
              );
804
            },
805
            function () {}
806
          );
807
      });
808

809
      it('should drop any where clause bindings', function () {
7✔
810
        return knex('accounts')
1✔
811
          .where('id', '>', 1)
812
          .orWhere('x', 2)
813
          .insert(
814
            {
815
              first_name: 'Test',
816
              last_name: 'User',
817
              email: 'test6@example.com',
818
              about: 'Lorem ipsum Dolore labore incididunt enim.',
819
              logins: 2,
820
              created_at: TEST_TIMESTAMP,
821
              updated_at: TEST_TIMESTAMP,
822
            },
823
            'id',
824
            insertTriggerOptions
825
          )
826
          .testSql(function (tester) {
827
            tester(
1✔
828
              'mysql',
829
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
830
              [
831
                'Lorem ipsum Dolore labore incididunt enim.',
832
                TEST_TIMESTAMP,
833
                'test6@example.com',
834
                'Test',
835
                'User',
836
                2,
837
                TEST_TIMESTAMP,
838
              ],
839
              [1]
840
            );
841
            tester(
1✔
842
              'pg',
843
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id"',
844
              [
845
                'Lorem ipsum Dolore labore incididunt enim.',
846
                TEST_TIMESTAMP,
847
                'test6@example.com',
848
                'Test',
849
                'User',
850
                2,
851
                TEST_TIMESTAMP,
852
              ],
853
              [{ id: '1' }]
854
            );
855
            tester(
1✔
856
              'pg-redshift',
857
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?)',
858
              [
859
                'Lorem ipsum Dolore labore incididunt enim.',
860
                TEST_TIMESTAMP,
861
                'test6@example.com',
862
                'Test',
863
                'User',
864
                2,
865
                TEST_TIMESTAMP,
866
              ],
867
              1
868
            );
869
            tester(
1✔
870
              'sqlite3',
871
              'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
872
              [
873
                'Lorem ipsum Dolore labore incididunt enim.',
874
                TEST_TIMESTAMP,
875
                'test6@example.com',
876
                'Test',
877
                'User',
878
                2,
879
                TEST_TIMESTAMP,
880
              ],
881
              [6]
882
            );
883
            tester(
1✔
884
              'oracledb',
885
              'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id" into ?',
886
              [
887
                'Lorem ipsum Dolore labore incididunt enim.',
888
                TEST_TIMESTAMP,
889
                'test6@example.com',
890
                'Test',
891
                'User',
892
                2,
893
                TEST_TIMESTAMP,
894
                function (v) {
895
                  return v.toString() === '[object ReturningHelper:id]';
×
896
                },
897
              ],
898
              ['1']
899
            );
900
            tester(
1✔
901
              'mssql',
902
              'select top(0) [t].[id] into #out from [accounts] as t left join [accounts] on 0=1;insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] into #out values (?, ?, ?, ?, ?, ?, ?); select [id] from #out; drop table #out;',
903
              [
904
                'Lorem ipsum Dolore labore incididunt enim.',
905
                TEST_TIMESTAMP,
906
                'test6@example.com',
907
                'Test',
908
                'User',
909
                2,
910
                TEST_TIMESTAMP,
911
              ],
912
              [{ id: '1' }]
913
            );
914
          });
915
      });
916

917
      it('should handle empty inserts', async function () {
7✔
918
        await knex.schema.dropTableIfExists('trigger_retest_insert');
1✔
919
        return await knex.schema
1✔
920
          .createTable('trigger_retest_insert', function (qb) {
921
            qb.increments().primary();
1✔
922
            qb.string('string').defaultTo('hello');
1✔
923
            qb.tinyint('tinyint').defaultTo(0);
1✔
924
            qb.text('text').nullable();
1✔
925
          })
926
          .then(function () {
927
            knex('trigger_retest_insert')
1✔
928
              .insert({}, 'id', insertTriggerOptions)
929
              .testSql(function (tester) {
930
                tester(
1✔
931
                  'mysql',
932
                  'insert into `trigger_retest_insert` () values ()',
933
                  [],
934
                  [1]
935
                );
936
                tester(
1✔
937
                  'pg',
938
                  'insert into "trigger_retest_insert" default values returning "id"',
939
                  [],
940
                  [{ id: 1 }]
941
                );
942
                tester(
1✔
943
                  'pg-redshift',
944
                  'insert into "trigger_retest_insert" default values',
945
                  [],
946
                  1
947
                );
948
                tester(
1✔
949
                  'sqlite3',
950
                  'insert into `trigger_retest_insert` default values',
951
                  [],
952
                  [1]
953
                );
954
                tester(
1✔
955
                  'oracledb',
956
                  'insert into "trigger_retest_insert" ("id") values (default) returning "id" into ?',
957
                  [
958
                    function (v) {
959
                      return v.toString() === '[object ReturningHelper:id]';
×
960
                    },
961
                  ],
962
                  [{ id: 1 }]
963
                );
964
                tester(
1✔
965
                  'mssql',
966
                  'select top(0) [t].[id] into #out from [trigger_retest_insert] as t left join [trigger_retest_insert] on 0=1;insert into [trigger_retest_insert] output inserted.[id] into #out default values; select [id] from #out; drop table #out;',
967
                  [],
968
                  [{ id: 1 }]
969
                );
970
              });
971
          });
972
      });
973

974
      it('should handle empty arrays inserts', async function () {
7✔
975
        await knex.schema.dropTableIfExists('trigger_retest_insert2');
1✔
976
        return await knex.schema
1✔
977
          .createTable('trigger_retest_insert2', function (qb) {
978
            qb.increments().primary();
1✔
979
            qb.string('string').defaultTo('hello');
1✔
980
            qb.tinyint('tinyint').defaultTo(0);
1✔
981
            qb.text('text').nullable();
1✔
982
          })
983
          .then(function () {
984
            knex('trigger_retest_insert2')
1✔
985
              .insert([{}], 'id', insertTriggerOptions)
986
              .testSql(function (tester) {
987
                tester(
1✔
988
                  'mysql',
989
                  'insert into `trigger_retest_insert2` () values ()',
990
                  [],
991
                  [1]
992
                );
993
                tester(
1✔
994
                  'pg',
995
                  'insert into "trigger_retest_insert2" default values returning "id"',
996
                  [],
997
                  [{ id: '1' }]
998
                );
999
                tester(
1✔
1000
                  'pg-redshift',
1001
                  'insert into "trigger_retest_insert2" default values',
1002
                  [],
1003
                  1
1004
                );
1005
                tester(
1✔
1006
                  'sqlite3',
1007
                  'insert into `trigger_retest_insert2` default values',
1008
                  [],
1009
                  [1]
1010
                );
1011
                tester(
1✔
1012
                  'oracledb',
1013
                  'insert into "trigger_retest_insert2" ("id") values (default) returning "id" into ?',
1014
                  [
1015
                    function (v) {
1016
                      return v.toString() === '[object ReturningHelper:id]';
×
1017
                    },
1018
                  ],
1019
                  ['1']
1020
                );
1021
                tester(
1✔
1022
                  'mssql',
1023
                  'select top(0) [t].[id] into #out from [trigger_retest_insert2] as t left join [trigger_retest_insert2] on 0=1;insert into [trigger_retest_insert2] output inserted.[id] into #out default values; select [id] from #out; drop table #out;',
1024
                  [],
1025
                  [{ id: '1' }]
1026
                );
1027
              });
1028
          });
1029
      });
1030

1031
      it('should take an array of columns to return in oracle or postgres', function () {
7✔
1032
        const insertData = {
1✔
1033
          account_id: 10,
1034
          details:
1035
            'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1036
          status: 0,
1037
        };
1038
        return knex('test_table_two')
1✔
1039
          .insert(insertData, ['account_id', 'details'], insertTriggerOptions)
1040
          .testSql(function (tester) {
1041
            tester(
1✔
1042
              'mysql',
1043
              'insert into `test_table_two` (`account_id`, `details`, `status`) values (?, ?, ?)',
1044
              [
1045
                10,
1046
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1047
                0,
1048
              ],
1049
              [1]
1050
            );
1051
            tester(
1✔
1052
              'pg',
1053
              'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning "account_id", "details"',
1054
              [
1055
                10,
1056
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1057
                0,
1058
              ],
1059
              [
1060
                {
1061
                  account_id: 10,
1062
                  details:
1063
                    'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1064
                },
1065
              ]
1066
            );
1067
            tester(
1✔
1068
              'pg-redshift',
1069
              'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?)',
1070
              [
1071
                10,
1072
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1073
                0,
1074
              ],
1075
              1
1076
            );
1077
            tester(
1✔
1078
              'sqlite3',
1079
              'insert into `test_table_two` (`account_id`, `details`, `status`) values (?, ?, ?)',
1080
              [
1081
                10,
1082
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1083
                0,
1084
              ],
1085
              [1]
1086
            );
1087
            tester(
1✔
1088
              'oracledb',
1089
              `insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning "account_id","details" into ?,?`,
1090
              [
1091
                10,
1092
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1093
                0,
1094
                function (v) {
1095
                  return v.toString() === '[object ReturningHelper:account_id]';
×
1096
                },
1097
                function (v) {
1098
                  return v.toString() === '[object ReturningHelper:details]';
×
1099
                },
1100
              ],
1101
              [
1102
                {
1103
                  account_id: '10',
1104
                  details:
1105
                    'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1106
                },
1107
              ]
1108
            );
1109
            tester(
1✔
1110
              'mssql',
1111
              'select top(0) [t].[account_id],[t].[details] into #out from [test_table_two] as t left join [test_table_two] on 0=1;insert into [test_table_two] ([account_id], [details], [status]) output inserted.[account_id], inserted.[details] into #out values (?, ?, ?); select [account_id],[details] from #out; drop table #out;',
1112
              [
1113
                10,
1114
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1115
                0,
1116
              ],
1117
              [
1118
                {
1119
                  account_id: 10,
1120
                  details:
1121
                    'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1122
                },
1123
              ]
1124
            );
1125
          })
1126
          .then(function (rows) {
1127
            if (isRedshift(knex)) {
1!
1128
              return expect(rows).to.equal(1);
×
1129
            }
1130
            expect(rows.length).to.equal(1);
1✔
1131
            if (isPostgreSQL(knex)) {
1!
1132
              expect(_.keys(rows[0]).length).to.equal(2);
×
1133
              expect(rows[0].account_id).to.equal(insertData.account_id);
×
1134
              expect(rows[0].details).to.equal(insertData.details);
×
1135
            }
1136
          });
1137
      });
1138

1139
      it('should allow a * for returning in postgres and oracle', function () {
7✔
1140
        if (isRedshift(knex)) {
1!
1141
          return this.skip();
×
1142
        }
1143
        const insertData = {
1✔
1144
          account_id: 10,
1145
          details:
1146
            'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1147
          status: 0,
1148
        };
1149

1150
        const returningColumn = '*';
1✔
1151
        return knex('test_table_two')
1✔
1152
          .insert(insertData, returningColumn, insertTriggerOptions)
1153
          .testSql(function (tester) {
1154
            tester(
1✔
1155
              'pg',
1156
              'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning *',
1157
              [
1158
                10,
1159
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1160
                0,
1161
              ],
1162
              [
1163
                {
1164
                  id: 1,
1165
                  account_id: 10,
1166
                  details:
1167
                    'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1168
                  status: 0,
1169
                },
1170
              ]
1171
            );
1172
            tester(
1✔
1173
              'oracledb',
1174
              'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning "ROWID" into ?',
1175
              [
1176
                10,
1177
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1178
                0,
1179
                function (v) {
1180
                  return v.toString() === '[object ReturningHelper:ROWID]';
×
1181
                },
1182
              ],
1183
              [
1184
                {
1185
                  id: 1,
1186
                  account_id: 10,
1187
                  details:
1188
                    'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1189
                  status: 0,
1190
                },
1191
              ]
1192
            );
1193
            tester(
1✔
1194
              'mssql',
1195
              'select top(0) [t].* into #out from [test_table_two] as t left join [test_table_two] on 0=1;insert into [test_table_two] ([account_id], [details], [status]) output inserted.* into #out values (?, ?, ?); select * from #out; drop table #out;',
1196
              [
1197
                10,
1198
                'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1199
                0,
1200
              ],
1201
              [
1202
                {
1203
                  id: 1,
1204
                  account_id: 10,
1205
                  details:
1206
                    'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
1207
                  status: 0,
1208
                },
1209
              ]
1210
            );
1211
          })
1212
          .then(function (rows) {
1213
            expect(rows.length).to.equal(1);
1✔
1214
            if (isPgBased(knex)) {
1!
1215
              expect(_.keys(rows[0]).length).to.equal(5);
×
1216
              expect(rows[0].account_id).to.equal(insertData.account_id);
×
1217
              expect(rows[0].details).to.equal(insertData.details);
×
1218
              expect(rows[0].status).to.equal(insertData.status);
×
1219
            }
1220
          });
1221
      });
1222

1223
      it('should replace undefined keys in multi insert with DEFAULT', function () {
7✔
1224
        if (isSQLite(knex)) {
1!
1225
          return true;
×
1226
        }
1227
        return knex('accounts')
1✔
1228
          .insert(
1229
            [
1230
              {
1231
                first_name: 'TestUser',
1232
                last_name: 'First Item',
1233
                phone: '01',
1234
                email: 'single-test1@example.com',
1235
                balance: 1.23,
1236
                about: 'Lorem ipsum Dolore labore incididunt enim.',
1237
                created_at: new Date(),
1238
                updated_at: new Date(),
1239
              },
1240
              {
1241
                last_name: 'Second Item',
1242
                email: 'double-test1@example.com',
1243
                logins: 2,
1244
                created_at: new Date(),
1245
                updated_at: new Date(),
1246
              },
1247
            ],
1248
            '*',
1249
            insertTriggerOptions
1250
          )
1251
          .then(function () {
1252
            return knex('accounts')
1✔
1253
              .whereIn('email', [
1254
                'single-test1@example.com',
1255
                'double-test1@example.com',
1256
              ])
1257
              .orderBy('email', 'desc');
1258
          })
1259
          .then(function (results) {
1260
            expect(results[0].logins).to.equal(1);
1✔
1261
            expect(results[1].about).to.equal(null);
1✔
1262
            // cleanup to prevent needs for too much changes to other tests
1263
            return knex('accounts')
1✔
1264
              .delete()
1265
              .whereIn(
1266
                'id',
1267
                results.map(function (row) {
1268
                  return row.id;
2✔
1269
                })
1270
              );
1271
          });
1272
      });
1273

1274
      it('will silently do nothing when multiple inserts are made into a unique column and ignore is specified', async function () {
7✔
1275
        if (isRedshift(knex)) {
1!
1276
          return this.skip();
×
1277
        }
1278

1279
        // Setup: Create table with unique email column
1280
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1281
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1282
          table.string('name');
1✔
1283
          table.string('email');
1✔
1284
          table.unique('email');
1✔
1285
        });
1286

1287
        // Setup: Create row to conflict against
1288
        await knex('upsert_tests').insert({
1✔
1289
          email: 'ignoretest1@example.com',
1290
          name: 'BEFORE',
1291
        });
1292

1293
        // Test: Insert..ignore with same email as existing row
1294
        try {
1✔
1295
          await knex('upsert_tests')
1✔
1296
            .insert(
1297
              { email: 'ignoretest1@example.com', name: 'AFTER' },
1298
              'email',
1299
              insertTriggerOptions
1300
            )
1301
            .onConflict('email')
1302
            .ignore()
1303
            .testSql(function (tester) {
1304
              tester(
1✔
1305
                'mysql',
1306
                'insert ignore into `upsert_tests` (`email`, `name`) values (?, ?)',
1307
                ['ignoretest1@example.com', 'AFTER']
1308
              );
1309
              tester(
1✔
1310
                'pg',
1311
                'insert into "upsert_tests" ("email", "name") values (?, ?) on conflict ("email") do nothing returning "email"',
1312
                ['ignoretest1@example.com', 'AFTER']
1313
              );
1314
              tester(
1✔
1315
                'sqlite3',
1316
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on conflict (`email`) do nothing',
1317
                ['ignoretest1@example.com', 'AFTER']
1318
              );
1319
            });
1320
        } catch (err) {
1321
          if (isOracle(knex) || isMssql(knex)) {
1!
1322
            expect(err).to.be.an('error');
1✔
1323
            if (err.message.includes('.onConflict() is not supported for'))
1!
1324
              return;
1✔
1325
          }
1326
          throw err;
×
1327
        }
1328

1329
        // Assert: there is still only 1 row, and that it HAS NOT been updated
1330
        const rows = await knex('upsert_tests')
×
1331
          .where({ email: 'ignoretest1@example.com' })
1332
          .select();
1333
        expect(rows.length).to.equal(1);
×
1334
        expect(rows[0].name).to.equal('BEFORE');
×
1335
      });
1336

1337
      it('will silently do nothing when multiple inserts are made into a composite unique column and ignore is specified', async function () {
7✔
1338
        if (isRedshift(knex)) {
1!
1339
          return this.skip();
×
1340
        }
1341

1342
        // Setup: Create table with unique email column
1343
        await knex.schema.dropTableIfExists('upsert_composite_key_tests');
1✔
1344
        await knex.schema.createTable('upsert_composite_key_tests', (table) => {
1✔
1345
          table.string('name');
1✔
1346
          table.string('email');
1✔
1347
          table.string('org');
1✔
1348
          table.unique(['org', 'email']);
1✔
1349
        });
1350

1351
        // Setup: Create row to conflict against
1352
        await knex('upsert_composite_key_tests').insert({
1✔
1353
          org: 'acme-inc',
1354
          email: 'ignoretest1@example.com',
1355
          name: 'BEFORE',
1356
        });
1357

1358
        // Test: Insert..ignore with same email as existing row
1359
        try {
1✔
1360
          await knex('upsert_composite_key_tests')
1✔
1361
            .insert(
1362
              {
1363
                org: 'acme-inc',
1364
                email: 'ignoretest1@example.com',
1365
                name: 'AFTER',
1366
              },
1367
              'email',
1368
              insertTriggerOptions
1369
            )
1370
            .onConflict(['org', 'email'])
1371
            .ignore()
1372
            .testSql(function (tester) {
1373
              tester(
1✔
1374
                'mysql',
1375
                'insert ignore into `upsert_composite_key_tests` (`email`, `name`, `org`) values (?, ?, ?)',
1376
                ['ignoretest1@example.com', 'AFTER', 'acme-inc']
1377
              );
1378
              tester(
1✔
1379
                'pg',
1380
                'insert into "upsert_composite_key_tests" ("email", "name", "org") values (?, ?, ?) on conflict ("org", "email") do nothing returning "email"',
1381
                ['ignoretest1@example.com', 'AFTER', 'acme-inc']
1382
              );
1383
              tester(
1✔
1384
                'sqlite3',
1385
                'insert into `upsert_composite_key_tests` (`email`, `name`, `org`) values (?, ?, ?) on conflict (`org`, `email`) do nothing',
1386
                ['ignoretest1@example.com', 'AFTER', 'acme-inc']
1387
              );
1388
            });
1389
        } catch (err) {
1390
          if (isOracle(knex) || isMssql(knex)) {
1!
1391
            expect(err).to.be.an('error');
1✔
1392
            if (err.message.includes('.onConflict() is not supported for'))
1!
1393
              return;
1✔
1394
          }
1395
          throw err;
×
1396
        }
1397

1398
        // Assert: there is still only 1 row, and that it HAS NOT been updated
1399
        const rows = await knex('upsert_composite_key_tests')
×
1400
          .where({ email: 'ignoretest1@example.com' })
1401
          .select();
1402
        expect(rows.length).to.equal(1);
×
1403
        expect(rows[0].name).to.equal('BEFORE');
×
1404
      });
1405

1406
      it('updates columns when inserting a duplicate key to unique column and merge is specified', async function () {
7✔
1407
        if (isRedshift(knex)) {
1!
1408
          return this.skip();
×
1409
        }
1410

1411
        // Setup: Create table with unique email column
1412
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1413
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1414
          table.string('name');
1✔
1415
          table.string('email');
1✔
1416
          table.unique('email');
1✔
1417
        });
1418

1419
        // Setup: Create row to conflict against
1420
        await knex('upsert_tests').insert({
1✔
1421
          email: 'mergetest1@example.com',
1422
          name: 'BEFORE',
1423
        });
1424

1425
        // Perform insert..merge (upsert)
1426
        try {
1✔
1427
          await knex('upsert_tests')
1✔
1428
            .insert(
1429
              { email: 'mergetest1@example.com', name: 'AFTER' },
1430
              'email',
1431
              insertTriggerOptions
1432
            )
1433
            .onConflict('email')
1434
            .merge()
1435
            .testSql(function (tester) {
1436
              tester(
1✔
1437
                'mysql',
1438
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on duplicate key update `email` = values(`email`), `name` = values(`name`)',
1439
                ['mergetest1@example.com', 'AFTER']
1440
              );
1441
              tester(
1✔
1442
                'pg',
1443
                'insert into "upsert_tests" ("email", "name") values (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name" returning "email"',
1444
                ['mergetest1@example.com', 'AFTER']
1445
              );
1446
              tester(
1✔
1447
                'sqlite3',
1448
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name`',
1449
                ['mergetest1@example.com', 'AFTER']
1450
              );
1451
            });
1452
        } catch (err) {
1453
          if (isOracle(knex) || isMssql(knex)) {
1!
1454
            expect(err).to.be.an('error');
1✔
1455
            if (err.message.includes('.onConflict() is not supported for'))
1!
1456
              return;
1✔
1457
          }
1458
          throw err;
×
1459
        }
1460

1461
        // Check that row HAS been updated
1462
        const rows = await knex('upsert_tests')
×
1463
          .where({ email: 'mergetest1@example.com' })
1464
          .select();
1465
        expect(rows.length).to.equal(1);
×
1466
        expect(rows[0].name).to.equal('AFTER');
×
1467
      });
1468

1469
      it('conditionally updates rows when inserting a duplicate key to unique column and merge with where clause matching row(s) is specified', async function () {
7✔
1470
        if (isRedshift(knex)) {
1!
1471
          return this.skip();
×
1472
        }
1473

1474
        // Setup: Create table with unique email column
1475
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1476
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1477
          table.string('name');
1✔
1478
          table.string('email');
1✔
1479
          table.string('role');
1✔
1480
          table.unique('email');
1✔
1481
        });
1482

1483
        // Setup: Create row to conflict against
1484
        await knex('upsert_tests').insert({
1✔
1485
          email: 'mergetest1@example.com',
1486
          role: 'tester',
1487
          name: 'BEFORE',
1488
        });
1489

1490
        // Perform insert..merge (upsert)
1491
        try {
1✔
1492
          await knex('upsert_tests')
1✔
1493
            .insert(
1494
              { email: 'mergetest1@example.com', name: 'AFTER' },
1495
              'email',
1496
              insertTriggerOptions
1497
            )
1498
            .onConflict('email')
1499
            .merge()
1500
            .where('upsert_tests.role', 'tester')
1501
            .testSql(function (tester) {
1502
              tester(
1✔
1503
                'pg',
1504
                'insert into "upsert_tests" ("email", "name") values (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name" where "upsert_tests"."role" = ? returning "email"',
1505
                ['mergetest1@example.com', 'AFTER', 'tester']
1506
              );
1507
              tester(
1✔
1508
                'sqlite3',
1509
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name` where `upsert_tests`.`role` = ?',
1510
                ['mergetest1@example.com', 'AFTER', 'tester']
1511
              );
1512
            });
1513
        } catch (err) {
1514
          if (isOracle(knex) || isMssql(knex)) {
1!
1515
            expect(err).to.be.an('error');
1✔
1516
            if (err.message.includes('.onConflict() is not supported for'))
1!
1517
              return;
1✔
1518
          }
1519
          if (isMysql(knex)) {
×
1520
            expect(err).to.be.an('error');
×
1521
            if (
×
1522
              err.message.includes(
1523
                '.onConflict().merge().where() is not supported for'
1524
              )
1525
            )
1526
              return;
×
1527
          }
1528
          throw err;
×
1529
        }
1530

1531
        // Check that row HAS been updated
1532
        const rows = await knex('upsert_tests')
×
1533
          .where({ email: 'mergetest1@example.com' })
1534
          .select();
1535
        expect(rows.length).to.equal(1);
×
1536
        expect(rows[0].name).to.equal('AFTER');
×
1537
      });
1538

1539
      it('will silently do nothing when inserting a duplicate key to unique column and merge with where clause matching no rows is specified', async function () {
7✔
1540
        if (isRedshift(knex)) {
1!
1541
          return this.skip();
×
1542
        }
1543

1544
        // Setup: Create table with unique email column
1545
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1546
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1547
          table.string('name');
1✔
1548
          table.string('email');
1✔
1549
          table.string('role');
1✔
1550
          table.unique('email');
1✔
1551
        });
1552

1553
        // Setup: Create row to conflict against
1554
        await knex('upsert_tests').insert({
1✔
1555
          email: 'mergetest1@example.com',
1556
          role: 'tester',
1557
          name: 'BEFORE',
1558
        });
1559

1560
        // Perform insert..merge (upsert)
1561
        try {
1✔
1562
          await knex('upsert_tests')
1✔
1563
            .insert(
1564
              { email: 'mergetest1@example.com', name: 'AFTER' },
1565
              'email',
1566
              insertTriggerOptions
1567
            )
1568
            .onConflict('email')
1569
            .merge()
1570
            .where('upsert_tests.role', 'fake-role')
1571
            .testSql(function (tester) {
1572
              tester(
1✔
1573
                'pg',
1574
                'insert into "upsert_tests" ("email", "name") values (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name" where "upsert_tests"."role" = ? returning "email"',
1575
                ['mergetest1@example.com', 'AFTER', 'fake-role']
1576
              );
1577
              tester(
1✔
1578
                'sqlite3',
1579
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name` where `upsert_tests`.`role` = ?',
1580
                ['mergetest1@example.com', 'AFTER', 'fake-role']
1581
              );
1582
            });
1583
        } catch (err) {
1584
          if (isOracle(knex) || isMssql(knex)) {
1!
1585
            expect(err).to.be.an('error');
1✔
1586
            if (err.message.includes('.onConflict() is not supported for'))
1!
1587
              return;
1✔
1588
          }
1589
          if (isMysql(knex)) {
×
1590
            expect(err).to.be.an('error');
×
1591
            if (
×
1592
              err.message.includes(
1593
                '.onConflict().merge().where() is not supported for'
1594
              )
1595
            )
1596
              return;
×
1597
          }
1598
          throw err;
×
1599
        }
1600

1601
        // Check that row HAS NOT been updated
1602
        const rows = await knex('upsert_tests')
×
1603
          .where({ email: 'mergetest1@example.com' })
1604
          .select();
1605
        expect(rows.length).to.equal(1);
×
1606
        expect(rows[0].name).to.equal('BEFORE');
×
1607
      });
1608

1609
      it('updates columns with raw value when inserting a duplicate key to unique column and merge is specified', async function () {
7✔
1610
        if (isRedshift(knex)) {
1!
NEW
1611
          return this.skip();
×
1612
        }
1613

1614
        // Setup: Create table with unique email column
1615
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1616
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1617
          table.string('name');
1✔
1618
          table.string('email');
1✔
1619
          table.unique('email');
1✔
1620
        });
1621

1622
        // Setup: Create row to conflict against
1623
        await knex('upsert_tests').insert([
1✔
1624
          { email: 'mergesource@example.com', name: 'SOURCE' },
1625
          { email: 'mergedest@example.com', name: 'DEST' },
1626
        ]);
1627

1628
        // Perform insert..merge (upsert)
1629
        try {
1✔
1630
          await knex('upsert_tests')
1✔
1631
            .insert(
1632
              {
1633
                email: 'mergedest@example.com',
1634
                name: knex.raw(
1635
                  "(SELECT name FROM (SELECT * FROM upsert_tests) AS t WHERE email = 'mergesource@example.com')"
1636
                ),
1637
              },
1638
              'email',
1639
              insertTriggerOptions
1640
            )
1641
            .onConflict('email')
1642
            .merge()
1643
            .testSql(function (tester) {
1644
              tester(
1✔
1645
                'mysql',
1646
                "insert into `upsert_tests` (`email`, `name`) values (?, (SELECT name FROM (SELECT * FROM upsert_tests) AS t WHERE email = 'mergesource@example.com')) on duplicate key update `email` = values(`email`), `name` = values(`name`)",
1647
                ['mergedest@example.com']
1648
              );
1649
              tester(
1✔
1650
                'pg',
1651
                'insert into "upsert_tests" ("email", "name") values (?, (SELECT name FROM (SELECT * FROM upsert_tests) AS t WHERE email = \'mergesource@example.com\')) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name" returning "email"',
1652
                ['mergedest@example.com']
1653
              );
1654
              tester(
1✔
1655
                'sqlite3',
1656
                "insert into `upsert_tests` (`email`, `name`) values (?, (SELECT name FROM (SELECT * FROM upsert_tests) AS t WHERE email = 'mergesource@example.com')) on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name`",
1657
                ['mergedest@example.com']
1658
              );
1659
            });
1660
        } catch (err) {
1661
          if (isOracle(knex) || isMssql(knex)) {
1!
1662
            expect(err).to.be.an('error');
1✔
1663
            if (err.message.includes('.onConflict() is not supported for'))
1!
1664
              return;
1✔
1665
          }
NEW
1666
          throw err;
×
1667
        }
1668

1669
        // Check that row HAS been updated
NEW
1670
        const rows = await knex('upsert_tests')
×
1671
          .where({ email: 'mergedest@example.com' })
1672
          .select();
NEW
1673
        expect(rows.length).to.equal(1);
×
NEW
1674
        expect(rows[0].name).to.equal('SOURCE');
×
1675
      });
1676

1677
      it('updates columns with raw value when inserting a duplicate key to unique column and merge with updates is specified', async function () {
7✔
1678
        if (isRedshift(knex)) {
1!
NEW
1679
          return this.skip();
×
1680
        }
1681

1682
        // Setup table for testing knex.raw with
1683
        await knex.schema.dropTableIfExists('upsert_value_source');
1✔
1684
        await knex.schema.createTable('upsert_value_source', (table) => {
1✔
1685
          table.string('name');
1✔
1686
        });
1687
        await knex('upsert_value_source').insert([{ name: 'SOURCE' }]);
1✔
1688

1689
        // Setup: Create table with unique email column
1690
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1691
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1692
          table.string('name');
1✔
1693
          table.string('email');
1✔
1694
          table.unique('email');
1✔
1695
        });
1696

1697
        // Setup: Create row to conflict against
1698
        await knex('upsert_tests').insert([
1✔
1699
          { email: 'mergedest@example.com', name: 'DEST' },
1700
        ]);
1701

1702
        // Perform insert..merge (upsert)
1703
        try {
1✔
1704
          await knex('upsert_tests')
1✔
1705
            .insert(
1706
              { email: 'mergedest@example.com', name: 'SHOULD NOT BE USED' },
1707
              'email',
1708
              insertTriggerOptions
1709
            )
1710
            .onConflict('email')
1711
            .merge({ name: knex.raw('(SELECT name FROM upsert_value_source)') })
1712
            .testSql(function (tester) {
1713
              tester(
1✔
1714
                'mysql',
1715
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on duplicate key update `name` = (SELECT name FROM upsert_value_source)',
1716
                ['mergedest@example.com', 'SHOULD NOT BE USED']
1717
              );
1718
              tester(
1✔
1719
                'pg',
1720
                'insert into "upsert_tests" ("email", "name") values (?, ?) on conflict ("email") do update set "name" = (SELECT name FROM upsert_value_source) returning "email"',
1721
                ['mergedest@example.com', 'SHOULD NOT BE USED']
1722
              );
1723
              tester(
1✔
1724
                'sqlite3',
1725
                'insert into `upsert_tests` (`email`, `name`) values (?, ?) on conflict (`email`) do update set `name` = (SELECT name FROM upsert_value_source)',
1726
                ['mergedest@example.com', 'SHOULD NOT BE USED']
1727
              );
1728
            });
1729
        } catch (err) {
1730
          if (isOracle(knex) || isMssql(knex)) {
1!
1731
            expect(err).to.be.an('error');
1✔
1732
            if (err.message.includes('.onConflict() is not supported for'))
1!
1733
              return;
1✔
1734
          }
NEW
1735
          throw err;
×
1736
        }
1737

1738
        // Check that row HAS been updated
NEW
1739
        const rows = await knex('upsert_tests')
×
1740
          .where({ email: 'mergedest@example.com' })
1741
          .select();
NEW
1742
        expect(rows.length).to.equal(1);
×
NEW
1743
        expect(rows[0].name).to.equal('SOURCE');
×
1744
      });
1745

1746
      it('updates and inserts columns when inserting multiple rows merge is specified', async function () {
7✔
1747
        if (isRedshift(knex)) {
1!
NEW
1748
          return this.skip();
×
1749
        }
1750

1751
        // Setup: Create table with unique email column
1752
        await knex.schema.dropTableIfExists('upsert_tests');
1✔
1753
        await knex.schema.createTable('upsert_tests', (table) => {
1✔
1754
          table.string('name');
1✔
1755
          table.string('email');
1✔
1756
          table.unique('email');
1✔
1757
        });
1758

1759
        // Setup: Create row to conflict against
1760
        await knex('upsert_tests').insert([
1✔
1761
          { email: 'one@example.com', name: 'BEFORE' },
1762
          { email: 'two@example.com', name: 'BEFORE' },
1763
        ]);
1764

1765
        // Perform insert..merge (upsert)
1766
        try {
1✔
1767
          await knex('upsert_tests')
1✔
1768
            .insert(
1769
              [
1770
                { email: 'two@example.com', name: 'AFTER' },
1771
                { email: 'three@example.com', name: 'AFTER' },
1772
              ],
1773
              'email',
1774
              insertTriggerOptions
1775
            )
1776
            .onConflict('email')
1777
            .merge()
1778
            .testSql(function (tester) {
1779
              tester(
1✔
1780
                'mysql',
1781
                'insert into `upsert_tests` (`email`, `name`) values (?, ?), (?, ?) on duplicate key update `email` = values(`email`), `name` = values(`name`)',
1782
                ['two@example.com', 'AFTER', 'three@example.com', 'AFTER']
1783
              );
1784
              tester(
1✔
1785
                'pg',
1786
                'insert into "upsert_tests" ("email", "name") values (?, ?), (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name" returning "email"',
1787
                ['two@example.com', 'AFTER', 'three@example.com', 'AFTER']
1788
              );
1789
              tester(
1✔
1790
                'sqlite3',
1791
                'insert into `upsert_tests` (`email`, `name`) select ? as `email`, ? as `name` union all select ? as `email`, ? as `name` where true on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name`',
1792
                ['two@example.com', 'AFTER', 'three@example.com', 'AFTER']
1793
              );
1794
            });
1795
        } catch (err) {
1796
          if (isOracle(knex) || isMssql(knex)) {
1!
1797
            expect(err).to.be.an('error');
1✔
1798
            if (err.message.includes('.onConflict() is not supported for'))
1!
1799
              return;
1✔
1800
          }
UNCOV
1801
          throw err;
×
1802
        }
1803

1804
        // Check that row HAS been updated
UNCOV
1805
        const rows = await knex('upsert_tests').select();
×
UNCOV
1806
        expect(rows.length).to.equal(3);
×
1807

UNCOV
1808
        const row1 = rows.find((row) => row.email === 'one@example.com');
×
UNCOV
1809
        expect(row1 && row1.name).to.equal('BEFORE');
×
UNCOV
1810
        const row2 = rows.find((row) => row.email === 'two@example.com');
×
UNCOV
1811
        expect(row2 && row2.name).to.equal('AFTER');
×
UNCOV
1812
        const row3 = rows.find((row) => row.email === 'three@example.com');
×
UNCOV
1813
        expect(row3 && row3.name).to.equal('AFTER');
×
1814
      });
1815
    });
1816
  });
1817
};
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc