Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser.
Plenty of interesting databases fit into less than a MB even.
Additionally, SQLite in WebAssembly with a HTTP-Range-request based virtual file system greatly simplifies interactive visualisation of large datasets https://observablehq.com/@mjbo/sqljs-httpvfs (my own demo)
Plenty of interesting databases fit into less than a MB even.
I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser: https://simonwillison.net/2022/May/4/datasette-lite/ - here's an article showing how that can be useful: https://simonwillison.net/2022/Aug/21/scotrail/
It's also available in Observable notebooks, which is really handy. Here's a project I built on top of that: https://simonwillison.net/2022/Nov/20/tracking-mastodon/ - notebook here: https://observablehq.com/@simonw/mastodon-users-and-statuses...