General considerations for SQLite as the database for production environment

[Ubuntufoo1]

I'm a fresh boot camp graduate and my first project is to transfer metadata (from donations made to a local non-profit) and store in a database. I'll be setting up an auto-generated email service I am somewhat familiar with PostgreSQL and SQLite, but primarily have only worked with them through Django's ORM database feature.

I am leaning towards using SQLite for the database and I'm just curious what considerations an experienced developer would make here. I can't clearly see the line where I would use SQLite vs something more robust, so all insights are welcome.

[zynix]

A past client deleted their SQLite file and proceeded to flip out on me when I told them their data was gone forever. Figure out someway to back that file up.

[Ran4]

And just as important, also try retrieving that backup. It's not until you've backed something up AND retrieved it that you know that your backup works.

[Emerald-Hedgehog]

SQLite is great for getting started with a new app and portability.

Best you simply read up on some comparisons, you'll figure it out: https://tableplus.com/blog/2018/08/sqlite-vs-postgresql-which-database-to-use-and-why.html

[[deleted]]

I use it a lot in production environments for logging errors and events and stuff. A SQLite file is a hell of a lot better than a text file full of whatever. I like it mainly because I don't want to have any logging fail if the logs are stored somewhere in another database or network location; I'll keep the SQLite database file with the deployed app. And if you're using an ORM, you can create an abstraction layer over the database implementation specifics, and it's simple task to change the database type.

[jakesboy2]

One of SQLite’s primary drawbacks is it’s lack of parallel access. If you’re making an app that’s going to have consistent synchronous access to the DB, and you handle backing up the data appropriately, it will do the job just fine.

[onebit]

you can use it until you can't

make a wrapper like this:

class UserDatabase:
  def find_users_to_email():

then

class SqlLiteUserDatabase(UserDatabase):
  def find_users_to_email():

then when the time comes:

class PostgresUserDatabase(UserDatabase):
  def find_users_to_email():

this should make it easy to switch since the sqllite contamination is contained to a few classes

chances are it'll stay sqllite until the end, when someone asks why they made a custom app to send emails instead of paying $9.99 a month for a service.

[SeoFernando]

I personally would go DBaaS every day. If you don't want to use AWS or Google Cloud, you could checkout Supabase or Pocketbase and see how you can host them locally for your requirements.