Sqlite Is The Most Used Database

Recorded live on twitch, GET IN
Reviewed Tweet
/ 1774464622067679738
By: / iavins
My Stream
/ theprimeagen
Best Way To Support Me
Become a backend engineer. Its my favorite site
boot.dev/?promo=PRIMEYT
This is also the best way to support me is to support yourself becoming a better backend engineer.
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
Kinesis Advantage 360: bit.ly/Prime-Kinesis
Hey I am sponsored by Turso, an edge database. I think they are pretty neet. Give them a try for free and if you want you can get a decent amount off (the free tier is the best (better than planetscale or any other))
turso.tech/deeznuts

[@Bar1noYee]

It’s maintained by 3 people and they don’t allow outside contributions? Huh.. I hope they don’t go on road trips together.

[@testacals]

I mean, you can fork it

[@davidcozziii]

I believe two of the maintainers are married

[@JasminUwU]

​@@davidcozziiiHow very christian of them

[@vaisakhkm783]

gotta love Bus factor of the things that runs the world

[@VezWay007]

@@JasminUwUah the classic 1+1=3

[@TreeLuvBurdpu]

We went from "NoSQL will replace SQL" to "there are more SQL DBs than people"

[@hinzster]

That might also be because most people misunderstand what NoSQL stands for - it means "Not Only SQL", not literally NO SQL. So NoSQL databases usually have at least a subset of SQL in them. To go completely off on a tangent: IBM was really good at making things that are taken for granted today, hard disks, the byte, SQL, DOS (no, not the one on the PC, the "Disk Operating System" as a concept), the concept of a "batch"... yes, they had their share of stupid terminology, like DASD or calling their editor/shell combination ISPF (Interactive System Programming Facility), but you'd be surprised how innovative they at least once were.

[@monad_tcp]

NoSQL is a lie, it was all SQLite embedded and hidden away under a layer of ORM all along.

[@user-baev]

Yea, because NoSQL is shit. And I'm not saying it in a good sense, but rather very literal. Let's replace great, time-proven, stable, based on solid foundation, relational model with stupid JSON arrays and other javascript-programmer-invented "ideas" and pretend it is good. Screw NoSQL and all the followers who replace relational databases with stuff like MongoDB. I hate that this abomination become popular.

[@TreeLuvBurdpu]

@@hinzster don't forget EBCDIC. I really liked their command-based text editor on the VM 370 CMS, although I forgot the name. xEdit maybe. But today i settle for VS Code and VIM mode.

[@zaccanoy]

People also use SQLite for NoSQL-like things, like key-value document stores. idk how good an idea that is, but they do it. also as a file system, which i still don’t understand.

[@the_mastermage]

Chromium browsers History is stored in a SQLite db. That already makes a few dozen billions probably

[@XDarkGreyX]

Plus games....

[@guigazalu]

Firefox as well. Just a Ff profile, has many sqlite databases.

[@lmnk]

@@guigazalu that's kind of a philosophy of SQLite, you can have many small databases scattered however you want instead of one big all-in-one database

[@huge_letters]

lots of mobile apps use sqlite - so you would have ~5-50+ sqlite DBs per smartphone

[@smddev]

@@huge_lettersi may be mistaken but my understanding is that virtually all iOS apps use SQLite. I didn't pay much attention in my iOS class so idk lol

[@cuzsleepisthecousinofdeath]

"It's just faster than fopen()" - creators of sqlite

[@gravisan]

Faster that mmap

[@techpiller2558]

That's a powerful value proposition right there.

[@tropictiger2387]

SQLite has the incredible levels of testing because Richard Hipp heard about the DO-178B standard for aviation products, which requires 100% MCDC, and used that as an inspiration for their test suite.

[@user-baev]

I mean, it is better be tested as an aircraft, since it is so popular. Also, SQLite probably used by NASA and who knows where else.

[@cuzsleepisthecousinofdeath]

Well, sqlite is used by airplane software so yeah

[@lpls]

@@user-baevjust not like Boeing's.

[@user-baev]

@@lpls Maybe they need to adopt SQLite testing model, since their own performs not so good lately 😏

[@Jak132619]

100% code coverage because it's stipulated contractually / by managers is crap that devs will avoid using every trick in the book. 100% code coverage because some spartan gigachad devs are thoroughly committed to the reliability and security of a product they wholeheartedly believe in is a win.

[@mattymattffs]

Working with MS they forced that on us. We had like 70% of tests just returning success. They didn't validate shit

[@bren.r]

Anyone who believes in 100% code coverage is so out of touch 🙄 Having unit tests for specific things and relying on error reporting through some SaaS is far more effective to uncover real issues.

[@RoflMcCopter]

​@bren.r Like most other things in the industry, it is good for specific instances but not most. For a DB that is so widely used, I think it makes sense. For random SaaS projects or WordPress plugins or whatever, it's a waste.

[@bren.r]

@@RoflMcCopter disagreed. If you've ever tried to chase 100% code coverage, you'll realize it means nothing. Bugs/issues can still occur. 100% is a misleading figure and gives a false sense of confidence.

[@adissentingopinion848]

When you are actively downloading, storing, and manipulating data from god knows where, the attack surface is YES. A widespread data corruption bug could obliterate entire business sectors! It would be like introducing a bug into the concept of a transistor itself. If you're not confident enough to store a cancer patient's medical data in your database, you're not SQLite.

• 100% code coverage: Drake nope • 600 tests for every line of code: Drake yep

[@ooodman]

60,000% code coverage

[@Hive-wm1vj]

Stilll getting bugs in prod 😂

[@siliconhawk9293]

god would write test code for the test code

[@taqial-faris6421]

@@siliconhawk9293 unit tests are for people without faith in the god, he wouldn't let his chosen to write a buggy code

[@tomasruzicka9835]

100% code coverage != no bugs But if you have 70% coverage, that tells me that 30% of your code is there for an unknown reason because you don't even know how to run it. And I mean line coverage. Branch coverage, yeah I can see a low % of branch coverage.

[@Viviko]

Local DB on mobile devices. :)

[@thebarnave]

At least 50 for each android device yes

[@mtarek2005]

yeah, WhatsApp, iMessage, google messenger, and more

[@geomorillo]

Yes I'm using it in an app❤

[@marcosmercedesn]

Don't worry, those apps upload all the telemetry data to the cloud ☠️

[@XDarkGreyX]

Unity games and many others

[@titfortat4405]

"squeal-lite"

[@byebeybyebey]

Lawful Good: Sequel Neutral Good: S-Q-L Chaotic Good: Squirrel Chaotic Evil: Squeal

[@garad123456]

Finnish: äs kuu äl

[@blenderpanzi]

@@byebeybyebey 's cool

[@NoX-512]

@@byebeybyebey Prime is chaotic neutral, leaning towards good.

[@Dalendrion]

@@garad123456 Ask you well.

[@connorskudlarek8598]

That meme about 1 dev holding up a trillion dollar industry in Nebraska is, like, not a joke. :'D

[@DigitalDesignET]

I used to work as embedded device engineer for home appliances and we always use sqtlite for our db. That on itself is huge amount.

[@todo9633]

3 Contributors: one for the father, one for the son, and one for the holy spirit.

[@kneekoo]

Amen.

[@CodeWithZeee]

its not baked into PHP, its added via an extension... that extension however has been shipped with and enabled by default since like PHP 5 so kinda close enough..

[@ChrisCox-wv7oo]

Same with python

[@kevin.malone]

SQLite is my favorite db by miles. Can't be topped

[@jan.tichavsky]

Until you need concurrent access. Sqlite is single threaded and locks the whole db/file even if you read single row from single table, that is its main downside.

[@bren.r]

@@jan.tichavskymaybe correct me if I’m wrong but isn’t that how it has to be? Just thinking of how conflicts are resolved with cascading changes.

[@jan.tichavsky]

@@bren.r Regular DB systems offer more granular locking so if one process accesses other table for writing or the same table for reading they can do so at the same time, there is no conflict. The DB server should handle it on its own with queues and what not, I don't know the details, but you won't get error message that the whole database is locked and you can't open it like you do with SQLite and application layer like PHP (default is zero busy timeout for some reason).

[@Rohinthas]

As someone who is genuinely interested in Interpreters, Compilers and DBs but currently cant make time to look into them, I appreciate these vids so much! Got the books btw hopefully I got some free time again in a couple of months! Thanks for the discount, saw the books being recommended in a couple of places!

[@mxlje]

I haven’t finished watching this yet but the reason they don’t allow contributions is stated clearly on their website, and it has to do with it being released into the public domain and they don’t want copyright issues there.

[@mxlje]

4:00

[@mxlje]

They have other cool stuff on their website, for example how SQLite is great for custom file formats for apps, how they have committed to support it until at least 2050, etc. SQLite is fantastic.

[@testacals]

couldn't they just make people sign a contract that says the code is public domain ?

[@niter43]

@@testacals they do 4:30 but it's small project that's managed by 3 people just fine, so unless there's a clear need for some new maintainers why risk? E.g. some contributor had no right to release it into public domain as he was doing his commits on company clock, and after 5 years company tries to sue everyone patent-troll style?

[@scotmcpherson]

I totally use sqlite...it's great. Don't have to deal with connectors and drivers...you just build it right in. All of my game servers have sqlite dbs for their stage 1 databasing.

[@Davidlavieri]

What about concurrency

[@scotmcpherson]

@@Davidlavieri you mean between multiple instances?

[@Quarky_]

SQLite is an in-process DB, that's why it's everywhere (including programming languages). It's easily embedable.

[@djchrisi]

sqlite is just fantastic. It's also not difficult to write own extensions

[@Reavenk]

Ada Lovelace gets the cred because she is the one with the epiphany that the numbers being calculated with these machines could represent other things that transcend pure number crunching purposes. As symbols for anything, not just quantities. Numbers as letters that form words, such as ASCII? Numbers as pixels that form an image? Numbers as an array of tensors that represent states of AI thought? Numbers in a series of voltage values to a speaker over time that form audio samples? As FSM states? As DAW music sheet notes? As elements of a vector representing points in 3D space and their connectivity into triangles to represent 3D shapes? As enumerated IDs for different Pokemon and their various learned abilities? All that and more are tied to her realization. Plus, she's a great example of women in STEM; historically, there's only a small handful, but they're all amazing.

[@thewiirocks]

All y'all are just too damn young. When computing was taking off in the 80s, Babbage and his Differential Machine were mentioned in Every. Damn. Book. On. Computing. Lovelace was then mentioned as the first programmer of the machine. It was only with current generation and overfocus on girl-power that Lovelace has taken center stage.

[@kinuthiasteve4505]

Python mentioned...let's Go!!

[@DestopLine]

Go mentioned...let's C!!

[@NoX-512]

C mentioned...let’s Zig!!

[@Murderbits]

ZIG mentioned... so BASIC.

[@NoX-512]

BASIC mentioned...let's Rust!!

[@ustav_o]

Rust mentioned! lets brainf####!

[@leakyabstraction]

Haha, I just designed, implemented and deployed a Sqlite database based solution which uses Kubernetes persistent volume (with ORM, code first migrations, etc). I'm still kinda terrified, but looked into it a bit deeper (previously I only used it for testing), and Sqlite is quite robust. Its main limitation is concurrency.

[@Murderbits]

SQLite tends to be my scaffolding solution and when I'm farther along with development of something that is definitely going to be a full fledged thing, I transition it to something like Postgres. Having a serverless solution is just so smooth.

[@arthurmoore9488]

If you want concurrency and resiliency, and are already using Kubernetes, consider etcd. That's the key/value store Kubernetes uses as it's back-end. Sometimes Sqlite is the answer, sometimes etcd is, and sometimes it's PostgreSQL. Just depends on what you're trying to do.

[@pioussutherland]

I use PHP. I had absolutely NO IDEA it was baked into the language. I’ll have to look it up.

[@axMf3qTI]

Wouldn't be surpriced at all. I have people seen use sqlite in the front end through WASM.

[@mek101whatif7]

Lol php

[@albertoarmando6711]

@@mek101whatif7 your LOL is only valid if you are not using javascript

[@bkucenski]

It's not "baked in" it just includes libraries for MySQL, SQLite and other things that are optional to include.

[@Leonhart_93]

More like the API to easily access one, because the DB software itself is quite massive.

[@username7763]

Sqlite is great for the right use case. No network hop or layers between makes it super fast. It also doesn't require network configuration, DB configuration, installation, dockers or anything to install. A few limitations though, the schema doesn't enforce typing. Type a column as an int and put a string or blob in it no problem. This means your code has to protect against this while developers are used to the database doing it. The locking mechanism is very course-grained. The entire DB file gets locked. There are ways around this such as splitting up database files or using the write-head-log. Table modifications can be challenging as you often have to create an entirely new table and copy the data over to it. But it is amazing how much it is capable of. I used it on a project where we planned on supporting both Sqlite and a database server; we were able to push it further than expected and delay using a database server.

[@davidfrischknecht8261]

SQLite is intended to be used for app-local storage. It was never intended for the same instance to be used by multiple apps at the same time.

[@owenwexler7214]

6:47 daaaaammmnnnnn and I thought my app having the Golden Rule on our code of conduct page was heavy.

[@0xDEAD_Inside]

Which app of yours?

[@Prenderrem]

So much wisdom in those rules, especially the ones instructing you to not give in to anger or to nurse a grudge.

[@konkoism]

In multiple companies I've worked at: 1 PG/MySQL/MariaDB production database, 1-2 staging dbs of the same make, and then every single dev runs tests against sqlite DBs locally. So yeah, figures.

[@mmmhorsesteaks]

I do feel Turing got treated worse than Babbage. He might not have minded the shafting as much tho.

[@MrMeltdown]

It ain’t a competition…

[@harrytsang1501]

4:00 You have reached the peak of productivity: For Real Agile

[@SoftBreadSoft]

17:30 Grace Hopper, Richard Milton Bloch, Robert Campbell created the first architecture-agnostic compiled programming language which was called FLOW-MATIC.

[@iooosef6006]

tbf Babbage's Difference Engine was never constructed in his lifetime Ada Lovelace is goat for creating algorithm in a never-before-constructed computing machine

[@kuhluhOG]

19:05 While probably not as shafted as Charles Babbage, Konrad Zuse was also quite shafted.

[@JR-uy2nd]

SQLite os use to register the settings on Android and Firefox. In Firefox the perfs.js and the user.js files, the files that register all the settings on Firefox are SQLite databases, so for everything instance of Firefox you have at least two SQLite databases. SQLite is super simple and not full feature at all but because it soon simple and so lite and easy to implement and scalable that because of that is used everywhere.

[@EvanEdwards]

I quite like the general idea of a Code of Ethics: "This is what we aspire to, but do not expect or demand from anybody else. We may fall short, but this is a statement of the shared values we strive to embody." It's a cross between a Code of Conduct (which is a more binding set of expected minimums) and a Mission Statement (which is project outcome oriented), but more fundamental and internal. It would be interested to see different cultures produce these documents. We often assume that other open source authors approach life in the same mindset, but several times (like Chinese state backdoors, or the "just commit anything to get a job" concept), that has proven to be a bad assumption.

[@username7763]

How effective unit testing is depends on the code being tested. This is why I don't like devs who say things like everything needs unit tests. I've seen so many unit tests in my career that had 0 chance of catching any bugs. But the best unit tests were ones that tested core algorithms and data structures. It totally depends on what you are testing.

[@Schadowofmorning]

I have to use SQLite at work currently and it ways of handling schemas and foreign keys is... interesting.

[@cherubin7th]

I gonna add that code of conduct to my project.

[@TheERAUEagle]

"Bury the dead" - is that why Prime went to React Miami?

[@cherubin7th]

Too be fair setting up a DB and then having to make a table and so on sucks, while SQLite is just inside the app and creates basically itself.

[@brentsaner]

The tzdb thing re: 2 people is... misleading, it's not that fragile lol. And SQLite is correctly labeled opensource; source being open refers *explicitly* to the license. "Public source" is more apt to e.g. Terraform and Redis' licenses. (Don't confuse "open source" with "open source *development model*"; they're wholly different things with no relation.) Lastly, BASED SQLITE TEAM

[@johnbruhling8018]

the struggle is the glory!

[@cabanford]

I want to know what type of coffee beans you use to make your morning coffee? Damn dude. ❤

[@carloslfu]

Damn! That Code of Ethics is so ... interesting.

[@archibald-yc5le]

With great power comes great responsibility - and sqlite devs are a testament to that

[@d7ffab979]

72 ... DEFINITELY this is way older and based on Pythagoras 'Carmen Aureum'.

[@sacredgeometry]

0:14 because its deployed with apps.

[@DeviantFox]

this was a rabbit hole and a half

[@ataboo]

Did not expect a squeal lite lore dump today.

[@themichaelw]

SQLite is used in the flight computer avionics for the airbus A350

[@laden6675]

Posting the VOD 1 month after the stream is crazy

[@RichardRemer]

When I was young, Babbage was by far the more well-known figure. It makes sense that as computing shifted from hardware to software, the fame shifted from Babbage to Lovelace.

[@hellowill]

Platforms like sqllite deserve that test coverage, because they have a massive blast radius. If you're developing something smaller like an API, or single page app, then you don't need such extensive testing.

[@ethanbuttazzi2602]

Testing/code coverage doesnt guarantee that the code is bug free, however, it is a measure on how resiliant the code is to unexpected factors, which makes a lot of sense for data bases, since losing data can be a catasthrophic

[@alanmcdade2459]

Depends how you count most used! Most deployed possibly although the file system is the most deployed data store by a long way. Equally a single busy database, perhaps a 1000 deploys that runs full tilt 24/7 is worth millions of phone databases on usage.

[@rj7250a]

It counts usage by amount of installed instances. So in a Android Phone, you can have 1 Sqlite DB for the OS, another for chrome, another for Skype and so on. While you only have 1 filesystem for most devices. (Does 2 partitions of the same filesystem on a device count as 2 instances?)

[@alanmcdade2459]

@@rj7250a usages isn’t the number of the installed instances, is simply the number of installed instances. Whoever “it” is doesn’t get to define a common idea. Usages is amount of work done by an instance and work done can aggregated over all instances to find the total usage perhaps in time spent or bytes moved. Then once we have a meaningful metric we can see which is the most used data store. I understand SQLite I have used it, as I am a dev. Given that SQlite is reliant on the file system, it stores its data there and you live without SQlite but you can’t live without the file system. The file system is used by literally everything, even on embedded computers and used all the time it would have far higher usage per day. The useful work down by large DB is simply orders of magnitude greater than SQLite. If there are numbers out there I would be interested to see which wins.

[@thomasphilipmeadows4569]

I was watching the first 5 minutes literally muttering "please find the code of conduct, please find the code of conduct.." 🤣

[@OS-Advertisingg]

the entire world is running on the edge of collapse

[@gregroyclark]

There could potentially be enough IoT devices out there (a lot of telemetry devices utilize in-memory strategies)

[@seyproductions]

I did not expect to see the Ten Commandments in a software Code of Ethics.

[@beest_]

Testing is not necessarily only for security, but for stability, accuracy, fidelity and feature compatibility. 😢

[@yuu-kun3461]

Imagine having a software using a legacy thing called: DBASE database file or .dbf. To give you an idea, the file type was introduced in 1983. Sqlite was released in 2000! There have never been any issues with dbf. Ever. /s

[@leakyabstraction]

Why would it be "fragile"? I think it's a nonsense modern idea that everything needs to be maintained with constant updates. It's a mature product that works. And since it doesn't run like a server you could connect to, but instead it's file based, there are arguably no data security risks.

[@edgardoarriagada9467]

Upload the one with uncle Bob. I missed that 😢

[@ChrisCox-wv7oo]

Python has to be compiled with the sqlite extensions. Just had to do it Friday.

[@Hwyadylaw]

100% coverage is bad as a primary goal, but it's great if you get to 100% simply as a consequence of how extensive your test suite is.

[@precumming]

I do tend to add in extra tests even for things that aren't strictly necessary because I can just tell AI to make them, so I have the coverage without the effort; it's just a "I might as well"

[@jboss1073]

So one of the contributors to Sqlite is also a maintainer of Tcl/Tk. This explains why Sqlite "just works", there's someone who maintains an entire language that "just works" on the team. I like Sqlite 10x more now.

[@the99thProphet]

Does this count each individual app install that uses it?

[@spaceyraygun]

Babbage had a popular video game store named after him in the 80s/90s. Unfortunately, it's now called GameStop.

[@krtirtho]

Just think someone might upload the entire Bible labelled "Code of Conduct" 😂

[@Euphorya]

sqlite in std lib is so dope. One of the best things about python.

[@ChrisCox-wv7oo]

I have 1000 .db files on my computer, opened two dozen of them (randomly through the range) and only one wouldn't open with DB Browser for SQL... 1100 on my work desktop, 366 on my work laptop. 2500 between just three devices (I have probably 4 more computers under my command I didn't search) 1 trillion isn't unreasonable.

[@tmthyha]

i think the Charles Babbage vs Ada Lovelace thing is Ada has a cooler name.

[@ahmadizzuddin]

To be fair, the analytical engine never reached completion. So, the program by Lovelace, while created, was never ran either.

[@crhntr]

"Do not covet"... is probably a good idea for software.

[@jerrygreenest]

«I store all my data in memory» Like if computer needs some data, Devon AI sends me a message in skype, then I remember and answer him? 2030 programming

[@melanovapedia7924]

"I am subscribed to you" LMAO, nice 8:03

[@ricardogamer2]

SQLite. I have some many prod systems on it for small businesses. It's easily in my top 5 engineering projects ever 🎉

[@daltonyon]

Not only the most used, but faster db too

[@its_finn96]

Howdy prime!

[@lmnk]

Came for funni video, stayed for the sermon. Amen 🙏

[@lomasko1093]

I love SQLite

[@Hapkumdo]

I had no idea, the when I clicked on a SQLite video, that it would turn out to be a sermon.

[@hellowill]

Yup. Many apps use sql lite. Each device you have these days has like 10+ instances running.

[@gwaptiva]

Running instances? Or just "potential instances"? or tables? or potential tables? In that case, I have a machine with DB2, how many DB2s does that add to the total number of DB2s in the world?

[@robgrainger5314]

Effiicient BTree implementation is notoriously hard - I suspect most programmers are not up to it.

[@cosmicaug]

That code of ethics is simply crazy! To be honest, though, ReiserFS could have made good use of point number 3.

[@another212shadow]

that prime burn was so hot, even I'm sweating.

[@yanis.mellikeche]

him selecting but the fist and last letter drives me nuts

[@danielsan901998]

Funny the rule of do not murder when you know the history of ReiserFS, good that Linux does not have that rule.

[@TheItamarp]

Sqlite being maintained by 3 people is something else. Regarding Babbage and Lovelace, Babbage designed what is essentially a steam-powered mechanical calculator, but never built it because it was super expensive and no one wanted to foot the bill. Lovelace had a brainwave that you could use the machine to do more complex computation and not just math. She was able to write programs, compile them, and debug them - all on a machine that existed only on paper - before "programming", "compiling", and "debugging" were even a thing. She was essentially a computer scientist back when "computer" was a person who did mathematical calculations

[@pulancheck]

In Android support is native.. so any app can use sqlite databases (can even be multiple dbs per app), i think each such instance = an app counts as 1.. so millions of Android devices * 5-10-20 apps .. + web apps can also use it natively, and there are 1 billion sites. On iOS, i know they have own persistence shit.. but it would be funny to find out, that some proj use too (say they want 1:1 code with Android counterpart). I even think it make sense even for backend, for small projects (say you have not that many users / records / connections) So, it probably make sense to be most used DB engine.

[@SilverDashie]

SQLight was too slow and limited for what I needed. And what I needed was So simple and small. Lot's of reads/writes every so often.

[@FleischmannMVD]

aspirational levels of BASED

[@CodeRedsGames]

SQLite Purpose sounds like an order of knights like the templar 😂

[@JimAllen-Persona]

I wonder if Oracle gets its TZ updates from SQLite. Would make sense. It’s just a plist.

[@owenwexler7214]

This is the Ronald from Nebraska/runk meme in real life on a massive scale.

[@ZehMatt]

You will be surprised to how many tests Oracle has, it runs for weeks.

[@Impatient_Ape]

Babbage beggin' for cabbage to build a savage machine that could average faster than people could manage giving his donors advantage

[@umapessoa6051]

Mistachkin = Mistake + Mustache + Pumpkin?

[@sebastiankorner2350]

Every time you update a column you theoretically have a new Database. Maybe thats where the 1 Trillion comes from :P