I use sqlite3 as database for most of my projects (I mostly just need read-only data). One useful command to know is ".mode table" which makes query results much more readable, especially when you have multiline strings in one of the columns!
Table mode works great until there is a really long named column or the content texts are really long. What do you do then? I just use ".mode column" with headers turned on
SQLite is great! Love the in-memory feature as it allows you to write tests for a web app without having to spin up a whole database (like postgres) or you can also plot it to a file storage for backup and recovery and whatnot. Super super nice.
Multiple writers works fine with Sqlite, just use 'PRAGMA busy_timeout' when you open the database. You can test it like this: seq 1 10 | xargs -P 5 --replace sqlite3 db.db "PRAGMA busy_timeout = 5000" "insert into wat values ({}')"
Ah, this is the reason why Ive never seen concurrent write failures myself, python's default timeout value is 5s, so it just waits in a busy loop if I have 'concurrent writers'
thank you anthony i am a beginnre coder and i was told sqlite is the best fo rthe small pet project im working on this is very helpful and easy to digest!
Interesting that you mention prototyping. What I found most frustrating with SQL are database migrations. I want a new column, different datatype, new table, change the key, but somehow keep the data. How do you deal with these things?
for me migrations are an extreme rarity so I haven't bothered with any complexity for them. when they come up I do a 3 stage rollout and forget that the old schema ever existed
I've been watching sqlite videos for quite some time and I can't wrap my head around this, how is the whole ecosystem so bullish on a db where migrations are such a pita
Don't have a clue what I just watched. My phone downloads this torrent all by itself every night which consists of a jpg, an SQLite and an XML. I delete it and the next time I go to sleep I wake up to find the same 15K file. Anyone know what could be going on?
Very nice video. Thanks! For a Flat File DB, I find SQLite amazingly fast and easy to interface with GAMBAS in Linux as my GUI. The ONLY issue I've been unable to solve is to have a copy on my NAS {RasPi running OpenMediaVault} or just a RasPi with a thumb drive so that 2 users, can read/write to it {but never at the same time, usually hours apart} Reads are fine but I can never write to it. on my NAS or standalone Pi and it's thumb drive. Every attempt to CHOWN or setting permissions to 777 fail. As I run SSDs on everything I'm very interested in the in-memory feature to minimize writes especially during the development/testing/debugging phase.
File locking on network devices is buggy both on Linux and Windows. Maybe thats the issue. You commented a year ago, but if you still have the same problem here is a solution: Since the read/write operations are hours appart, and assuming your db is not huge, you can just copy the db to local disk, do whatever needed, and upload the file back to the network device
i tried duckdb too. i guess it depends what use case you use it for. transactions i would keep in sqlite. duckdb still has no backwards compatibility in storage format, need to be careful with that
Woah, 500! Just 12 until a big round-number milestone!
I use sqlite3 as database for most of my projects (I mostly just need read-only data). One useful command to know is ".mode table" which makes query results much more readable, especially when you have multiline strings in one of the columns!
Except when the content texts or column names are so long, the table's format become distorted
Table mode works great until there is a really long named column or the content texts are really long. What do you do then?
I just use ".mode column" with headers turned on
SQLite is great! Love the in-memory feature as it allows you to write tests for a web app without having to spin up a whole database (like postgres) or you can also plot it to a file storage for backup and recovery and whatnot. Super super nice.
#500 !!! congrats !
Sqlite is incredible. You can even embed it into application binaries.
WAL mode to avoid the locked database
Went to comment to write this
Regarding concurrency: WAL mode broaw.
Happy 500th
Multiple writers works fine with Sqlite, just use 'PRAGMA busy_timeout' when you open the database. You can test it like this:
seq 1 10 | xargs -P 5 --replace sqlite3 db.db "PRAGMA busy_timeout = 5000" "insert into wat values ({}')"
if by "works fine" you mean busy loops and waits while the whole db is locked then sure -- but you might as well use a mutex in code then
@@anthonywritescode Yes you could, but this is build in and works the same on Windows and Linux.
Ah, this is the reason why Ive never seen concurrent write failures myself, python's default timeout value is 5s, so it just waits in a busy loop if I have 'concurrent writers'
Happy 500th video!!
good upload, one question, how do you define custom functions in async connections? a simple implementation of regex would do, thank you
Run command .mode box 📦
To show neat lines around your data and headers 👍🏼
thank you anthony i am a beginnre coder and i was told sqlite is the best fo rthe small pet project im working on
this is very helpful and easy to digest!
It's excellent for typical any single user desktop PC applications.
Interesting that you mention prototyping. What I found most frustrating with SQL are database migrations. I want a new column, different datatype, new table, change the key, but somehow keep the data. How do you deal with these things?
for me migrations are an extreme rarity so I haven't bothered with any complexity for them. when they come up I do a 3 stage rollout and forget that the old schema ever existed
I've been watching sqlite videos for quite some time and I can't wrap my head around this, how is the whole ecosystem so bullish on a db where migrations are such a pita
In-memory databases are great for high i/o apps with transient data, like multiplayer game states.
it would be so cool to see how you did the twitch chat database step by step. great cxontent. thank you!
it was all developed on twitch! there's vods originally on this channel but then on @anthonywritescode-vods
Would you use orm like sqlalchemy or do you like your queries raw?
I rarely reach for an ORM, mostly dealing with parametrized plain queries. then again I'm rarely at a scale where the complexity warrants an ORM
Secret sauce: learn from your mistakes
.
Double plus secret sauce: do a lot of projects on your own so you have a lot of mistakes to learn from
Don't have a clue what I just watched. My phone downloads this torrent all by itself every night which consists of a jpg, an SQLite and an XML. I delete it and the next time I go to sleep I wake up to find the same 15K file. Anyone know what could be going on?
Do you have any good videos on sync between SQLite on android and a MySQL server?
i only want to open an .exe file that is saved as sqlite
why is it so hard ?
i still cant figure it out
Any alternatives that are good at handling multiple writers ? I looking for an alternative with python binding.
Great Video. Any chance you could share your terminal theme?
Isn't that just Ubuntu default, or maybe even gnome default.
What is that keyboard though! Lol
Very nice video. Thanks! For a Flat File DB, I find SQLite amazingly fast and easy to interface with GAMBAS in Linux as my GUI. The ONLY issue I've been unable to solve is to have a copy on my NAS {RasPi running OpenMediaVault} or just a RasPi with a thumb drive so that 2 users, can read/write to it {but never at the same time, usually hours apart} Reads are fine but I can never write to it. on my NAS or standalone Pi and it's thumb drive. Every attempt to CHOWN or setting permissions to 777 fail.
As I run SSDs on everything I'm very interested in the in-memory feature to minimize writes especially during the development/testing/debugging phase.
File locking on network devices is buggy both on Linux and Windows. Maybe thats the issue.
You commented a year ago, but if you still have the same problem here is a solution:
Since the read/write operations are hours appart, and assuming your db is not huge, you can just copy the db to local disk, do whatever needed, and upload the file back to the network device
Hi. What keyboard is that?
there's an faq playlist linked in the channel which has a video about the keyboards
Hi, do you know where to host django project with sqlite online?
How can I use my own data?
Whats a wat
duckdb has replaced sqllite for me.
i tried duckdb too. i guess it depends what use case you use it for. transactions i would keep in sqlite. duckdb still has no backwards compatibility in storage format, need to be careful with that