6 ms·
SQLite Turns 20
- sargun 6y agoSQLite is a much younger project than I thought it was. Given the ubiquity, and capabilities that it provides, it's impressive. On the other hand, Linux and Python are 29 years old. Postgres, in its current form is ~23 years old. It started in the 1980s.
- lstamour 6y agoI’m also surprised — it feels both like a long time and yet not that long at all. SQLite felt like it had been around forever when Rails adopted it to make development environments simpler, and I remember finding it easy to use when writing iPhone apps for the first time. It was remarkably stable at an early age, it seems. I still think it’s a shame we never got SQLite into the browser because every browser implemented the same backend and therefore “it wasn’t a standard” — https://www.w3.org/TR/webdatabase/ https://www.w3.org/TR/webdatabase/ (note the box in red) Chrome still supports it, but not in “modern” places like web workers: https://caniuse.com/#feat=sql-storage https://caniuse.com/#feat=sql-storage
- justinclift 6y agoTypo. 33 not 23.
- zoomablemind 6y agoWhat amazes me the most, it's a work by mostly 2-4 long-standing developers, with about 40 contributors through the history. On the other hand SQLite is being used by thousands of devs and likely billions of users (mobile at least).
- qubex 6y agoTo be honest I’m quite surprised it’s already 20 years old. The first time I ever encountered SQLite was when poking through what made the very first iPhone tick, “way back” in 2007 (a whopping 13 years ago). Before then it had never occurred to me that one might use a database-as-an-executable as the generic information storage back-end for pretty much any application... I thought of SQL and databases as enterprise-type deployments and thought of applications as having custom data formats. Looking back, Wikipedia tells me that Apple had made CoreData available with Mac OS X 10.4 ‘Tiger’, released in mid-2005, so I wasn’t exactly up-to-date at the time... but still, it feels like a remarkably modern development. And an impressive one in any timeframe. EDIT: Grammar.
- whalesalad 6y agoI always love reading the comments section of an SQLite post to hear about all of the incredibly unique ways people are using it.
- melq 6y agoCool to see a well-loved, gold standard of an open source project reach a milestone like this. That said, the few cherry picked commits on that page were not particularly interesting (atleast to me, not a db expert by any means), and the color coding is inscrutable.
- zoomablemind 6y agoIt's hard to guess what's sort of curiosity was leading you, but there's a mirror (unofficial, yet reasonably current) on GitHub.
- melq 6y agoI didn't understand your reply at first, and then it dawned on me that the linked page wasn't handwritten to celebrate the milestone (which is what i thought at first), but rather just today's version of a programmatically generated "today in history" type page. Ignore my misguided complaints :)
- ekvintroj 6y agoWhat a noble piece of software.
- me551ah 6y agoWhere can you use SQLite? Embedded Systems: Yes Raspberry Pi : Yes Mobile Apps. : Yes Desktop Apps : Yes Browsers : No Servers : Yes Supercomputers : Yes
- mcpherrinm 6y agoWebSQL was an attempt at supporting sqlite in the browser. Now, you can use it via wasm!
- tehbeard 6y agoNo it was an attempt at supporting SQL in the browser. It fell through because everyone choose SQLite as the implementation, and having one implementation means it's quirks would be enshrined as the standard. Really speaks to SQLite's quality that major vendors would just reach for it without considering making their own.
- bouk 6y agoBrowsers: yes https://github.com/sql-js/sql.js https://github.com/sql-js/sql.js
- me551ah 6y agoIt's a sqlite browser port which doesn't persist data. I think that's kind of essential for a database.
- lsb 6y agoSure it does! You can `POST` the buffer that backs the database to your favorite blob store, and read it back when you're done. You can write to it as much as you like when the page is active.
- me551ah 6y agoSo every time somebody loads up your website, you read a text blob, initialize a new database and populate it?
- nus07 6y agoI heard the creator Richard Hipp speak at a conference a few years back and I absolutely loved listening to him . He is humble and seems a bit of a throwback compared to Silicon Valley tech moguls . May the genius and simplicity of SQLite shine on .
- mercer 6y agoI quite liked The Changelog's interview with him too: https://changelog.com/podcast/201 https://changelog.com/podcast/201
- deepspace 6y agoI use SQLite frequently, often without thinking about it, but every once in a while I stand back and reflect on what an amazing piece of software it is. An RDBMS without a standalone database engine, delivering rock-solid performance, through exceptional code quality. It scales unexpectedly well with large data sets, and the performance is way beyond what you would expect from a library (as opposed to a 'real' database). Hats off to the developers for creating and maintaining this amazing software.
- ashtonkem 6y agoThe code quality of SQLite is often understated to a hilarious degree. The project has four independent test harnesses totally 644x as many lines of test code as there is actual code, with the final test count in the millions. This includes the things most of us do, fuzz tests, regression tests, performance tests, as well as out of memory tests, IO failure tests, power loss tests, and more. Of course, it’s also easy to forget that the original application of SQLite was guiding missiles, so it has to work. Well, at least from the operator’s perspective.
- therealdrag0 6y agoI recently learned that Expensify build a database server (BedrockDB) on top of SQLite. At first I was like "that's absurd!" but after reading a bit more and realizing SQLite's "library" nature could just be a building block, it seems less absurd. Pretty impressive it's so flexible.
- person_of_color 6y agoI believe the SQLite license is one of the greatest contributions in technology. Hope to see it on something game changing one day.
- brycelarkin 6y agoI think the iPhone uses SQLite.
- jannes 6y agoIt does. It's an implementation detail below Core Data. I'm not sure if you are allowed to send raw queries to it. I believe that app developers who want to interface directly with SQLite have to use something like FMDB. https://github.com/ccgus/fmdb https://github.com/ccgus/fmdb
- DenseComet 6y agoThey have pages on this. https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
- sterlind 6y agoI love how they have a cute logo for the Flame malware nestled between Facebook and GE.
- oefrha 6y agoOne of the notable aspects of SQLite as a open source project is being open source but not open-contribution. drh put it in public domain, and doesn’t accept outside contributions to prevent it from being contaminated in any way. In an open source landscape where bazaar seems to have mostly won, SQLite remains one of the (last?) cathedral strongholds, and serves as a good reminder to aggressive contributors that open source maintainers don’t have to accept or even consider their PRs. https://sqlite.org/copyright.html https://sqlite.org/copyright.html
- sjwright 6y agoHas there been much agitation for (or serious attempts at) forking SQLite? That's probably the real test of the stewardship of an open source project.
- justinclift 6y agoNothing springs to mind, as of yet. However, with the very latest release series (3.32.x), one of the pieces ("SQLITE_HAS_CODEC") used by third party encryption libraries (eg SQLCipher, various others) was removed without notice. https://sqlite.org/forum/forumpost/a48e4672da https://sqlite.org/forum/forumpost/a48e4672da There does seem to be a way forward, via reimplementing the encryption as a SQLite VFS. If that didn't exist though, then some kind of forking would be on the cards.
- iveqy 6y agoI believe it's the same with lua
- Cyph0n 6y agoTurns out the company that employs all SQLite devs is based in NC! I wonder if the devs are based here, too.
- asveikau 6y agoThe primary author is: https://en.wikipedia.org/wiki/D._Richard_Hipp https://en.wikipedia.org/wiki/D._Richard_Hipp If you hear him on a podcast or speaking engagement he's also got the accent to match.
- shirshak55 6y agoWhile doing small project sqlite is a very good tool. Thanks to sqlite team for making such a great software.
- ardit33 6y agoHas anybody used Sqlite in a server side, production level capacity.... I know it works great on embeded cases, but how about server side with a decent user load?
- sk0g 6y agoSQLite is for use cases where a dedicated DB is overkill/impossible. Considering the limitations it has, it probably isn't usable for most production DBs.
- marvel_boy 6y agoNot true. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- sk0g 6y agoSome of the limitations make it a no-go for me at least [0] [0] https://www.sqlite.org/omitted.html https://www.sqlite.org/omitted.html
- sterlind 6y agoI was about to ask what the use-case would be, since I assumed server SQL databases would use Paxos or Raft or something, but apparently I was wrong! I guess durability is done through persisted volumes, storing tables in files and being deliberate about journal structure and fsync? In that kind of world I guess sqlite is as good a choice as any if you don't need a SQL endpoint.
- kinkora 6y agoPieter Levels (levelsio) of Nomadlist famously gets mocked for using a single index.php and sqlite but does 5 figure MRR. https://nomadlist.com/open https://nomadlist.com/open He used to get a lot of flack for it and i never understood why since you use whatever gets the job done. Too many people doing the "gatekeeper" thing.
- 6y ago
- zubairq 6y agoAgain I love SQLite! https://github.com/zubairq/pilot https://github.com/zubairq/pilot Would not be possible without it! Why? SQLite works on the server, in Electron (barely), and purely client side on the browser, and is fast!
- anyfoo 6y agoLiterally the only criticisms I have is that sqlite is actually dynamically typed, not statically as you would expect! The schema only defines a field's "recommended" type. The documentation is very explicit about that[1], but it might still come unexpected, be forgotten, or, worst of all, cause problems that manifest much much later than if they would have at insertion. I remember one particular very ugly episode of that. But that is all. As a whole, sqlite is amazing, rock solid, amazingly documented, and my immediate go-to if I need a (serverless) database. I don't remember encountering a bug, in mission-critical software.
- sradman 6y agoI find the type system unique compared to other RDBMS systems but I too wish that constraint integrity was enforced by the engine. I don’t want to give up on variant types (call them ANY types) as they can solve some interesting problems that fall in the flexible schema space but I don’t want to go through hoops to constrain something like a fixed length binary column to hold a UUID. I understand the historical reason; it allows for almost any flavor of SQL to behave as expected. SQLite is often used in a developer sandbox to work on a copy of an existing enterprise SQL engine. A workbench SQL if you will. As mentioned in last week’s SQLite thread, it doesn’t have a storage type for datetime. I also miss exact NUMERIC(p,s) types but I don’t want to expand the storage classes, I just wish the Domain/Type constraints could be optionally enforced in a simple way.
- dang 6y agoThere was a considerable SQLite thread less than a week ago: https://news.ycombinator.com/item?id=23281994 https://news.ycombinator.com/item?id=23281994.
- battery_cowboy 6y agoI would be proud to make something a hundredth as useful and prevalent as SQLite.
- fmajid 6y agoI just checked and I have been using it since 2003...