Can I use PostgreSQL with Nerves?

I believe it is an option.. but..

Full Databases such as PostgreSQL or MongoDB

PROS: Ecto adapters make these relatively easy, multi user, etc.
CONS: Require a large setup on Nerves - Custom system, configs, setup etc.

From: https://embedded-elixir.com/post/2017-09-22-using-ecto-and-sqlite3-with-nerves

What kind of stuff will you use the server for? I’ve been thinking of ways to use Nerves myself so curious what you will be using it for

But is it a hard drive/SSD? Given
Clarification on DETS/Nerves issue mentioned at ElixirConf
I’d imagine that PostgreSQL might drive a flash drive pretty quickly into the ground.

It should work fine for your use case. The reason there is not a lot of PosrgreSQL with Nerves is that it does not handle sudden power failures all that well. This is normally a deal breaker for embedded devices

If all you want SQL + Ecto, Connor has done a great job of fixing the Ecto adapter for SQLite https://github.com/ConnorRigby/sqlite_ecto2. It would be much easier to add to a Nerves project than PostgreSQL.

I’m mainly thinking of using nerves to create a simple (and easy to “administer”) Linux server out of a raspberry pi and some hard drives that I have laying around. It’s really kind of my other topic restated: Can you use nerves to deploy a cloud-based server?

Although I have also thought about trying to build an automated alarm clock that would open up the blinds in the morning. Just not sure how to find a compatible RF remote.

I haven’t decided yet but I’ll probably just do a spinning hard drive since I have a few of those. Although I’m not too worried about PostgreSQL wearing out an SSD, I think often times that type of concern (with SSD’s) is a bit overblown and this server will be strictly for my personal use so it won’t get a lot of activity.

Yeah I may end up relenting, but PostgreSQL is much nicer to develop with than SQLite (although I haven’t used SQLite in ages so maybe I should try it out again).

The concern wasn’t about SSDs which implement wear leveling but about cheaper SD card/flash drive storage solutions with inferior flash memory controllers.

Example: USB 3.1 Solid State Flash Drive vs USB 3.1 Flash Drive

I have a bit of a bias, but setting up Sqlite is so much easier. Postgres not only requires you to build your own custom system but is a huge overhead for packaging your firmware. for example a basic Nerves based app that uses sqlite and ecto will weigh in at less than 60 megs. This takes about 3 seconds to write to an SD card, or 15 to update over the network. Compare that to about 160 megs with postgres and all its dependencies, 10 seconds to flash, and 30 to update over the network. Those amounts of time may not seem like much now, but i promise you - they add up.

There is also the power loss issue, which you may or may not care about.

Kevlin Henney - The Architecture of Uncertainty

Instead of trying to decide between options A and B, the question becomes “How do I design so that the choice between A and B is less signifcant?”. The most interesting thing is not actually the choice between A and B, but the fact there is a choice between A and B (and that the appropriate choice is not necessarily obvious or stable).

You could approach it as a design challenge. Given that you are more comfortable with PostgreSQL, use it to explore (right now, on your development machine) “what” you want to build. In parallel explore the constraints that SQLite will impose upon you. Then come up with a design that can be accommodated by either.

If you want to explore the notion of “Persistance Ignorance (PI)” you’ll likely end up implementing your own repository (not to be confused with Ecto.Repo).

PI means clean, ordinary [data structures] where you focus on the business problem at hand without adding stuff for infrastructure-related reasons. (Applying domain driven design patterns, p.183)

With PI the type of storage (SQL/RDBMS, key/value store, files) etc. would be hidden away behind the repository boundary. The repository could then either simply defer to Ecto or implement persistence in some other way.

Is this approach overkill for a system running on a single board computer?
Probably, most likely, yes.
But I think that using PostgreSQL with a mapper could also be considered overkill - though one cannot argue with the initial development convenience.

It’s about what you want to get out of this project experience - it could be a good opportunity to experiment with a variety of persistence solutions.