5 ms·
Why SQLite succeeded as a database (2016)
- bob1029 4y agoI'd say SQLite entered into a new dimension of applicability once local disks got very fast. You can insert a SQLite row in ~40uS on local NVMe storage today, assuming you can be bothered to set 2 pragmas. For certain applications, a millisecond is too long to wait for a trip to the database. I've got some control loops written around SQLite that operate at 1kHz and beyond.
- unixhero 4y agoI tried and failed to read up on what pragma queries are. Would you like to elaborate? This wasn't able to inform me https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html
- carterschonwald 4y agoI believe he means build flag cpp pragmas
- warmwaffles 4y agoNo there are some PRAGMA calls that people typically don't set or bother to adjust from the defaults that really improve performance. Some are setting the `journal_mode`, `cache_spill`, and `cache_size`.
- bob1029 4y agoThese are the specific pragmas I am referring to: https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/pragma.html#pragma_journal_mode I set them to "NORMAL" and "WAL" respectively.
- gpderetta 4y ago> A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash That's a pretty big failure to preserve durability.
- zepolen 4y agoYes and the speedup is not that great, I hear piping changes to /dev/null is even faster.
- Filligree 4y agoMy computer rolling back a couple seconds on crashes is, actually, perfectly fine! Furthermore, I think most personal computer owners would agree. The near impossibility of achieving ACI semantics on modern filesystems (other than ZFS) is one of my biggest pet peeves with how data is being handled. Servers may need ACID, but practically no home computers do.
- pantsforbirds 4y agoI mean if you read the full quote: > When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode. It seems extremely reasonable to use WAL with synchronous=NORMAL
- gpderetta 4y agoYou might roll back committed transactions in case of a system crash. It might be acceptable for some applications, but I wouldn't certainly call it extremely reasonable. If you do not care about system crashes, why sync at all?
- ragebol 4y agoControl loops with SQLite no just in the loop but written around it, could you please elaborate? Sounds interesting.
- bob1029 4y agoApplication defined functions are exposed to SQL. At the beginning of each tick an entry point query is executed. Everything flows from there. Makes integrating computation and data very easy for domain experts.
- swyx 4y agoprobably one of the dark horse reasons for success is its "open source but not open contributions" model and strong personal leadership from Richard. that clarity of vision and very personal stewardship of the project (listen to him talk with pride about SQLite's aircraft certifications.. not a joke) is something very few open source projects ever have
- rlv-dan 4y agoHow does HN feel about "open source but not open contributions"? I have several applications with source on github, but I'm not really interested in contributions. I feel kind of bad about it, but they are "my" applications. The source is not secret and I don't mind people using it. That is why I publish it. Not to get help.
- jeremy_wiebe 4y agoI personally think that’s fine. There are still many benefits to an open source but no contributions model (such as others can see how you did things, it can be forked). Taking contributions requires effort on the part of the maintainer and I think it’s completely fair to want to open the code but not deal with that aspect of open source.
- docandrew 4y agoFor smaller projects that don’t obviously have a lot of contributors, I think it is good etiquette to ask the maintainers ahead of time if they’re open to PR’s before submitting one, because like you said, it takes effort to accept patches.
- jraph 4y agoWell, contributors should check if there are stated policies, but I think it's the author's duty to document that they don't take contributions and to disable the relevant features in the tools they use. A contribution is a nice thing from the contributor. As a contributor, I won't ask and then open a MR for a trivial contribution. It's too much trouble. I will ask for a bigger contribution to know if the author had related plans or their ways though. As an author, I do state that I'd rather not have medium/big contributions before we discussed about it in the project's CONTRIBUTING file.
- turbobooster 4y agoWhenever I read SQLite I think of missiles
- HPsquared 4y ago"The transaction knows where it is because it knows where it isn't..."
- ronsor 4y agoThank goodness my weapons of mass destruction won't lose data.
- DwnVoteHoneyPot 4y agoFor those downvoting, from wikipedia: D. Richard Hipp designed SQLite in the spring of 2000 while working for General Dynamics on contract with the United States Navy. Hipp was designing software used for a damage-control system aboard guided-missile destroyers.
- sph 4y agoWait until you learn who funded and designed what we now call the Internet!
- btbuildem 4y agoI didn't quite get past the nostalgia stories, but here's why SQLite succeeded as a database in my projects: I can run a single lightweight binary and I have a database. It's simple and it "just works". For 99% of projects it's enough. For those that it's not, replacing it with something more heavy-duty is easy.
- mxuribe 4y ago> ...here's why SQLite succeeded as a database in my projects: I can run a single lightweight binary and I have a database. It's simple and it "just works". For 99% of projects it's enough. For those that it's not, replacing it with something more heavy-duty is easy... Yep, that right there is exactly why i love me and use sqlite!
- teddyh 4y agoSQLite succeeded because Berkeley DB files sooner or later always become corrupted, and it doesn’t hurt that SQL is a more useful interface than a simple key-value store.
- giantrobot 4y agoThis was my path to SQLite. I used Berkeley DB which worked fine for me until it didn't. I also had to massage everything into a KV model which meant I had to implement search logic. SQLite was a nice local DB but gave me all the capability of SQL. I reduced the boilerplate I had to write in my code and I had a SQL front end I could use on my data stores.
- chasil 4y agoIt is not difficult to corrupt a SQLite database if it is not treated with proper care. https://sqlite.org/howtocorrupt.html https://sqlite.org/howtocorrupt.html
- snorkel 4y agoIt’s powerful, compact, fast, solid, and reliable … and unlike the majority of other database engines, it doesn’t include an opinionated client/server front end welded onto it
- paulryanrogers 4y agoPublic domain license is a big factor IMO.
- me551ah 4y agoI really wish that modern browsers add support for it. It’s supported virtually everywhere except for browsers
- vbezhenar 4y agoWhy do you need any special support from browsers? Just compile it to JS or Wasm and use it like any other library.
- JohnBooty 4y agoWell, I mean, with enough work you can make a key/value store (what browsers have) sort of act like a relational store, but the results aren't necessarily going to be awesome. You could brute force it and just compile SQLite to WASM, maybe, like you say? need to persist that data into IndexedDB's key/data store somehow which is probably not going to be totally awesome. There are polyfill solutions like JSStore that let you treat IndexedDB like a SQL database which would probably make more sense. That's why it would be nice (for some use cases) if SQLite was baked into browsers themselves and exposed to Javascript. We had that at one point with WebSQL, and I think Firefox ships with a non-exposed dependency on SQLite for bookmark storage anyway, so it's not too unreasonable.
- chasil 4y agoIt is actually supported in browsers. It is not a formal standard, because two implementations are required for that, and there can be only one SQLite. "Mozilla's argument against it becoming a standard was because it would codify the quirks of SQLite." https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
- laurencerowe 4y agoHowever it is in the process of being deprecated and removed: https://developer.chrome.com/blog/deprecating-web-sql/ https://developer.chrome.com/blog/deprecating-web-sql/
- adamgordonbell 4y agoOne reason it succeeded is because of Android. Once it was one a kazillion android devices a lot of the bugs got shaken out. > Richard: We were going around boasting to everybody naively that SQLite didn’t have any bugs in it, or no serious bugs, but Android definitely proved us wrong. Look, I used to think that I could write software with no bugs in it. It’s amazing how many bugs will crop up when your software suddenly gets shipped on millions of devices. https://corecursive.com/066-sqlite-with-richard-hipp/#enter-android https://corecursive.com/066-sqlite-with-richard-hipp/#enter-... All credit to Richard, but I think having customers that demand a lot of stability and performance leads projects to have a lot of stability and performance. (The original use case was running on a battleship.)
- mftb 4y ago...or it leads to projects that fail, or remain bug-riddled. Increased usage on Android was an input. The bugs had to be fixed, to get to the increased stability, and performance.
- ok_dad 4y agoYea, the real answer is that the SQLite team works hard and does a good job of prioritizing things and so people trust that software to the most important jobs.
- adamgordonbell 4y agoI agree. I wasn't trying to minimize the amazing work that was done I was just trying to make the anti-fragile argument that by overcoming all the inputs the product got very strong.
- mftb 4y agoI gotcha. Agreed.
- chasil 4y agoActually, it was Rockwell-Collins in Cedar Rapids, IA that advocated DO-178B certification. Once that certification was obtained, Android immediately benefited. "...some avionics manufacturers were expressing interest in SQLite, which prompted the SQLite developers to design TH3 to support the rigorous testing standards of DO-178B." https://sqlite.org/th3.html#history https://sqlite.org/th3.html#history
- l5870uoo9y 4y agoCan anybody give a summary? On a similar note Cloudflare's D1 database is build on SQLite.
- mamcx 4y agoIs almost alone on the hill: It don't have any major competitor. Firebase is the other only RDBMS that could ship embedded but not on iOS/android (and maybe other exotic setups). After this, good luck finding a RDBMS you can use without a server!.
- zbentley 4y agoWell, I'm not so sure about that. It's rare among embedded databases in that it supports a rich and standardized query language, but it's SQLness alone isn't sufficient for SQLite to avoid competition. There's plenty of healthy tension and switches between SQLite and e.g. RocksDB, cabinet, and LMDB, and there are plenty of other contenders as well.
- soundnote 4y agoI for one thoroughly appreciate how fresh it is for a software project to adopt the Rule of St. Benedict as their code of ethics.
- mcdonje 4y ago>Jerod Santo: ...if I want this to be a varchar 40 and you let me put anything in there, then why did I declare it to be a varchar 40 in the first place? You know what I’m saying? >Richard Hipp: Yeah, exactly. If you say it’s a varchar 40 and you an integer there, it will change it into text. Or if you have a comment that’s declared integer and you try to put text in it, it looks like an integer and it can be converted without loss. It will convert and store it as an integer. But if you try and put a blob into a short int or something, there’s no way to convert that, so it just stores the original and it gives flexibility there. Am I the only one annoyed by this? Having a var datatype that acts this way is useful, but not being able to trust other datatypes to enforce type constraints is not ideal. Or if you don't actually have a varchar 40 type, and you just have something that's text-like without other more specific constraints, then call it 'text'. Or allow strongly typed types along with var-type types. Or have a config to choose how types behave. Like, mssql differs from ANSI for NULL handling, but you can specify USE ANSI NULLS.
- Scaevolus 4y agoYou can opt into this now with STRICT tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- debugnik 4y ago> not being able to trust other datatypes to enforce type constraints is not ideal You can have more reasonable behaviour now by creating tables as "strict"[1], in which SQLite will fail if it can't perform a valid conversion. For storing values of arbitrary type it makes available a new "any" type instead. > if you don't actually have a varchar 40 type, and you just have something that's text-like without other more specific constraints, then call it 'text' It is in fact called text, it just supports aliases for common ways of specifying text fields in other DBs. However, "strict" tables to the rescue again; they also disable type names other than the native ones. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- deleted 4y ago[deleted]
- 4y ago
- rongenre 4y agoI've been having a ton of success using sqlite to analyze structured textfiles. CSV data, etc. Beforehand, I would have either used some hacked up unix tools (grep, cut, awk, uniq, sort), or if things are super complex loaded them into something like postgres - which requires a lot more overhead. Sqlite seems to be in the sweet spot where seeing tabular data within a db takes almost no work at all.
- hartator 4y agoBecause you can just copy/paste the database file?