编程与软件工程tech.marksblogg.com
SQLite 极简指南
A Minimalist Guide to SQLite
2017 — 2021，它一次次回到首页
黑点是上首页（≥ 50 分）的那次投稿，空心点是没冲上去的投稿，点越大分数越高。
导读
这是 Mark Litwintschik 撰写的 SQLite 极简入门指南，介绍 SQLite 的基本用法和实用特性，包括 CSV 导入、JSON1 扩展等功能。文章在 2017 年和 2021 年两次登上 Hacker News 首页，是学习 SQLite 的经典教程。
为什么经久不衰：SQLite 是应用最广泛的数据库之一，这篇简洁的指南覆盖了日常最常用的功能，多年后仍是快速上手的好参考。
HN 评论精选
SQLite 的默认设置不佳，几个重要 PRAGMA 需手动开启
我很喜欢 SQLite（在我们的应用栈中大量使用），但它的默认设置很糟糕：外键约束（及级联删除）默认关闭，需要 PRAGMA foreign_keys = ON；WAL 日志模式几乎没有缺点且有很多好处（至少对本地磁盘数据库而言），不开的话读写会冲突，多线程环境下是个问题，但也得手动开启 PRAGMA journal_mode = WAL；完全自动 vacuum 在开始写入后无法启用，除非先开了增量 auto-vacuum，所以最好一开始就设 PRAGMA auto_vacuum = INCREMENTAL。当你用命令行对应用数据库做临时查询时，忘记设置这些 PRAGMA 就会出问题。我希望数据库文件能内置默认 PRAGMA。
krallin查看英文原文
I love SQLite (and use it in a large number of places in our application stack!), but I feel that it suffers from a case of bad defaults. To name a few: - Foreign key checking (and cascading deletion for that matter) are turned off by default. You need to enable them using `PRAGMA foreign_keys = ON;`. - There are practically no downsides (and a number of upsides) to using the WAL journalling mode (at least for "use a local database and store it on the disk" use cases). The main one being that reads will conflict with writes if you don't enable it! (which is a problem in a multi-threaded environment) Unfortunately, that's another feature you must remember to enable: `PRAGMA journal_mode = WAL;` (for obvious reasons, this one "stays enabled" after you turn it on). - Full auto-vacuum cannot be enabled after you start writing to the database unless you enabled incremental auto-vacuum. If you're unsure, it's a good idea to enable incremental auto-vacuum to keep that option open. But, here again, that's not the default: you need `PRAGMA auto_vacuum = INCREMENTAL`. This tends to be explicitly problematic if you have to perform one or more ad-hoc queries using the SQLite command line on an app's database, and forget to apply the relevant PRAGMAs! I wish there was a way to add "default" PRAGMAs on a sqlite database file to avoid this. (note: some of these defaults are configurable when compiling sqlite from scratch, but if you're dynamically linking with an OS-provided instance of the library, you can't really do that).
SQLite 是性能、可靠性和简洁性的典范
SQLite 是我开发者生涯中用过的最好的软件之一。它高性能、可靠、简单且行为一致。sqlite3 能部署在这么多地方是有原因的。
echlebek查看英文原文
SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.
用 SQLite 的 JSON1 扩展查询 JSON 数据非常方便
SQLite 很适合作为 JSON 数据存储。我们从不同来源接收实时 JSON 数据，先存入 MongoDB 但查询不便，于是把数据导入 SQLite，用 JSON1 扩展就能直接跑 SQL 查询，比如用 json_extract 提取字段并按时间排序。
ilitirit查看英文原文
Sqlite is great as data store for storing JSON. We receive realtime data from different sources in JSON format. I dump the raw data into MongoDB, but it's still not very easy to query. This is where Sqlite comes in handy. I import the data straight into Sqlite from MongoDB which then enables me to run SQL queries using the JSON1 extension. Currently I'm using it to store data that drive feed simulators. We can extract the data using queries like: SELECT json_extract(RawData, '$.sourceId') SourceId , json_extract(RawData, '$.messageId') MessageId , json_extract(RawData, '$.message') Message , json_extract(RawData, '$.message.parties[0].name') Party , json_extract(RawData, '$.timestamp') Timestamp FROM RawFeed JOIN Source ON SourceId = Source.Id WHERE Source.Name = 'Foo Company' ORDER BY Timestamp
CSV 导入让快速原型化数据库变得简单
如果只是想从杂乱的 CSV 数据快速原型化一个数据库，CSV 导入功能非常顺手——不用操心类型，直接用 SQL 处理，之后如果需要也可以导出到 Postgres 等更合适的数据库。
jamal-kumar查看英文原文
The feature to load csv is pretty handy if you just need to figure out how to prototype a database from jumbled CSV data without worrying about types and then using SQL instead of messing around too hard on scripts to merge things into a third normal form. From there, export to something like postgres can happen if that's more suited to your use case.
历次讨论
同类还值得读
开源应用架构
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编辑精选