Database is not persisted and error while selecting from table

[mkjawadi]

Hi. First of all, thank you for your great library. 😊 The getDatabaseInfo() always returns persisted: false and I get error while selecting rows from table. As you can see in my vite.config.ts file, I've already set the headers and yes I'm using https. I've tested from various browsers but I still have the same issue.

{
...
optimizeDeps: {
      exclude: ["sqlocal"],
    },
    plugins: [
      react(),
      VitePWA(PWAOptions),
      // This is needed for SQLocal to work
      {
        name: "configure-response-headers",
        configureServer: (server) => {
          server.middlewares.use((_req, res, next) => {
            res.setHeader("Cross-Origin-Embedder-Policy", "require-corp");
            res.setHeader("Cross-Origin-Opener-Policy", "same-origin");
            next();
          });
        },
      },
    ]
}

here is my code.

// db.ts

/* eslint-disable @typescript-eslint/no-unused-expressions */
import { SQLocal } from "sqlocal";

export const db = new SQLocal({
  databasePath: "database.sqlite3",
  readOnly: false,
  verbose: false,
  onInit: (sql) => {
    try {
      sql`CREATE TABLE IF NOT EXISTS categories (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          active BOOLEAN NOT NULL DEFAULT 1,
          last_updated_by TEXT NOT NULL,
          last_updated_at DATETIME NOT NULL,
          is_synced BOOLEAN NOT NULL DEFAULT 0,
          is_deleted BOOLEAN NOT NULL DEFAULT 0
        );`;
      console.log("Table created successfully.");

      const id = "01JJFDKBW8TS7C9MENGZDRMG4S";
      const name = "Test category";
      sql`INSERT INTO categories (id, name, active, last_updated_by, last_updated_at)
         VALUES (${id}, ${name}, 1, 'system', CURRENT_TIMESTAMP);`;
      console.log(`${name} inserted successfully.`);
    } catch (error) {
      console.error("Error creating table:", error);
    }
  },
  onConnect: (reason) => {
    console.log("Database connected:", reason);
  },
});

Here is how I used it.

import { db } from "./utils/db";

const info = await db.getDatabaseInfo();
console.log("Database Info: ", info);

const categories = await db.sql`SELECT * FROM categories;`;
console.log("Categories: ", categories);

This is the console output.

Table created successfully.
Test category inserted successfully.
Database connected: initial
Database Info:  {databasePath: 'database.sqlite3', databaseSizeBytes: 0, storageType: 'opfs', persisted: false}
Uncaught Error: SQLITE_ERROR: sqlite3 result code 1: no such table: categories

Also, I'd greatly appreciate if you could kindly guide on recommended approach for using this library to share the same database between main thread (for offline support in PWA) as well as service worker (for periodic background sync - fetching unsynced data from database and sending them to the server) 🙏

Many thanks in advance.

[DallasHoff]

onInit should return the SQL statements that you want to run before anything else can run on the database connection. In your code, you are preparing the SQL statements that create the table, but you are not actually running them, which is why you are getting the "no such table: categories" error.

onInit: (sql) => {
  return [sql`CREATE TABLE IF NOT EXISTS categories (...)`];
}

getDatabaseInfo is returning false because you probably have not made the OPFS persistent by using navigator.storage.persist() on your app. See the docs for getDatabaseInfo.

[mkjawadi]

Thanks @DallasHoff for getting back to me. Even with navigator.storage.persist() it still returns false. Also, any advice on using this with service worker in PWA for background data sync logic to and from server?