Different treatment of undefined values for update vs. insert

[fkaempfer]

Environment

Knex version: 2.4.2
Database + version: Sqlite3 v. 5.1.2, also postgres
OS: Linux

Bug

1. Explain what kind of behaviour you are getting and how you think it should do
   Consider the following code:

const knex = require('knex')({
	client: 'sqlite3', // or 'better-sqlite3'
	connection: {
		filename: ":memory:"
	},
	useNullAsDefault: true

});

async function test() {
	await knex.raw(`CREATE TABLE test(id INT);`)


	await knex('test').update({
		id: 4,
		asd: undefined
	})

	await knex('test').insert({
		id: 4,
		asd: undefined
	})

	await knex.destroy()
}

test()

The update statement works, the asd column is ignored. The insert statement throws an error:

2. Error message

[Error: insert into `test` (`asd`, `id`) values (NULL, 4) - SQLITE_ERROR: table test has no column named asd] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

3. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real
   database connection to MySQL or PostgreSQL, then single file example which initializes
   needed data and demonstrates the problem.

See above

Use case

Basically I stumbled upon this when trying to delete a field by setting it to undefined. Often when for example using the spread operator it is useful to delete fields that don't exist in the DB.

knex('test').update({
...stuff,
non_existing_column: undefined
})

But it's of course also possible to delete the field beforehand. Either way, I think that the behavior should be consistent between insert/update.

[jlaustill]

I just ran into this as well, SUPER annoying...