7 ms·

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Set

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - 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.

mekster 5y ago
So you trade for some risk for an hour.
- kyawzazaw 5y ago
  Pieter Levels has been using SQLite for nomadlist and I think it's been going well for him.
- TheRealPomax 5y ago
  Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (and less failure-prone for light db work) than an independently running database service.
  - deckard1 5y ago
    your app has full access to the SQLite file. MySQL/PostgreSQL have users and permissions. Security is about layers, and SQLite is removing one layer of security. You can, for example, put DELETEs or access to certain tables on a separate user that your web app has no access to. With SQLite, if your app gets hacked then they can do anything with the whole DB they want to. In addition, with a separate DB process you get audit logs. If someone hacks your SQLite app they may have access for months before you realize it, if you ever do. Especially if they are doing something subtle like UPDATEs on specific tables/fields that may go unnoticed but provide the hacker some benefit. This is why you can't simply rely on the idea of using a backup. That's only going to help if the hacker totally trashes your DB. 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.
    - TheRealPomax 5y ago
      If your server or API can be exploited, it doesn't matter whether there's an auth layer in between. Your SQL server runs as a service to connect to, your sqlite3 file is a file that you need access to. They're the same kind of layer: you need to break through the server's security to ever get to them directly, and if your app gets hacked such that the hackers gain file system access, then: 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.
      - ipaddr 5y ago
        With sqlite your server, api or application can be hacked. The most common and likely hack would be somewhere in your application. It really doesn't make sense to use sqlite here. Setup a separate database server and use it for all of your projects. That one hour pays off each and every project.
  - mekster 5y ago
    The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning. 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.
    - TheRealPomax 5y ago
      That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project). 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.
      - mekster 5y ago
        Inefficiency or not, if you start down the path of SQLite, you need to invest good amount of time refactoring into another DB if you feel like migrating away. 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.
        TheRealPomax 5y ago
        While that's an unfortunate discovery, I do need to point out that that's a problem of your own making, not SQLite's fault. SQLite is very clear on what you can expect, with dynamic typing being one of the main differences between it and "regular SQL" databases. 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 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 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 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.
- post-it 5y ago
  Some risk of what?
jamesrr39 5y ago
docker-compose is the way to go for keeping the dev versions synced with the production version. And for the backup script scheduled mysqldump and copy to storage should see you through quite far, so not really any more effort than copying an SQLite database.
- b20000 5y ago
  and now you need to learn docker and 10 other things.
- orthecreedence 5y ago
  This person is talking about reducing complexity, so I don't think adding more moving pieces to the machine is the way to go.
marcosdumay 5y ago
Hum... MySQL is one of those labor-generating technologies that a Luddite would fall in love for. Those are best avoided. But for Postgres... - 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.

mekster 5y ago

So you trade for some risk for an hour.

kyawzazaw 5y ago
Pieter Levels has been using SQLite for nomadlist and I think it's been going well for him.
TheRealPomax 5y ago
Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (and less failure-prone for light db work) than an independently running database service.
- deckard1 5y ago
  your app has full access to the SQLite file. MySQL/PostgreSQL have users and permissions. Security is about layers, and SQLite is removing one layer of security. You can, for example, put DELETEs or access to certain tables on a separate user that your web app has no access to. With SQLite, if your app gets hacked then they can do anything with the whole DB they want to. In addition, with a separate DB process you get audit logs. If someone hacks your SQLite app they may have access for months before you realize it, if you ever do. Especially if they are doing something subtle like UPDATEs on specific tables/fields that may go unnoticed but provide the hacker some benefit. This is why you can't simply rely on the idea of using a backup. That's only going to help if the hacker totally trashes your DB. 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.
  - TheRealPomax 5y ago
    If your server or API can be exploited, it doesn't matter whether there's an auth layer in between. Your SQL server runs as a service to connect to, your sqlite3 file is a file that you need access to. They're the same kind of layer: you need to break through the server's security to ever get to them directly, and if your app gets hacked such that the hackers gain file system access, then: 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.
    - ipaddr 5y ago
      With sqlite your server, api or application can be hacked. The most common and likely hack would be somewhere in your application. It really doesn't make sense to use sqlite here. Setup a separate database server and use it for all of your projects. That one hour pays off each and every project.
- mekster 5y ago
  The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning. 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.
  - TheRealPomax 5y ago
    That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project). 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.
    - mekster 5y ago
      Inefficiency or not, if you start down the path of SQLite, you need to invest good amount of time refactoring into another DB if you feel like migrating away. 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.
      - TheRealPomax 5y ago
        While that's an unfortunate discovery, I do need to point out that that's a problem of your own making, not SQLite's fault. SQLite is very clear on what you can expect, with dynamic typing being one of the main differences between it and "regular SQL" databases. 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 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 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 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.
post-it 5y ago
Some risk of what?

jamesrr39 5y ago

docker-compose is the way to go for keeping the dev versions synced with the production version. And for the backup script scheduled mysqldump and copy to storage should see you through quite far, so not really any more effort than copying an SQLite database.

b20000 5y ago
and now you need to learn docker and 10 other things.
orthecreedence 5y ago
This person is talking about reducing complexity, so I don't think adding more moving pieces to the machine is the way to go.

marcosdumay 5y ago

Hum... MySQL is one of those labor-generating technologies that a Luddite would fall in love for. Those are best avoided. But for Postgres... - 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.