3572813994

Committed 04 Dec 2022 06:36PM UTC coverage: 54.923% (-2.8%) from 57.76%

Build Type

github

Committed by Kevin Saliou

Commit Message

chore: remove all tabs & trailing spaces from SQL files

Pull Request Pull Request #648: chore: remove all tabs & trailing spaces from SQL files

Run Details

3057 of 5566 relevant lines covered (54.92%)

498.14 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

98.81

/internal/db/diff/migra.go

package diff

import (
        "context"
        _ "embed"
        "errors"
        "fmt"
        "io"
        "net/url"
        "os"
        "path/filepath"
        "regexp"
        "strconv"
        "strings"
        "time"

        "github.com/docker/docker/api/types/container"
        "github.com/docker/go-connections/nat"
        "github.com/jackc/pgconn"
        "github.com/jackc/pgx/v4"
        "github.com/spf13/afero"
        "github.com/supabase/cli/internal/db/lint"
        "github.com/supabase/cli/internal/utils"
        "github.com/supabase/cli/internal/utils/parser"
)

var (
        initSchemaPattern = regexp.MustCompile(`([0-9]{14})_init\.sql`)
        //go:embed templates/migra.sh
        diffSchemaScript string
)

func RunMigra(ctx context.Context, schema []string, file string, password string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
        // Sanity checks.
        if err := utils.LoadConfigFS(fsys); err != nil {
                return err
        }
        // 1. Determine local or remote target
        target, err := buildTargetUrl(password, fsys)
        if err != nil {
                return err
        }
        // 2. Create shadow database
        fmt.Fprintln(os.Stderr, "Creating shadow database...")
        shadow, err := createShadowDatabase(ctx)
        if err != nil {
                return err
        }
        defer utils.DockerStop(shadow)
        if err := migrateShadowDatabase(ctx, fsys, options...); err != nil {
                return err
        }
        // 3. Run migra to diff schema
        progress := "Diffing local database..."
        if len(password) > 0 {
                progress = "Diffing linked project..."
        }
        fmt.Fprintln(os.Stderr, progress)
        source := "postgresql://postgres:postgres@" + shadow[:12] + ":5432/postgres"
        out, err := diffSchema(ctx, source, target, schema)
        if err != nil {
                return err
        }
        branch, err := utils.GetCurrentBranchFS(fsys)
        if err != nil {
                branch = "main"
        }
        fmt.Fprintln(os.Stderr, "Finished "+utils.Aqua("supabase db diff")+" on branch "+utils.Aqua(branch)+".\n")
        return SaveDiff(out, file, fsys)
}

// Builds a postgres connection string for local or remote database
func buildTargetUrl(password string, fsys afero.Fs) (target string, err error) {
        if len(password) > 0 {
                ref, err := utils.LoadProjectRef(fsys)
                if err != nil {
                        return target, err
                }
                target = fmt.Sprintf(
                        "postgresql://%s@%s:6543/postgres",
                        url.UserPassword("postgres", password),
                        utils.GetSupabaseDbHost(ref),
                )
        } else {
                if err := utils.AssertSupabaseDbIsRunning(); err != nil {
                        return target, err
                }
                target = "postgresql://postgres:postgres@" + utils.DbId + ":5432/postgres"
        }
        return target, err
}

func createShadowDatabase(ctx context.Context) (string, error) {
        config := container.Config{
                Image: utils.DbImage,
                Env:   []string{"POSTGRES_PASSWORD=postgres"},
        }
        if utils.Config.Db.MajorVersion >= 14 {
                config.Cmd = []string{"postgres",
                        "-c", "config_file=/etc/postgresql/postgresql.conf",
                        // Ref: https://postgrespro.com/list/thread-id/2448092
                        "-c", `search_path="$user",public,extensions`,
                }
        }
        hostPort := strconv.FormatUint(uint64(utils.Config.Db.ShadowPort), 10)
        hostConfig := container.HostConfig{
                PortBindings: nat.PortMap{"5432/tcp": []nat.PortBinding{{HostPort: hostPort}}},
                Binds:        []string{"/dev/null:/docker-entrypoint-initdb.d/migrate.sh:ro"},
                AutoRemove:   true,
        }
        return utils.DockerStart(ctx, config, hostConfig, utils.DbId)
}

func connectShadowDatabase(ctx context.Context, timeout time.Duration, options ...func(*pgx.ConnConfig)) (conn *pgx.Conn, err error) {
        now := time.Now()
        expiry := now.Add(timeout)
        ticker := time.NewTicker(time.Second)
        defer ticker.Stop()
        // Retry until connected, cancelled, or timeout
        for t := now; t.Before(expiry); t = <-ticker.C {
                conn, err = lint.ConnectLocalPostgres(ctx, "localhost", utils.Config.Db.ShadowPort, "postgres", options...)
                if err == nil || errors.Is(ctx.Err(), context.Canceled) {
                        break
                }
        }
        return conn, err
}

func migrateShadowDatabase(ctx context.Context, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
        conn, err := connectShadowDatabase(ctx, 10*time.Second, options...)
        if err != nil {
                return err
        }
        defer conn.Close(context.Background())
        fmt.Fprintln(os.Stderr, "Initialising schema...")
        if err := BatchExecDDL(ctx, conn, strings.NewReader(utils.GlobalsSql)); err != nil {
                return err
        }
        if err := BatchExecDDL(ctx, conn, strings.NewReader(utils.InitialSchemaSql)); err != nil {
                return err
        }
        return MigrateDatabase(ctx, conn, fsys)
}

// Applies local migration scripts to a database.
func ApplyMigrations(ctx context.Context, url string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
        // Parse connection url
        config, err := pgx.ParseConfig(url)
        if err != nil {
                return err
        }
        // Apply config overrides
        for _, op := range options {
                op(config)
        }
        // Connect to database
        conn, err := pgx.ConnectConfig(ctx, config)
        if err != nil {
                return err
        }
        defer conn.Close(context.Background())
        return MigrateDatabase(ctx, conn, fsys)
}

func shouldSkip(name string) bool {
        // NOTE: To handle backward-compatibility. `<timestamp>_init.sql` as
        // the first migration (prev versions of the CLI) is deprecated.
        matches := initSchemaPattern.FindStringSubmatch(name)
        if len(matches) == 2 {
                if timestamp, err := strconv.ParseUint(matches[1], 10, 64); err == nil && timestamp < 20211209000000 {
                        return true
                }
        }
        return false
}

func MigrateDatabase(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) error {
        // Apply migrations
        if migrations, err := afero.ReadDir(fsys, utils.MigrationsDir); err == nil {
                for i, migration := range migrations {
                        if i == 0 && shouldSkip(migration.Name()) {
                                fmt.Fprintln(os.Stderr, "Skipping migration "+utils.Bold(migration.Name())+`... (replace "init" with a different file name to apply this migration)`)
                                continue
                        }
                        fmt.Fprintln(os.Stderr, "Applying migration "+utils.Bold(migration.Name())+"...")
                        sql, err := fsys.Open(filepath.Join(utils.MigrationsDir, migration.Name()))
                        if err != nil {
                                return err
                        }
                        defer sql.Close()
                        if err := BatchExecDDL(ctx, conn, sql); err != nil {
                                return err
                        }
                }
        }
        return nil
}

func BatchExecDDL(ctx context.Context, conn *pgx.Conn, sql io.Reader) error {
        // Batch migration commands, without using statement cache
        batch := pgconn.Batch{}
        lines, err := parser.Split(sql)
        if err != nil {
                var stat string
                if len(lines) > 0 {
                        stat = lines[len(lines)-1]
                }
                return fmt.Errorf("%v\nAfter statement %d: %s", err, len(lines), utils.Aqua(stat))
        }
        for _, line := range lines {
                trim := strings.TrimSpace(strings.TrimRight(line, ";"))
                if len(trim) > 0 {
                        batch.ExecParams(trim, nil, nil, nil, nil)
                }
        }
        if result, err := conn.PgConn().ExecBatch(ctx, &batch).ReadAll(); err != nil {
                var stat string
                if len(result) < len(lines) {
                        stat = lines[len(result)]
                }
                return fmt.Errorf("%v\nAt statement %d: %s", err, len(result), utils.Aqua(stat))
        }
        return nil
}

// Diffs local database schema against shadow, dumps output to stdout.
func diffSchema(ctx context.Context, source, target string, schema []string) (string, error) {
        env := []string{"SOURCE=" + source, "TARGET=" + target}
        // Passing in script string means command line args must be set manually, ie. "$@"
        args := "set -- " + strings.Join(schema, " ") + ";"
        cmd := []string{"/bin/sh", "-c", args + diffSchemaScript}
        out, err := utils.DockerRunOnce(ctx, utils.MigraImage, env, cmd)
        if err != nil {
                return "", errors.New("error diffing schema")
        }
        return out, nil
}

1	package diff
2
3	import (
4	"context"
5	_ "embed"
6	"errors"
7	"fmt"
8	"io"
9	"net/url"
10	"os"
11	"path/filepath"
12	"regexp"
13	"strconv"
14	"strings"
15	"time"
16
17	"github.com/docker/docker/api/types/container"
18	"github.com/docker/go-connections/nat"
19	"github.com/jackc/pgconn"
20	"github.com/jackc/pgx/v4"
21	"github.com/spf13/afero"
22	"github.com/supabase/cli/internal/db/lint"
23	"github.com/supabase/cli/internal/utils"
24	"github.com/supabase/cli/internal/utils/parser"
25	)
26
27	var (
28	initSchemaPattern = regexp.MustCompile(`([0-9]{14})_init\.sql`)
29	//go:embed templates/migra.sh
30	diffSchemaScript string
31	)
32
33	func RunMigra(ctx context.Context, schema []string, file string, password string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {	7✔
34	// Sanity checks.	7✔
35	if err := utils.LoadConfigFS(fsys); err != nil {	8✔
36	return err	1✔
37	}	1✔
38	// 1. Determine local or remote target
39	target, err := buildTargetUrl(password, fsys)	6✔
40	if err != nil {	8✔
41	return err	2✔
42	}	2✔
43	// 2. Create shadow database
44	fmt.Fprintln(os.Stderr, "Creating shadow database...")	4✔
45	shadow, err := createShadowDatabase(ctx)	4✔
46	if err != nil {	5✔
47	return err	1✔
48	}	1✔
49	defer utils.DockerStop(shadow)	3✔
50	if err := migrateShadowDatabase(ctx, fsys, options...); err != nil {	4✔
51	return err	1✔
52	}	1✔
53	// 3. Run migra to diff schema
54	progress := "Diffing local database..."	2✔
55	if len(password) > 0 {	4✔
56	progress = "Diffing linked project..."	2✔
57	}	2✔
58	fmt.Fprintln(os.Stderr, progress)	2✔
59	source := "postgresql://postgres:postgres@" + shadow[:12] + ":5432/postgres"	2✔
60	out, err := diffSchema(ctx, source, target, schema)	2✔
61	if err != nil {	3✔
62	return err	1✔
63	}	1✔
64	branch, err := utils.GetCurrentBranchFS(fsys)	1✔
65	if err != nil {	2✔
66	branch = "main"	1✔
67	}	1✔
68	fmt.Fprintln(os.Stderr, "Finished "+utils.Aqua("supabase db diff")+" on branch "+utils.Aqua(branch)+".\n")	1✔
69	return SaveDiff(out, file, fsys)	1✔
70	}
71
72	// Builds a postgres connection string for local or remote database
73	func buildTargetUrl(password string, fsys afero.Fs) (target string, err error) {	8✔
74	if len(password) > 0 {	14✔
75	ref, err := utils.LoadProjectRef(fsys)	6✔
76	if err != nil {	7✔
77	return target, err	1✔
78	}	1✔
79	target = fmt.Sprintf(	5✔
80	"postgresql://%s@%s:6543/postgres",	5✔
81	url.UserPassword("postgres", password),	5✔
82	utils.GetSupabaseDbHost(ref),	5✔
83	)	5✔
84	} else {	2✔
85	if err := utils.AssertSupabaseDbIsRunning(); err != nil {	3✔
86	return target, err	1✔
87	}	1✔
88	target = "postgresql://postgres:postgres@" + utils.DbId + ":5432/postgres"	1✔
89	}
90	return target, err	6✔
91	}
92
93	func createShadowDatabase(ctx context.Context) (string, error) {	4✔
94	config := container.Config{	4✔
95	Image: utils.DbImage,	4✔
96	Env: []string{"POSTGRES_PASSWORD=postgres"},	4✔
97	}	4✔
98	if utils.Config.Db.MajorVersion >= 14 {	8✔
99	config.Cmd = []string{"postgres",	4✔
100	"-c", "config_file=/etc/postgresql/postgresql.conf",	4✔
101	// Ref: https://postgrespro.com/list/thread-id/2448092	4✔
102	"-c", `search_path="$user",public,extensions`,	4✔
103	}	4✔
104	}	4✔
105	hostPort := strconv.FormatUint(uint64(utils.Config.Db.ShadowPort), 10)	4✔
106	hostConfig := container.HostConfig{	4✔
107	PortBindings: nat.PortMap{"5432/tcp": []nat.PortBinding{{HostPort: hostPort}}},	4✔
108	Binds: []string{"/dev/null:/docker-entrypoint-initdb.d/migrate.sh:ro"},	4✔
109	AutoRemove: true,	4✔
110	}	4✔
111	return utils.DockerStart(ctx, config, hostConfig, utils.DbId)	4✔
112	}
113
114	func connectShadowDatabase(ctx context.Context, timeout time.Duration, options ...func(pgx.ConnConfig)) (conn pgx.Conn, err error) {	6✔
115	now := time.Now()	6✔
116	expiry := now.Add(timeout)	6✔
117	ticker := time.NewTicker(time.Second)	6✔
118	defer ticker.Stop()	6✔
119	// Retry until connected, cancelled, or timeout	6✔
120	for t := now; t.Before(expiry); t = <-ticker.C {	12✔
121	conn, err = lint.ConnectLocalPostgres(ctx, "localhost", utils.Config.Db.ShadowPort, "postgres", options...)	6✔
122	if err == nil \|\| errors.Is(ctx.Err(), context.Canceled) {	12✔
123	break	6✔
124	}
125	}
126	return conn, err	6✔
127	}
128
129	func migrateShadowDatabase(ctx context.Context, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {	6✔
130	conn, err := connectShadowDatabase(ctx, 10*time.Second, options...)	6✔
131	if err != nil {	7✔
132	return err	1✔
133	}	1✔
134	defer conn.Close(context.Background())	5✔
135	fmt.Fprintln(os.Stderr, "Initialising schema...")	5✔
136	if err := BatchExecDDL(ctx, conn, strings.NewReader(utils.GlobalsSql)); err != nil {	7✔
137	return err	2✔
138	}	2✔
139	if err := BatchExecDDL(ctx, conn, strings.NewReader(utils.InitialSchemaSql)); err != nil {	4✔
140	return err	1✔
141	}	1✔
142	return MigrateDatabase(ctx, conn, fsys)	2✔
143	}
144
145	// Applies local migration scripts to a database.
146	func ApplyMigrations(ctx context.Context, url string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {	4✔
147	// Parse connection url	4✔
148	config, err := pgx.ParseConfig(url)	4✔
149	if err != nil {	5✔
150	return err	1✔
151	}	1✔
152	// Apply config overrides
153	for _, op := range options {	5✔
154	op(config)	2✔
155	}	2✔
156	// Connect to database
157	conn, err := pgx.ConnectConfig(ctx, config)	3✔
158	if err != nil {	4✔
159	return err	1✔
160	}	1✔
161	defer conn.Close(context.Background())	2✔
162	return MigrateDatabase(ctx, conn, fsys)	2✔
163	}
164
165	func shouldSkip(name string) bool {	5✔
166	// NOTE: To handle backward-compatibility. `<timestamp>_init.sql` as	5✔
167	// the first migration (prev versions of the CLI) is deprecated.	5✔
168	matches := initSchemaPattern.FindStringSubmatch(name)	5✔
169	if len(matches) == 2 {	7✔
170	if timestamp, err := strconv.ParseUint(matches[1], 10, 64); err == nil && timestamp < 20211209000000 {	3✔
171	return true	1✔
172	}	1✔
173	}
174	return false	4✔
175	}
176
177	func MigrateDatabase(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) error {	11✔
178	// Apply migrations	11✔
179	if migrations, err := afero.ReadDir(fsys, utils.MigrationsDir); err == nil {	16✔
180	for i, migration := range migrations {	11✔
181	if i == 0 && shouldSkip(migration.Name()) {	7✔
182	fmt.Fprintln(os.Stderr, "Skipping migration "+utils.Bold(migration.Name())+`... (replace "init" with a different file name to apply this migration)`)	1✔
183	continue	1✔
184	}
185	fmt.Fprintln(os.Stderr, "Applying migration "+utils.Bold(migration.Name())+"...")	5✔
186	sql, err := fsys.Open(filepath.Join(utils.MigrationsDir, migration.Name()))	5✔
187	if err != nil {	5✔
188	return err	×
189	}	×
190	defer sql.Close()	5✔
191	if err := BatchExecDDL(ctx, conn, sql); err != nil {	8✔
192	return err	3✔
193	}	3✔
194	}
195	}
196	return nil	8✔
197	}
198
199	func BatchExecDDL(ctx context.Context, conn *pgx.Conn, sql io.Reader) error {	24✔
200	// Batch migration commands, without using statement cache	24✔
201	batch := pgconn.Batch{}	24✔
202	lines, err := parser.Split(sql)	24✔
203	if err != nil {	25✔
204	var stat string	1✔
205	if len(lines) > 0 {	2✔
206	stat = lines[len(lines)-1]	1✔
207	}	1✔
208	return fmt.Errorf("%v\nAfter statement %d: %s", err, len(lines), utils.Aqua(stat))	1✔
209	}
210	for _, line := range lines {	191✔
211	trim := strings.TrimSpace(strings.TrimRight(line, ";"))	168✔
212	if len(trim) > 0 {	332✔
213	batch.ExecParams(trim, nil, nil, nil, nil)	164✔
214	}	164✔
215	}
216	if result, err := conn.PgConn().ExecBatch(ctx, &batch).ReadAll(); err != nil {	30✔
217	var stat string	7✔
218	if len(result) < len(lines) {	14✔
219	stat = lines[len(result)]	7✔
220	}	7✔
221	return fmt.Errorf("%v\nAt statement %d: %s", err, len(result), utils.Aqua(stat))	7✔
222	}
223	return nil	16✔
224	}
225
226	// Diffs local database schema against shadow, dumps output to stdout.
227	func diffSchema(ctx context.Context, source, target string, schema []string) (string, error) {	2✔
228	env := []string{"SOURCE=" + source, "TARGET=" + target}	2✔
229	// Passing in script string means command line args must be set manually, ie. "$@"	2✔
230	args := "set -- " + strings.Join(schema, " ") + ";"	2✔
231	cmd := []string{"/bin/sh", "-c", args + diffSchemaScript}	2✔
232	out, err := utils.DockerRunOnce(ctx, utils.MigraImage, env, cmd)	2✔
233	if err != nil {	3✔
234	return "", errors.New("error diffing schema")	1✔
235	}	1✔
236	return out, nil	1✔
237	}

supabase / cli / 3572813994

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous