Hi Kent! Love your videos and your SQL article is amazing! Looking forward to reviewing more of your content. Let me know what might be a good article to read and create another video 🙌🎉
I think SQLite is for most use cases for small businesses. Using WAL journaling you can get around 10K write simultaneously. Not every saas, has a target to get 1M users. If you are around and below 1K users. You should be fine to do anything that other db can do. SQLite can do more than you can think of. 🎉 Also... SQLite is so flexible than you can easily migrate to MYSQL or Postgres Later :)
I think it's worth mentioning that a given SQLite file can only have one active connection at a time, which adds some complication for use on a web server where there may be multiple applications trying to connect to the file at once. It also does not support password protection, so it is not suitable for use with sensitive data on a web server.
that's a fantastic point. The single connection requires the developer to plan ahead on concurrency considerations. For example you could use MPSC/channels to create a dedicated SQLite thread that services requests in your application. This is extra work for the developer. SQLite skips security altogether. Services like PosgreSQL and MySQL have security and concurrency already built-in and taken care of for you! 😄
@@StephenBlum multiple threads can connect to a single sqlite database file without a problem, so you'd never need to implement something like a dedicated SQLite thread in your application. The default journal_mode doesn't allow concurrent reads though, but you can fix this by switching to write ahead logging (WAL). Writes and transactions are problematic because they block your whole database - so for write heavy apps MySQL/Postgres are much better since they support per table/per row locks.
@@anze3db That's sounds amazing! Write Ahead Logging WAL is a great option. The developer won't have to implement read concurrency when they use WAL mode. Your idea is fantastic. Thank you! 😄🙌
I know this, so I never tried it in an app that is deployed to multiple devices. How would you get it to sync? The crazy idea I had was to store it on SharePoint as a file. Then, each person syncs with that SharePoint document library. They each have their own copy, but when OneDrive syncs with the connection to SharePoint, the database will sync changes made by that individual user. It's too scary to try in real life, but that was my thought. How else can you have multiple users?
@@bryanstark324 good question! Multi-user local DB "on-device" sync'ed with cloud file storage. That's a really good idea! The safest way is to start with an event-based delivery like PubNub (for delivery guarantee) to send/receive the read/write/create/delete events. Then you can commit those changes to each device in real-time. Each device will receive changes from all other devices (like mobile apps and web apps). Also the devices can catch-up when they boot up using the write log sync Persistence API. This allows each DB to stay in-sync in real-time and to sync offline data as well. This is similar to CRDT. Another approach is to use WAL mode SQLite and Min.io or S3 to and a lambda cloud function to accept all writes to a primary writer store, then each device receives WAL events by syncing from the S3 File, or periodically just fetches the S3 file for changes using HEAD requests. Lots of options! The first option will allow for instant sync. The second option will have a delay based on HEAD request frequency.
Just make sure whatever database you go with supports the types of data you might want to query (and index). I don’t believe JSON and spatial are supported.
@denysolleik9896 good point! There's considerations to take when looking at SQLite. It's simpler and has less built-in features. For JSON support you have to go a few steps further creating indexes leveraging a virtual column pattern. antonz.org/json-virtual-columns/ explains you can create secondary columns from JSON data, this allows you to create indexes from specific fields inside the JSON payload. For spatial queries you also need to take a step further and index number fields to be compared within bounding-boxes and distance queries.
To play Devil's advocate, why SHOULD SQLite support JSON? It supports strings. Why not parse the JSON with a library better equipped to do so, and then replace the cell data back with JSON as a string, again, using the JSON parser or library?
I love using python. People are like but it is a slow Language. Most the time the bottlenecks will be the database. Most IO operations are written in C for Python so there is not much performance difference. Then you have other c libraries like numpy, pandas etc... Sometimes I use multiple languages together it just really depends on the project I am working on.
you're spot on! 😄 Python might not be the fastest language in terms of raw execution speed. It has strengths! It is easy to read. It has a lot of community support and a vast ecosystem of libraries like NumPy, pandas, PyTorch. These libraries are often written in C or C++ for performance. Python can use the C library speed. And you're right about the database. The bottleneck in applications is I/O operations rather than the language itself. Using Python in combination with C, C++, or even languages like Rust when necessary provides robustness and high-performance. Choosing the right tool for the job! Great to hear you're enjoying your work with Python and other languages. Keep experimenting and pushing boundaries! 🚀🐍
I am trying to learn how to build out my own server and run things like appflowy on it. Then share it with a small team to manage projects. Could I interview you? I'm looking for advice.
@@StephenBlum Wow! Sweet! I'm not sure what the best way is to share what I would like to do.... I'll message you on LinkedIn and we will go from there.
Good question! 😄 One of my favorite advantages of SQLite is the ability to make multiple SQL calls with nearly zero latency impact. SQLite is located in your applications memory space. PostgreSQL and MySQL are further away from your application code on another computer.
@@StephenBlum on another computer? Only if that's where you put them. Which you generally wouldn't for small projects anyway. Weird thing to say. Seems you're taking about things you don't have any experience with.
@@HappyCheeryChap good catch! 😄 Yes this is a point you make. And I skipped past this entirely. Often the details are critical. And you found a critical detail 🙌 When building access to SQLite on a remote system, you also have to build in a web service which listens and binds on a host port that is accessible to the calling client. Thank you for mentioning! This is important. I like SQLite because you get excellent speed and you get to build how it is accessed. Which does require extra effort.
Oh hi 👋
Hi Kent! Love your videos and your SQL article is amazing! Looking forward to reviewing more of your content. Let me know what might be a good article to read and create another video 🙌🎉
I think SQLite is for most use cases for small businesses.
Using WAL journaling you can get around 10K write simultaneously.
Not every saas, has a target to get 1M users. If you are around and below 1K users. You should be fine to do anything that other db can do.
SQLite can do more than you can think of. 🎉
Also... SQLite is so flexible than you can easily migrate to MYSQL or Postgres Later :)
Nice! 🙂 Those are good stats. SQLite is a great starting point. It is comforting to know that, when needed, you can migrate to my SQL or PostgreSQL
It is already recommended :)
Most web apps never reach a million users because the vast majority of companies don’t have a million customers.
@@KikkerFishYES, exactly what you said.
I think it's worth mentioning that a given SQLite file can only have one active connection at a time, which adds some complication for use on a web server where there may be multiple applications trying to connect to the file at once. It also does not support password protection, so it is not suitable for use with sensitive data on a web server.
that's a fantastic point. The single connection requires the developer to plan ahead on concurrency considerations. For example you could use MPSC/channels to create a dedicated SQLite thread that services requests in your application. This is extra work for the developer. SQLite skips security altogether. Services like PosgreSQL and MySQL have security and concurrency already built-in and taken care of for you! 😄
@@StephenBlum multiple threads can connect to a single sqlite database file without a problem, so you'd never need to implement something like a dedicated SQLite thread in your application. The default journal_mode doesn't allow concurrent reads though, but you can fix this by switching to write ahead logging (WAL). Writes and transactions are problematic because they block your whole database - so for write heavy apps MySQL/Postgres are much better since they support per table/per row locks.
@@anze3db That's sounds amazing! Write Ahead Logging WAL is a great option. The developer won't have to implement read concurrency when they use WAL mode. Your idea is fantastic. Thank you! 😄🙌
I know this, so I never tried it in an app that is deployed to multiple devices. How would you get it to sync? The crazy idea I had was to store it on SharePoint as a file. Then, each person syncs with that SharePoint document library. They each have their own copy, but when OneDrive syncs with the connection to SharePoint, the database will sync changes made by that individual user. It's too scary to try in real life, but that was my thought. How else can you have multiple users?
@@bryanstark324 good question! Multi-user local DB "on-device" sync'ed with cloud file storage. That's a really good idea! The safest way is to start with an event-based delivery like PubNub (for delivery guarantee) to send/receive the read/write/create/delete events. Then you can commit those changes to each device in real-time. Each device will receive changes from all other devices (like mobile apps and web apps). Also the devices can catch-up when they boot up using the write log sync Persistence API. This allows each DB to stay in-sync in real-time and to sync offline data as well. This is similar to CRDT. Another approach is to use WAL mode SQLite and Min.io or S3 to and a lambda cloud function to accept all writes to a primary writer store, then each device receives WAL events by syncing from the S3 File, or periodically just fetches the S3 file for changes using HEAD requests. Lots of options! The first option will allow for instant sync. The second option will have a delay based on HEAD request frequency.
Just make sure whatever database you go with supports the types of data you might want to query (and index). I don’t believe JSON and spatial are supported.
@denysolleik9896 good point! There's considerations to take when looking at SQLite. It's simpler and has less built-in features. For JSON support you have to go a few steps further creating indexes leveraging a virtual column pattern. antonz.org/json-virtual-columns/ explains you can create secondary columns from JSON data, this allows you to create indexes from specific fields inside the JSON payload. For spatial queries you also need to take a step further and index number fields to be compared within bounding-boxes and distance queries.
JSON is supported by SQLite and there are plugins and a special type of index for Geo data
@@yjawhar that's right! SQLite just recently added support for JSON 🎉🎉🎉
@denysolleik9896 SQLite added JSON support 🎉
To play Devil's advocate, why SHOULD SQLite support JSON? It supports strings. Why not parse the JSON with a library better equipped to do so, and then replace the cell data back with JSON as a string, again, using the JSON parser or library?
I love using python. People are like but it is a slow Language. Most the time the bottlenecks will be the database. Most IO operations are written in C for Python so there is not much performance difference. Then you have other c libraries like numpy, pandas etc... Sometimes I use multiple languages together it just really depends on the project I am working on.
you're spot on! 😄 Python might not be the fastest language in terms of raw execution speed. It has strengths! It is easy to read. It has a lot of community support and a vast ecosystem of libraries like NumPy, pandas, PyTorch. These libraries are often written in C or C++ for performance. Python can use the C library speed. And you're right about the database. The bottleneck in applications is I/O operations rather than the language itself. Using Python in combination with C, C++, or even languages like Rust when necessary provides robustness and high-performance. Choosing the right tool for the job! Great to hear you're enjoying your work with Python and other languages. Keep experimenting and pushing boundaries! 🚀🐍
i wish python added braces it'd be so cool
Bro, this was so informative thank you so much.
Glad it was helpful! DB technologies have a lot of variety. The tradeoffs can help you decide the best option for your use case 😊🎉
I am trying to learn how to build out my own server and run things like appflowy on it. Then share it with a small team to manage projects. Could I interview you? I'm looking for advice.
Yes absolutely let me know what you have in mind 😄
@@StephenBlum Wow! Sweet! I'm not sure what the best way is to share what I would like to do.... I'll message you on LinkedIn and we will go from there.
@@HowWebsite sounds great! www.linkedin.com/in/stephenlb/
Spock has a RUclips Channel?! Live long and prosper 🖖🏼
star trek ❤ live long and prosper! 🖖😀
sqlite has a limit on 281 terabytes; so no exabyte....
281 terabytes is a lot! and you are right that 281 isn't exabyte range for a single DB. Good distinction thank you!
So what does "Better-SQLite" do.....better?
Good question! 😄 One of my favorite advantages of SQLite is the ability to make multiple SQL calls with nearly zero latency impact. SQLite is located in your applications memory space. PostgreSQL and MySQL are further away from your application code on another computer.
@@StephenBlum on another computer? Only if that's where you put them. Which you generally wouldn't for small projects anyway.
Weird thing to say. Seems you're taking about things you don't have any experience with.
@@HappyCheeryChap good catch! 😄 Yes this is a point you make. And I skipped past this entirely. Often the details are critical. And you found a critical detail 🙌 When building access to SQLite on a remote system, you also have to build in a web service which listens and binds on a host port that is accessible to the calling client. Thank you for mentioning! This is important. I like SQLite because you get excellent speed and you get to build how it is accessed. Which does require extra effort.
Very helpful video. Thanks! Keep at it.
Glad it was helpful! More videos are in the works 😊🙌
Close your eyes and he's Willy Wonka
Yes can see that 🍫💝 and hear it 😄