Sqlite – Most Deployed Database in the World | Hacker News Reader

Sqlite – Most Deployed Database in the World

sqlite.org

148 pointsgitgud45 comments

I also like this page of the docs: "SQLite as an application file format": https://www.sqlite.org/appfileformat.html

Also on my list of favorites: https://www.sqlite.org/whentouse.html

Back when I worked at a large semi conductor company they used GDSII[0] and Calibre[1] files along with internal formats and I always wondered why they wouldn't just use SQLite. SQLite is even threadsafe[2].

[0]: https://en.wikipedia.org/wiki/GDSII

[1]: https://www.mentor.com/products/ic_nanometer_design/verifica...

[2]: https://www.sqlite.org/threadsafe.html

https://www.sqlite.org/testing.html

The testing each release goes through is quite impressive. I bet more time is spent on testing (or writing tests) than actually developing the features.

"the project has 711 times as much test code and test scripts" than source code. That's a really impressive feat.

Huh, at that point it'd probably make more sense to formally verify it. For sel4 last time I checked the ratio was 25/1 proof code/verified code.

As far as I understand, formal verification does not certify the implementation even though the algorithms used are sound.

It depends—many proof-assistant languages let you extract an implementation directly out of your formally-specified algorithm (generally by dropping rich typing-ish information, but you've already verified it's correct). Or take seL4, which amazingly proves that not just the C code but also the binary faithfully matches their formally-specified algorithms. https://sel4.systems/Info/FAQ/proof.pml

Of course who knows what your CPU microcode is doing, but for the question at hand—ensuring a highly portable C library does what it claims to do more efficiently and accurately than exhaustive testing—formal verification is certainly a reasonable approach.

SQLite is used on many platforms, built using many compilers. All of those compilers might have bugs themselves (who am I kidding? They do), something verification cannot account for.

Sel4 takes the compiler out of the trusted computing base as well as well by verfying the binary directly.

Additionally, the majority of that test code is closed source, so it isn't getting run on nearly all the platforms sel4 supports anyway.

Thanks, I did not know that.

I would contribute towards a Wall St Journal full page ad "SQLite: Most widely used DB in the world" with little misleading graphs comparing to Oracle and DB2

Indeed it would be interesting to also see comparison by the volume of data stored and the number of requests processed.

Trying to make Larry Ellison blow a fuse?

He has reasons to, given that Oracle owns BerkeleyDB which used to dominate the embedded DB use case in the past, being often replaced by sqlite.

I love sqlite. It is far to under used. It scales incredibly well and if you out grow you can easily move it into a different dB system.

There is a way that it does not. It is flexibly typed so you can insert a string into an integer column. This is by design.

This also means you’ll run into trouble when you try to move it to most SQL databases which are strongly typed.

> This is by design.

Oh god, why?

No. In reality, it's incredibly hard to migrate any database to another database. Even if you have high test coverage, there can be some corner cases bite you later. Hack, simply a version upgrade can have all kinds of problems.

> SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. SQLite is found in:

> Every Firefox, Chrome, and Safari web browser

Where is SQLite in Firefox? It would be wonderful to use SQL in the browser, on the server, in mobile devices (everywhere!), but alas I suspect SQLite can be "found" in Firefox as the underlying storage engine on top of which IndexedDB is built.

Dammit Mozilla, set SQLite free!

That was Web SQL.

https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

> That was Web SQL

and very much still is in Chrome and Safari, nearly 10 years after WebSQL was "deprecated". Firefox removed WebSQL, replacing it with IndexedDB, a simple k -> v store.

Cross platform SQL everywhere? Not with Firefox still in the mix.

Considering the Type-flexibility of SQLite, would it necessarily be too difficult to have an object-relational mapping that works across both DB's? I guess it depends on the application. Surely it would be a pain, but perhaps still a possibility.

https://support.mozilla.org/en-US/kb/profiles-where-firefox-...

https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage...

I believe that quotation is just saying that Firefox uses SQLite to store some of its internal state, not that it has an API to interact with general SQLite databases.

> not that it has an API to interact with general SQLite databases

Well, it did, but they removed it, and in fact spearheaded the movement to deprecate WebSQL circa 2010...when Firefox had significant marketshare. Chrome and Safari still, 10 years later, ship with WebSQL support, which makes it pretty clear which side of the fence they're on.

This makes me wonder why there are no alternative implementations of it (i.e. I felt like I would appreciate a .Net-native implementation many times).

It's written in C, you should be able to FFI into it from anywhere. The API is reasonably small, too.

I know it's written in C. And it usually takes hours of shamanic dances to make it work in a Visual Studio WinForms app (while performance is rarely important for these projects).

A C# native port[0] of Sqlite was attempted in 2009 and eventually abandoned in 2012. It was a lot of work and the developer didn't get 100% of the verification tests to pass.

To be fair to the programmer, he said the C-to-C# translation was mostly a self-learning exercise rather than a serious attempt at creating a production-quality library.

I think the obvious answer is that there's no market demand for a C# native version of SQLite. People just use the C# ADO.NET wrapper that calls the "unsafe" C compiled DLL[1]. Using that method, it's easier to stay up-to-date with the canonical C source code that has the latest bug fixes and new features. If you look at high frequency of updates in the release history[2], you can see that constantly porting the new C code to C# to maintain feature parity would be a tremendous amount of work.

Also, if you create a C to C# port, it means using the C Language style API such as "sqlite3_open()" instead of the more natural C# paradigm of ADO.NET "new SQLiteConnection(@"Data Source=x")". Somebody would than have to write another ADO.NET wrapper to specifically interface with the C# port of SQLite.

[0] https://code.google.com/archive/p/csharp-sqlite/

[1] https://system.data.sqlite.org/index.html/doc/trunk/www/down...

[2] https://www.sqlite.org/changes.html

> Also, if you create a C to C# port, it means using the C Language style API

Is this really necessary? Is it impossible to just write a .Net-native library with whatever an API that would just open an SQLite file and run SQL queries against it? As for me that's all I ever needed of it.

>Is it impossible to just write a .Net-native library with whatever an API

It's not impossible but the issue is whether it's worth the amount of effort involved. So yes, you can theoretically write the C# code in any way that makes a convenient API for the C# coder.

But when attempting real-world implementations, there are varying degrees of difficulty/maintainability of porting the C code to C#. You want to balance the tradeoffs of minimizing the C# programming code effort and maximizing the leverage of the original C source code. The effort continuum would look something like this:

- easy: unsafe C DLL with C# ADO.NET wrapper (the 'C' source code is mostly unchanged and is leveraged for highest reuse; this is what we have today)

- harder: straight C-to-C# port with extra C# code to provide an ADO.NET wrapper (an automated tool can attempt to transpile most of the C into C# which is followed up by a human programmer to manually fix/code the parts the transpiler couldn't; this effort was attempted in 2009 and the incomplete project was eventually abandoned)

- hardest: C-to-C# total rewrite where the programmer built in an ADO.NET api (or whatever API the C# programmer wants to expose). In this way the original SQLite C source is more of a "specification" rather than an input file to a transpiler.

The "harder" effort which wasn't even 100% complete was abandoned. It means the "hardest" implementation, while technically not impossible, is even more unrealistic for anyone to code and maintain. That last method of C# rewrite has immense costs and would fall further and further behind the original C source code in feature parity and bug fixes.

To answer your question on why there are no alternative implementations: it's because it's a lot of programming work that nobody wants to do.

I wonder why there aren't more DB like bedrock which builds on top of SQLite.

https://github.com/Expensify/Bedrock

LXD has a distributed cluster system built upon SQLite[1], that's based on the raft consensus protocol.

1. https://github.com/CanonicalLtd/go-dqlite/

Most = highest number of installations. I bet the winner for largest amount of data managed could be another one (Oracle? MySQL?)

It might still be SQLite in that case. The largest data management systems are proprietary (i.e. Google's ever-evolving data layer)

"Every Windows10 machine" , did not know that, where is it located ?

It's built into UWP

https://docs.microsoft.com/en-us/windows/uwp/data-access/sql...

c:\WINDOWS\System32\winsqlite3.dll

also see GeoPackage https://en.wikipedia.org/wiki/GeoPackage

Most of android apps use it as well ;)

Isnt it Microsoft windows registry?

No, even if you count that as a database, it isn't the most deployed. Look at the list in the article. SQLite is on every Android device and Windows 10 device, and many/most personal computers with other operating systems (including earlier Windows versions). There are more Android devices in use than Windows ones: https://www.computerworld.com/article/2919104/windows-pcs/wh...

I don't think Sqlite will maintain this position for long.

Apps are moving to LSM based storage engines.

RocksDB or Badger are the future.

Now, you just need to make embedded DB with Sqlite API using rocksdb/badger and sell it as direct competitor to Sqlite.

It will be a dropin replacement for Sqlite, written in a safer (hacker hype) language.

For more Omph, use Rust as a language for this and create libaries using FFI for python/ruby/node/c#

> and sell it as direct competitor to Sqlite

Pretty sure selling it won't work. For many, a convenient mini-storage is an afterthought during a product development and it doesn't warrant a cost.

Selling as in marketing it to same crowd.

Reply on news.ycombinator.com