better-sqlite3 : I get undefined instead of the default values of the table

Question

I expected to see the default of values set for the table, but I get undefined

This is the code:

import Database from 'better-sqlite3-multiple-ciphers'


// https://gist.github.com/anhdiepmmk/b8dcd1c7be8c09580f607ef186529192
// https://www.sqlite.org/quirks.html#no_separate_boolean_datatype
// https://www.sqlite.org/stricttables.html#strict_tables

const commandToCreateJitsiSettingsTable = `
  CREATE TABLE IF NOT EXISTS jitsiTable (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    alwaysOnTopWindowEnable INTEGER NOT NULL DEFAULT 1,
    disableAGC INTEGER NOT NULL DEFAULT 0,
    serverURL TEXT,
    serverTimeout REAL DEFAULT undefined,

    created_at timestamp NOT NULL DEFAULT current_timestamp,
    updated_at timestamp NOT NULL DEFAULT current_timestamp
  )
`

if (!fs.existsSync(path.join(settingsUserDataFolder, "Settings.db")) {
  const SettingsDB = new Database(path.join(settingsUserDataFolder,"Settings.db"), {})
  SettingsDB.pragma('journal_mode = WAL')
  SettingsDB.pragma("rekey='secret-key'");


  SettingsDB.exec(commandToCreateJitsiSettingsTable)

  SettingsDB.close();
} else {

  console.log("Settings.db already exists")

  const SettingsDB = require('better-sqlite3-multiple-ciphers')(path.join(settingsUserDataFolder,"Settings.db"), {})
  SettingsDB.pragma("key='secret-key'");

  const row = SettingsDB.prepare("SELECT * FROM jitsiTable");
  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, row.serverTimeout, row.created_at, row.updated_at)

  SettingsDB.close();

}

Output:

Settings.db already exists
undefined undefined undefined undefined undefined undefined

How to correctly set and then get the default values in the table?

Answer 1

You have prepared the statement but have not retrieved any data. You set the row variable to a prepared statement, not data. You need to use one of these methods, such as .get() or .all() depending on your needs on the prepared statement to retrieve the data. In your case, probably .get(). See the API docs for details.

For example, you could replace these lines

  const row = SettingsDB.prepare("SELECT * FROM jitsiTable");
  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, row.serverTimeout, row.created_at, row.updated_at)

with

const stmt = SettingsDB.prepare("SELECT * FROM jitsiTable");

for (const row of stmt.iterate()) {
  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, row.serverTimeout, row.created_at, row.updated_at)
}

Answer 2

Thanks to @PChemGuy and to a kind guy of better-sqlite3 repo I realized that insert operation was missing :

const SettingsDB = require('better-sqlite3-multiple-ciphers')
(path.join(settingsUserDataFolder,"Settings.db"), {})
  SettingsDB.pragma("key='secret-key'");


const insert = SettingsDB.prepare('INSERT INTO jitsiTable  
(alwaysOnTopWindowEnable, disableAGC, serverTimeout) VALUES (?, ?, ?)'); 
  const row = SettingsDB.prepare("SELECT * FROM jitsiTable").get();

  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, 
  row.serverTimeout, row.created_at, row.updated_at) 

Output:

0 1 null 30 2022-12-10 11:00:22 2022-12-10 11:00:22

iterate() is for getting all the insertions so far:

const SettingsDB = require('better-sqlite3-multiple-ciphers')
(path.join(settingsUserDataFolder,"Settings.db"), {})
SettingsDB.pragma("key='secret-key'");

const insert = SettingsDB.prepare('INSERT INTO jitsiTable  
(alwaysOnTopWindowEnable, disableAGC, serverTimeout) VALUES (?, ?, 
?)');

  insert.run(0, 1, 30)

  const stmt = SettingsDB.prepare("SELECT * FROM jitsiTable");
  for (const row of stmt.iterate()) {
    console.log(row.alwaysOnTopWindowEnable, row.disableAGC,  
  row.serverURL, row.serverTimeout, row.created_at, row.updated_at)
  }

SettingsDB.close();

Output :

0 1 null 30 2022-12-10 11:00:22 2022-12-10 11:00:22
0 1 null 30 2022-12-10 11:05:46 2022-12-10 11:05:46
0 1 null 30 2022-12-10 11:18:42 2022-12-10 11:18:42
0 1 null 30 2022-12-10 11:22:10 2022-12-10 11:22:10
0 1 null 30 2022-12-10 11:24:18 2022-12-10 11:24:18
0 1 null 30 2022-12-10 11:27:17 2022-12-10 11:27:17
0 1 null 30 2022-12-10 11:35:28 2022-12-10 11:35:28