编程与软件工程simonwillison.net
把 SQL 当作 API 语言
SQL as an API language
2018 — 2022，它一次次回到首页
黑点是上首页（≥ 50 分）的那次投稿，空心点是没冲上去的投稿，点越大分数越高。
导读
这是 Simon Willison 关于 Datasette 的一组设计构想中的一节，讨论把 SQL 直接当作对外 API 语言：客户端通过受控的 SQL 查询已发布的数据，而不必为每种需求新增 REST 端点。文中涉及安全、性能（查询超时）、隐藏实现细节（用视图和 canned query 暴露稳定接口）以及 keyset 分页等具体问题。评论区则围绕“这招只适合静态数据”“用 PostgreSQL + PostgREST 更完整”等展开辩论。
为什么经久不衰：它把“给数据开一个只读 SQL 接口”这件事的收益与坑一次讲透，至今仍是开放数据发布和内部数据 API 设计的经典参考。
HN 评论精选
SQL 是适用范围最广、最值得优先掌握的“底层通用语言”。
稍微切题地说：在给人不请自来的技术职业建议时，我最爱说的一句话就是“学 SQL”。没有计算机背景？从 SQL 开始。靠写编译器为生但不了解 SQL？那你当然该学 SQL。被 ORM 绑住的 Web 开发者？学 SQL。想搞懂本部门 IT 系统怎么运转的管理者？也是 SQL。如此等等。SQL 大概是当今藏在系统底层最普遍的语言。
forkandwait查看英文原文
Slightly on topic: when giving unsolicited tech career advice, my favorite trope is "learn sql". Don't have any computer background? Start with SQL. Already write compilers for living but don't know SQL? Of course you should learn sql. A www dev who is tied to ORMs? Learn SQL. A manager who wants to understand the IT that runs your department? Yep, SQL. Etc, etc. SQL is probably the most ubiquitous language under hood today.
PostgreSQL + PostgREST 能更完整地解决安全、性能和隐藏实现细节问题，且避免了 GraphQL 的额外映射负担。
用 PostgreSQL + PostgREST 可以得到更完整的方案。针对文中几个顾虑：安全（不想让人乱动数据）——行级安全加上角色/授权基本就够了；性能——SQLite 有取消超时查询的机制，但光靠超时，别人并行发起昂贵查询照样能打垮服务，更好的做法是按查询计划过滤，PostgreSQL 有 pg_plan_filter 扩展，另外它本身也有 statement_timeout；隐藏实现细节——可以只暴露一个专用 schema，里面没有表，只有视图和存储函数，靠事务性 DDL 迁移这个 schema 很容易。不过关于 GraphQL 的感想我同意：除了多一门查询语言，你还得把数据库 schema 映射到 GraphQL schema，多一层阻抗失配。
steve-chavez查看英文原文
You could get a more complete solution with PostgreSQL + PostgREST. Regarding the concerns: > Security: we don’t want people messing up our data Row level security + roles/grants should be enough to cover this one. > On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold. With a timeout, what you'll need is to request the expensive queries in parallel to ddos the service. Would be better to filter by the query plan, in PostgreSQL there's an extension for this https://github.com/pgexperts/pg_plan_filter. Though pg also has a `statement_timeout`. > Hiding implementation details You can expose a dedicated schema for you API that doesn't contain a single table but only views and stored functions. Migrating this schema should be easy thanks to transactional DDL. That being said, I agree with the sentiment about GraphQL, besides the extra query language, there's an additional impedance mismatch since now you have to map your DB schema to the GraphQL schema.
SQL 即 API 在静态数据上很好用，但对动态数据会锁死数据库重构的自由，这是主要阻碍。
注意这只适用于静态数据集，所以这个想法其实相当有限——大多数数据集要么是动态的，要么小到直接拷一份就行。我不是要否定 Datasette，它对所要解决的问题设计得很出色；我主要是在回应这次 HN 投稿的标题“SQL as an API language”，它暗示的范围恐怕超出“查询静态数据”。说实话，我很想对动态数据也把 SQL 当作 API 语言来用。我在公司（talkjs.com）就探索过把客户托管在我们这里的聊天与消息数据做成基于 SQL 的公开 API，这能一次解决大量客户请求，远比每类问题加一个 REST 资源划算。卡住我们的正是这一点：即便精心设计一组视图来保证向后兼容，也会严重限制我们做数据库重构——尤其是加冗余、反范式化之类的操作。
skrebbel查看英文原文
Note that this is solely in the context of a static data set. That makes this a relatively limited idea, since most datasets are either dynamic or small enough to just copy over. I write this not to dismiss Datasette, which looks like an excellently designed tool for the problem it's meant to tackle, but mostly as a response to the title of this particular HN submission, "SQL as an API language" which suggests maybe more than "just" querying static data. Thing is, I really want to use/expose SQL as an API language for dynamic data. E.g. the author writes: > On hidden implementation details: since we are publishing static data rather than maintaining an evolving API, we can mostly ignore this issue. If you are really worried about it you can take advantage of canned queries and SQL view definitions to expose a carefully selected forward-compatible view into your data. At my company (https://talkjs.com) we explored making our public API SQL-based for the chat & message data that our customers host with us. It would solve lots of customer requests in one go, much more so than adding a new REST resource for every kind of question a customer might want to ask our API. What's been holding us back is exactly this: Even if we'd carefully design a set of database views to guarantee backward compatibility, doing so will severely limit the kinds of database refactorings we'd be able to do. Especially when it comes to adding redundancy, denormalizing stuff, etc. If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem?
keyset 分页那一节最有价值，而可变数据上的分页仍是开放难题。
光是里面关于 keyset 分页的思考就值回票价了。我自己为可变数据笨拙地实现过这个，边做边抓狂：比如一条按每个用户个人偏好排序、可单独创建和删除的帖子流，而你没有足够内存把所有查询结果缓存起来再不可变地返回给每个用户。据我所知这仍是个未解决的问题。
zackmorris查看英文原文
Worth it just for the thoughts on keyset pagination alone: https://simonwillison.net/2018/Oct/4/datasette-ideas/#Keyset_pagination https://use-the-index-luke.com/sql/partial-results/fetch-next-page I've implemented this myself (poorly) for mutable data as I slowly began to lose my mind. For example a feed of users' posts (ordered uniquely for each user based on their personal preference) that can be individually created and deleted, and you don't have enough memory to cache all of the query results to be returned to each user immutably. As far as I can tell, this is an open problem.
历次讨论
同类还值得读
开源应用架构
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编辑精选