Switching from Sqlite3 to MySql or Postgresql and how
I now have the problem that whenever I deploye something with capistrano my Sqlite3 database disappears. I have already read that it is in a different folder or that there are problems with it.
Now I'm thinking about switching to MySql or Postgresql, but this seems quite difficult to me. I now have a frustrating day behind me, which led me to go back to a git version from yesterday ... and then I was very annoyed not to take a snapshot of my DO server. That set me back very much and the SSH key confusion continues again.
Nevertheless I would like to know if there is a simple workaround for the sqlite3 problem or if I should really try to change the database again. This seems to have some effect on the code as well. I already had them running locally. Only on the server it didn't work then and then, when I tried to undo everything via Git version control my website always crashed.
Thats the walk of pain ...
If you're using sqlite3 in production, you'd need to make sure the database file is stored in a shared directory and symlinked every deploy. You can do that pretty easily by adding to the symlink stuff in Capistrano.
Every deploy creates a new folder with your code in it, so you need to link the database to each one so they share it. This setup allows you to rollback deployments to previous releases if there is a problem.
MySQL or Postgres is definitely recommended for production though. They're designed for performance and having many users read and write to the database quickly. Sqlite is a file on disk so it is significantly slower and not something you'd want to run in production.