12 ms·
Fun facts about SQLite
- pavel_lishin 2y agoI thought to myself, "surely 'insane' is a bit of hyperbole...", and, no, it's not. > This is one my favorite lore. SQLite had to default prefix from sqlite_ to etilqs_ when users started calling developers in the middle of the night
- monktastic1 2y agoIn case anyone else was mildly confused by the wording, it seems the author simply missed a few words: "had to change the default prefix from..."
- avinassh 2y agooops, I fixed the typo. Thank you!
- jitl 2y agoThe OP failed to mention that SQLite does have opt-in strict tables that enforce types, you just need to do `CREATE TABLE name (stuff TEXT) STRICT`, see https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html Like journal mode being ROLLBACK by default instead of WAL, foreign key constraints being off by default, tables being lax by default is part of SQLite’s dedication to backwards compatibility.
- avinassh 2y ago> The OP failed to mention that SQLite does have opt-in strict tables I did not, but probably I did not phrase correctly: > Strong typed columns are opt-in.
- jitl 2y agoYou write: > 18. I hate that it doesn’t have types. It’s totally YOLO: In the point where you say that you didn’t mention STRICT mode, which seems to directly address your complaint.
- avinassh 2y agoThat sentence builds up on the previously mentioned sentence about types. > It is “weakly typed”. SQLite calls it “type affinity”. Meaning you can insert whatever in a column even though you have defined a type. Strong typed columns are opt-in and then I call it > I hate that it doesn’t have types. It’s totally YOLO
- ghusbands 2y agoThat's simply self-contradictory, as written. You say that it has (opt-in) types, then say that it doesn't have types. You could say "I hate that it doesn't have types by default", but it would be even more accurate to say "I hate that it doesn't enforce types by default", since it does have types, both strict and not.
- agilob 2y ago>But, unlike most databases, SQLite has a single writer model. As opposed to Redis (mentioned one line above the quote) which also is single-threaded?
- zbentley 2y agoSingle-writer is orthogonal to single-threaded. Single threaded systems can and do allow multiple concurrent writes to proceed, e.g. via interleaving, write batching/combining, asynchronous I/O.
- agilob 2y agoOne interesting fact is that for one release of SQLite the team worked on lots of micro-optimisations that resulted in speed up of even 50% https://sqlite-users.sqlite.narkive.com/CVRvSKBs/50-faster-than-3-7-17 https://sqlite-users.sqlite.narkive.com/CVRvSKBs/50-faster-t...
- tiffanyh 2y agoThe perf visual, by release: https://www.sqlite.org/images/sschart20221116.jpg https://www.sqlite.org/images/sschart20221116.jpg The bulk of the gains happened between 2013-2017.
- didgetmaster 2y agoDid they stop thinking of performance gains as a priority after 2017? The improvements have been very weak since then. Just wondering if they ran out of low hanging fruit or if they just didn't think it was important anymore.
- 83457 2y agoMass adoption of SSDs may explain the change.
- agilob 2y agothe metric is in CPU cycles, not IOPS
- x-complexity 2y ago> Just wondering if they ran out of low hanging fruit or if they just didn't think it was important anymore. It's very much likely that the low hanging fruit's been picked clean, rather than the latter. Take for example: You're given a bog standard codebase with no performance optimizations. It can be for whatever application, library, or service you could think of. Running down the list of (increasingly not) obvious improvements: - Removing duplicate work - Multi-processing & multi-threading - (if supported) Async I/O to remove I/O blocking - Substituting data structures for more compact representations - Understanding CPU caches & increasing cache hits - (Very high effort - only as a last resort) Move to a compiled high perf language (C, Zig, Rust, etc.) - (If applicable) Eliminating pointer use in code to prevent cache misses - (If applicable) SIMD vectorization - (If applicable) GPU processing And each one of the above can only be done a certain amount of times: Once the improvement's been made, you can't gain the same boost by implementing it again exactly as before. This isn't even mentioning that there's a base amount of work that needs to be done for a given task: Adding 2 numbers together requires at least 1 add instruction in x86 assembly, and you can't have 0 instructions. What we're seeing here is that SQLite's hitting the floor: They likely can't go lower than this without a breakthrough in algorithms.
- chrismorgan 2y ago> SQLite is not open source in the legal sense, as “open source” has a specific definition and requires licenses approved by the Open Source Initiative (OSI). This is wrong, and harmfully wrong. OSI are not the arbiters of open source. Their Open Source Definition, though generally useful and accepted, is not without legitimate criticism and controversy. As for their approval, that’s a dreadful thing to rely on for any purpose; <https://writing.kemitchell.com/2019/05/05/Rely-on-OSI.html https://writing.kemitchell.com/2019/05/05/Rely-on-OSI.html> is a good description of most of what’s wrong with it (it doesn’t really get into the broken politics enough; but some of his other articles contain more), and I like its summary: “The list of OSI-approved licenses reflects OSI’s practical and political history, not any useful, consistently functional category of license terms.” As for whether SQLite is open source, well, the only reason a public domain dedication doesn’t meet the OSD is that it’s not a license. It’s more open. In a way that is legally mildly uncertain in some jurisdictions, sure, but to call it “not open source in the legal sense” is just wrong.
- mschuster91 2y agoThe thing is, in many corporate and government settings, OSI is trusted by default as "this is open source", and anything not explicitly covered by OSI will cause you a world of pain with your legal department to get an exception.
- WaxProlix 2y agoAnd yet sqlite is the most distributed database code on the planet by an order of magnitude.
- datavirtue 2y agoNo one does any due diligence on software. It's a free for all.
- sroussey 2y agoIt’s distributed in MacOS, Chrome, Firefox, etc. I think these companies do due diligence.
- hitekker 2y agoThis blog feels like karma farming. Recycled, old points in listicle format on a popular topic with questionable accuracy. This is on top of mixing in grievances the author's startup holds against Dr Richard Hipp, like "won't accept (our) outside contributions" and "not actually open source according to OSI". As far as content goes, this listicle is probably great for view counts/engagement/flamewars. I'd personally prefer deeper thinking which this blog's previous posts-- high in hype and low in technical rigor-- have not yet provided.
- avinassh 2y ago> Recycled, old points in listicle format on a popular topic with questionable accuracy. Could you please state which are inaccurate? I am happy to correct them. As for the rest of the comment, well, I don't know what to say. I am a beginner in databases and I am journaling the things I'm learning. Some of my posts might not have depth because I don't know much myself.
- 7thpower 2y agoI just wanted to chime in and say I hope you keep it up; not only learning and creating the content, but also engaging.
- avinassh 2y agothank you for your kind words!
- simplegeek 2y agoA little off topic, but just wanted to say please keep blogging. I learn from your content as I’m also a beginner in databases.
- chasil 2y agoI am fairly certain that Dr. Hipp has discussed changes in SQLite that were desired by Microsoft for integration into Windows, which came to pass via a foundation membership. I believe that this was mentioned in the video below (I am not able to verify for now): https://m.youtube.com/watch?v=Jib2AmRb_rk https://m.youtube.com/watch?v=Jib2AmRb_rk It would be interesting to see what is required for an organization to negotiate non-trivial changes in SQLite.
- chrismorgan 2y ago> SQLite does not have a Code of Conduct (CoC), rather Code of Ethics There was confusion over this, because of different usage of words. Simplifying well beyond the point of strict accuracy, a CoC is a weapon to bind and control external contributors’ behaviour, the CoE is SQLite developers declaring their intended conduct towards others. > SQLite is pronounced as “Ess-Cue-El-Lite”. This doesn’t match the quote that follows, which says “S-Q-L-ite, like a mineral”. And that’s just how one guy chooses to pronounce it… I wonder how many others do; certainly I’ve never heard it.
- dkjaudyeqooe 2y agoThere is some controversy over how to pronounce SQL with some using S-Q-L and others (like me) pronouncing it like the word "sequel". I believe the latter is the correct name for historical reasons. IBM originally named SQL 'SEQUEL' but were forced to rename it for trademark reasons. So I pronounce SQLite sequelite.
- chrismorgan 2y ago> So I pronounce SQLite sequelite. This is by far the most popular pronunciation I’ve encountered, and I think by far the most practical one too.
- tucnak 2y agoPopular in America, but not elsewhere.
- lucasoshiro 2y agoI also never heard it. In Brazil I only have heard SQL pronunced as sequel in NoSQL. SQLite is mostly pronounced S-Q-Light here, with the S and Q in Portuguese (weird, but it happens often)
- Izkata 2y ago
- drzaiusx11 2y agoI worked with Richard Hipp on a project to integrate his query engine into a custom scripting language with persistent storage for embedded devices (think c#'s linq but backed by flash storage). He was a pleasure to work with and it seemed he made a decent living just off of support contracts for projects like this. One of the few one-man-shops that really, really worked out.
- Imustaskforhelp 2y agoooh very interesting , could you share the link ? I am interested in a thing where the whole programming language / program stack and everything is stored in the memory so you can have a language where it can run from where it was paused , inspired by some comment on some other hackernews thread . I had spent some of my weekends trying it but no use
- 392 2y agoSounds like you'd be interested in flawless.dev
- drzaiusx11 2y agoWhat you're describing is exactly how early versions of PalmOS worked as they just kept parts of the OS in static ram so you always continue applications exactly how you left them.
- Tempest1981 2y agoI think I would have serious imposter syndrome. What was that like?
- drzaiusx11 2y agoI was still just a "kid" at the time (early 20s, I'm over 40 now for context) so I was just excited to be working with folks that seemed to know what they were doing (as I clearly didn't.) Every day I was excited to go to work and absorb as much as I could. Richard and the other seniors were never condescending and everyone was able to communicate the inner workings of their systems to a naive kid straight out of college. The same project (nTAG Interactive LLC) also involved Brian Silverman who famously made a Babbage style mechanical computer capable of playing tick tac toe using only tinker toys for construction while he was still attending university. He also created the original Lego "computing brick" which ran Logo, a lisp-adjacent language, via interpreter/pcode vm he wrote using HC11 ASM (and later he ported to PIC for the "Cricket" robotics controller.) That brick project spun off into what is today Lego Mindstorms. So I've had the privilege of working with some very creative and intelligent folks over the years. I had only stumbled into the opportunity by way of working as an assistant at my local university's robotics lab (to help pay for undergrad) when my professor thought I would be a good fit for the MIT Media Lab startup opening. The job was a tremendously fun time and to this day I still think of it as my "favorite" work experience. Sadly, as with most startups, ours didn't make it when funding dried up, so the fun eventually ended. To be honest, I've been involved with a number of other early stage startups (and various larger size orgs) since and none have come close to the experience. There truly isn't anything better than being surrounded by folks who are masters at their craft and are willing to help you learn.
- BobbyTables2 2y agoSQLite write locking should be the poster-child of how to incorrectly implement concurrency in the absolute worst possible way.
- LinuxAmbulance 2y agoNot familiar with database concurrency implementations, how does it get it wrong vs say, MySQL?
- tobyhinloopen 2y agoIdk, MySQL will just fail horribly in some way in my experience. I must be using it wrong hah
- ryanianian 2y agoPlease elaborate.
- ElectricalUnion 2y agoIf you have constant, multiple, concurrent writes on a non-append-only database, it is bound to perform poorly no matter what database you pick. SQLite in this case nicely points out that you probably have a major architectural issue in your application. On more productive notes: * Are you using WAL mode? * Are you using Batch inserts/updates/upserts? * Are you using `BEGIN IMMEDIATE` when you need DML? Suddenly upgrading from autocommit mode or `BEGIN DEFERRED` "DQL" transactions to `BEGIN IMMEDIATE` "DML" ones implicitly by suddenly starting DML on what used to be a sequence of DQL queries is bad on any database, but worse on SQLite;
- wolfgang42 2y ago> If you have constant, multiple, concurrent writes on a non-append-only database, it is bound to perform poorly no matter what database you pick. This is obviously incorrect, since Postgres can handle more than one simultaneous write transaction just fine. The rest of your post is accurate, but this is an intentional design decision to simplify SQLite’s implementation, not some fundamental limitation.
- allo37 2y agoAnother interesting way they make money is security: SEE costs a pretty penny. Worth it though, IME.
- hellcow 2y agoInteresting. So it's an "open core" model.
- hakcermani 2y agoBig fan of Sqlite and DRH. Wondering what the succession plan for sqlite is ? May DRH live long and prosper though !
- SigmundA 2y ago> So DRH asked the question: what if the database just worked without any server? This was an innovative idea back then. Strange my recollection of the time was file based databases were much more popular. FoxPro, Access (jet) and Dbase where all in wide use in 90's and early 2000's and ran a lot of business software using network file shares instead of a database server.
- vidarh 2y agoIndeed, you're right - there was a multitude of products like that, and linked in libraries for managing databases. That said, I'm not aware of anyone doing that with SQL before SQLite. Though I might well have missed some.
- Kwpolska 2y agoMicrosoft Access/JET: https://en.wikipedia.org/wiki/Access_Database_Engine https://en.wikipedia.org/wiki/Access_Database_Engine
- colejohnson66 2y agoAccess is indeed an RDBMS, but it did not originally support SQL.
- SigmundA 2y agoHere is an example straight out of the MS access 1.0 introduction to programming from 1992, page 100, brings back some memories: > You can also create a Dynaset variable using an SQL string instead of the name of an existing table or query: Dim db As Database, dsSomeData As Dynaset, SQL Set db = OpenDatabase("NWIND.MDB") SQL = "SELECT * FROM Employees WHERE Employees![City] = 'London';" Set dsSomeData = db.CreateDynaset(SQL) It had a nice visual builder for queries took me a while to appreciate writing them in SQL, many people never knew it was in there.
- beagle3 2y agoBorland’s Interbase. I also vaguely remember an optional SQL interface for Btrieve circa 1990, but I might be mistaken.
- red_admiral 2y agoIf you doubt that 10x coders exist, we just found some in the SQLite project.
- Svoka 2y agoCan't stop laughing from TIMMYSTAMP.
- gs17 2y agoI enjoy that SPONGEBLOB really sort of works.
- deleted 2y ago[deleted]
- threatofrain 2y agoI've been using SQLite for quick prototyping and as a log dump for later aggregation, but one thing I've found is that it's easy to accidentally want multi-writer in today's multi-service world. That's repeatedly been my biggest stumbling block, and I wouldn't be surprised if it's the foremost reason for others to "outgrow" sqlite.
- TekMol 2y agoMy latest favorite fun fact about SQLite is that it is not only my favorite SQL database, but also my favorite NoSql database. Since 2024, all my new database tables have only one column. Everything just goes into one single JSON column, which I always call "data". SELECT cities.data->>'name' city_name, countries.data->>'name' country_name FROM cities JOIN countries ON countries.data->'id' = cities.data->'country_id' I looked around a bit if some NoSql database would make this easier. But it turned out no, even now that I only use Json everywhere, SQLite is still the best tool.
- rafram 2y agoBut why?
- advisedwang 2y agoTo get the worst of both worlds
- TekMol 2y agoMultiple reasons. Let me start with one: Flexibility. I can add a new attribute the very moment I create a new row. When I want to add "not_available_before: 2026" to a car, I can do that right away. No need to alter the table and add a new column.
- rafram 2y agoThat just seems like it encourages bad practices. There’s nothing but upside to tracking your schema changes.
- MrLeap 2y agoCognitive overhead is a cost!
- TekMol 2y agoWhat do you mean by "tracking"? So far, we have not talked about tracking anything. Only about how to add a new field. I just named a downside of the columns approach: It slows things down. Being able to add add fields faster leads to faster experimentation and development.
- throw0101d 2y ago> Since SQLite is used extensively in every smartphone, and there are more than 4.0 billion (4.0e9) smartphones in active use, each holding hundreds of SQLite database files, it is seems likely that there are over one trillion (1e12) SQLite databases in active use. I find the scientific notation of the counts amusing given the sometimes different meanings of "billion" and "trillion" (especially with English as a second language): * https://en.wikipedia.org/wiki/Long_and_short_scales https://en.wikipedia.org/wiki/Long_and_short_scales * https://www.youtube.com/watch?v=C-52AI_ojyQ https://www.youtube.com/watch?v=C-52AI_ojyQ * https://www.youtube.com/watch?v=WM1FFhaWj9w https://www.youtube.com/watch?v=WM1FFhaWj9w (bonus: on French numbers)
- kerblang 2y ago> D. Richard Hipp (DRH) was building software for the USS Oscar Austin, a Navy destroyer. The existing software would just stop working whenever the server went down (this was in the 2000s). For a battleship, this was unacceptable. Maybe it's a nitpick, but a destroyer is not a battleship. The latter weren't even in service in the 2000's.
- kstrauser 2y agoAs a Navy veteran, I agree with you. As a descriptivist, I can accept someone referring to a destroyer, carrier, gator freighter, or anything else that carries lots of armament a "battle ship".
- kerblang 2y agoWarship
- avinassh 2y agoBoth the words "Battleship" and "War ship" are used by DRH to describe USS Oscar Austin, so I used the same. I did not know about distinction, so TIL!
- juneyi 2y agoi really enjoyed reading this post. people are really critical here...
- qwertox 2y ago> SQLite takes backward compatibility very seriously - All releases of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is so remarkable and reminds me of my troubles with MongoDB and specially InfluxDB. My MongoDBs are mostly still on 4.4 because of the complicated upgrade path (mostly related to the Python drivers), and InfluxDB is now officially split into 1.x and 2.x for me, where I have no plans for upgrading. And I specially will keep my hands off of 3.x because I've learned my lesson.
- JonChesterfield 2y agoThis has the nice effect that fossil, the source control system built on sqlite, will open repos one hasn't looked at for years without trouble. I suspect lots of software works very much better in practice because they chose sqlite as the storage layer somewhere in the distant past.
- ghusbands 2y agoBeing able to open decades-old repos is very much the norm for widely-used version control systems.
- andypants 2y ago> This was also changed recently in 2010 by adding WAL mode. 2010 is closer to sqlite's creation than today, not very recent
- lambdaone 2y agoWhat's the reasoning behind the 10^12 instances claim? It is based on something like 20^9 mobile devices, each with 50 apps, each with an SQLite database? Or some other calculation?
- avinassh 2y agothis is how SQLite page says: > Since SQLite is used extensively in every smartphone, and there are more than 4.0 billion (4.0e9) smartphones in active use, each holding hundreds of SQLite database files, it is seems likely that there are over one trillion (1e12) SQLite databases in active use.
- panzi 2y agoWhy is much of this in images? Images without alt text even!
- Tolexx 2y agoI like the Code of Ethics rules. It really resonates with me.
- swat535 2y agoAs a Catholic, I appreciate how Christian it is of course but I would be surprised if other people, especially atheists felt the same. I’m wondering how it hasn’t come under scrutiny yet?
- nextworddev 2y agoSQLite is probably the best client side caching solution I have found. Breeze to use for most crud scenarios.