Sqlite Is The Most Used Database
Vložit
- čas přidán 26. 04. 2024
- 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 - Věda a technologie
It’s maintained by 3 people and they don’t allow outside contributions? Huh.. I hope they don’t go on road trips together.
I mean, you can fork it
I believe two of the maintainers are married
@@davidcozziiiHow very christian of them
gotta love Bus factor of the things that runs the world
@@JasminUwUah the classic 1+1=3
We went from "NoSQL will replace SQL" to "there are more SQL DBs than people"
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.
NoSQL is a lie, it was all SQLite embedded and hidden away under a layer of ORM all along.
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.
@@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.
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.
"It's just faster than fopen()" - creators of sqlite
Faster that mmap
That's a powerful value proposition right there.
Chromium browsers History is stored in a SQLite db. That already makes a few dozen billions probably
Plus games....
Firefox as well. Just a Ff profile, has many sqlite databases.
@@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
lots of mobile apps use sqlite - so you would have ~5-50+ sqlite DBs per smartphone
@@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
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.
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.
Well, sqlite is used by airplane software so yeah
@@user-baevjust not like Boeing's.
@@lpls Maybe they need to adopt SQLite testing model, since their own performs not so good lately 😏
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.
Working with MS they forced that on us. We had like 70% of tests just returning success. They didn't validate shit
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.
@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.
@@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.
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.
That meme about 1 dev holding up a trillion dollar industry in Nebraska is, like, not a joke. :'D
• 100% code coverage: Drake nope
• 600 tests for every line of code: Drake yep
60,000% code coverage
Stilll getting bugs in prod 😂
god would write test code for the test code
@@siliconhawk9293 unit tests are for people without faith in the god, he wouldn't let his chosen to write a buggy code
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.
"squeal-lite"
Lawful Good: Sequel
Neutral Good: S-Q-L
Chaotic Good: Squirrel
Chaotic Evil: Squeal
Finnish: äs kuu äl
@@byebeybyebey 's cool
@@byebeybyebey Prime is chaotic neutral, leaning towards good.
@@garad123456 Ask you well.
Local DB on mobile devices. :)
At least 50 for each android device yes
yeah, WhatsApp, iMessage, google messenger, and more
Yes I'm using it in an app❤
Don't worry, those apps upload all the telemetry data to the cloud ☠️
Unity games and many others
3 Contributors: one for the father, one for the son, and one for the holy spirit.
Amen.
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.
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..
Same with python
SQLite is my favorite db by miles. Can't be topped
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.
@@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.
@@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).
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.
4:00
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.
couldn't they just make people sign a contract that says the code is public domain ?
@@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?
@@testacals : Yeah, AIUI that's exactly what they do.
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!
I use PHP.
I had absolutely NO IDEA it was baked into the language. I’ll have to look it up.
Wouldn't be surpriced at all. I have people seen use sqlite in the front end through WASM.
Lol php
@@mek101whatif7 your LOL is only valid if you are not using javascript
It's not "baked in" it just includes libraries for MySQL, SQLite and other things that are optional to include.
More like the API to easily access one, because the DB software itself is quite massive.
sqlite is just fantastic. It's also not difficult to write own extensions
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.
What about concurrency
@@Davidlavieri you mean between multiple instances?
Python mentioned...let's Go!!
Go mentioned...let's C!!
C mentioned...let’s Zig!!
ZIG mentioned... so BASIC.
BASIC mentioned...let's Rust!!
Rust mentioned! lets brainf####!
SQLite is an in-process DB, that's why it's everywhere (including programming languages). It's easily embedable.
So much wisdom in those rules, especially the ones instructing you to not give in to anger or to nurse a grudge.
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.
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.
6:47 daaaaammmnnnnn and I thought my app having the Golden Rule on our code of conduct page was heavy.
Which app of yours?
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.
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.
_"Numbers as pixels that form an image?"_
en.wikipedia.org/wiki/Jacquard_machine
You forcefully trying to highlight women in those fields, as if they are something special, makes me want to forget them out of spite. You'd probably land a good job in the Soviet Union with that kind of progressive collective thinking.
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.
4:00 You have reached the peak of productivity: For Real Agile
this was a rabbit hole and a half
19:05 While probably not as shafted as Charles Babbage, Konrad Zuse was also quite shafted.
I do feel Turing got treated worse than Babbage.
He might not have minded the shafting as much tho.
It ain’t a competition…
"Bury the dead" - is that why Prime went to React Miami?
Posting the VOD 1 month after the stream is crazy
Did not expect a squeal lite lore dump today.
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
Damn! That Code of Ethics is so ... interesting.
0:14 because its deployed with apps.
I gonna add that code of conduct to my project.
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.
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.
"I'm subscribed to you" was the sickest burn ever 😂
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.
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.
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.
I want to know what type of coffee beans you use to make your morning coffee? Damn dude. ❤
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.
I did not expect to see the Ten Commandments in a software Code of Ethics.
72 ... DEFINITELY this is way older and based on Pythagoras 'Carmen Aureum'.
Upload the one with uncle Bob. I missed that 😢
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.
SQLite is used in the flight computer avionics for the airbus A350
Python has to be compiled with the sqlite extensions. Just had to do it Friday.
him selecting but the fist and last letter drives me nuts
the struggle is the glory!
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?
There could potentially be enough IoT devices out there (a lot of telemetry devices utilize in-memory strategies)
With great power comes great responsibility - and sqlite devs are a testament to that
I have to use SQLite at work currently and it ways of handling schemas and foreign keys is... interesting.
Yeah, it mostly doesn't care... So use SQlite with care.
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.
"Do not covet"... is probably a good idea for software.
I am going to put "Do not murder" in any code of conduct I'm responsible for
Babbage had a popular video game store named after him in the 80s/90s. Unfortunately, it's now called GameStop.
Howdy prime!
I was watching the first 5 minutes literally muttering "please find the code of conduct, please find the code of conduct.." 🤣
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.
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?)
@@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.
i think the Charles Babbage vs Ada Lovelace thing is Ada has a cooler name.
I wonder if Oracle gets its TZ updates from SQLite. Would make sense. It’s just a plist.
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
Not only the most used, but faster db too
Eggert was my prof and people would say “i just got egged” when they failed his exams LOL
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.
Just think someone might upload the entire Bible labelled "Code of Conduct" 😂
"Do not steal"
Programmers: well, fuck...
that prime burn was so hot, even I'm sweating.
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"
Came for funni video, stayed for the sermon. Amen 🙏
sqlite in std lib is so dope. One of the best things about python.
I had no idea, the when I clicked on a SQLite video, that it would turn out to be a sermon.
You will be surprised to how many tests Oracle has, it runs for weeks.
A lot of file formats are SQLite dbs in different guises
That code of ethics is simply crazy! To be honest, though, ReiserFS could have made good use of point number 3.
"I am subscribed to you" LMAO, nice 8:03
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.
SQLite, i wonder how many file formats and random files are just around being SQLite databases + in memory ones.
Does this count each individual app install that uses it?
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
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.
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.
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.
Trillion sounds quite a lot, but then again I had to deal with server application that had 6 SQLite databases on its own so I don't think it's completely unlikely for that to be true.
To be fair, the analytical engine never reached completion. So, the program by Lovelace, while created, was never ran either.
I love SQLite
Testing is not necessarily only for security, but for stability, accuracy, fidelity and feature compatibility.
😢
Effiicient BTree implementation is notoriously hard - I suspect most programmers are not up to it.
Mistachkin = Mistake + Mustache + Pumpkin?
SQLite Purpose sounds like an order of knights like the templar 😂
This is the Ronald from Nebraska/runk meme in real life on a massive scale.
You can almost certainly say that the entire software industry is being kept on float by some random opemnsource library that is being maintained by one elderly man in his free time
Id believe it, nearly any android app installation has one
Yup. Many apps use sql lite. Each device you have these days has like 10+ instances running.
Maintained by three Benedictine monks?
Funny the rule of do not murder when you know the history of ReiserFS, good that Linux does not have that rule.
SQLite. I have some many prod systems on it for small businesses. It's easily in my top 5 engineering projects ever 🎉