Ask Lemmy

If I'm writing an app and need it to embed a db, SQLite. If it's running on a server, postgres.

There isn’t a one size fits all database. It really depends on what you want to do

SQLite for almost everything I've ever done that wasn't professional. In those cases it's "whatever the project already uses, or else postgres".

As long as it isn't a shoehorned-in freaking document database that's being used on relational data.

Postgres is what the cool kids use these days i think

What do you use and why?

I've used sqlite for structured storage by scripts and the like. From a performance standpoint, it's not amazing, but easy to set up and doesn't have resource usage if it's not actually being used.

If I were going to use an always-active daemon, I'd probably default to PostgreSQL, mostly because decades back, the last time I looked at it, it was considered to have some technical advantages over MySQL. And at one point I did some hacking on its scheduler, so I have a bit of a warm feeling for it.

Do you think server hardware is required for most operability or can a good desktop computer and internet speed suffice as long as downtime isn't a problem?

I probably wouldn't think of things as "server" hardware" and "desktop" hardware. That's really a product of vendors trying to segment the market up, which lets them engage in price discrimination, as business buyers tend to be less price sensitive. Maybe you want more memory or a faster form of storage or something.

As to performance, I mean, it depends entirely on your use case. I would guess that the vast majority of database-using software out there has performance be a non-issue.

If you're looking for potential land mines, I'd suggest making sure that your backup system is aware of the database and can take an atomic snapshot, so that you don't get a garbled, being-written copy every time you do a backup.

If you're trying to figure out whether to buy a lot of hardware, though, I can tell you that without knowing what you're doing, I would not go out and buy new hardware just over performance concerns until you've actually tried doing your project on existing stuff and found that it's inadequate. People have run useful, large databases for many decades on much weaker hardware and slower storage than is generally-available in 2024.

PostgreSQL. It's head and shoulders above the competition... clearly superior. :)

Those services come with their own databases that you don't have to directly manage.

Grab their respective Docker compose files and run them up and let the app handle the db.

The !selfhosted@lemmy.world communities can give far better answers than a generic community like this.

Is this for hosting at home with a home internet? Use a PC or even something like a raspberry pi. Just be frequent about backups if there is anything important on it. It might be easier to use a cheap VPS though, even if that doesn't qualify as total self-hosting.

If the software you want to run recommends a particular database, use that. Otherwise, for small stuff, SQLite is good enough for most purposes, and less hassle than most alternatives.

The only database I have worked with is MariaDB/MySQL, I use it as a database to a temperature logger that logs the temp of water at a swimming hole and uploads the data to website.

The logger is ab rpi zero, with a timer and a DS18B20 under water temperature probe.

The timer will turn on the rpi once every 30 min, I set up a bash script that gets the temp data, formats it, checks for errors, if found, checks again, up to three times in total if needed then using curl connects to a webpage to send the data (Name, Temp and Status), waits 30 sec, then shuts down the rpi, shortly before the timer cuts power.

The script is run a on startup as a systemd service, the reason for the 30 sec wait before shutting off the pi is to give me time to logon to the pi and stop the script if I need to do maintenence on it.

The website it built in PHP and MySQL/MariaDB, and runs on a webhotel, there are a few pages, one to upload data, one to get a list of the latest 40 readings, snd one mobile page that shows the current temp in larger font and a graph of the temperature changes.

The correct term is lemrade

Downvoted because people didn't like my attempt at a "hook" tagline, darnit