Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

feat(scripts): add script to check schema between migrations#13037

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
johnstcn merged 4 commits intomainfromcj/test-migrations-upgrade
Apr 23, 2024

Conversation

johnstcn
Copy link
Member

  • migrations: allow passing in a custom migrate.FS when callingmigrate.Up
  • gen/dump: extract pg_dump used by gen/dump to dbtestutil, replace sed calls
  • scripts: write script to test migrations between schema versions

When run on the problematic commit with the gappy migrations:

go run ./scripts/migrate-test/main.go --from=bf296b2f1931aa259c573f43d58712f837139c1e --to=mainRead schema at version "main"Read migrations for "bf296b2f1931aa259c573f43d58712f837139c1e"Read migrations for "main"Connect to postgresNo previous migration detected.Init database at version "bf296b2f1931aa259c573f43d58712f837139c1e"Migrate to version "main"Dump schema at version "main"Schema differs from expected after migration:   (        """        ... // 741 identical lines        );  -       CREATE TABLE template_version_parameters (-           template_version_id uuid NOT NULL,-           name text NOT NULL,-           description text NOT NULL,-           type text NOT NULL,-           mutable boolean NOT NULL,-           default_value text NOT NULL,-           icon text NOT NULL,-           options jsonb DEFAULT '[]'::jsonb NOT NULL,-           validation_regex text NOT NULL,-           validation_min integer,-           validation_max integer,-           validation_error text DEFAULT ''::text NOT NULL,-           validation_monotonic text DEFAULT ''::text NOT NULL,-           required boolean DEFAULT true NOT NULL,-           display_name text DEFAULT ''::text NOT NULL,-           display_order integer DEFAULT 0 NOT NULL,-           ephemeral boolean DEFAULT false NOT NULL,-           CONSTRAINT validation_monotonic_order CHECK ((validation_monotonic = ANY (ARRAY['increasing'::text, 'decreasing'::text, ''::text])))+       CREATE TABLE template_usage_stats (+           start_time timestamp with time zone NOT NULL,+           end_time timestamp with time zone NOT NULL,+           template_id uuid NOT NULL,+           user_id uuid NOT NULL,+           median_latency_ms real,+           usage_mins smallint NOT NULL,+           ssh_mins smallint NOT NULL,+           sftp_mins smallint NOT NULL,+           reconnecting_pty_mins smallint NOT NULL,+           vscode_mins smallint NOT NULL,+           jetbrains_mins smallint NOT NULL,+           app_usage_mins jsonb        ... // 697 identical, 348 removed, and 408 inserted lines        """  )exit status 1

When run on the subsequent commit that fixed it:

go run ./scripts/migrate-test/main.go --from=cfa5be52f7c57042690aa60fdbea8098ada93acc --to=mainRead schema at version "main"Read migrations for "cfa5be52f7c57042690aa60fdbea8098ada93acc"Read migrations for "main"Connect to postgresNo previous migration detected.Init database at version "cfa5be52f7c57042690aa60fdbea8098ada93acc"Migrate to version "main"Dump schema at version "main"OK

- migrations: allow passing in a custom migrate.FS- gen/dump: extract some functions to dbtestutil- scripts: write script to test migrations
if err != nil {
panic(err)
}

for _, sed := range []string{
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

review: moved todbtestutil.normalizeDump


err = migrations.Up(db)
if err != nil {
panic(err)
}

hasPGDump := false
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

review: moved todbtestutil.PGDumpSchemaOnly

Comment on lines +299 to +307
schema = regexp.MustCompile(`(?im)^(--.*)$`).ReplaceAll(schema, []byte{})
// Public is implicit in the schema.
schema = regexp.MustCompile(`(?im)( |::|'|\()public\.`).ReplaceAll(schema, []byte(`$1`))
// Remove database settings.
schema = regexp.MustCompile(`(?im)^(SET.*;)`).ReplaceAll(schema, []byte(``))
// Remove select statements
schema = regexp.MustCompile(`(?im)^(SELECT.*;)`).ReplaceAll(schema, []byte(``))
// Removes multiple newlines.
schema = regexp.MustCompile(`(?im)\n{3,}`).ReplaceAll(schema, []byte("\n\n"))
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

review: we could pre-compile these but we've already lost enough readability by using regexen IMO

_, _ = buf.WriteRune('\n')
// PGDumpSchemaOnly is for use by gen/dump only.
// It runs pg_dump against dbURL and sets a consistent timezone and encoding.
func PGDumpSchemaOnly(dbURL string) ([]byte, error) {
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

review: I wanted to DRY this up withPGDump but it would have been more fiddly than I'd have liked.

func pgDump(dbURL string) ([]byte, error) {
// PGDump runs pg_dump against dbURL and returns the output.
// It is used by DumpOnFailure().
func PGDump(dbURL string) ([]byte, error) {
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

review: Pre-emptively exported in case other packages find it useful.

Comment on lines +53 to +59
func Up(db *sql.DB) error {
return UpWithFS(db, migrations)
}

// UpWithFS runs SQL migrations in the given fs.
func UpWithFS(db *sql.DB, migs fs.FS) (retErr error) {
_, m, err := setup(db, migs)
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

review: this make it possible to test arbitrary migration versions


func makeMigrateFS(version string) (fs.FS, error) {
// Export the migrations from the requested version to a zip archive
out, err := exec.Command("git", "archive", "--format=zip", version, "coderd/database/migrations").CombinedOutput()
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

if you want to go native, I bet you can find a library forgit too.

Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Yep, there'shttps://github.com/go-git/go-git but I'm happy to just shell out here for now.

mtojek reacted with thumbs up emoji
// 2. Checks out $NEW_VERSION and runs migrations.
// 3. Compares database schema post-migrate to that in VCS.
// If any diffs are found, exits with an error.
func main() {
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Who/what/when is going to run this script? The hidden question is: can somebody forget to run it and there will be a disaster?

Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Point 👍 I need to add this to the CI workflow so it gets run on each commit.

mtojek reacted with rocket emoji
Copy link
MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Actually, it makes more sense to integrate with the script Mathias is working on. I'll add a Makefile target at least for now.

Copy link
Member

@mtojekmtojek left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

This is in a good shape so feel free to merge it 👍

@johnstcn
Copy link
MemberAuthor

This is in a good shape so feel free to merge it 👍

Will add to a GH workflow in a separate PR

@johnstcnjohnstcn merged commite57ca3c intomainApr 23, 2024
@johnstcnjohnstcn deleted the cj/test-migrations-upgrade branchApril 23, 2024 11:43
@github-actionsgithub-actionsbot locked and limited conversation to collaboratorsApr 23, 2024
Sign up for freeto subscribe to this conversation on GitHub. Already have an account?Sign in.
Reviewers

@mtojekmtojekmtojek approved these changes

@mafredrimafredriAwaiting requested review from mafredri

Assignees

@johnstcnjohnstcn

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

2 participants
@johnstcn@mtojek

[8]ページ先頭

©2009-2025 Movatter.jp