How does knex handle default values in SQLite?

Question

We currently use mysql / knex, and I'm adding SQLite as a database for testing purposes. I'm getting

Knex:warning - sqlite does not support inserting default values. Set the useNullAsDefault flag to hide this warning. (see docs http://knexjs.org/#Builder-insert).

How does Knex handle default values? Does it just drop any defaults, or does it add in the defaults after an insert as following UPDATE statements?

I don't want to change all of our codebase (swap out all default values), trying to do the minimal change that will allow me to run SQLite in our tests... concerned this will introduce bugs.

ArchNoob ArchNoob · Accepted Answer · 2017-02-22T21:08:47

I'm learning knex.js so I can use it in a project involving PostgreSQL. While trying out Sqlite I came across this issue.

Turns out it's documented!

If one prefers that undefined keys are replaced with NULL instead of DEFAULT one may give useNullAsDefault configuration parameter in knex config.

And they give this code:

var knex = require('knex')({
  client: 'sqlite3',
  connection: {
     filename: "./mydb.sqlite"
  },
  useNullAsDefault: true
});

knex('coords').insert([{x: 20}, {y: 30}, {x: 10, y: 20}])
// insert into `coords` (`x`, `y`) values (20, NULL), (NULL, 30), (10, 20)"

This removed the warning message for me.

How does knex handle default values in SQLite?

1 Answers