Can you please explain more? How would a local sqlite database work with a typical, say a CRUD blogging app as an example? Do invidividual users have their own database in the browser?
It probably wouldn't be suitable for a blog or classic CRUD app. The use cases I've heard about are more for education and online editor/IDE.
For example: interactive documentation that runs code in the browser with an ephemeral/temporary database, possibly imported from the user's desktop. For teaching programming languages in the browser, with the compiler compiled to WASM, so the student doesn't have to set up a local development environment. Or a database explorer that's a purely static site with no server, neither remote nor directly on user's computer - just virtually in the browser, which is a more secure sandbox. You could have a folder of HTML, CSS, JS that's a full-stack application with client UI and server (theoretically).
The web is evolving. Python with SQLite running in the browser is part of the web.