3 ms·

In 2024, not using SQLite as your application file format, should really force an engineering team to have a damn good explanation.... https://www.sqlite.org/f

by belter 2y ago

In 2024, not using SQLite as your application file format, should really force an engineering team to have a damn good explanation.... https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html

throwup238 2y ago
> The United States Library of Congress recognizes SQLite as a recommended storage format for preservation of digital content. That’s one hell of an endorsement.
threatofrain 2y ago
Is SQLite the best solution for on-device logs?
ecuaflo 2y ago
unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb
- sgbeal 2y ago
  > unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb That argument implies that we're stuck with the browser's single built-in home page because every other web page has to be downloaded. How is having to download sqlite3.wasm any different from having to download HTML, CSS, JS, images, etc.?
  - w0m 2y ago
    > How is having to download sqlite3.wasm any different from 'unnecessary' file size. K matter on the web still. I'm not normally a web guy, but from https://sqlite.org/download.html https://sqlite.org/download.html it looks like it's ~800k extra added to initial page load? Based on average mobile speed in US of 97.09Mbps; that's an extra half second added to initial page load. That's not trivial; maybe an extra 15% bounce rate on initial hits.
    - sgbeal 2y ago
      > ~800k extra added to initial page load? If you're serving it uncompressed, which no production-grade site will (for a given definition of "no"/"none"). > K matter on the web still. Not, i opine, for the types of apps which want to host client-side databases. These are client-side applications, not "web pages." Even a bare-bones, database-less google.com is now 3.14mb uncompressed (1.32 compressed), and that's not counting the pieces which uBlock Origin keep from loading. It loads somewhere around 2MB (uncompressed) of JS. Last i checked, gdrive downloaded some 14mb to get up and running. > that's an extra half second added to initial page load. That's not trivial; We'll have to agree to disagree on whether half a second extra initial-hit-only load time is trivial.
      - fwip 2y ago
        > If you're serving it uncompressed That 847 KB is actually the size of the (compressed) zip file, but it does contain other files. The compressed WASM + the JS to load it is probably about 500K, depending on the specifics of compression & minification.
    - Digit-Al 2y ago
      Makes me laugh. When I got my first computer in 1987 it took so long to load a small game from cassette that I could go downstairs, boil a kettle, make a cuppa, and bring it back to my room, and the game would be just about finished loading. Now people are moaning about an extra half a second lol How things change.
      - paulddraper 2y ago
        No, the narrative here is that things used to be fast and idiot JS devs have made everything bloated and slow.
        deleted 2y ago
        [deleted]
        andrepd 2y ago
- iansinnott 2y ago
  It's too bad WebSQL never took off, as that was sqlite built in to the browser. From memory the wasm binary of wa-sqlite is ~1mb, which is certainly not nothing but is an acceptable one-time download size for many web apps. I've seen websites with single image files larger than that. Not an advocating for more bloated web apps, but a sqlite download might not be a deal breaker.
fauigerzigerk 2y ago
Those journal/wal files create a number of potential issues for user visible application files. E.g. a user could copy a database file into a directory that already has an old journal file, corrupting the database [1]. Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc. These issues can be worked around, but I wonder how many apps that use SQLite actually bother doing that. Clearly not all of them [2]. Avoiding file corruption is non-trivial regardless of file format, but giving users additional ways to corrupt their data is never a good thing. So I think for small, user visible application files that don't need any database functionality, the onus is still on engineers to justify their choice of SQLite. [1] https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html [2] https://forum.audacityteam.org/t/aup3-wal-file-remains-and-leads-to-corrupt-aup3/63309/7 https://forum.audacityteam.org/t/aup3-wal-file-remains-and-l...
- wil421 2y ago
  >Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc. This reminds me of a college roommate who was dissatisfied with his computer, likely due to malware from sketchy sites. He decided to delete large or suspicious files from window’s system32 folder. By suspicious I mean he didn’t like the file name. He had to get a new computer later in the semester.
  - macintux 2y ago
    Decades ago I troubleshot a Solaris server that wouldn’t boot, and discovered the kernel file was missing. I suspect someone had tried to deal with a too-small root partition by removing large files.
- eli 2y ago
  You could simply turn off WAL for a simple application database, if you were really worried. You lose some performance and scale, but it should work fine.
eternityforest 2y ago
There's one big obvious one. Git and SyncThing. If the user could benefit from either of those, atomically updated plain files are great. Otherwise, SQLite is perfect. Of course there's always workarounds to use SQLite in memory and dump to SQL code and such.

by belter 2y ago

throwup238 2y ago

> The United States Library of Congress recognizes SQLite as a recommended storage format for preservation of digital content. That’s one hell of an endorsement.

threatofrain 2y ago

Is SQLite the best solution for on-device logs?

ecuaflo 2y ago

unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb

sgbeal 2y ago
> unfortunately in the browser it needs to be downloaded so you’re stuck with indexeddb That argument implies that we're stuck with the browser's single built-in home page because every other web page has to be downloaded. How is having to download sqlite3.wasm any different from having to download HTML, CSS, JS, images, etc.?
- w0m 2y ago
  > How is having to download sqlite3.wasm any different from 'unnecessary' file size. K matter on the web still. I'm not normally a web guy, but from https://sqlite.org/download.html https://sqlite.org/download.html it looks like it's ~800k extra added to initial page load? Based on average mobile speed in US of 97.09Mbps; that's an extra half second added to initial page load. That's not trivial; maybe an extra 15% bounce rate on initial hits.
  - sgbeal 2y ago
    > ~800k extra added to initial page load? If you're serving it uncompressed, which no production-grade site will (for a given definition of "no"/"none"). > K matter on the web still. Not, i opine, for the types of apps which want to host client-side databases. These are client-side applications, not "web pages." Even a bare-bones, database-less google.com is now 3.14mb uncompressed (1.32 compressed), and that's not counting the pieces which uBlock Origin keep from loading. It loads somewhere around 2MB (uncompressed) of JS. Last i checked, gdrive downloaded some 14mb to get up and running. > that's an extra half second added to initial page load. That's not trivial; We'll have to agree to disagree on whether half a second extra initial-hit-only load time is trivial.
    - fwip 2y ago
      > If you're serving it uncompressed That 847 KB is actually the size of the (compressed) zip file, but it does contain other files. The compressed WASM + the JS to load it is probably about 500K, depending on the specifics of compression & minification.
  - Digit-Al 2y ago
    Makes me laugh. When I got my first computer in 1987 it took so long to load a small game from cassette that I could go downstairs, boil a kettle, make a cuppa, and bring it back to my room, and the game would be just about finished loading. Now people are moaning about an extra half a second lol How things change.
    - paulddraper 2y ago
      No, the narrative here is that things used to be fast and idiot JS devs have made everything bloated and slow.
      - deleted 2y ago
        [deleted]
      - andrepd 2y ago
iansinnott 2y ago
It's too bad WebSQL never took off, as that was sqlite built in to the browser. From memory the wasm binary of wa-sqlite is ~1mb, which is certainly not nothing but is an acceptable one-time download size for many web apps. I've seen websites with single image files larger than that. Not an advocating for more bloated web apps, but a sqlite download might not be a deal breaker.

fauigerzigerk 2y ago

Those journal/wal files create a number of potential issues for user visible application files. E.g. a user could copy a database file into a directory that already has an old journal file, corrupting the database [1]. Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc. These issues can be worked around, but I wonder how many apps that use SQLite actually bother doing that. Clearly not all of them [2]. Avoiding file corruption is non-trivial regardless of file format, but giving users additional ways to corrupt their data is never a good thing. So I think for small, user visible application files that don't need any database functionality, the onus is still on engineers to justify their choice of SQLite. [1] https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html [2] https://forum.audacityteam.org/t/aup3-wal-file-remains-and-leads-to-corrupt-aup3/63309/7 https://forum.audacityteam.org/t/aup3-wal-file-remains-and-l...

wil421 2y ago
>Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc. This reminds me of a college roommate who was dissatisfied with his computer, likely due to malware from sketchy sites. He decided to delete large or suspicious files from window’s system32 folder. By suspicious I mean he didn’t like the file name. He had to get a new computer later in the semester.
- macintux 2y ago
  Decades ago I troubleshot a Solaris server that wouldn’t boot, and discovered the kernel file was missing. I suspect someone had tried to deal with a too-small root partition by removing large files.
eli 2y ago
You could simply turn off WAL for a simple application database, if you were really worried. You lose some performance and scale, but it should work fine.

eternityforest 2y ago

There's one big obvious one. Git and SyncThing. If the user could benefit from either of those, atomically updated plain files are great. Otherwise, SQLite is perfect. Of course there's always workarounds to use SQLite in memory and dump to SQL code and such.