User defined functions is not working in better-sqlite3-CodePudding

I have a node.js server that connects and initializes a sqlite database. I am using the better-sqlite3 library.

But the server never gets started. It gives below error:

Tables schema:

CREATE TABLE IF NOT EXISTS users (

    user_id       text        NOT NULL DEFAULT gen_random_uuid() ,
    email         text UNIQUE NOT NULL                           ,
    full_name     text        NOT NULL                           ,
    profile_photo text                 DEFAULT ''                ,    
    passhash      text        NOT NULL                           ,
    created_time  int         NOT NULL DEFAULT now()             ,
    settings      text        NOT NULL DEFAULT '{}'              ,       

    CHECK ( length ( user_id ) = 36 ) ,
    CHECK ( length ( email     ) >= 3  AND length ( email     ) <= 100 ) ,
    CHECK ( length ( full_name ) >= 1  AND length ( full_name ) <= 100 ) ,
    CHECK ( length ( passhash  ) >= 8  AND length ( passhash  ) <= 100 ) ,
    CHECK ( length ( profile_photo  ) >= 0  AND length ( profile_photo  ) <= 1000 ) , 

    PRIMARY KEY ( user_id )

) STRICT;

Part of node.js server code:

function setup (config) {
    db = new Database('colube.db', { verbose: console.log }); 
    db.pragma('journal_mode = WAL');

    if (!isTablesCreated(db)) {
        addFns(db);

        let path      = getPath('db/migrations/V0001__sqlite3_initial_version.sql');
        let migration = fs.readFileSync(path, 'utf8');

        db.exec(migration);
    }
}

My user-defined functions:

function addFns (db) {

    db.function('gen_random_uuid', () => {
        return v4();
    }); 
    
    db.function('now', () => {
        return Date.now();
    }); 
}

The error is thrown when executing sqlite migration file . Migration works fine when removing the use of gen_random_uuid and now(user-defined functions).

Since the document of better-sqlite3 states clear that it supports user-defined functions, I don't know how to solve this problem.

better-sqlite3 document: user defined function

CodePudding user response：

I got a reply from better-sqlite3 community on github that solved my problem, the link of the reply: Help: syntax error. Below is the content of the reply:

The problem is that you're using invalid syntax. The correct syntax is described as:

An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.

In other words, you must enclose gen_random_uuid() in parenthesis, like (gen_random_uuid()). The same must be done for now() -> (now()).