FRESH

Hacker News

Home

How SQLite is tested

319 points by whatisabcdefgh

by bastardoperator

10 subcomments

Over a decade ago, the maintainer of SQLite gave a talk at OSCON about their testing practices. One concept that stood out to me was the power of checklists, the same tool pilots rely on before every flight.
He also mentioned Doctors Without Borders, who weren't seeing the outcomes they expected when it came to saving lives. One surprising reason? The medical teams often didn't speak the same language or even know each other's names.
The solution was simple: a pre-surgery checklist. Before any procedure, team members would state their name and role. This small ritual dramatically improved their success rates, not through better technique, but through better communication.
https://sqlite.org/src/ext/checklist/3070700/index

by dang

0 subcomment

Related. Others?
How SQLite Is Tested - https://news.ycombinator.com/item?id=38963383 - Jan 2024 (1 comment)
How SQLite Is Tested - https://news.ycombinator.com/item?id=29460240 - Dec 2021 (47 comments)
How SQLite Is Tested - https://news.ycombinator.com/item?id=11936435 - June 2016 (57 comments)
How SQLite Is Tested - https://news.ycombinator.com/item?id=9095836 - Feb 2015 (17 comments)
How SQLite is tested - https://news.ycombinator.com/item?id=6815321 - Nov 2013 (37 comments)
How SQLite is tested - https://news.ycombinator.com/item?id=4799878 - Nov 2012 (6 comments)
How SQLite is tested - https://news.ycombinator.com/item?id=4616548 - Oct 2012 (40 comments)
How SQLite Is Tested - https://news.ycombinator.com/item?id=633151 - May 2009 (28 comments)
(Reposts are fine after a year or so; links to past threads are just to satisfy extra-curious readers)

by piker

1 subcomments

Always makes me a bit envious as well as awestruck. What a joy it must be in a lot of ways to be able to grind and perfect a piece of software like this. Truly a work of craftsmanship.

by Seattle3503

2 subcomments

I love sqlite, it's a great piece of software. The website is full of useful information, rather than the slick marketing we are used to, even on open source projects.
With that said, I find it strange how the official website seems to be making its way through the HN front page piecemeal.

by marc_abonce

2 subcomments

> The TH3 test harness is a set of proprietary tests [...]
> The dbsqlfuzz engine is a proprietary fuzz tester.
It's interesting that an open-source (actually public domain) software uses some proprietary tests. It never occurred to me that this was a possibility, though in retrospective it's obviously possible as long as the tests are not part of the release.
Could this be an alternative business model for "almost-open-source" projects? Similar to open-core, but in this case the project would easy to copy (open features), hard to modify (closed tests).

by BinaryIgor

0 subcomment

No less impressive than the SQLite project itself; especially 100% branch coverage! That's really hard to pull off and especially to maintain as the development continues.

by montroser

2 subcomments

This looks so very cool, and so then all the more thought provoking that the tests themselves are closed-source, unlike the rest of the codebase. In this evolving world of rapidly improving llm coding agent productivity, the idea that the tests are more important than the implementation starts to ring true.
I was thinking about sqlite's test landscape as described here, in relation to simonw's recent writing about porting/recreating the justHTML engine from python to js via codex, nearly "automatically" with just a prompt and light steering.

by realityfactchex

0 subcomment

I was pleasantly surprised recently when planning to "upgrade" a light web app to be portable between SQLite and DuckDB, and the LLM I was working with really made the case that SQLite is better if concurrent operations were to occur.

by tzone

1 subcomments

I am surprised to see that there isn't a lot of information about performance regression testing.
Correctness testing is important but the way SQLLite is used, potential performance drops in specific code paths or specific type of queries could be really bad for apps that use it in critical paths.

by tigranbs

1 subcomments

Based on the stability track record, I was more curious about how SQLite has done the anomaly testing. Sadly, the article has just a few words about it.
Truly one of the best software products! It is used on every single device, and it is just pure rock-solid.

by dev_l1x_be

4 subcomments

What is the story with Fossil? Is it used outside of Sqlite?

by saberience

2 subcomments

They need to do better testing to stop the whole database file getting corrupted, which happened a ton to me with SQLite.

by SmartHypercube

0 subcomment

I love SQLite's quality and their docs explaining this kind of things. However, not all parts of SQLite have the same level of quality. I was very disappointed when I found bugs related to its JSON functions (and several other similar bugs related to other features):
SQLite supports a set of JSON functions that let you query and index JSON columns directly, which looks very convenient—but be careful:
1. `json('{"a/b": 1}') != json('{"a\/b": 1}')`
Although the two objects are identical in terms of JSON semantics, SQLite treats them as different.
2. `json_extract('{"a\/b": 1}', '$.a/b') is null`, `json_extract('{"\u0031":1}', '$.1') is null`, `json_extract('{"\u6211":1}', '$.我') is null`
This issue only exists in older versions of SQLite; the latest versions have fixed it.
In many cases you can't control how your JSON library escapes characters. For example, `/` doesn’t need to be escaped, but some libraries will escape it as `\/`. So this is a rather nasty pitfall, you can end up failing to match keys during extraction with seemingly no reason.

0 subcomment

by dizzy9

1 subcomments

Perhaps someone in the know can answer this: How reliable is SQLite at retaining data integrity and avoiding data corruption, compared to say, flat text files?

by nateb2022

0 subcomment

Previously: https://news.ycombinator.com/item?id=29460240

by Trung0246

0 subcomment

Interesting, TH3 is proprietary.

by rcx141

0 subcomment

... very thoroughly is the answer
What a superb piece of software SQLite is.
Install and forget.

by jadenconelly6

0 subcomment

[dead]

by twelvechess

0 subcomment

[dead]