Is there any way to save wasm with table data to indexedDB?

[lpfy]

I am trying to use duckdb-wasm to process a large volume of data, small table will be 100k+ rows, around 80MB, large table will be 1 million rows, around 250MB. I am wondering how can I preserve table data in WASM once it loaded from RestAPI (json)?

Data will be updated weekly, my goal is to avoid db initialization and data load before the next data refresh is triggered.

for example

const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
const c = await db.connect();

await db.registerFileText(
  'rows.json',
  JSON.stringify(APIresponse.data)
);
await c.insertJSONFromPath('rows.json', { name: 'jsondata'});

Should I save db variable to indexedDB? and load it after the user re-visit the site?

BTW: is there any better way to save json/js array (RestAPI response) to duckDB?

[ceymard]

I have the exact same question.

Being able to have an SQL database that persists its data offline would be huge for webapps.

[carlopi]

A solution based on saving state after each transaction WebWorker-side should be doable, an hacky way on top of existing APIs should be doable right now, happy to dig in that direction in the next days, and then can be refined into what's the proper API here.

A better integrated solution, as in a solution that cuts the amount of bytes copied around, would require a more integrated approach and potentially the use of Origin File System to drastically reduce the copies being made.

One thing to note that since there is the concept of atomic transactions, the synchronisation points are at least easy to determine.

[ceymard]

I recall that someone once did so with sql.js (and named it absurd-sql, but it was not that absurd, really,) playing with Atomics.wait to achieve saves in IndexedDB in a somewhat synchronous manner since SQLite is not asynchronous at all.

Maybe there is some stuff to draw inspiration from there ?

What is the Origin File System ? Do you mean making a fake filesystem on top of indexedb ?

[lpfy]

I think Origin File System mentioned by @carlopi is origin private file system (OPFS) (Chrome Developers site)

[danilo-css]

@lpfy Yes. I have tested it and it works. I have already been able to write parquet files and geojson files, though I am still trying to learn duckdb-wasm to query parquet files.

You can find the TypeScript OPFS functions I used here: #1575 (comment)

[lpfy]

My current temp solution is using Dexie.js (indexedDB) with DuckDB wasm, but only preserving JSON response from RestAPI, still have to load JSON to DuckDB every time the web app starts.

Basically, I dump 80MB JSON text string into indexedDB as Blob (seems this is the fastest way to save data into indexedDB??), then every time the web app starts, it check whether indexedDB data exists or not:

1. if exists then save to DuckDB, make table ready to be queried
2. if not then call RestAPI -> load json -> save to indexedDB -> save to DuckDB

One interesting behaviour I discovered is the SUM query for the integer column in DuckDB returns as String, DOUBLE column type will return as Number, not sure what's going on here... @carlopi do you experience the same behaviour? I have tried to define table schema, it won't fix the issue, that is why in my code I have to ALTER the table column to DOUBLE after data is loaded into DuckDB. Please see SQL query in the last section of my code, very simple SQL...

The following code is my Vue3 / Nuxt3 / Pinia store file DuckDB.ts (using Vue3 composition API rather than options API)

import Dexie, { Table } from 'dexie'
import * as duckdb from '@duckdb/duckdb-wasm';
import duckdb_wasm from '@duckdb/duckdb-wasm/dist/duckdb-mvp.wasm?url';
import mvp_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-mvp.worker.js?url';
import duckdb_wasm_eh from '@duckdb/duckdb-wasm/dist/duckdb-eh.wasm?url';
import eh_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-eh.worker.js?url';

/* IndexedDB database schema*/
interface ParkData {
  id?: number;
  tblName?: string;
  JsonData?: Blob;
}
// Declare Database
class ParkDatabase extends Dexie {
  public tblData!: Table<ParkData, number>; // id is number in this case

  public constructor() {
      super("ParkDatabase");
      this.version(1).stores({
        tblData: "++id,tblName,JsonData"
      });
  }
}
/* ------------------------- */

export const useDuckDBStore = defineStore('DuckDBStore', () => {
  
  interface DBobj {
    db?: duckdb.AsyncDuckDB;
    conn?: duckdb.AsyncDuckDBConnection;
  }

  const queryRS = ref();
  const DBobj = ref<DBobj>(); //DuckDB db and conn objects stored and can be accessed by all pages/components

  const initDuckDB = async(logType: string) => {

    const MANUAL_BUNDLES: duckdb.DuckDBBundles = {
      mvp: {
          mainModule: duckdb_wasm,
          mainWorker: mvp_worker,
      },
      eh: {
          mainModule: duckdb_wasm_eh,
          mainWorker: eh_worker,
      },
    };
    const SilentLogger = {
      log: () => {
        /* Silent Logger do nothing */
      },
    };
    // Select a bundle based on browser checks
    const bundle = await duckdb.selectBundle(MANUAL_BUNDLES);
    // Instantiate the asynchronus version of DuckDB-wasm
    const worker = new Worker(bundle.mainWorker!);
    const logger = logType === 'SilentLogging' ? SilentLogger : new duckdb.ConsoleLogger();
    const db = new duckdb.AsyncDuckDB(logger, worker);
    await db.instantiate(bundle.mainModule, bundle.pthreadWorker);

    // Connect to db
    const conn = await db.connect();
    
    console.log("\uD83E\uDD86 DuckDB initiating.")

    return {db, conn}
  }

  const APItoTblDuckDB = async (db: duckdb.AsyncDuckDB, c: duckdb.AsyncDuckDBConnection, APIUrl: string, TblName: string) => {
    
    const JSONtoTblDuckDB = async (json_txt: string, resolve: (value: string | PromiseLike<string>) => void) => {
      // Save the response data to DuckDB
      await db.registerFileText(
        'rows.json',
        json_txt
      );
      c.insertJSONFromPath('rows.json', { 
        name: TblName
      }).then( async ()=> {
        console.log(`\uD83E\uDD86 DuckDB table ${TblName} created.`)
        c.query(`ALTER TABLE ${TblName} ALTER NumVisitors TYPE DOUBLE;`);
        c.query(`ALTER TABLE ${TblName} ALTER NumBooking TYPE DOUBLE;`);
        //All processes executed
        resolve('Done'); 
      });
    }

    return new Promise<string>( async (resolve) => {
      
      //Processing     
      let tblExist: boolean = false;

      const idb = new ParkDatabase();

      tblExist = await idb.tblData.where({tblName: TblName}).count() === 0 ? false : true;

      if(tblExist){
        console.log(`\uD83D\uDEE2 IndexedDB table ${TblName} exists`);
        console.log(`\uD83D\uDEE2 IndexedDB Loading local table ${TblName} started`);

        const savedData = await idb.tblData.get({tblName: TblName});

        savedData?.JsonData?.text().then( json_txt => {
          console.log(`\uD83D\uDEE2 IndexedDB Loading local table ${TblName} finished`);
          // Save the local data to DuckDB
          JSONtoTblDuckDB(json_txt, resolve);
        });
        
      } else {
        console.log(`\uD83D\uDEE2 IndexedDB table ${TblName} doesn't exist, need to re-fetch data`);

        const { data, pending, error, refresh } = await useFetch(
          APIUrl,
          {
            method: 'GET',
            query: { dbFY: null, timestamp: new Date().getTime() },
            credentials: 'include',
            async onResponse({ request, response, options }) {
              console.log("\uD83D\uDD03 Data fetched from RestAPI");
              let json_txt: string = JSON.stringify(response._data);
              let txtblob: Blob = new Blob([json_txt], {
                type: 'text/plain'
              });
              // Save the response data to IndexedDB
              console.log(`\uD83D\uDEE2 Saving ${TblName} to IndexedDB started`)
              idb.tblData
                  .add({tblName: TblName, JsonData: txtblob})
                  .then( (rid)=> {
                    console.log(`\uD83D\uDEE2 Saving ${TblName} to IndexedDB finished, RowID ${rid}`)
                  });
              // Save the response data to DuckDB
              JSONtoTblDuckDB(json_txt, resolve);     
    
              return response._data;
            },
          }
        );
      }

    });
  }

  const query = async (conn: duckdb.AsyncDuckDBConnection, FYear: String) => {
    const q = await conn.query(`
      SELECT SUM(NumBooking) AS NumBooking, SUM(NumVisitors) AS NumVisitors, SUM(Revenue) AS Revenue FROM VisitorUseData WHERE Financial_Year = '${FYear}'
    `);

    queryRS.value = JSON.parse(JSON.stringify(q.toArray()));
  }


  return {queryRS, DBobj, initDuckDB, APItoTblDuckDB, query}
})

the following code is from a page (test.vue) which using pinia store and Vuetify UI framework

<template>
  <div>
    <section>
      <v-btn color="primary" class="ma-2" @click="query(conn, '2020-2021')"> call query 2020-2021 </v-btn>
      <v-btn color="primary" class="ma-2" @click="query(conn, '2021-2022')"> call query 2021-2022 </v-btn>
      <v-btn color="primary" class="ma-2" @click="query(conn, '2022-2023')"> call query 2022-2023 </v-btn>
      <p>{{ queryRS }}</p>
    </section>
  </div>
</template>
<script setup lang="ts">
import { useDuckDBStore } from '@/stores/DuckDB'

// access the `DuckDBStore` variable anywhere in the component
const DuckDBStore = useDuckDBStore();
const { queryRS, DBobj } = storeToRefs(DuckDBStore);
// the action can just be destructured
const { initDuckDB, APItoTblDuckDB, query } = DuckDBStore;

let db = DBobj.value.db,
      conn = DBobj.value.conn;

onMounted( async() => {
  //initDuckDB can be called from other page or onMounted or whatever the place 
  DBobj.value = await initDuckDB('SilentLogging');
  
  //Action can be called after DuckDB initialized 
  APItoTblDuckDB(
      DBobj.value.db, 
      DBobj.value.conn, 
      'http://RestAPIURL', 
      'VisitorUseData'
  ).then( async (p_res) => {
      if (p_res === 'Done') {
        console.log("\uD83E\uDD86 Data loaded into table. Ready to query.");
      }
  });
});
</script>

[ceymard]

@lpfy do you modify the contents of your database ? Because I want to perform updates and persist the changes.

In your scenario, if I understand it right, you just persist the data you're importing to duckdb, which can in effect be handled by IndexedDB, but could also be done with a ServiceWorker with Cache API.

I'm really talking about having a truly persisted database to which I do a lot of changes.

I've since then seen that sqlite-wasm does just that, with OPFS support. Some inspiration to take there to be sure !

[lpfy]

At the moment, CRUD is made via traditional RestAPI, the product I am doing is an internal dashboard for a booking system, so DuckDB is mainly for large data analysis/aggregate rather than CRUD. Once DuckDB WASM can be persisted, I would love to switch CRUD to local, then sync data between local and central DB

[ceymard]

This is precisely what I want to do, an offline first application based on DuckDB.

I could do it with SQLite, but DuckDB has much more to offer.

[ceymard]

@carlopi what is the DuckDBRuntime used for ? Only access to parquet / json / arrow data files or could it potentially be used by DuckDB to use OPFS and persist itself ?

[carlopi]

It can (and eventually should, just not yet) be used also to allow session caching mechanisms.