Posted by ajayvk 7 hours ago
Here are the notes I made at the time: https://simonwillison.net/2020/Jul/30/fun-binary-data-and-sq...
I built https://datasette.io/plugins/datasette-media as a plugin for serving static files from SQLite via Datasette and it works fine, but honestly I've not used it much since I built it.
A related concept is using SQLite to serve map tiles - this plugin https://datasette.io/plugins/datasette-tiles does that, using the MBTiles format which it turns out is a SQLite database full of PNGs.
If you want to experiment with SQLite to serve files you may find my "sqlite-utils insert-files" CLI tool useful for bootstrapping the database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
Your JS bundler (if you use one!) might be effectively rewriting imports anyway so it can be convenient to do the content-hash-named-files rewrites in the same place. But not everyone wants to use a bundler for that.
Clace can also run containers, where the UI is served by the container. For example, Streamlit/Gradio based apps. In that case, Clace acts like an app server and reverse proxy to the container, no file name rewrites are done.
Clace includes esbuild for being able to import npm packages as ES modules https://clace.io/docs/develop/javascript/. There is no support for any JS bundling.
One major downside I see would be backing up the database. You can do granular backups on a filesytem. Even rsync would work fine. You’d need to basically snapshot the entire thing here.
Your server on its own, whether it uses SQLite or the filesystem, cannot save you from having broken webapps during an update. Each page in the browser is a _tree_ of resources, which are fetched with separate HTTP requests and therefore not subject to your server-side transaction/atomic update system. You can change all resources transactionally on the server side but the browser can still observe a combination of old and new resources.
The common solution here is to ensure that all sub-resources of a page (javascript bundle(s), stylesheet(s), media, etc) are named (ie, in the URL) using a content hash or version. All resources (from the root HTML document down) need to refer to the specific content-hash or versioned name, so that if the browser loads version X of the root HTML document then it will load the _corresponding version_ of all sub-resources. Not only that, but if you're updating a page from version X to version Y, all the version-X sub-resources need to remain available after you start serving page version Y, until you're confident that no browser could reasonably be still loading version X of the page, otherwise you can break page version X while someone is still loading it.
This means you actually specifically don't want to put sub-resources for a page into one atomically-switched-out bundle along with the root HTML document, because if you do that you'll be removing the previous versions of sub-resources while they may still be referenced.
Also of course in some cases there might be some sub-resources (e.g., perhaps some media files) that you want to be versioned "separately" from the HTML document that contains them, so that you can update them without busting caches for all of the page/app structural elements like your javascript blobs and stylesheets and so on, and you _might_ need to take that into account in your page build system as well.
During experiments of this stuff at a big company (which saw a large portion of the web during this time):
- we saw most (>80%) of users staying on a web app ~2-3 days (most likely skewed from people leaving tabs open over the weekend).
- 95% was ~2 weeks
- 100% was about 600 day (yes, apparently we had users with a tab open for nearly 2 years)
If you are seeking 100%, you are going to be waiting a while.
: this is 100% from memory, I don't work there anymore.
I'm impressed you kept the measurements going long enough to track the 600-day users though!
I agree generally with your comment. Transactional updates prevent only one category of update related issues. There can be other issues at the app level which can cause a broken experience. Continuing to serve older versions of static content when referenced by content hash is possible, it is not currently implemented by Clace.
The main trick is upload all of the non-html changes before the html changes, so that no file is referenced before it exists. If you want to make the app as complex as possible you do a depth first search for upload. But if you value your sanity you relax the problem and have assets-first in your app.
It was hella fast how quickly we could load our assets (this was for a mobile game so only a handful of assets were not in the db). It's been neat seeing people adopt it further.
One aspect someone might also not realize is you can store near infinite metadata alongside your content, so you can always just query the database to find "similar" files. We threw so much metadata into the DB and I think in the end the pak file was 200MB, and the database was something like 20MB. Again, this was a mobile game. I think the worst thing we had happen on the client side was a single double inner join that we couldn't figure out how to reduce due to some server side complexity (we weren't able to implement the server, which was frustrating as the people we worked with were very bad™ at software development so our builds would break out of nowhere when they would change the entire backend spec without alerting us )
We also used a separate sqlite3 database for game replays so you could (post match completion) replay an entire game and see what each opponent was doing during said game. This was super nice for automated testing as well.
- File locking, concurrency, etc., are problems that SQLite solves
- Using SQLite allows drumroll querying files with SQL instead of platform-dependent fs APIs
- Using SQL queries is typesafe with Kysely https://kysely.dev/ (without the need for an ORM)
Wow, this is even better than what I've seen people do with F# type providers. Cool cool cool.
[0] https://github.com/opral/monorepo/blob/99356e577f558f4442a95...
diff --git a/packages/lix-sdk/src/query-utilities/is-in-simulated-branch.ts b/packages/lix-sdk/src/query-utilities/is-in-simulated-branch.ts
index 7d677477e..39502f245 100644
--- a/packages/lix-sdk/src/query-utilities/is-in-simulated-branch.ts
+++ b/packages/lix-sdk/src/query-utilities/is-in-simulated-branch.ts
@@ -21,10 +21,9 @@ export function isInSimulatedCurrentBranch(
// change is not in a conflict
eb("change.id", "not in", (subquery) =>
subquery.selectFrom("conflict").select("conflict.change_id").unionAll(
- // @ts-expect-error - no idea why
subquery
.selectFrom("conflict")
- .select("conflict.conflicting_change_id"),
+ .select("conflict.conflicting_change_id as change_id"),
),
),
// change is in a conflict that has not been resolved
- target the browser
- supports any file format, not just text files
- understands changes, not just versioning of files
- suited to build web apps on top
- ultimately enable 1000's of artists, designers, lawyers, civil engineers, etc. to collaborate
we are 2 years into the journey. dec 16, we'll release a public preview
Source code is available here if you wanna take a look: https://code.up8.edu/pablo/cigala
A warning: the code is not written to be pretty, it's written to be simple and effective. The initial goal being to have the whole software in a single PHP file that could even be used offline to produce a fully static website.
Used to be hackily done by overwriting symlinks, but now there's https://manpages.debian.org/testing/manpages-dev/renameat2.2...
> The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic ... The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time. See also: https://news.ycombinator.com/item?id=33975635
A friend of mine in astronomy once observed that a lot of people in the sciences should get comfortable working with databases because otherwise they just end up inadvertently spending a huge amount of effort rolling their own really crappy database.
Because file system excels at handling files. Need an atomic update? Checkout into a new directory and switch a symlink.
I've seen various versions of using a database as a filesystem. They have a nice side to them and then a nightmare side to them when the shit hits the fan.
1. I should make blog engine using flat files. That way I can diff the plain text.
2. But flat files are hard to read/write structured data from, so I should use SQLite instead.
3. But SQLite is hard to diff. GOTO 1.
diff <(sqlite3 db 'select text from posts where id = 1') <(sqlite3 db 'select text from posts where id = 2')
There's no reason you can't do the same but including the content too.
That way you're always committing a text friendly version of the database rather than the database binary encoding. And your diffs will work great.