postgres support #360
postgres support #360
Conversation
| func SQLReplacer(src string) string { | ||
| for nParam := 1; strings.Contains(src, "?"); nParam++ { | ||
| src = strings.Replace(src, "?", fmt.Sprintf("$%d", nParam), 1) | ||
| } | ||
| return src | ||
| } |
There was a problem hiding this comment.
Is that the only change in the code compared to the mysql backend or was there more you had to change?
There was a problem hiding this comment.
If I can remember it correct, the migration sql files and the SQLReplacer was the only thing i changed in order to get postgres to work. Wanted the query's to remain as same as possible so there would be only copy-paste without much thinking to do.
There was a problem hiding this comment.
there are minor changes in backend/postgres/postgres.go file:
"github.com/golang-migrate/migrate/v4/database/postgres"
dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require", host, port, user, password, database)
db, err := sql.Open("postgres", dsn)
db, err := sql.Open("postgres", schemaDsn)
dbi, err := postgres.WithInstance(db, &postgres.Config{})
m, err := migrate.NewWithInstance("iofs", migrations, "postgres", dbi)
| opt(options) | ||
| } | ||
|
|
||
| dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require", host, port, user, password, database) |
|
Any idea when this may be reviewed? I just stumbled across this and would really like postgres as a backend. |
| @@ -0,0 +1,70 @@ | |||
| CREATE TABLE IF NOT EXISTS instances ( | |||
There was a problem hiding this comment.
Any reason NOT EXISTS is used here? In cases like migration (especially creation) being critical path, this may skip creating a table when one already exists with different schema. Reentrancy here has too much of an edge case.
backend/postgres/postgres.go
Outdated
| "github.com/golang-migrate/migrate/v4/database/postgres" | ||
| "github.com/golang-migrate/migrate/v4/source/iofs" | ||
| "github.com/google/uuid" | ||
| _ "github.com/lib/pq" |
There was a problem hiding this comment.
lib/pq is largely stale at this point, jackc/pgx is actively worked on. it is compatible with database/sql` as well, see https://pkg.go.dev/github.com/jackc/pgx/v5/stdlib
| } | ||
| defer tx.Rollback() | ||
|
|
||
| row := tx.QueryRowContext(ctx, SQLReplacer("SELECT state FROM instances WHERE instance_id = ? AND execution_id = ? LIMIT 1"), instance.InstanceID, instance.ExecutionID) |
There was a problem hiding this comment.
@erma07 I think you are fine to use positional parameters when using pgx, e.g.
| row := tx.QueryRowContext(ctx, SQLReplacer("SELECT state FROM instances WHERE instance_id = ? AND execution_id = ? LIMIT 1"), instance.InstanceID, instance.ExecutionID) | |
| row := tx.QueryRowContext( | |
| ctx, | |
| "SELECT state FROM instances WHERE instance_id = $1 AND execution_id = $2 LIMIT 1", | |
| instance.InstanceID, | |
| instance.ExecutionID, | |
| ) |
|
Is this being worked on? |
No description provided.