Better-sqlite3: Error when using trigger to update timestamp

[nmay231]

Check out this gist for a minimal example.

In short, I have a table with a trigger that changes the _last_updated row on each update.

CREATE TABLE Test (
    id integer primary key autoincrement,
    data text not null,
    _last_updated datetime default current_timestamp
);

CREATE TRIGGER update_test_timestamp
AFTER UPDATE ON Test BEGIN
UPDATE Test SET _last_updated = datetime("now") WHERE id = new.id;
END;

INSERT INTO Test(data) VALUES("blah blah blah");

When updating a row manually, such as with the sqlite repl, it works as expected.

sqlite> select * from test;
1|blah blah blah|2020-05-06 00:28:48
sqlite> update test set data="blah blah 2.0" where id=1;
sqlite> select * from test;
1|blah blah 2.0|2020-05-06 00:29:31

However, performing the same action with better-sqlite3 produces a very obscure error.

const connect = require('better-sqlite3')

const db = connect('test.db', { fileMustExist: true })

db.prepare('UPDATE Test SET data=? WHERE id=?').run('blah blah 3.0', 1)

```
/home/nmay/cur/tmp/sqlite-bug/update_db.js:5
db.prepare('UPDATE Test SET data=? WHERE id=?').run('blah blah 3.0', 1)
^
SqliteError: no such column: now
at Object. (/home/nmay/cur/tmp/sqlite-bug/update_db.js:5:4)
at Module._compile (internal/modules/cjs/loader.js:1156:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1176:10)
at Module.load (internal/modules/cjs/loader.js:1000:32)
at Function.Module._load (internal/modules/cjs/loader.js:899:14)
at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:74:12)
at internal/main/run_main_module.js:18:47

Removing the trigger and recreating the database gets rid of this issue:
```diff
- CREATE TRIGGER update_test_timestamp
- AFTER UPDATE ON Test BEGIN
- UPDATE Test SET _last_updated = datetime("now") WHERE id = new.id;
- END;

I'm at a loss why the trigger would give rise to this error.

Versions:

OS             -- Ubuntu 18.04
sqlite3        -- 3.29.0
node           -- v12.16.2
better-sqlite3 -- 7.0.1

Let me know what other information you require :+1:

[JoshuaWise]

This error is happening because by default better-sqlite3 is compiled with the SQLITE_DQS=0 compile-time option. This compile-time option disables SQLite3's "double quoted string literal misfeature".

Long story short, the correct way to write a string in standard SQL is to use single quotes. In standard SQL, double quotes are used to identify columns and tables (for example, if the column name contains a space). When SQLite was first created, it allowed double quotes to be used for strings in situations where it could deduce that such a column/table name does not exist. This was originally done to be compatible with MySQL which also accepts double quotes as strings. However, both MySQL and SQLite now recommend that single quotes should always be used for strings, to be compliant with the SQL standard. SQLite still allows double quoted strings for legacy compatibility, but discourages the behavior, and even lists SQLITE_DQS=0 as one of its recommended compile-time options.

So you should change datetime("now") to datetime('now').