4 ms·

It seems SQLite could be evolve to solve this by just bundling itself entirely in the data files? After all, the binary is less than 1MB, anywhere you’re puttin

It seems SQLite could be evolve to solve this by just bundling itself entirely in the data files? After all, the binary is less than 1MB, anywhere you’re putting a database surely has at least that much overhead, for most applications it’s less than a drop in the bucket. I’d be interested to learn if there are any db implementations that take this approach, or reasons this wouldn’t work.

mort96 3mo ago
Well you'd have the problem that an sqlite database file created on a Linux AMD64 box could be copied to an AArch64 macOS machine to be read there. And quite a lot of (cross platform) software build their file formats on top of SQLite.
- appplication 3mo ago
  I think my silly and unserious naive response would be linear scaling bundled binaries with number of platforms doesn’t seem to be that materially different in terms of total size. But I see your point, didn’t consider the architectures
  - afiori 3mo ago
    it would be sorta enough to bundle the wasm binary
    - Certhas 3mo ago
      That's the idea behind the future file format: https://github.com/future-file-format/F3 https://github.com/future-file-format/F3 Bundle a wasm decoder as a fallback when native decoder isn't available.
      - mort96 3mo ago
        The issue then is that instead of just bundling a tiny 1MB sqlite library with your program, you have to bundle a huge WASM runtime. I looked at the size of wasm on my system just to get an idea and it's 46MB; that's huge. For context, I have a game I'm working on where I build stand-alone builds with all dependencies bundled. The app bundle is 14MB right now. If I added sqlite, it would grow to 15MB. If I added sqlite-but-with-wasm, it'd more than quadruple in size to 60MB.
        mort96 3mo ago
        Ugh I can't edit now but just noticed I should've said I looked at the size of wasmtime, not "the size of wasm".
- andai 3mo ago
  Perhaps it could be an "actually portable executable"? https://news.ycombinator.com/item?id=26273960 https://news.ycombinator.com/item?id=26273960
  - eesmith 3mo ago
    FWIW, that's existed for a while. Source at https://github.com/jart/cosmopolitan/tree/master/third_party/sqlite3 https://github.com/jart/cosmopolitan/tree/master/third_party... with downloadable binary at https://cosmo.zip/pub/cosmos/bin/sqlite3 https://cosmo.zip/pub/cosmos/bin/sqlite3 . % curl -LO 'https://cosmo.zip/pub/cosmos/bin/sqlite3' % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 4845k 100 4845k 0 0 1384k 0 0:00:03 0:00:03 --:--:-- 1384k % chmod +x sqlite3 % file sqlite3 sqlite3: DOS/MBR boot sector; partition 1 : ID=0x7f, active, start-CHS (0x0,0,1), end-CHS (0x3ff,255,63), startsector 0, 4294967295 sectors % ./sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>:
arijun 3mo ago
So for every new SQLite db you want to access would have to run a new untrusted executable? That seems… hilariously bad for security.
- appplication 3mo ago
  Fair point! Was mostly an intrusive technical thought
  - anitil 3mo ago
    I mean.... it does sound like a fun project
anitil 3mo ago
There is a way to do this with sqlite in readonly mode using the append vfs [0] which allows you to put your DB appended to the end of the executable, and then the executable can read it (like a zip file the header is then at the end of the db rather than the front). Or you could embed a normal db as a binary blob with the linker. Allowing writes is more tricky because most OSes don't typically allow executables to edit their own executable memory (on unix I believe executables always load as r/x or r/o depending on the region, maybe with some minor exceptions). And a lot of users of sqlite statically link sqlite within their executable, they actually recommend that instead of dynamically linking. [0] https://sqlite.org/vfs.html https://sqlite.org/vfs.html ctrl-f for 'append' Edit: I got confused between the sqlar command and the append vfs so fixed it.

mort96 3mo ago

Well you'd have the problem that an sqlite database file created on a Linux AMD64 box could be copied to an AArch64 macOS machine to be read there. And quite a lot of (cross platform) software build their file formats on top of SQLite.

appplication 3mo ago
I think my silly and unserious naive response would be linear scaling bundled binaries with number of platforms doesn’t seem to be that materially different in terms of total size. But I see your point, didn’t consider the architectures
- afiori 3mo ago
  it would be sorta enough to bundle the wasm binary
  - Certhas 3mo ago
    That's the idea behind the future file format: https://github.com/future-file-format/F3 https://github.com/future-file-format/F3 Bundle a wasm decoder as a fallback when native decoder isn't available.
    - mort96 3mo ago
      The issue then is that instead of just bundling a tiny 1MB sqlite library with your program, you have to bundle a huge WASM runtime. I looked at the size of wasm on my system just to get an idea and it's 46MB; that's huge. For context, I have a game I'm working on where I build stand-alone builds with all dependencies bundled. The app bundle is 14MB right now. If I added sqlite, it would grow to 15MB. If I added sqlite-but-with-wasm, it'd more than quadruple in size to 60MB.
      - mort96 3mo ago
        Ugh I can't edit now but just noticed I should've said I looked at the size of wasmtime, not "the size of wasm".
andai 3mo ago
Perhaps it could be an "actually portable executable"? https://news.ycombinator.com/item?id=26273960 https://news.ycombinator.com/item?id=26273960
- eesmith 3mo ago
  FWIW, that's existed for a while. Source at https://github.com/jart/cosmopolitan/tree/master/third_party/sqlite3 https://github.com/jart/cosmopolitan/tree/master/third_party... with downloadable binary at https://cosmo.zip/pub/cosmos/bin/sqlite3 https://cosmo.zip/pub/cosmos/bin/sqlite3 . % curl -LO 'https://cosmo.zip/pub/cosmos/bin/sqlite3' % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 4845k 100 4845k 0 0 1384k 0 0:00:03 0:00:03 --:--:-- 1384k % chmod +x sqlite3 % file sqlite3 sqlite3: DOS/MBR boot sector; partition 1 : ID=0x7f, active, start-CHS (0x0,0,1), end-CHS (0x3ff,255,63), startsector 0, 4294967295 sectors % ./sqlite3 SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>:

arijun 3mo ago

So for every new SQLite db you want to access would have to run a new untrusted executable? That seems… hilariously bad for security.

appplication 3mo ago
Fair point! Was mostly an intrusive technical thought
- anitil 3mo ago
  I mean.... it does sound like a fun project

anitil 3mo ago

There is a way to do this with sqlite in readonly mode using the append vfs [0] which allows you to put your DB appended to the end of the executable, and then the executable can read it (like a zip file the header is then at the end of the db rather than the front). Or you could embed a normal db as a binary blob with the linker. Allowing writes is more tricky because most OSes don't typically allow executables to edit their own executable memory (on unix I believe executables always load as r/x or r/o depending on the region, maybe with some minor exceptions). And a lot of users of sqlite statically link sqlite within their executable, they actually recommend that instead of dynamically linking. [0] https://sqlite.org/vfs.html https://sqlite.org/vfs.html ctrl-f for 'append' Edit: I got confused between the sqlar command and the append vfs so fixed it.