Backing Up ?vfs=memdb

[beeekind]

I'm experimenting with Golang + WASM + SQLite in the browser and I want to dump and hydrate the database using indexdb purely as a proof-of-concept. I appreciate that this library isn't designed for or advise this use case, I just think it would be cool to try it out.

Rough example:

"github.com/ncruces/go-sqlite3/driver"
_ "github.com/ncruces/go-sqlite3/embed"
_ "github.com/ncruces/go-sqlite3/vfs/memdb"

db, err = sql.Open("sqlite3", "file:/test.db?vfs=memdb")
if err != nil {
    panic(err)
}

err = conn.Raw(func(driverConn any) error {
    conn := driverConn.(driver.Conn)
    return conn.Raw().Backup("test.db", backupName)
})

Any general advice in this area would be appreciated. Thank you for creating this library and Happy New Year.

[ncruces]

Did you try this out and failed? Is this a question on what's wrong?

If so, I think one issue with your code is the name of the database in Backup(srcDB, dstURI string), it should be main for the srcDB (as you're backing up the main DB in the file:/test.db?vfs=memdb" connection) and a URI for the dstURI.

I'm not really sure what you can use to save/load from IndexedDB, though, or if that's even possible.

[beeekind]

The above code (corrected to use main) fails with CANTOPEN when running within the browser, because there is no filesystem access to open the destinationURI with. My goal is to dump the database in a binary format into IndexDB and then reload the database from that blob on program startup.

So my immediate question is: does the go-sqlite3 API provide a way to dump and load data without filesystem access?

Ultimately I need to dive deeper into Sqlite, the browser WASM runtime, WASI, and related topics. I am very far away from a production ready answer to persisting sqlite with Go and WASM in the browser. But I know companies like Notion have found an effective means to do it so I know there are answers out there.

https://www.notion.com/blog/how-we-sped-up-notion-in-the-browser-with-wasm-sqlite

So my second question becomes: in your opinion is this library an appropriate building block for pursuing this architecture?

[ncruces]

So my immediate question is: does the go-sqlite3 API provide a way to dump and load data without filesystem access?

No, it does not. But this is something I could add. Loading from a []byte can be done with memdb.Create. You can also Restore from a reader. I'd need to add backing up to a writer.

So my second question becomes: in your opinion is this library an appropriate building block for pursuing this architecture?

As I said on Reddit, this should work, but you may be disappointed with the performance.

[ncruces]

See 7dc4520.