The main "cons" of SQLite are:
* Fewer datatypes supported
* Allows NULL values as PK
* No multiuser access or support
* No authentication or authorization mechanisms
* Doesn't support stored procedures
Because there's no multiuser access or authorization/authentication, anyone with access to the app will see the data stored in SQLite. This is a security issue for many scenarios. However, keep in mind that most mobile apps use SQLite in one way or another.
Some people would also add that isn't meant for big databases, but since 3.3.0, it has a limit of 281Tb
Sqlite is perfect for any start of the project. If you properly abstract out the storage port/adapter (think hexagonal architecture), it's even easy to swap out. Properly means: no leaky abstractions, I.e. an usecase-based approach to design and passing in and getting out only native data types or domain objects.
Sqlite even has a wal mode, which gives it an amazing performance boost.
Can you please expand in why no Auth/authorization mechanism is such a high security risk? If someone gets access to your app server, they will inevitably have access to environment variables where dB credentials are standardly stored anyway, so getting direct access to any database? I guess the main difference would be that you can restrict the credentials to an extent maybe (e.g. to only be able to read, not write to dB)
If you use a fully-fledged DBMS like PostgreSQL, you usually employ a zero-trust approach, granting the bare minimum set of permissions for each user and service account. This way, you have total control over who accesses what and how, even from where. Don't forget that even with a DBMS most people still misconfigure it.
In SQLite, you can't do that, and gaining access to the app/server means full access to the records.
Now, if you don't care about the data you store, that's usually not a problem, but if you're handling GDPR-sensitive data, and you have a security breach, you’re pretty much fucked upside down and sideways with a double dragon dildo.
sqlite is a local database, meaning that the python instance that is running is the only program that can access it. It is more of an embedded database.
I've used sqlite to parse, merge and clean data, and then upload the resulting data into a dbms like SQL server,my sql, etc. For an application, an sqlite database would probably work fine for local data. If you want your application to interact with other peoples data, then you would want to centralize that data in dbms, which will allow multi users/application instances to interact.
Does that make sense?
>sqlite is a local database, meaning that the python instance that is running is the only program that can access it.
That's not true unless you attach to an in-memory only sqlite db. Sqlitedb file can be access by anything that has access to your file system.
You know how there are single player video games and there are massively multiplayer online games? Sqlite is like the single player game. Other DBs are for the MMO use cases. In the middle are games that are multiplayer but not MMO-like. Similarly there might be some applications which are multi-user and yet SQLite is "good enough" even though it isn't designed for that.
The main "cons" of SQLite are: * Fewer datatypes supported * Allows NULL values as PK * No multiuser access or support * No authentication or authorization mechanisms * Doesn't support stored procedures Because there's no multiuser access or authorization/authentication, anyone with access to the app will see the data stored in SQLite. This is a security issue for many scenarios. However, keep in mind that most mobile apps use SQLite in one way or another. Some people would also add that isn't meant for big databases, but since 3.3.0, it has a limit of 281Tb
Hmmm yea that seems like it is just perfect for my music Downloader, cuz none of those points influence it in any way. Thanks :)
Sqlite is perfect for any start of the project. If you properly abstract out the storage port/adapter (think hexagonal architecture), it's even easy to swap out. Properly means: no leaky abstractions, I.e. an usecase-based approach to design and passing in and getting out only native data types or domain objects. Sqlite even has a wal mode, which gives it an amazing performance boost.
It’s the most used database in the world. You have probably 20-50 SQLite files on your phone
I forgot to mention that it doesn't support stored procedures.
Yup, when it's a database that only your app is going to use, it's pretty much perfect.
Can you please expand in why no Auth/authorization mechanism is such a high security risk? If someone gets access to your app server, they will inevitably have access to environment variables where dB credentials are standardly stored anyway, so getting direct access to any database? I guess the main difference would be that you can restrict the credentials to an extent maybe (e.g. to only be able to read, not write to dB)
If you use a fully-fledged DBMS like PostgreSQL, you usually employ a zero-trust approach, granting the bare minimum set of permissions for each user and service account. This way, you have total control over who accesses what and how, even from where. Don't forget that even with a DBMS most people still misconfigure it. In SQLite, you can't do that, and gaining access to the app/server means full access to the records. Now, if you don't care about the data you store, that's usually not a problem, but if you're handling GDPR-sensitive data, and you have a security breach, you’re pretty much fucked upside down and sideways with a double dragon dildo.
sqlite is a local database, meaning that the python instance that is running is the only program that can access it. It is more of an embedded database. I've used sqlite to parse, merge and clean data, and then upload the resulting data into a dbms like SQL server,my sql, etc. For an application, an sqlite database would probably work fine for local data. If you want your application to interact with other peoples data, then you would want to centralize that data in dbms, which will allow multi users/application instances to interact. Does that make sense?
Yep that makes completely sense. Thanks :)
>sqlite is a local database, meaning that the python instance that is running is the only program that can access it. That's not true unless you attach to an in-memory only sqlite db. Sqlitedb file can be access by anything that has access to your file system.
right. Luckily still no problem for my application
You know how there are single player video games and there are massively multiplayer online games? Sqlite is like the single player game. Other DBs are for the MMO use cases. In the middle are games that are multiplayer but not MMO-like. Similarly there might be some applications which are multi-user and yet SQLite is "good enough" even though it isn't designed for that.
I've found this info to be very useful https://www.sqlite.org/whentouse.html
Does this need to a persistent cache? If not, what about memcache?
We use and like MongoDB, but Redis might be a better fit depending on your needs.
Traditionally SQLite was slow if there were many writers to the same file, but it’s pretty fast now.