FIX[Sqlite3] - Date/DateTime columns string value normalization

[briosheje]

Hi! 👋

Firstly, thanks for your work on this project! 🙂

Today I used patch-package to patch knex@3.0.1 for the project I'm working on.

Here is the diff that solved my problem:

diff --git a/node_modules/knex/lib/dialects/sqlite3/query/sqlite-querycompiler.js b/node_modules/knex/lib/dialects/sqlite3/query/sqlite-querycompiler.js
index 47b282e..1c1e676 100644
--- a/node_modules/knex/lib/dialects/sqlite3/query/sqlite-querycompiler.js
+++ b/node_modules/knex/lib/dialects/sqlite3/query/sqlite-querycompiler.js
@@ -9,6 +9,7 @@ const reduce = require('lodash/reduce');
 const QueryCompiler = require('../../../query/querycompiler');
 const noop = require('../../../util/noop');
 const { isString } = require('../../../util/is');
+const { dateToString } = require('../../../util/string');
 const {
   wrapString,
   columnize: columnize_,
@@ -82,6 +83,13 @@ class QueryCompiler_SQLite3 extends QueryCompiler {
       });
     }
 
+    insertData.values.forEach((values, i) =>
+      values.forEach((val, j) => {
+        if (val instanceof Date) {
+          insertData.values[i][j] = dateToString(val);
+        }
+      })
+    );
     if (insertData.values.length === 1) {
       const parameters = this.client.parameterize(
         insertData.values[0],
@@ -117,6 +125,9 @@ class QueryCompiler_SQLite3 extends QueryCompiler {
       const block = (blocks[i] = []);
       let current = insertData.values[i];
       current = current === undefined ? this.client.valueForUndefined : current;
+
+      if (current instanceof Date) current = dateToString(current);
+      
       while (++i2 < insertData.columns.length) {
         block.push(
           this.client.alias(
@@ -152,6 +163,15 @@ class QueryCompiler_SQLite3 extends QueryCompiler {
   // Compiles an `update` query, allowing for a return value.
   update() {
     const withSQL = this.with();
+    if (this.single.update) {
+      // -- Patch: update entries that are dates so that they are serialized
+      // as strings as intended.
+      Object.entries(this.single.update).forEach(([k, v]) => {
+        if (v instanceof Date) {
+          this.single.update[k] = dateToString(v);
+        }
+      });
+    }
     const updateData = this._prepUpdate(this.single.update);
     const wheres = this.where();
     const { returning } = this.single;

This issue body was partially generated by patch-package.

Abstract about the problem

When using the sqlite3 adapter, the date / datetime values are not written properly to the database.

The patch from #5272 only partially addresses the issue since it only works for inserts but not for updates.

This issue contains a proposal for fixing the issue by adding these changes:

• Applying the same changes from fix: js date inserted as a number instead of DATETIME for sqlite3 #5272 by correctly converting the values that are instance of Date to a date string prior writing to the database.
• Applies the same when running an update query.

Unfortunately there is not much documentation about such integration and I'm not 100% sure by design you intend to have it working in such direction, so it would be nice to have some discussion about potential issues that this PR would create.