PostgreSQL support

[junpet]

Is your feature request related to a problem? Please describe. I'm just gonna spin up and I've noticed only SQLite is supported. It would be nice to use with a proper database.

Describe the solution you'd like Use PostgreSQL instead of SQLite.

Describe alternatives you've considered Maybe MySQL support for some people.

Additional context

[jmattheis]

It would be nice to have to support postgres or mysql but I don't think it is worth the development effort. SQLite will work nicely with small userbases and thats exactly for what it was designed (:.

[Skeen]

Please reconsider this @jmattheis.

While SQLite does indeed work nicely with small userbases and all, some users may already have managed database infrastructure in place which they wish to use, for instance because it provides proper backup, etc.

I was able to get Traggo running under Postgres by with minimal changes:

diff --git a/model/all.go b/model/all.go
index 27d2378..a5893ab 100644
--- a/model/all.go
+++ b/model/all.go
@@ -3,8 +3,8 @@ package model
 // All returns all schema instances.
 func All() []interface{} {
        return []interface{}{
-               new(TagDefinition),
                new(User),
+               new(TagDefinition),
                new(Device),
                new(TimeSpan),
                new(TimeSpanTag),

This makes the migration work, by ensuring that inter-relation dependencies are ordered. As the code stands now TagDefinition depends on User which makes the automigration fail on Postgres.

diff --git a/model/device.go b/model/device.go
index 589ca63..ed8d594 100644
--- a/model/device.go
+++ b/model/device.go
@@ -14,7 +14,7 @@ type Device struct {
        Name      string
        UserID    int `gorm:"type:int REFERENCES users(id) ON DELETE CASCADE"`
        CreatedAt time.Time
-       Type      DeviceType
+       Type      DeviceType `gorm:"type:bytea"`
        ActiveAt  time.Time
 }

This makes the device lookups work as the code seem to expect byte[] access. I expected it to work out of the box, but it does not on Postgres. I tried to use Gorm's bytes type, but not in fact work for Postgres, it yields an error that bytes is not a known type in postgres, so the appropriate type would be bytea.

This last change is however destructive to SQLite support as SQLite does not have a bytea type. I don't know Gorm, so I am not sure how to specify a different type for each database dialect.

[jmattheis]

It could be done similar to https://github.com/gotify/server/blob/master/database/database.go#L70, I'm open to accept a PR for this. But please make sure, the statistics endpoint for calculating the dashboard aggregation does work https://github.com/traggo/server/blob/master/statistics/summary.go#L38

[alonsomoya]

I'm indeed experiencing concurrency issues while deploying traggo in kubernetes. With SQLite & CIFS, and the DB being locked even with a simple pod/container, so I could not make traggo to work with PersistentVolumes. Did postgres support got implemented in the meanwhile? I would surely be interested

[jmattheis]

No, not implemented and likely won't in the future. See explanation in my first comment.