mirror of
https://github.com/knex/knex.git
synced 2025-07-29 11:50:41 +00:00
d50d00644c
The underlying issue was that query *always* committed, even during a transaction. The previous fix was to just disable commitAsync in a transaction but then that also disabled explicit commit calls. Instead, this fix disables query's commits during transactions so that explicit commits still work.
1310 lines
43 KiB
JavaScript
1310 lines
43 KiB
JavaScript
/*global expect, d*/
|
|
|
|
'use strict';
|
|
|
|
const uuid = require('uuid');
|
|
const _ = require('lodash');
|
|
const bluebird = require('bluebird');
|
|
const sinon = require('sinon');
|
|
|
|
module.exports = function(knex) {
|
|
describe('Inserts', function() {
|
|
it('should handle simple inserts', function() {
|
|
return knex('accounts')
|
|
.insert(
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test@example.com',
|
|
logins: 1,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: d,
|
|
updated_at: d,
|
|
},
|
|
'id'
|
|
)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
],
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id"',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
],
|
|
['1']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
],
|
|
1
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
],
|
|
[1]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id" into ?',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['1']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
],
|
|
['1']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('should handle multi inserts', function() {
|
|
return knex('accounts')
|
|
.insert(
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test2@example.com',
|
|
logins: 1,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: d,
|
|
updated_at: d,
|
|
},
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test3@example.com',
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
logins: 2,
|
|
created_at: d,
|
|
updated_at: d,
|
|
},
|
|
],
|
|
'id'
|
|
)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test2@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test3@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
[2]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?) returning "id"',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test2@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test3@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
['2', '3']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test2@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test3@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
2
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'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`',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test2@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test3@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
[3]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'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;',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test2@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test3@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['2', '3']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test2@example.com',
|
|
'Test',
|
|
'User',
|
|
1,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test3@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
['2', '3']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('should allow for using the `asCallback` interface', function(ok) {
|
|
knex('test_table_two')
|
|
.insert(
|
|
[
|
|
{
|
|
account_id: 1,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 0,
|
|
},
|
|
{
|
|
account_id: 2,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 1,
|
|
},
|
|
{
|
|
account_id: 3,
|
|
details: '',
|
|
status: 1,
|
|
},
|
|
],
|
|
'id'
|
|
)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'oracledb',
|
|
'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;',
|
|
[
|
|
1,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
2,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
1,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
3,
|
|
'',
|
|
1,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['1', '2', '3']
|
|
);
|
|
})
|
|
.asCallback(function(err) {
|
|
if (err) return ok(err);
|
|
ok();
|
|
});
|
|
});
|
|
|
|
it('should take hashes passed into insert and keep them in the correct order', function() {
|
|
return knex('accounts')
|
|
.insert(
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test4@example.com',
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
logins: 2,
|
|
created_at: d,
|
|
updated_at: d,
|
|
},
|
|
{
|
|
first_name: 'Test',
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
logins: 2,
|
|
created_at: d,
|
|
updated_at: d,
|
|
last_name: 'User',
|
|
email: 'test5@example.com',
|
|
},
|
|
],
|
|
'id'
|
|
)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test4@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
[4]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?) returning "id"',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test4@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
['4', '5']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test4@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
2
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'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`',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test4@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
[5]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'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;',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test4@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['4', '5']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test4@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
['4', '5']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('will fail when multiple inserts are made into a unique column', function() {
|
|
if (/redshift/i.test(knex.client.driverName)) {
|
|
return;
|
|
}
|
|
return knex('accounts')
|
|
.where('id', '>', 1)
|
|
.orWhere('x', 2)
|
|
.insert(
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test5@example.com',
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
logins: 2,
|
|
created_at: d,
|
|
updated_at: d,
|
|
},
|
|
'id'
|
|
)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id"',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id" into ?',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test5@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
]
|
|
);
|
|
})
|
|
.then(
|
|
function() {
|
|
throw new Error(
|
|
'There should be a fail when multi-insert are made in unique col.'
|
|
);
|
|
},
|
|
function() {}
|
|
);
|
|
});
|
|
|
|
it('should drop any where clause bindings', function() {
|
|
return knex('accounts')
|
|
.where('id', '>', 1)
|
|
.orWhere('x', 2)
|
|
.insert(
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test6@example.com',
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
logins: 2,
|
|
created_at: d,
|
|
updated_at: d,
|
|
},
|
|
'id'
|
|
)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test6@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
[7]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id"',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test6@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
['7']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test6@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
1
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `accounts` (`about`, `created_at`, `email`, `first_name`, `last_name`, `logins`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test6@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
[6]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "accounts" ("about", "created_at", "email", "first_name", "last_name", "logins", "updated_at") values (?, ?, ?, ?, ?, ?, ?) returning "id" into ?',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test6@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['7']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [accounts] ([about], [created_at], [email], [first_name], [last_name], [logins], [updated_at]) output inserted.[id] values (?, ?, ?, ?, ?, ?, ?)',
|
|
[
|
|
'Lorem ipsum Dolore labore incididunt enim.',
|
|
d,
|
|
'test6@example.com',
|
|
'Test',
|
|
'User',
|
|
2,
|
|
d,
|
|
],
|
|
['7']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('should not allow inserting invalid values into enum fields', function() {
|
|
return knex('datatype_test')
|
|
.insert({ enum_value: 'd' })
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `datatype_test` (`enum_value`) values (?)',
|
|
['d']
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "datatype_test" ("enum_value") values (?)',
|
|
['d']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "datatype_test" ("enum_value") values (?)',
|
|
['d']
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `datatype_test` (`enum_value`) values (?)',
|
|
['d'],
|
|
[1]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "datatype_test" ("enum_value") values (?)',
|
|
['d']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [datatype_test] ([enum_value]) values (?)',
|
|
['d']
|
|
);
|
|
})
|
|
.then(
|
|
function() {
|
|
// No errors happen in sqlite3, which doesn't have native support
|
|
// for the enum type.
|
|
if (knex.client.driverName !== 'sqlite3') {
|
|
throw new Error(
|
|
'There should be an error for invalid enum inserts'
|
|
);
|
|
}
|
|
},
|
|
function() {}
|
|
);
|
|
});
|
|
|
|
it('should not allow invalid uuids in postgresql', function() {
|
|
return knex('datatype_test')
|
|
.insert({
|
|
enum_value: 'c',
|
|
uuid: uuid.v4(),
|
|
})
|
|
.then(function() {
|
|
return knex('datatype_test').insert({
|
|
enum_value: 'c',
|
|
uuid: 'test',
|
|
});
|
|
})
|
|
.then(
|
|
function() {
|
|
// No errors happen in sqlite3 or mysql, which don't have native support
|
|
// for the uuid type.
|
|
if (
|
|
knex.client.driverName === 'pg' ||
|
|
knex.client.driverName === 'mssql'
|
|
) {
|
|
throw new Error(
|
|
'There should be an error in postgresql for uuids'
|
|
);
|
|
}
|
|
},
|
|
function() {}
|
|
);
|
|
});
|
|
|
|
it('should not mutate the array passed in', function() {
|
|
const a = { enum_value: 'a', uuid: uuid.v4() };
|
|
const b = { enum_value: 'c', uuid: uuid.v4() };
|
|
const x = [a, b];
|
|
|
|
return knex('datatype_test')
|
|
.insert(x)
|
|
.then(function() {
|
|
expect(x).to.eql([a, b]);
|
|
});
|
|
});
|
|
|
|
it('should handle empty inserts', function() {
|
|
return knex.schema
|
|
.createTable('test_default_table', function(qb) {
|
|
qb.increments().primary();
|
|
qb.string('string').defaultTo('hello');
|
|
qb.tinyint('tinyint').defaultTo(0);
|
|
qb.text('text').nullable();
|
|
})
|
|
.then(function() {
|
|
return knex('test_default_table')
|
|
.insert({}, 'id')
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `test_default_table` () values ()',
|
|
[],
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "test_default_table" default values returning "id"',
|
|
[],
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "test_default_table" default values',
|
|
[],
|
|
1
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `test_default_table` default values',
|
|
[],
|
|
[1]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "test_default_table" ("id") values (default) returning "id" into ?',
|
|
[
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['1']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [test_default_table] output inserted.[id] default values',
|
|
[],
|
|
[1]
|
|
);
|
|
});
|
|
});
|
|
});
|
|
|
|
it('should handle empty arrays inserts', function() {
|
|
return knex.schema
|
|
.createTable('test_default_table2', function(qb) {
|
|
qb.increments().primary();
|
|
qb.string('string').defaultTo('hello');
|
|
qb.tinyint('tinyint').defaultTo(0);
|
|
qb.text('text').nullable();
|
|
})
|
|
.then(function() {
|
|
return knex('test_default_table2')
|
|
.insert([{}], 'id')
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `test_default_table2` () values ()',
|
|
[],
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "test_default_table2" default values returning "id"',
|
|
[],
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "test_default_table2" default values',
|
|
[],
|
|
1
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `test_default_table2` default values',
|
|
[],
|
|
[1]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "test_default_table2" ("id") values (default) returning "id" into ?',
|
|
[
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:id]';
|
|
},
|
|
],
|
|
['1']
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [test_default_table2] output inserted.[id] default values',
|
|
[],
|
|
[1]
|
|
);
|
|
});
|
|
});
|
|
});
|
|
|
|
it('should take an array of columns to return in oracle or postgres', function() {
|
|
const insertData = {
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 0,
|
|
};
|
|
return knex('test_table_two')
|
|
.insert(insertData, ['account_id', 'details'])
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'mysql',
|
|
'insert into `test_table_two` (`account_id`, `details`, `status`) values (?, ?, ?)',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
[4]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning "account_id", "details"',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
[
|
|
{
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?)',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
1
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'insert into `test_table_two` (`account_id`, `details`, `status`) values (?, ?, ?)',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
[4]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
`insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning "account_id","details" into ?,?`,
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:account_id]';
|
|
},
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:details]';
|
|
},
|
|
],
|
|
[
|
|
{
|
|
account_id: '10',
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [test_table_two] ([account_id], [details], [status]) output inserted.[account_id], inserted.[details] values (?, ?, ?)',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
[
|
|
{
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
},
|
|
]
|
|
);
|
|
})
|
|
.then(function(rows) {
|
|
if (/redshift/.test(knex.client.driverName)) {
|
|
return expect(rows).to.equal(1);
|
|
}
|
|
expect(rows.length).to.equal(1);
|
|
if (knex.client.driverName === 'pg') {
|
|
expect(_.keys(rows[0]).length).to.equal(2);
|
|
expect(rows[0].account_id).to.equal(insertData.account_id);
|
|
expect(rows[0].details).to.equal(insertData.details);
|
|
}
|
|
});
|
|
});
|
|
|
|
it('should allow a * for returning in postgres and oracle', function() {
|
|
if (/redshift/i.test(knex.client.driverName)) {
|
|
return;
|
|
}
|
|
const insertData = {
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 0,
|
|
};
|
|
|
|
const returningColumn = '*';
|
|
return knex('test_table_two')
|
|
.insert(insertData, returningColumn)
|
|
.testSql(function(tester) {
|
|
tester(
|
|
'pg',
|
|
'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning *',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
[
|
|
{
|
|
id: 5,
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 0,
|
|
json_data: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'insert into "test_table_two" ("account_id", "details", "status") values (?, ?, ?) returning "ROWID" into ?',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
function(v) {
|
|
return v.toString() === '[object ReturningHelper:ROWID]';
|
|
},
|
|
],
|
|
[
|
|
{
|
|
id: 5,
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 0,
|
|
json_data: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'insert into [test_table_two] ([account_id], [details], [status]) output inserted.* values (?, ?, ?)',
|
|
[
|
|
10,
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
0,
|
|
],
|
|
[
|
|
{
|
|
id: 5,
|
|
account_id: 10,
|
|
details:
|
|
'Lorem ipsum Minim nostrud Excepteur consectetur enim ut qui sint in veniam in nulla anim do cillum sunt voluptate Duis non incididunt.',
|
|
status: 0,
|
|
json_data: null,
|
|
},
|
|
]
|
|
);
|
|
})
|
|
.then(function(rows) {
|
|
expect(rows.length).to.equal(1);
|
|
if (knex.client.driverName === 'pg') {
|
|
expect(_.keys(rows[0]).length).to.equal(5);
|
|
expect(rows[0].account_id).to.equal(insertData.account_id);
|
|
expect(rows[0].details).to.equal(insertData.details);
|
|
expect(rows[0].status).to.equal(insertData.status);
|
|
expect(rows[0].json_data).to.equal(null);
|
|
}
|
|
});
|
|
});
|
|
|
|
describe('batchInsert', function() {
|
|
const driverName = knex.client.driverName;
|
|
const fiftyLengthString =
|
|
'rO8F8YrFS6uoivuRiVnwrO8F8YrFS6uoivuRiVnwuoivuRiVnw';
|
|
const items = [];
|
|
const amountOfItems = 100;
|
|
const amountOfColumns = 30;
|
|
for (let i = 0; i < amountOfItems; i++) {
|
|
const item = {};
|
|
for (let x = 0; x < amountOfColumns; x++) {
|
|
item['Col' + x] = fiftyLengthString;
|
|
}
|
|
items.push(item);
|
|
}
|
|
|
|
beforeEach(function() {
|
|
return knex.schema.dropTableIfExists('BatchInsert').then(function() {
|
|
return knex.schema.createTable('BatchInsert', function(table) {
|
|
for (let i = 0; i < amountOfColumns; i++) {
|
|
table.string('Col' + i, 50);
|
|
}
|
|
});
|
|
});
|
|
});
|
|
|
|
it('#757 - knex.batchInsert(tableName, bulk, chunkSize)', function() {
|
|
this.timeout(30000);
|
|
return knex
|
|
.batchInsert('BatchInsert', items, 30)
|
|
.returning(['Col1', 'Col2'])
|
|
.then(function(result) {
|
|
//Returning only supported by some dialects.
|
|
if (['pg', 'oracledb'].indexOf(driverName) !== -1) {
|
|
result.forEach(function(item) {
|
|
expect(item.Col1).to.equal(fiftyLengthString);
|
|
expect(item.Col2).to.equal(fiftyLengthString);
|
|
});
|
|
}
|
|
return knex('BatchInsert').select();
|
|
})
|
|
.then(function(result) {
|
|
const count = result.length;
|
|
expect(count).to.equal(amountOfItems);
|
|
});
|
|
});
|
|
|
|
it('#1880 - Duplicate keys in batchInsert should not throw unhandled exception', async function() {
|
|
if (/redshift/i.test(knex.client.driverName)) {
|
|
return;
|
|
}
|
|
const fn = sinon.stub();
|
|
process.on('unhandledRejection', fn);
|
|
await new bluebird(function(resolve, reject) {
|
|
return knex.schema
|
|
.dropTableIfExists('batchInsertDuplicateKey')
|
|
.then(function() {
|
|
return knex.schema.createTable(
|
|
'batchInsertDuplicateKey',
|
|
function(table) {
|
|
table.string('col');
|
|
table.primary('col');
|
|
}
|
|
);
|
|
})
|
|
.then(function() {
|
|
const rows = [{ col: 'a' }, { col: 'a' }];
|
|
return knex.batchInsert(
|
|
'batchInsertDuplicateKey',
|
|
rows,
|
|
rows.length
|
|
);
|
|
})
|
|
.then(function() {
|
|
return reject(new Error('Should not reach this point'));
|
|
})
|
|
.catch(function(error) {
|
|
//Should reach this point before timeout of 10s
|
|
expect(error.message.toLowerCase()).to.include(
|
|
'batchinsertduplicatekey'
|
|
);
|
|
resolve(error);
|
|
});
|
|
}).timeout(10000);
|
|
expect(fn).have.not.been.called;
|
|
process.removeListener('unhandledRejection', fn);
|
|
});
|
|
|
|
it('knex.batchInsert with specified transaction', function() {
|
|
return knex.transaction(function(tr) {
|
|
knex
|
|
.batchInsert('BatchInsert', items, 30)
|
|
.returning(['Col1', 'Col2'])
|
|
.transacting(tr)
|
|
.then(tr.commit)
|
|
.catch(tr.rollback);
|
|
});
|
|
});
|
|
|
|
it('transaction.batchInsert using specified transaction', function() {
|
|
return knex.transaction(function(tr) {
|
|
return tr
|
|
.batchInsert('BatchInsert', items, 30)
|
|
.returning(['Col1', 'Col2']);
|
|
});
|
|
});
|
|
});
|
|
|
|
it('should validate batchInsert batchSize parameter', function() {
|
|
//Should not throw, batchSize default
|
|
return knex
|
|
.batchInsert('test', [])
|
|
.then(function() {
|
|
//Should throw, null not valid
|
|
return knex.batchInsert('test', [], null);
|
|
})
|
|
.catch(function(error) {
|
|
expect(error.message).to.equal('Invalid chunkSize: null');
|
|
|
|
//Should throw, 0 is not a valid chunkSize
|
|
return knex.batchInsert('test', [], 0);
|
|
})
|
|
.catch(function(error) {
|
|
expect(error.message).to.equal('Invalid chunkSize: 0');
|
|
|
|
//Also faulty
|
|
return knex.batchInsert('test', [], 'still no good');
|
|
})
|
|
.catch(function(error) {
|
|
expect(error.message).to.equal('Invalid chunkSize: still no good');
|
|
|
|
return true;
|
|
});
|
|
});
|
|
|
|
it('should replace undefined keys in multi insert with DEFAULT', function() {
|
|
if (knex.client.driverName === 'sqlite3') {
|
|
return true;
|
|
}
|
|
return knex('accounts')
|
|
.insert(
|
|
[
|
|
{
|
|
last_name: 'First Item',
|
|
email: 'single-test1@example.com',
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: new Date(),
|
|
updated_at: new Date(),
|
|
},
|
|
{
|
|
last_name: 'Second Item',
|
|
email: 'double-test1@example.com',
|
|
logins: 2,
|
|
created_at: new Date(),
|
|
updated_at: new Date(),
|
|
},
|
|
],
|
|
'*'
|
|
)
|
|
.then(function() {
|
|
return knex('accounts')
|
|
.whereIn('email', [
|
|
'single-test1@example.com',
|
|
'double-test1@example.com',
|
|
])
|
|
.orderBy('email', 'desc');
|
|
})
|
|
.then(function(results) {
|
|
expect(results[0].logins).to.equal(1);
|
|
expect(results[1].about).to.equal(null);
|
|
// cleanup to prevent needs for too much changes to other tests
|
|
return knex('accounts')
|
|
.delete()
|
|
.whereIn(
|
|
'id',
|
|
results.map(function(row) {
|
|
return row.id;
|
|
})
|
|
);
|
|
});
|
|
});
|
|
|
|
it('#1423 should replace undefined keys in single insert with DEFAULT also in transacting query', function() {
|
|
if (knex.client.driverName === 'sqlite3') {
|
|
return true;
|
|
}
|
|
return knex.transaction(function(trx) {
|
|
return trx('accounts')
|
|
.insert({
|
|
last_name: 'First Item',
|
|
email: 'findme@example.com',
|
|
logins: undefined,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: new Date(),
|
|
updated_at: new Date(),
|
|
})
|
|
.then(function(results) {
|
|
return trx('accounts').where('email', 'findme@example.com');
|
|
})
|
|
.then(function(results) {
|
|
expect(results[0].logins).to.equal(1);
|
|
// cleanup to prevent needs for too much changes to other tests
|
|
return trx('accounts')
|
|
.delete()
|
|
.where('id', results[0].id);
|
|
});
|
|
});
|
|
});
|
|
});
|
|
};
|