How to enable READ_WRITE acces mode when attaching a duckdb file with DuckDBClient?

[balazskosi]

When loading a duckdb database with DuckDBClient access mode is hardcoded to be READ_ONLY:

https://github.com/observablehq/framework/blob/main/src/client/stdlib/duckdb.js#L286

Is there a way to open it in READ_WRITE mode? Also is there a way to set the search_path that persists across queries?

const db = await DuckDBClient.of(
    { base: FileAttachment('data/database.db') },
    { accessMode: DuckDBAccessMode.READ_WRITE  });

[balazskosi]

Tried to manually attach the file like this:

import { DuckDBAccessMode, DuckDBDataProtocol } from "npm:@duckdb/duckdb-wasm";

const dbFile = FileAttachment('data/database.db');
const buffer = await dbFile.arrayBuffer();
const db = await DuckDBClient.of({}, { accessMode: DuckDBAccessMode.READ_WRITE  });
await db._db.registerFileBuffer(dbFile.name, new Uint8Array(buffer));
await db.sql([`attach '${dbFile.name}' as base (READ_WRITE)`]);

now it throws:
Error: TransactionContext Error: Failed to commit: File is not opened in write mode
instead of :
Error: Invalid Input Error: Cannot execute statement of type "INSERT" on database "base" which is attached in read-only mode!

[balazskosi]

I found a solution. I copy the read only database into the memory db:

---
sql:
    base: data/database.db
---

await sql`
    use memory;
    copy from database base to memory;
    detach base`