DB PostgreSQL support

I’m running PostgreSQL for my Mastodon instance, and I’d like to use the same DB for writely to save resources and keep costs down.

Any chance of supporting a DB abstraction to allow both maria and PostgreSQL?

The next (and likely final) database engine the team is going to build support for will be SQLite. Our primary goal is to make WriteFreely installation as easy as possible, so that’ll help everyone avoid these relatively heavy engines and keep everything self-contained. However, if anyone in the community wants to contribute the changes necessary for PostgreSQL support – and especially if they want to help maintain it – we’ll gladly welcome that.

With SQLite support we’ll also add test cases to ensure database functions work consistently across different storage engines, etc., so those changes should make supporting other backends (like PostgreSQL) much easier.

(Note: we’ll continue the previous GitHub discussion about this on this thread.)

I’ll be working on the SQLite support this weekend. While I’m doing that work, I’ll also be noting things that might need to be changed to work with PostgreSQL.

1 Like

Awesome, that’ll help a lot :+1:

+1 for wanting PostgreSQL support, other federated software uses it as well (Mastodon, Pleroma, Matrix) so it’s nice to run them all in the same db server

Is anybody else working on this right now?

Would people be happy to see Postgres support shoe-horned into the existing database.go, or would some other approach be needed?

Bump. I’m all for SQLite as a primary DB if that’s the goal…

but mastodon/pixelfed/funkwhale and others are all on postgres… so I’m keen on hosting less moving parts

We’ll be happy to support Postgres as well – we just doesn’t have the bandwidth for all that extra maintenance work without dedicated funding or development help from the community.

MySQL is our priority since we use it in production on Write.as, and SQLite ensures WriteFreely is easy to deploy, a primary product goal for us. So we just need some extra help for other DB engines.

@robw Adding it into database.go should work just fine! Everything else is in there already.

I’d also be interested in PostgreSQL support, as I run a small server that uses PostgreSQL-only software and would rather share a single database system. That said, I 100% understand that you don’t have the bandwidth to implement it without external help, and I definitely feel that MySQL is more appropriate for larger-scale applications.

1 Like

This task might be easier if WriteFreely used something like xorm.

On one hand, database abstraction layer would be nice and open new possibilities to introduce different engines. On the other hand, even most common relational databases have certain differences so it might add extra overhead to implement.

SQLite, in my view, is applicable only to tiny implementations or apps (Android uses it as app data backend). Main issue that doesn’t allow me to treat SQLite seriously is dynamic/weak datatypes. Therefore any decent relational database is much better for integrity and scalability.

So official support for at least one modern RDBMS is a must. When it comes to web apps, MySQL/MariaDB is so popular due to easier maintenance (my subjective opinion), but as other fediverse servers like Mastodon, PeerTube, etc. have chosen PostgreSQL, it might be useful to consider.

I myself haven’t gone deep into database model used by WriteFreely at the moment, but I might give some suggestions bit later.

1 Like

Yeah, I always prefer less abstraction, especially with critical layers of the app. The plain query system is a bit messy, but it works correctly and is easy to debug. Abstraction creates more work when things go wrong, and a new database driver on top will only multiply the effort needed.

For anyone interested in helping us move this forward, I just wrote a basic walkthrough of what’s needed: PostgreSQL support. Please feel free to give it a shot and let us know how it goes!