Sqlite Is The Most Used Database
Вставка
- Опубліковано 26 кві 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 - Наука та технологія
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.
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 😏
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
• 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.
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.
"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.
That meme about 1 dev holding up a trillion dollar industry in Nebraska is, like, not a joke. :'D
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?
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!
SQLite is an in-process DB, that's why it's everywhere (including programming languages). It's easily embedable.
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.
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.
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?
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.
sqlite is just fantastic. It's also not difficult to write own extensions
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 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.
I do feel Turing got treated worse than Babbage.
He might not have minded the shafting as much tho.
It ain’t a competition…
17:30 Grace Hopper, Richard Milton Bloch, Robert Campbell created the first architecture-agnostic compiled programming language which was called FLOW-MATIC.
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.
6:47 daaaaammmnnnnn and I thought my app having the Golden Rule on our code of conduct page was heavy.
Which app of yours?
19:05 While probably not as shafted as Charles Babbage, Konrad Zuse was also quite shafted.
So much wisdom in those rules, especially the ones instructing you to not give in to anger or to nurse a grudge.
4:00 You have reached the peak of productivity: For Real Agile
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.
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
0:14 because its deployed with apps.
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.
"Bury the dead" - is that why Prime went to React Miami?
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.
I have to use SQLite at work currently and it ways of handling schemas and foreign keys is... interesting.
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.
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.
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
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.
There could potentially be enough IoT devices out there (a lot of telemetry devices utilize in-memory strategies)
SQLite is used in the flight computer avionics for the airbus A350
I want to know what type of coffee beans you use to make your morning coffee? Damn dude. ❤
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.
72 ... DEFINITELY this is way older and based on Pythagoras 'Carmen Aureum'.
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.
Babbage had a popular video game store named after him in the 80s/90s. Unfortunately, it's now called GameStop.
Posting the VOD 1 month after the stream is crazy
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.
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.
To be fair, the analytical engine never reached completion. So, the program by Lovelace, while created, was never ran either.
Did not expect a squeal lite lore dump today.
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.
the entire world is running on the edge of collapse
I was watching the first 5 minutes literally muttering "please find the code of conduct, please find the code of conduct.." 🤣
Upload the one with uncle Bob. I missed that 😢
Damn! That Code of Ethics is so ... interesting.
the struggle is the glory!
I gonna add that code of conduct to my project.
Python has to be compiled with the sqlite extensions. Just had to do it Friday.
You don’t really need more than 2 people to maintain a timezone database (excel pretty much in this case). Throwing more people at a task gives you a false sense of security sometimes. Having 20 people maintain this database may introduce other complexities/security issues
Howdy prime!
Effiicient BTree implementation is notoriously hard - I suspect most programmers are not up to it.
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.
Yup. Many apps use sql lite. Each device you have these days has like 10+ instances running.
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?
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"
I did not expect to see the Ten Commandments in a software Code of Ethics.
SQLite, i wonder how many file formats and random files are just around being SQLite databases + in memory ones.
Just think someone might upload the entire Bible labelled "Code of Conduct" 😂
I wonder if Oracle gets its TZ updates from SQLite. Would make sense. It’s just a plist.
this was a rabbit hole and a half
16:14 you don't hate 100% test coverage, you hate that being a requirement for all projects then. On some cases its acceptable to require that, for example a critical load bearing piece of software like SQLite, for code that runs user interfaces, not so much
"I am subscribed to you" LMAO, nice 8:03
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
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
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
i think the Charles Babbage vs Ada Lovelace thing is Ada has a cooler name.
Many apps use SQLLite, firefox used for example, also every andoid phone has one as i know
why would they even allow contributors if they are succesful ?
contributors come when you are building
not when you finished and they want a piece of pie or sabotage it behind the scenes
and certainly not for others to copy-paste and benefit from their work without any form of reward in return
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.
With great power comes great responsibility - and sqlite devs are a testament to that
That code of ethics is simply crazy! To be honest, though, ReiserFS could have made good use of point number 3.
The difference engine was never built. It is impossible to build physically. The force needed to move all the gears would make it so hot, it would all melt. But the theoretical achievement of Babbage and Lovelace is still tremendous!
"Do not covet"... is probably a good idea for software.
Babbage beggin' for cabbage
to build a savage machine that could average
faster than people could manage
giving his donors advantage
A lot of file formats are SQLite dbs in different guises
I had no idea, the when I clicked on a SQLite video, that it would turn out to be a sermon.
Charles and Ada didn't actually build anything, it was all theory. Charles proposed that we could use the punch cards the textile industry was using at the time for computation. Charles and Ada were actually friends and she translated Charles's writings. She predicted computers would be able to do much more than just calculations
Eggert was my prof and people would say “i just got egged” when they failed his exams LOL
«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
that prime burn was so hot, even I'm sweating.
him selecting but the fist and last letter drives me nuts
Came for funni video, stayed for the sermon. Amen 🙏
You will be surprised to how many tests Oracle has, it runs for weeks.
Funny the rule of do not murder when you know the history of ReiserFS, good that Linux does not have that rule.
Id put money on the fact that almost nobody knows who Ada Lovelace is and doesnt know who Charles Babbage is. A few will, her dad was Lord Byron after all, but the vast majority know the pair.
She might get mentioned more frequently cause she was up against a unique set of challenges, had a more interesting family, is a comparatively rare role model, and Lovelace is an objectively more awesome name than Babbage...