help: SQLite extensions

[souzaramon]

Hi, thanks for your awesome job.

I'm current trying to run SQLite over https, with the vfs module.

I have found this extension
https://github.com/mlin/sqlite_web_vfs

Is possible to use this somehow with this project?

I'll appreciate anything you can talk about it

Cheers

[Prinzhorn]

1. Compile the extension
2. Compile your own better-sqlite3 with SQLITE_USE_URI=1
3. loadExtension

This should work

[Prinzhorn]

But I think it would be a lot easier to just use Node.js to download the file as Buffer and then pass that buffer to new Database(buffer) (or if it's too large write it to /tmp)

[souzaramon]

Unfortunately I can't save it to fs, cause i'm runing it on aws lambda, and the database is reaaaaly big, so i'm kinda traped in a vfs solution.

Thanks again, I will try to loadExtension, and if possible contribute with some docs
Cheers

[souzaramon]

Hello again, I'm really sorry to bother you, but I have one more question.

First of all, I actually did it!
after follow your instructions, it just work.

  const db = require('better-sqlite3')(':memory:', {});

  db.loadExtension(path.resolve(__dirname, '..', 'sqlite_web_vfs', 'build', 'web_vfs.so'));

This extension require a new connection to be open, as showed here:

import sqlite3
import urllib.parse

CHINOOK_URL = "https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"

con = sqlite3.connect(":memory:")  # just to load_extension
con.enable_load_extension(True)
con.load_extension("web_vfs")      # web_vfs.{so,dylib} in current directory
con = sqlite3.connect(
    f"file:/__web__?vfs=web&mode=ro&immutable=1&web_url={urllib.parse.quote(CHINOOK_URL)}",
    uri=True,
)
schema = list(con.execute("select type, name from sqlite_master"))
print(schema)

Since no connect method are exposed by the better-sqlite3 I'm kinda lost now

[JoshuaWise]

The "connect" method in that Python example is the same as new Database(...) in better-sqlite3.

[souzaramon]

Thanks for everything

[Prinzhorn]

cause i'm runing it on aws lambda, and the database is reaaaaly big, so i'm kinda traped in a vfs solution.

@souzaramon depending on your use case AWS Athena might be better suited than Lambda, but I'm not sure how well it works with SQLite or how feasible it is for you to dump the SQLite database in S3 in a different format (e.g. as *.csv.gz)

https://aws.amazon.com/athena/
https://github.com/dacort/athena-sqlite

[souzaramon]

Kinda hard to use athena in my specific problem, but I appreciate your recomendations