The lesson of the Web is that when you expose some interface for people to use, they will start depending on documented features, undocumented features, and downright bugs of the first popular implementation. Second and subsequent implementations will need to spend a bunch of time reverse-engineering those bugs so they can be documented and reliably implemented in future so that existing websites keep working.
The only alternative is to make sure all new interfaces have multiple, popular implementations simultaneously, so that authors can't afford to take advantage of bugs in one implementation. This is the sort of activity you see in WHATWG these days.
The problem with "a SQL dialect" being available for use on the Web is that every browser intended to use SQLite as the backend. Nobody wanted to invest the time and effort to write a second, compatible, equally-reliable implementation; nobody wanted to exhaustively research and document bugs and flaws in that specific version of SQLite; nobody wanted to commit to back-porting security fixes from later versions, or forward-porting the required bugs to later versions.
And since nobody wanted to do the work to make it possible, using SQL for local storage remains impossible.
The W3C attempted to say version X.Y.Z of SQLite will be the SQL standard for the web, mandating 1) that it be frozen in time 2) that the only way to be compliant was to put SQLite into all browsers, bit for bit, there would be no other way to be compliant.
As much as I wanted it, and was bummed that Mozilla protested so much, they were/did/are doing the right thing by saying that SQLite cannot be used as a standard, it isn't a spec.
What one can do, is compile SQLite to emscripten or wasm, or write a SQL engine in JS and use that in the browser. That is totally fine.
As someone who works on a product which compiles SQLite to JS to read a SQLite based file format in the browser - this is a terrible, terrible “solution” and doesn’t scale for files larger than X MB, where X is the device dependent limit based on memory available to the browser.
The only alternative is to make sure all new interfaces have multiple, popular implementations simultaneously, so that authors can't afford to take advantage of bugs in one implementation. This is the sort of activity you see in WHATWG these days.
The problem with "a SQL dialect" being available for use on the Web is that every browser intended to use SQLite as the backend. Nobody wanted to invest the time and effort to write a second, compatible, equally-reliable implementation; nobody wanted to exhaustively research and document bugs and flaws in that specific version of SQLite; nobody wanted to commit to back-porting security fixes from later versions, or forward-porting the required bugs to later versions.
And since nobody wanted to do the work to make it possible, using SQL for local storage remains impossible.