- Setting up an out-of-repo config file on the server with your MySQL credentials
- Setting up a backup script for your server data
It's only about an hour of work total, but it's an hour of work that I hate doing.
- You make sure the production version is larger or equal than the development, or you make sure to not use new features before they reach production, what is quite easy. There is no problem with different OSes (except for Windows itself not being very reliable, but I imagine you are not using Windows on production, as it's another one of those labor-generating techs).
- Trusting a local user is the same level of security you get with SQLite, no credentials required.
- And setting a backup script... Wait, you don't do that for SQLite? There's something missing here.
Yes, there are a lot of small tasks that add up when setting some new software. It's a pain. But it's a pain you suffer once, and it's over. It's worth optimizing, but not at any ongoing cost.
With a separate DB you may have a hope of detecting when someone hacked your app. But without that firewall, the question becomes: how much of the data in my SQLite can now be trusted? If you don't know what backup is safe to restore, then you can't trust any of it.
Again, this is about layers. Not saying MySQL/Postgres will save you. But they can increase the odds.
1. You're fucked. The end. It doesn't matter whether you were using mysql, postgres, or sqlite3, or S3, or Redis, or any other server your app was connecting to: they can just look at your environment vars.
That's not going to happen "because you're using Sqlite3", that's going to happen because you used some obscure server software, or worse, rolled your own.
People really do seem to put too much faith into "it has a username and password, it's more secure". It's not: if someone has access to your actual server, they have access to everything your server has access to. Sqlite3 is no more or less secure than a dbms daemon (or remote) in that sense.
Setup a separate database server and use it for all of your projects. That one hour pays off each and every project.
I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code.
If I rely on PostgreSQL, I feel covered for my use case for web apps but once you hit some little gotchas in SQLite, you may regret about saving 10 minutes (install db and set up a password) for nothing.
When we're talking about risks, think security exploits: how is sqlite3 more likely to get your data leaked, or flat out copied in its entirety, compared to using a mysql/postgres/etc.
When I realized SQLite would store any type of data in any kind of column type, it was obvious SQLite is different from others. They only added strict types about a year ago but scared me enough not to use it.
And how is SQLite any less secure? You can flat out copy its entirety using pg_dumpall. I'm not talking about security.
Yes, plenty of folks just go for SQLite blindly, but the consequences of that aren't SQLite's doing: if you take the time to read through https://www.sqlite.org/features.html because you want to know what it can actually do, you'll almost certainly click through to https://www.sqlite.org/omitted.html because you'll want to know what it doesn't do, and then you'll see the "See also the Quirks, Caveats, and Gotchas of SQLite." link and you're going to follow it an read through https://www.sqlite.org/quirks.html because those sound pretty important to know about before you commit to using something that is going to be your application/service data store for the foreseeable future.