Personally I never do permissions at the database layer anyway - it’s always done at the application layer.
I also never move the database to a separate server - that adds too much latency. If your database is local, and “hot” tables are cached in RAM, you can do several hundred thousand queries in a split second and having performance like that can drastically reduce complexity in your business logic (and therefore, drastically reduce bugs).
Regardless, I don’t see it as something that is the silver bullet that people make it out to be. Being able to introspect the production database, query it, and generally have a set of tools to properly manage your data as opposed to having everything in a file fully managed by your application is something useful for me that you lose with SQLite.
I’m not sure I understand your point? You can connect to and run queries/etc on the production database in SQLite.
I’m not really advocating for using SQLite by the way - I’ve only ever used it on smartphone apps myself where a full database wouldn’t even have enough RAM to run at all. I’m just pointing out that permissions isn’t a feature I’ve ever found useful.
For example, say I have an invoice table that is written to whenever a customer buys a product. Customers need to have write access to the table in the database. But I don’t want them to be able to write anything they want - there needs to be severe restrictions on what can be written, and those have to be done outside of the database.
Since you have to do some of your permissions outside the database, it’s more reliable to just do all of them there. Splitting things up with half your security in one place and half in another is asking for bugs.
The main reason I would avoid SQLite is the backup system, which essentially takes your whole database offline (for write access anyway) while the backup is running. That’s just not good enough once the database reaches a size where backups take more than a moment. But if you’re not storing much data, or not doing many writes, that’s a non-issue.
SQLite definitely has advantages. It’s often extremely fast for example. The lack of complex features removes performance bottlenecks all of the place and you can do millions of basic select queries per second in SQLite. Obviously not every query is that fast, but a lot of them are especially if you design your indexes/etc properly.
Definitely not a silver bullet, but I do think anyone who writes code that reads or writes data should be at least aware of the basic capabilities of SQLite. It’s free. It’s reliable. It runs literally on any platform (you can even run it client side in a webpage these days). So the only reason to avoid SQLite is if it’s the wrong tool for the job. And you can’t make that judgement call unless you have experience with it.
SQLite should be in every developer’s toolchain, even if you don’t have a use for it right now.
It’s useful for audit trails and the like, generally OS audit logs only tell you who accessed the machine not what they did on the production database. Things like that. Databases like postgres come with admin tooling in general that SQLite isn’t really meant for. As you said, backups as well are a problem.
Personally I never do permissions at the database layer anyway - it’s always done at the application layer.
I also never move the database to a separate server - that adds too much latency. If your database is local, and “hot” tables are cached in RAM, you can do several hundred thousand queries in a split second and having performance like that can drastically reduce complexity in your business logic (and therefore, drastically reduce bugs).
Regardless, I don’t see it as something that is the silver bullet that people make it out to be. Being able to introspect the production database, query it, and generally have a set of tools to properly manage your data as opposed to having everything in a file fully managed by your application is something useful for me that you lose with SQLite.
I’m not sure I understand your point? You can connect to and run queries/etc on the production database in SQLite.
I’m not really advocating for using SQLite by the way - I’ve only ever used it on smartphone apps myself where a full database wouldn’t even have enough RAM to run at all. I’m just pointing out that permissions isn’t a feature I’ve ever found useful.
For example, say I have an invoice table that is written to whenever a customer buys a product. Customers need to have write access to the table in the database. But I don’t want them to be able to write anything they want - there needs to be severe restrictions on what can be written, and those have to be done outside of the database.
Since you have to do some of your permissions outside the database, it’s more reliable to just do all of them there. Splitting things up with half your security in one place and half in another is asking for bugs.
The main reason I would avoid SQLite is the backup system, which essentially takes your whole database offline (for write access anyway) while the backup is running. That’s just not good enough once the database reaches a size where backups take more than a moment. But if you’re not storing much data, or not doing many writes, that’s a non-issue.
SQLite definitely has advantages. It’s often extremely fast for example. The lack of complex features removes performance bottlenecks all of the place and you can do millions of basic select queries per second in SQLite. Obviously not every query is that fast, but a lot of them are especially if you design your indexes/etc properly.
Definitely not a silver bullet, but I do think anyone who writes code that reads or writes data should be at least aware of the basic capabilities of SQLite. It’s free. It’s reliable. It runs literally on any platform (you can even run it client side in a webpage these days). So the only reason to avoid SQLite is if it’s the wrong tool for the job. And you can’t make that judgement call unless you have experience with it.
SQLite should be in every developer’s toolchain, even if you don’t have a use for it right now.
It’s useful for audit trails and the like, generally OS audit logs only tell you who accessed the machine not what they did on the production database. Things like that. Databases like postgres come with admin tooling in general that SQLite isn’t really meant for. As you said, backups as well are a problem.