Why SQLite succeeded as a database (2016)
changelog.com
changelog.com
> 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-...
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.)
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."
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.
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.
It is polite to do so, and I'd make the effort, but I certainly wouldn't call it a duty at all.
> and to disable the relevant features in the tools they use.
That isn't always possible, and even when it is this does not stop contributions perhaps coming in by other means (email if you have an address publicly known, issue reports, …).
They can study the thing. If someone wants something you don't provide, they have the right to make their own derivative. Possibly with open contributions. You can take from these forks if they are public and open source too (but you control what you take and how).
If closed to contributions is the way you prefer writing your software, go for it! And you can always change your mind if needed.
Taking contributions can be very fulfilling, satisfying, nice and everything, but it can also be tiring since it can require to be pedagogical, polite, possibly make compromises, argue, etc. Your call and thanks for releasing your software in any case :-)
You need enough funding to be able to work without outside help, an opinionated enough development philosophy that the cost of integrating external contributions exceeds the value they provide, and a reason to want to give away the source code.
Plenty of companies have the first two, but consider the program they are developing a competitive advantage.
> funding
Not everyone is coding for a commercial entity or otherwise monetising their projects, they might just be doing it for fun and want to share, but want to keep their version properly theirs.
A key reason for this can be licensing, if the licence is not a very permissive one. If you have a project that is AGPL and you later want to change to MIT, or if you do decide to go commercial, then if all the code is yours that is easy: just do it. You can't revoke the earlier licence of course, so the last GPL release is effectively dual-licensed. If you have numerous other contributors then you may have issues with relicensing their contributions. They might not care if you are going from AGPL to something more permissive (though that might*, depending on ideology) but they are more likely to if there is money involved.
Moving to a more restrictive license is often easier, but might still not be 100% plain sailing.
It's only bad if it seems open to contributing but actually isn't. Having the source available and even a copyleft license does NOT imply open for contributions.
The commitment to run an open source project is huge, it's time consuming and draining.
Just putting the source code of something you built out there and saying "I built this, do what ever you want with it" is an incredibly generous thing to do and should always be encouraged.
Indeed, and originally that is what open source was all about!
One of my pet peeves is the conflating of open source with some sort of communal development model. Open source certainly enables that model, but open source is much bigger than that. Furthermore, you need other things to make the communal model work well - specifically funding (like a foundation or other patronage funding model) to support development and community management.
It is the only major database that has obtained DO-178B certification, allowing it to operate legally in this avionics environment and role.
Relaxing the strict controls upon it, and allowing contributions that do not pass the tests (in th3) will remove newer versions from avionics applications.
There are some people who are actively trying to fork, which means precisely the above.
I read at some point that Airbus has a software support agreement in place for SQLite that expires in 2050.
I never understood this. Does it mean other open source projects are "open contributions" and anyone can just go ahead and commit to master? No. Literally every project requires you to have a discussion with the maintainer(s) before your contribution can be merged in. In larger projects, it requires going through a change process.
But they can be merged in.
SQLite clearly says "not interested in your code". At all.
It's not like they are unfriendly. They will happily discuss things in detail with you, but you can publish your own extension. SQLite proper is developed by Mr. Hipps and his friends/employees.
A very sensible model, if you ask me.
With SQLite, that's just not a process. There is no process whereby code written by anyone other than the SQLite maintainers enters the git repository.
I love open source, but I am a huge proponent of strong leadership and vision as the best model to create outstanding software. You need a BDFL, a Torvalds saying "no," a Jobs saying "this is what I want and I will not compromise." Too many open sources project adopt the anything goes model and remain mediocre. The Linux desktop is the most visible example of this.
Sadly 99% of open source projects tend to suffer from this problem, and add features just because someone took the time to write a PR. This is the only reason, IMO, why open source software is often a second rate alternative to commercial offerings: if the source is closed, it is harder for a product's vision to get diluted and lost over time. SQLite shows it is possible to create incredible open source software true to its original vision.
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.
https://www.sqlite.org/pragma.html#pragma_synchronous
https://www.sqlite.org/pragma.html#pragma_journal_mode
I set them to "NORMAL" and "WAL" respectively.
That's a pretty big failure to preserve durability.
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.
> 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
If you do not care about system crashes, why sync at all?
If you do not care about system crashes, why sync at all?
And there are scads of use cases where you want to:
- persist data
- enforce some sort of schema (even though SQLite is lax here)
- query/join it in SQL-y ways
- are willing to trade ACID-compliant journaled durability in exchange for performance
Cache stores are maybe the most obvious use case. This is roughly how Redis is configured out of the box after all (sans the SQL) IIRC. Not ideal if your cache is trashed but also, not a huge deal.
There are also a lot of logging/timeseries type applications where it's just not a big deal if you lose, say, a few minutes of data per year.
Transactions on WAL mode databases must use only a single database file to maintain ACID.
Makes integrating computation and data very easy for domain experts.
Yep, that right there is exactly why i love me and use sqlite!
>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.
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.
In practice it has never caused me a significant issue that I can recall and saves a lot of time casting things into compatible columns for exploratory queries. I'm still not sure I like it. But shifting into sqlite mindset is important for making the most of sqlite's strengths, and learning to think of these as "affinities" rather than types or constraints has been part of that.
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.
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.
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."
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.
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!.