Postgres Support

[mraerino]

- Do you want to request a feature or report a bug?

Feature request

- What is the current behavior?

The docs state only mysql to be supported

- What is the expected behavior?

I'd really like to have postgres support. What are the steps to get there? pop should support postgres right?

[goodforever]

It's good if gotrue could support gorm like gocommerce . I like postgresql too.

[mraerino]

The underlying orm (from gobuffalo) actually supports postgres and I actually got it working by converting the initial MySQL setup to postgres format

[goodforever]

@mraerino Can you share your codes with me ?

[mraerino]

@goodforever here is the postgres setup based on the current migrations: https://gist.github.com/mraerino/d6932e2c01c79fa453d705f5ae93e641

This is being used in a docker setup with a db user called gotrue

Please note: Any further migrations will not be covered and could lead to problems.

[goodforever]

@mraerino I don't start gotrue now ,so I have no data dump problem , I hope to modify gotrue to support postgresql ,then use it. Do you have modified gotrue codes for pg support ?

[rybit]

So, I remember there was a reason to rip out gorm, but for the life of me I can't remember what it was. We would happily consider a PR to add gorm -- or other ORM -- but I don't think at netlify will be adding it anytime soon. I def won't stop the community though :)

[goodforever]

I see https://github.com/netlify/gotrue/pull/47 ,It can support postgres, but there are no these code from git clone .

[mraerino]

@goodforever there are no code changes needed to support postgres. it is already possible.

Try this configuration:

DATABASE_URL=postgres://user:password@host/db?sslmode=disable
GOTRUE_DB_DRIVER=postgres

The dump I linked to before has to be applied once to the database before launching gotrue.

[goodforever]

@mraerino What driver package is it for this config ? I use github.com/jinzhu/gorm/dialects/postgres ,When ./gotrue migrate, I can migrate only one table "schema_migration" . I got errors FATA[2018-09-26T21:11:14.417997032+08:00] pq: syntax error at or near "`" github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop.(Connection).timeFunc /Users/work/space/go/src/github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop/connection.go:184 github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop.(Query).Exec /Users/work/space/go/src/github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop/executors.go:19 github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop.(*FileMigrator).findMigrations.func1.1 /Users//work/space/go/src/github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop/file_migrator.go:71 github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop.Migration.Run /Users/work/space/go/src/github.com/netlify/gotrue/vendor/github.com/gobuffalo/pop/migration_info.go:27 ........ etc.

[mraerino]

@goodforever my advice was to not apply any migrations but instead build the database using my dump. The migrations are mysql only and are not going to work.

[mraerino]

What driver package is it for this config ? I use github.com/jinzhu/gorm/dialects/postgres

@goodforever and since this project does not use gorm, you will not have any luck using any gorm tooling.

[goodforever]

@mraerino I just want to support postgres , I read doc of github.com/gobuffalo/pop ,it can support postgres ,but how can I let it work ? if I don't migrate mysql sql ,is it correct in following config ?

in dial.go file package storage

import ( "net/url" "reflect"

//_ "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/mysql"
//_ "github.com/go-sql-driver/mysql"
"github.com/gobuffalo/pop"
"github.com/gobuffalo/pop/columns"
//  _ "github.com/jinzhu/gorm/dialects/postgres"
"github.com/netlify/gotrue/conf"
"github.com/pkg/errors"
"github.com/sirupsen/logrus"

)

in .env file

GOTRUE_DB_DRIVER=postgres DATABASE_URL=postgres://gotrueuser:123456@localhost:30257/gotruedb?sslmode=disable

[stanislas-m]

Pop supports database suffixes in patch name: https://gobuffalo.io/en/docs/db/migrations#targeting-a-database

Supporting Postgres means renaming the existing migration files to add the suffix for MySQL, then create a Postgresql version of these patches.

[anuj509]

@mraerino your dump works for me thanks. I am able to connect postgres db via postgres db url given above. only change I did is, I replaced schema to public and owner to postgres as default. However automigrate isn't working. I was able to automigrate in netlify's another repo of goecommerce.

[mraerino]

@anuj509 See: https://github.com/netlify/gotrue/issues/176#issuecomment-424741190

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had activity in 1 year. It will be closed in 7 days if no further activity occurs. Thanks!

[github-actions[bot]]

This issue was closed because it had no activity for over 1 year.