编程与软件工程jlongster.com
SQL 在 Web 上的未来
A future for SQL on the web
2021 — 2023，它一次次回到首页
黑点是上首页（≥ 50 分）的那次投稿，空心点是没冲上去的投稿，点越大分数越高。
导读
James Long 的文章探讨在浏览器里运行 SQL 的可能性：他在 IndexedDB 之上实现了 absurd-sql，为 sql.js（编译到 WebAssembly 的 SQLite）提供分页式持久化后端，从而绕开内存限制，并让 SQLite 在性能上反而超过直接使用 IndexedDB。文中讨论了这一方案的实现细节、性能数据与局限。
为什么经久不衰：它展示了一个反直觉却实用的思路：用 SQLite 封装浏览器存储，既获得完整的 SQL 能力，又快过底层 API，至今仍是 Web 本地数据持久化的重要参考。
HN 评论精选
曾有的浏览器 SQLite 标准因不够时髦和 NIH 心态被放弃，令人惋惜。
这让我觉得又好笑又难过。浏览器里本来就有 SQLite（WebDatabase 规范），用起来其实相当不错，却因为基于 SQL、不够时髦（不是流行的“键值”“web scale”）而被否决。官方理由是“规范就是 SQLite 的行为”，不适合作为标准；但比 SQLite 更糟的标准基础多的是。我仍然认为它被毙掉是因为不够时髦和“非我发明”。
EvanAnderson查看英文原文
This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a standard, all things considered. I still believe it was torpedoed because of lack of trendiness and "not invented here". [0] https://www.w3.org/TR/webdatabase/
File System Access API 可能让网页直接把 SQLite 文件写到磁盘，把状态外置。
James 是了不起的技术冒险家，能间接分享这类疯狂探索让地球生活更有趣。他做过 sweet.js 宏、参与推动 Firefox 开发者工具转向 React，还领导了事实上的行业标准 JS 格式化器 Prettier。提醒一下：Chrome 正在推进 File System Access API，可能消除这里几乎所有的荒诞之处，网页甚至能把 .sql 文件写到你的磁盘供其他程序读取。让应用把状态写成 sql 文件、把状态外置到浏览器之外，越多越好！
rektide查看英文原文
James is one of the world's great techno-adventurers, & getting to para-socially share in wild adventures like this makes living on Spaceship Earth more lovely & lively! James has also done cool projects like sweet.js macros, helped kick off Firefox devtool's transition to react (iirc), oh and lead the basically industry standard JS formatter Priettier project. I'm forgetting a dozen other things over the years but it's always been fun. Just a heads-up, the File System Access API[1] is underway in Chrome, which potentially removes nearly all of the absurdity here. It has other benefits too. A web page using this could write a .sql file on to your drive, that other programs could then access. One of the other bright stars in my world is Karli Koss, who has an extensive personal data-extraction setup for a ridiculously colossal variety of services & devices[2]. A vast amount of this massive massive data-gathering framework is just reading sqlite databases of the various devices and apps. If the web can help participate more actively, can let apps write sql files to store state: so much the better I say. Help externalize your state beyond the browser, please! [1] https://wicg.github.io/file-system-access/#api-filesystemwritablefilestream https://caniuse.com/native-filesystem-api [2] https://beepb00p.xyz/myinfra.html
SQLite 性能惊人，甚至可作为产品唯一持久化层并承载业务逻辑。
> SQLite 虽然建立在 IndexedDB 之上，却在每一项性能指标上都轻松胜过 IndexedDB。真够荒诞的！ 这确实不可思议，同样道理也适用于文件系统：在磁盘上追踪数百万个 1KB 对象？从同一块磁盘用 SQLite 把整个集合载入内存会快得多。开启 WAL 并设置合理的 sync 标志后，写回磁盘也一样。SQLite 是我们产品最强大的依赖，过去 5-6 年我们把它作为全部数据的唯一持久化机制，最近还开始用它的 SQL 引擎处理全部业务逻辑。
bob1029查看英文原文
> SQLite, even though it’s implemented on top of IndexedDB, easily beats out IndexedDB in every single performance metric. The absurdity! This really is quite incredible. Same idea extends to your filesystem too. Tracking millions of 1KB objects on disk? You could load the whole set into memory substantially faster from SQLite using the same disk. If WAL is enabled with reasonable sync flags, the same applies going back out to disk as well. SQLite is the most powerful dependency that our product uses today. We have been using it in production as the sole persistence mechanism for 100% of our data for the last 5-6 years now. Recently, we have started leveraging the actual SQL engine to process all of our business logic as well.
sql.js 默认很快内存耗尽，需要分页或改用 alasql 等方案。
sql.js 直接使用很难，很快就会内存耗尽。我在一个开源数据 IDE 里想用它做内存 SQL，天真的 `SELECT * FROM VALUES (...)` 方式几百行就 OOM 了。后来换到 alasql，能处理约 80MB 数据（更大数据集还没测）。我不认为这是 sql.js 的根本限制——这篇文章证明可以为它实现自定义分页——但不做分页的话它很快会耗尽内存。如果有人知道在底层更有效利用 sql.js 内存的高层库请告诉我；和 absurd-sql 不同，我不需要结果持久化，只想要内存 SQL 做连接、过滤、分组。
eatonphil查看英文原文
sql.js is pretty hard to use as is otherwise you run out of memory really quickly. I was trying to use it as the in-memory SQL flavor for an open source data ide [0] but my naive approach of `SELECT * FROM VALUES (...), ...` would run out of memory after only a few hundred rows. I ended up switching to https://github.com/agershun/alasql which could handle up to 80MB of data or so. (I haven't yet tested on larger datasets so I don't know the actual limits.) I don't think this is a fundamental limitation of sql.js as the linked article proves that you can implement custom paging for sql.js. But unless you do that (which I haven't spent the time to figure out how to do) then sql.js will run out of memory very quickly. Just something to be aware of if you're investigating it. If there's a high-level library that makes more effective use of memory with sql.js under the hood let me know. Unlike absurd-sql I don't need the results to be permanent. I just wanted an in-memory SQL for joining, filtering, grouping data. [0] https://github.com/multiprocessio/datastation
历次讨论
同类还值得读
开源应用架构
The Architecture of Open Source Applications
《开源软件架构》（AOSA）是一本在线免费书，邀请知名开源项目的作者亲自剖析其内部设计，涵盖 SQLAlchemy、LLVM、Audacity、Apache、Bash 等系统。
编程与软件工程上首页 9 次 · 2011–2024aosabook.org编辑精选编程语言简史：简短、不完整且多半错误
A Brief, Incomplete, and Mostly Wrong History of Programming Languages
一篇以戏谑、故意不准确的方式回顾编程语言史的博客文章，用夸张轶事和讽刺定义串起 Lisp、C、C++、Java、JavaScript、PHP 等语言。
编程与软件工程上首页 8 次 · 2009–2021james-iry.blogspot.comSQLite 是如何测试的
How SQLite is tested
SQLite 官方文档页，系统介绍 SQLite 的测试体系：多种测试方法（TCL 测试、模糊测试、回归测试、静态分析等）和海量测试用例，展示了这个小型开源项目如何靠极致测试获得…
编程与软件工程上首页 8 次 · 2009–2025sqlite.org编辑精选高级编程语言（2009）
Advanced programming languages (2009)
Matt Might 的经典文章，按用途推荐“高级编程语言”：如果追求就业就选主流语言，如果追求表达力、学术研究或创业，则推荐 Haskell、OCaml、Scala 等函数式语…
编程与软件工程上首页 8 次 · 2009–2022matt.might.net我的 Forth 与栈机器经历
Yosefk - My history with Forth & stack machines
作者以亲身经历回顾了使用 Forth 语言和栈式机器的历程：既赞叹其简洁优雅、代码量小、可自举的优点，也坦率指出栈操作在实际编程中的痛苦，以及 Forth 社区文化中的问题。
编程与软件工程上首页 8 次 · 2010–2024yosefk.com编辑精选