Recent searches
Search options
@mo8it@fosstodon.orgWhat database do you prefer as a user of a self-hosted app? 
I want to support only one for
#OxiTraffic
PostgreSQL is much more flexible and powerful from a developer point of view. It can also scale much better just in case.
But SQLite does not require additional hosting. It is one file that can be automatically created by the app itself.
Comments on your choice are appreciated 
@mo8it for me I would say postgres, just because I already have it set up, including backups which means that any additional database created for a new service will also be automatically backed up, which would not be the case with sqlite.
I just create a volume for the container to put its sqlite file in, and my automation backs this up automatically without extra setup. I've never gone through the pain of adding SQL tables dumping to my backup automation.
@mo8it sure SQLite is great for many cases, but if you only want to support only one, i would say something that does scale better and support more cases like PostgreSQL is better, unless you are really sure that SQLite will always be enough for this particular case.
@mo8it Oftentimes PostgreSQL DBs come with an online admin tool. With SQLite easiest approach immediately available is to download the file to local, do changes, and then upload it back. Over time this can get a bit confusing and tedious, which is one reason why I ported SQLite app to PostgreSQL.
@mo8it PostgreSQL, mostly for developer reasons. Scaling, independent upgrades of the database tech, richer feature set: native time and array types…
As a user, those are pretty clearly the best two choices. SQLite is invisible to the user unless they need to handle the storage.
@mo8it As a _user_, SQLite all the way. Provided I don't have to touch the .sqlite file, and everything is handled by the software or Docker or whatever.
I have the cheapest VPS ever, and having to run a full RDBMS when I'm the single user of a self-hosted app makes no sense.
Edit: also as a user, making a backup of a .sqlite file is definitely easier than anything else.
@mo8it I used server-based database systems like Postges or MySQL when writing complex, high-traffic web applications for clients.
But for my own work—even public-facing websites that get a fair bit of traffic—I go as simple as possible. My most popular website doesn't even use SQLite; it uses some BerkeleyDB files.
I have never once regretted this philosophy, which I am sure has saved me much time and heartbreak on the projects I care about the most.
@mo8it I would say it depends on the bandwidth, db size and the need for reliability and concurrency.
Personally I think SQL should not exist as a language but should be a clean ABI, and I prefer no-SQL key-value such as sled.
@mo8it Postgres as then I can throw it on one of the many cloud Postgres hosts out there and know there will be backups and such without me having to care for any specific file on disk
And allows me to scale my code horizontally and to run it in environments that don’t support saving stuff to disk, like Heroku and Heroku clones
Basically: I like twelve factor apps and SQLite doesn’t really rhyme well with it? https://12factor.net/
@mo8it depends on what app you have in mind. For a standalone desktop application or a service with few users, SQLite can be a way to go. As you say, it's easy to set up. But beware of upgrades: the last time I checked, it did not support all ALTER TABLE statements. The second caveat is concurrency: even in WAL mode, it uses a lot of locking. If either is a problem, use a "grown-up" database.
@mo8it I'd use a framework like sqlx so that you can start with an SQLite file, but then it's almost trivial to just swap in Postgres if you get enough data/traffic/whatever.
@d2718 I do already use SQLx with PostgreSQL. But I have been asked if I could support SQLite to avoid hosting PostgreSQL.
@mo8it Well, I def vote for using SQLite until you can't anymore. In my experience SQLite starts to noticeably slow once the DB file starts to push 50 MB, but that may just have been my use case.
@mo8it While i understand the appeal of the simplicity one gets by using SQLite, I am currently having scaling problems with something that only supports SQLite.
@mo8it I really like Postgres but if the size and scale of the application is fine for SQLite, why complicate things?