Interest in a 'psql -f' migrater?

[advdv]

Thank you for this library, it was instantly useful to our team! The only think that required some minimal effort for us is that for migrations we instead use a "snapshot" file. A .sql file that describes the schema whole (not as migrations). We simply populate the database using psql -f. This was simple enough for us to setup by just implementing a custom migrater as seen below.

I wondered if there was interest in having this migrater in this repo? It is specific to postgresql but so is "tern" so maybe it would be ok. The other thing is that it depends on an external binary which makes it more fragile, but parsing the sql file in go is not worth it for us.

// Package snapshotmigrater is a migrater for pgtestdb that loads a snapshot using psql.
package snapshotmigrater

import (
	"bytes"
	"context"
	"crypto/sha256"
	"database/sql"
	"fmt"
	"os"
	"os/exec"

	"github.com/peterldowns/pgtestdb"
)

// SnapshotMigrater loads a migration from a postgres dump file.
type SnapshotMigrater struct {
	filename string
}

// New inits the migrater.
func New(filename string) *SnapshotMigrater {
	return &SnapshotMigrater{filename: filename}
}

// Hash implements the migrater interface.
func (m SnapshotMigrater) Hash() (string, error) {
	data, err := os.ReadFile(m.filename)
	if err != nil {
		return "", fmt.Errorf("failed to read snapshot file: %w", err)
	}

	return fmt.Sprintf("%x", sha256.Sum256(data)), nil
}

// Migrate performs the actual migration.
func (m SnapshotMigrater) Migrate(ctx context.Context, _ *sql.DB, cfg pgtestdb.Config) error {
	bin, err := exec.LookPath("psql")
	if err != nil {
		return fmt.Errorf("failed to lookup psql binary: %w", err)
	}

	errb := bytes.NewBuffer(nil)
	cmd := exec.CommandContext(ctx,
		bin,
		"-d", cfg.URL(),
		"-a", "--set", "ON_ERROR_STOP=on",
		"-f", m.filename)
	cmd.Stderr = errb

	if err := cmd.Run(); err != nil {
		return fmt.Errorf("failed to run psql to apply snapshot: %w: %s", err, errb.String())
	}

	return nil
}

var _ pgtestdb.Migrator = &SnapshotMigrater{}

[peterldowns]

I think you can achieve something equivalent by just using the pgmigrate migrator and treating the snapshot file as the single migration that you'd like to apply.

I'm not sure that this case of "I have a single migration I'd like to apply" is special enough to deserve its own migrator, but at the very least I should document it. One question for you — is there any reason you need to use psql to apply the migration, instead of calling db.ExecContext(ctx, migrationFileContents)?

[peterldowns]

Another question for you is how are you generating that single migration / snapshot — pg_dump?

[advdv]

We indeed creating the file with pg_dump. I don't know thát much about it but I believe the pg_dump output is kinda special and can't really be run in a single db.ExecContext. But, to be honest, that is just something i have in my head and i can't find a reliable source right now to back that up.

[peterldowns]

Got it. As an experiment that would help me learn some stuff, could you try applying your pg_dump-generated snapshot using db.ExecContext? I'd be interested to know if it works.

A "raw sql applier" migrator could be useful to add, regardless if it calls psql or if it uses db.ExecContext. Let me think about this a little more but I am leaning towards yes, it would be worth adding.

[advdv]

Good idea. With our prod schema and just running pg_dump without any flags, and then executing it with db.ExecContext it complains with:

failed to migrator.Migrate template testdb_tpl_cac0339d301809f9836e8ad6546254a7: failed to exec: ERROR: syntax error at or near "\" (SQLSTATE 42601)

This is probably because of these lines in the dump file (for loading data):

--
-- TOC entry 3864 (class 0 OID 16882)
-- Dependencies: 228
-- Data for Name: documents; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.documents (id, fielda, fieldb) FROM stdin;
\.

If i run pg_dump with some flags: "--schema-only", "--no-comments", "--no-owner". It does run fine with db.ExecContext.

It happens to be for us that we don't care about the data in the snapshot so we can run it with these flags. But for others it might be important.

[twpayne]

For what it's worth, I'm using a simple migrator in my tests to run zero or more SQL files:

type execSQLFromFileMigrator []string

func (m execSQLFromFileMigrator) Hash() (string, error) {
	hash := sha256.New()
	for _, filename := range m {
		fmt.Fprintln(hash, filename)
		data, err := os.ReadFile(filename)
		if err != nil {
			return "", err
		}
		if _, err := hash.Write(data); err != nil {
			return "", err
		}
	}
	return hex.EncodeToString(hash.Sum(nil)), nil
}

func (m execSQLFromFileMigrator) Migrate(ctx context.Context, db *sql.DB, config pgtestdb.Config) error {
	for _, filename := range m {
		sqlBytes, err := os.ReadFile(filename)
		if err != nil {
			return err
		}
		if _, err := db.ExecContext(ctx, string(sqlBytes)); err != nil {
			return err
		}
	}
	return nil
}

	connURL := pgtestdb.Custom(t, pgtestdb.Config{
		// ...
	}, execSQLFromFileMigrator{
		"schema.sql",
	}).URL()