SQLite is enough

[@thomasborg4442]

Didn't think about the staging environment, but you're 100% spot on

[@MartinBaun]

Thanks Thomas!

[@nodidog]

I use SQLite by default. If a project needs a client + server structure, anticipates lots of concurrent writes, or requires more granular permissions, then I'd use Postgres - but they rarely do. SQLite is very, very capable, im a big proponent. Also, it is trivial to spin up an in-memory database with SQLite - great for lightening fast tests

[@MartinBaun]

Hey Nodidog, you might check a new video about SQLite in Production kzbin.info/www/bejne/sGevhpqrpZiVqZI

[@TheDa6781]

Lack of background sound is enough. I dont know why but it is probably the most annoying background noise ever. Small children screaming would possibly be worse. Maybe.

[@MartinBaun]

My editor and I decided to up it. The next videos will have small children screaming ;) Just kidding, the next videos in line do not have this :)

[@fernandoacorreia]

I agree. I could only make it halfway through it. It’s too annoying.

[@MartinBaun]

@@fernandoacorreia sorry mate, it is the last video I made with this. Everyone after do not have this.

[@fernandoacorreia]

@@MartinBaun Thanks for replying. Constant improvement, that’s very smart. I’ll check out the next videos.

[@MartinBaun]

@@fernandoacorreia Thank you Fernando, You'd probably like this one (WITHOUT any whistling :) ) kzbin.info/www/bejne/sGevhpqrpZiVqZI

[@danishezwan9320]

I wasn't able to finish the video because I have trouble focusing on what you're trying to say but bottomline is I agree with the use of SQLite is enough for Small - Medium Company uses depending on the requirements The main thing SQLite can't do is horizontal scaling (which can be fixed by Marmot if you want to) But SQLite with WAL itself is powerful enough and it can handle multiple users reading, as for writing it will lock the writing and may clog up the pipes but for few milliseconds I myself have been using SQLite for my freelance projects which includes secluded factory and port operation systems that does not require distributed databases. If I want to put it to cloud I could make an ETL pipeline and do visualization from there but my main target is its ability to operate by itself without other dependencies.

[@MartinBaun]

Was it because of the whistle song? We'll never do that again, it is universally hated :D Yes WAL and some other things sohuld be setup before using it, but I'll challenge you a little. I think you can really push it to even bigger sites. Of course, you might have to think about what you put in it and maybe cache something, but unless you have some real heavy write/read it should be fine even for biiiig sites. The ETL pipeline and visualization - and something like Metabase - that's harder

[@denissorn]

Do libSQL and Turso help with horizontal scaling?

[@MartinBaun]

@@denissorn yes they do, and as much as I love SQLite, when you get to horizontal scaling you're probably better off with MySQL or Postgres. Though, you can do a lot of optimization that can keep that at bay. Such as redis caching, general caching, better indexing, data denormalization etc..

[@steffengroenandersen]

Bro, someone is whistling in the background music on this video 😅 It can make it hard to pay attention - atleast for me.

[@MartinBaun]

Hey Steffen! Yea we got told this. We are never doing that again hehe. Like 10 people complained about it so far. By the way, if you wanna know about running SQLite in Production, I just released this kzbin.info/www/bejne/sGevhpqrpZiVqZI Hygge :))

[@bumpersmith]

@@MartinBaun It would be better without the captions also. If we want captions they can be turned on.

[@MartinBaun]

@@bumpersmith thank you, I'll talk with my Editor what we can do. The next few videos are unfortunately already made so too late with those. I think we'll probably not have captions on the longer form, but then on the shorts we'll have.

[@paulorodriguez6288]

i was looking around, i thought it was somebody next to me,

[@YouTubePavlo]

How about difference between sqlite and, for example, postgres? There may be a lot of problems if you will be top 10% visited sites But in general SQLite is great for pet-projects, startups etc.

[@MartinBaun]

The 99% of websites should be fine unless you have very specific needs. The change that you'll become more than a 1% visited website is very low. And if you do, then switching from SQLite3 to Postgres is a lot easier than the reverse.

[@BosonCollider]

You need postgres if more than one running process needs to connect to the db. Postgres can be used as a message bus for example, while sqlite can't. But sqlite is very useful when you need a database that is owned by one application.

[@YouTubePavlo]

Does it mean that microservices can't eeven possibly work with SQLite?

[@MartinBaun]

​@@KZbinPavlo You can but it is probably not a great choice. You could for example have each microservice have their own sqlite database. Probably not great as I mentioned. As @bosoncollider mentioned, Postgres is probably better here

[@StephanBeal]

@@BosonCollider "You need postgres if more than one running process needs to connect to the db" is not true. SQLite's forum and source control system are both sqlite-based and are in concurrent use 24/7 by many users, each request being processed by a separate CGI application instance (as opposed to a single persistent server process). Aside from those, at least one separate application continually polls the databases for queued email notifications to send out.

[@justinoneill2837]

LOVE THE BG MUSIC

[@MartinBaun]

thank you! You're the first :D Welcome to the channel

[@dineshkumarramasamy9849]

Honest and clear man right here who could see through the drama.

[@MartinBaun]

Thank you! Yes, so much complicated tech for no reason. KISS is something I myself struggle with, but I try to get better every week

[@Imaginativeone_DF]

Magnificent video - thanks for making it.

[@MartinBaun]

Thank you! :) I also made a follow up here kzbin.info/www/bejne/sGevhpqrpZiVqZI Tell me what you think :)

[@jameshickman5401]

Filesystem metadata. If it's SQLite, the state can be LVM snap-shotted with the files with only seconds of downtime to perform a backup.

[@MartinBaun]

You're talking litestream?

[@jameshickman5401]

@MartinBaun Wrote my own abstract versiond file system back-end for a project years ago. Used SQLite for the metadata store, and backup is using LVM snapshots.

[@MartinBaun]

@@jameshickman5401 cool, did you have one global sqlite db for all files or one per file? I heard SQLite is often more perfomant than the filesystem as well

[@jameshickman5401]

@MartinBaun one per isolated per-tenant file store. Payloads stored in physical files in a directory based on a file UID and the timestamp for versioning. Virtual folders and metadata stored in the SQLITE database.

[@BosonCollider]

There's a few caveats to the "just copy the file" argument, you do need to lock the database file using sqlite commands to take backups if it is in journaling mode. It's a bit less of an issue in WAL mode where you can have multiple readers, but you still can't just use cp if the database file is in use. You can use tools like litestream but then you are technically back to having a streaming replica setup although a simpler one. Sqlite is really really nice when it is an option though. It makes testing a lot easier, and sharding between users or self-service/provisioning is absolutely trivial which is another major advantage imo. If you need a lot of databases sqlite is your friend.

[@MartinBaun]

Hey Boson, Yes, that's (kinda) true. They warn against using cp as it can corrupt some of the data, but I never had an issue doing this. For backups, I also use sqlite3 your.db ".dump" > backup.sql that should work wtihout any issues. Litestream is really cool concept, I haven't tried it but it is on the todo list. By the way, Monday I'll release a new video called "SQLite in Production - a master course". You'll probably enjoy that one ;)

[@MartinBaun]

Hey Boson, the SQLite in Production video is ready😎 kzbin.info/www/bejne/sGevhpqrpZiVqZI

[@vanillaface6097]

I agree SQLite is fast, but you also need replication if you don't want to be down when one server is down. There's BedrockDB which is distributed SQLite with same benefits, but it's replicated.

[@MartinBaun]

Yes, but it comes with a caveat. a lot more complexity. We have like 1 hour of downtime on most of our Hetzner machines per year. They just keep running when you 1. keep them overpowered and 2. keep it simple. BedrockDB sounds cool though, tell me more about it. How's the difference between litestream?

[@charliezugasti]

@@MartinBaun can LiteFS be used to replicate the db?

[@MartinBaun]

@@charliezugasti Cool! I haven't seen LiteFS before. It looks like it can. So many things are happening in SQLite Universe right now.

[@RihV]

Great video, will definitely give SQLite a try

[@MartinBaun]

Thank you :)) Tell me how it goes and if you have any issues!

[@KuopassaTv]

Don't need SQL-anything, can just use CSV, JSON files or plain text files. 😉

[@Alex-zm9ww]

are u planning to release the "config" video soon ?

[@MartinBaun]

Hey Alex, Thanks for asking. I completely forgot about this video. I'll start working on it. Probably have it for you in a few days ;) Stay tuned!

[@MartinBaun]

Hey Alex, the SQLite in Production video is ready kzbin.info/www/bejne/sGevhpqrpZiVqZI

[@Alex-zm9ww]

@@MartinBaun wow, thanks a lot!

[@MartinBaun]

@@Alex-zm9ww Thanks for reminding me :))

[@_shahdhaval]

How you handle multiple requests accessing the same db file?

[@MartinBaun]

Great question, SQLite can handle it in most cases. If you have many apps doing continous queries it can be a problem, but usually it is not. You should probably set a PRAGMA to WAL, but it is very easy one liner.

[@MartinBaun]

Hey Shahdhaval, the SQLite in Production video is ready kzbin.info/www/bejne/sGevhpqrpZiVqZI There I'm explaining why and how to use WAL

[@earthling017]

The video is informative and good to see. One feedback - the background score is very distracting. Please change it or get rid of it.

[@MartinBaun]

Hey Earthling, thank you! What do you mean with the background score? 1. the sound? 2. the memes ? 3. the background I am filming at?

[@samuelm2934]

@@MartinBaun it's the sound/background music for me

[@MartinBaun]

@@samuelm2934 Thank you so much Samuel! Yes, it is quite distracting. Next videos we'll avoid this.

[@MartinBaun]

Hey Earthlin👋, you can check our new video "SQLite in Production" kzbin.info/www/bejne/sGevhpqrpZiVqZI I think this time the background music is not distracting from the video. 😎

[@scotmcpherson]

SQLite is the single most deployed and used database solution on the planet.

[@MartinBaun]

yep, Linux, Firefox, Chrome, Phone apps. But most developer frown upon using it on the server as their main database. They probably should consider it before going with some more complicated stack

[@scotmcpherson]

@@MartinBaun I use it as the stage 1 database solution for my game servers.

[@MartinBaun]

@@scotmcpherson Cool! I just checked your channel and subscribed! Are you making more videos in the future? :)

[@scotmcpherson]

@@MartinBaun yes, since I am in transition from Unity to Godot, I don’t have anything fun to share yet, but getting there and will produce content again once there is something fun to show. No one who cares about games will care to know my network stack, database choices, and bitpacking. They want to see game content. Imagine that!! ☺️

[@the_ace248]

I honestly love the whistle lol. It adds a bit of 'fill' to the silence.

[@MartinBaun]

thanks for writing this. Hehe it is a bit funny, so many complained about the whistle, but it is also our most performant video so far :) By the way, if you liked this, I made a follow up video here kzbin.info/www/bejne/sGevhpqrpZiVqZI

[@CaribouDataScience]

But duckdb is enougher....

[@MartinBaun]

DuckDB looks very interesting. Can you tell me more about it? I see it is in-memory but persist to disc. So I could imagine it is super fast

[@karaloop9544]

@@MartinBaun sqlite = OLTP, duckdb = OLAP for what it's worth.

[@MartinBaun]

@@karaloop9544 didn't get that analogy at all. Can you elaborate Kara? :)

[@lldadb664]

@@MartinBaun I don’t know anything about DuckDB but I’m assuming based on what @karaloop9544 said is that it is made for analytical queries. I imagine a good candidate for extract transform and load (ETL) scenarios when you need a reporting (OnLine Analytical Processing OLAP) DB based off of your transactional (OnLine Transaction Processing OTLP) DB.

[@Sammi84]

Online analytical processing (OLAP) and online transaction processing (OLTP) are two different data processing systems designed for different purposes. OLAP is optimized for complex data analysis and reporting, while OLTP is optimized for transactional processing and real-time updates.

[@blueml08]

Great video, but pls next time - skip the whistle background sounds.

[@MartinBaun]

Thank you Blue, You're not the only one saying this, and I wont have this on next time :) By the way, I am soon making the configuring / mastering SQLite in production. Stay tuned :)

[@MartinBaun]

Hey Blueml08, you might want to check our new "SQLite in Production video" 😎 kzbin.info/www/bejne/sGevhpqrpZiVqZI

[@BrunoBernard-kn6vt]

Just doing a copy-paste of a db is so underrated 😂

[@MartinBaun]

exactly! It just makes things so much easier. Where do you use Sqlite?

[@BrunoBernard-kn6vt]

@@MartinBaun I have a inventory management system used by my clients. Single instance on a 6$ vps :) Had zero hiccups so far! 🎉

[@MartinBaun]

cool, yeah And backup is so easy and simple Kudos to you for not doing what most people would do. Making it "web-scalable" with cloud infrastructure and complicated stuff

[@BrunoBernard-kn6vt]

@@MartinBaun that's right! Also I am looking at SQLite extensions and litstream for disaster recovery. One example of extensions is to compress the data with time. Since everything is one server it has only 20Gb. I am planning to try it. Benchmarks say it can reduce 1GB of data to 72MB which is insane!

[@nuvotion-live]

I think the advantage of copy and pasting your database is a little overrated. If you are setting up a pipeline to test and deliver your software, replicating databases between environments is abstracted away and automated. Automation is easier than copy and pasting files between environments. It’s more work to set up the first time, but then more reliable and hands off from then on.

[@MartinBaun]

Hey Avsync - Audio Video Sync? :P For our staging environment we literally just run the following command locally scp user@PRODSERVER:./db.db /tmp/db.db scp /tmp/db.db user@STAGSERVER:./db.db and when we make a new project, we just copy paste this command. It is that easy. Sometimes we need to also copy uploaded files, and then we use rsync. It seems silly, it seems stupid, but it is so easy to work with comparaed to pipelines etc..

[@halcyonramirez6469]

Believe it or not sqlite is the most used database on the planet

[@mojo6689]

Great video

[@MartinBaun]

Thank you Mojo! If you like this, you'll probably like my newest video about SQLite in production! kzbin.info/www/bejne/sGevhpqrpZiVqZI if you have questions, just write and I'll reply :)

[@AdolfRizzler41]

Is there any good tutorial for beginners on sqlite i am searching for weeks

[@MartinBaun]

hey Adolf, I'd say you just get started. You'll need a few things 1. Just start using it with the sqlite3 command. 2. Find a library to use with your favorite programming language. If you're new preferably an ORM will help you. 3. This here is pretty good www.w3schools.com/sql/ Good luck mate:)

[@andreyv116]

Which language and framework are you using? Many DB packages take care of sqlite details and it's essentially becomes platform-agnostic SQL with syntactic sugar

[@NeUrOmAnCeRAI]

Wow so annoying, couldn't watch.

[@MartinBaun]

The background whistle? Sorry about this, this was the last one we added this to.