github.com/dynastymasra/migrate/v4@v4.11.0/database/postgres/postgres_test.go (about)

     1  package postgres
     2  
     3  // error codes https://github.com/lib/pq/blob/master/error.go
     4  
     5  import (
     6  	"context"
     7  	"database/sql"
     8  	sqldriver "database/sql/driver"
     9  	"fmt"
    10  	"log"
    11  
    12  	"github.com/golang-migrate/migrate/v4"
    13  	"io"
    14  	"strconv"
    15  	"strings"
    16  	"sync"
    17  	"testing"
    18  
    19  	"github.com/dhui/dktest"
    20  
    21  	"github.com/golang-migrate/migrate/v4/database"
    22  	dt "github.com/golang-migrate/migrate/v4/database/testing"
    23  	"github.com/golang-migrate/migrate/v4/dktesting"
    24  	_ "github.com/golang-migrate/migrate/v4/source/file"
    25  )
    26  
    27  const (
    28  	pgPassword = "postgres"
    29  )
    30  
    31  var (
    32  	opts = dktest.Options{
    33  		Env:          map[string]string{"POSTGRES_PASSWORD": pgPassword},
    34  		PortRequired: true, ReadyFunc: isReady}
    35  	// Supported versions: https://www.postgresql.org/support/versioning/
    36  	specs = []dktesting.ContainerSpec{
    37  		{ImageName: "postgres:9.5", Options: opts},
    38  		{ImageName: "postgres:9.6", Options: opts},
    39  		{ImageName: "postgres:10", Options: opts},
    40  		{ImageName: "postgres:11", Options: opts},
    41  		{ImageName: "postgres:12", Options: opts},
    42  	}
    43  )
    44  
    45  func pgConnectionString(host, port string) string {
    46  	return fmt.Sprintf("postgres://postgres:%s@%s:%s/postgres?sslmode=disable", pgPassword, host, port)
    47  }
    48  
    49  func isReady(ctx context.Context, c dktest.ContainerInfo) bool {
    50  	ip, port, err := c.FirstPort()
    51  	if err != nil {
    52  		return false
    53  	}
    54  
    55  	db, err := sql.Open("postgres", pgConnectionString(ip, port))
    56  	if err != nil {
    57  		return false
    58  	}
    59  	defer func() {
    60  		if err := db.Close(); err != nil {
    61  			log.Println("close error:", err)
    62  		}
    63  	}()
    64  	if err = db.PingContext(ctx); err != nil {
    65  		switch err {
    66  		case sqldriver.ErrBadConn, io.EOF:
    67  			return false
    68  		default:
    69  			log.Println(err)
    70  		}
    71  		return false
    72  	}
    73  
    74  	return true
    75  }
    76  
    77  func Test(t *testing.T) {
    78  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
    79  		ip, port, err := c.FirstPort()
    80  		if err != nil {
    81  			t.Fatal(err)
    82  		}
    83  
    84  		addr := pgConnectionString(ip, port)
    85  		p := &Postgres{}
    86  		d, err := p.Open(addr)
    87  		if err != nil {
    88  			t.Fatal(err)
    89  		}
    90  		defer func() {
    91  			if err := d.Close(); err != nil {
    92  				t.Error(err)
    93  			}
    94  		}()
    95  		dt.Test(t, d, []byte("SELECT 1"))
    96  	})
    97  }
    98  
    99  func TestMigrate(t *testing.T) {
   100  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   101  		ip, port, err := c.FirstPort()
   102  		if err != nil {
   103  			t.Fatal(err)
   104  		}
   105  
   106  		addr := pgConnectionString(ip, port)
   107  		p := &Postgres{}
   108  		d, err := p.Open(addr)
   109  		if err != nil {
   110  			t.Fatal(err)
   111  		}
   112  		defer func() {
   113  			if err := d.Close(); err != nil {
   114  				t.Error(err)
   115  			}
   116  		}()
   117  		m, err := migrate.NewWithDatabaseInstance("file://./examples/migrations", "postgres", d)
   118  		if err != nil {
   119  			t.Fatal(err)
   120  		}
   121  		dt.TestMigrate(t, m)
   122  	})
   123  }
   124  
   125  func TestMultiStatement(t *testing.T) {
   126  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   127  		ip, port, err := c.FirstPort()
   128  		if err != nil {
   129  			t.Fatal(err)
   130  		}
   131  
   132  		addr := pgConnectionString(ip, port)
   133  		p := &Postgres{}
   134  		d, err := p.Open(addr)
   135  		if err != nil {
   136  			t.Fatal(err)
   137  		}
   138  		defer func() {
   139  			if err := d.Close(); err != nil {
   140  				t.Error(err)
   141  			}
   142  		}()
   143  		if err := d.Run(strings.NewReader("CREATE TABLE foo (foo text); CREATE TABLE bar (bar text);")); err != nil {
   144  			t.Fatalf("expected err to be nil, got %v", err)
   145  		}
   146  
   147  		// make sure second table exists
   148  		var exists bool
   149  		if err := d.(*Postgres).conn.QueryRowContext(context.Background(), "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'bar' AND table_schema = (SELECT current_schema()))").Scan(&exists); err != nil {
   150  			t.Fatal(err)
   151  		}
   152  		if !exists {
   153  			t.Fatalf("expected table bar to exist")
   154  		}
   155  	})
   156  }
   157  
   158  func TestErrorParsing(t *testing.T) {
   159  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   160  		ip, port, err := c.FirstPort()
   161  		if err != nil {
   162  			t.Fatal(err)
   163  		}
   164  
   165  		addr := pgConnectionString(ip, port)
   166  		p := &Postgres{}
   167  		d, err := p.Open(addr)
   168  		if err != nil {
   169  			t.Fatal(err)
   170  		}
   171  		defer func() {
   172  			if err := d.Close(); err != nil {
   173  				t.Error(err)
   174  			}
   175  		}()
   176  
   177  		wantErr := `migration failed: syntax error at or near "TABLEE" (column 37) in line 1: CREATE TABLE foo ` +
   178  			`(foo text); CREATE TABLEE bar (bar text); (details: pq: syntax error at or near "TABLEE")`
   179  		if err := d.Run(strings.NewReader("CREATE TABLE foo (foo text); CREATE TABLEE bar (bar text);")); err == nil {
   180  			t.Fatal("expected err but got nil")
   181  		} else if err.Error() != wantErr {
   182  			t.Fatalf("expected '%s' but got '%s'", wantErr, err.Error())
   183  		}
   184  	})
   185  }
   186  
   187  func TestFilterCustomQuery(t *testing.T) {
   188  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   189  		ip, port, err := c.FirstPort()
   190  		if err != nil {
   191  			t.Fatal(err)
   192  		}
   193  
   194  		addr := fmt.Sprintf("postgres://postgres:%s@%v:%v/postgres?sslmode=disable&x-custom=foobar",
   195  			pgPassword, ip, port)
   196  		p := &Postgres{}
   197  		d, err := p.Open(addr)
   198  		if err != nil {
   199  			t.Fatal(err)
   200  		}
   201  		defer func() {
   202  			if err := d.Close(); err != nil {
   203  				t.Error(err)
   204  			}
   205  		}()
   206  	})
   207  }
   208  
   209  func TestWithSchema(t *testing.T) {
   210  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   211  		ip, port, err := c.FirstPort()
   212  		if err != nil {
   213  			t.Fatal(err)
   214  		}
   215  
   216  		addr := pgConnectionString(ip, port)
   217  		p := &Postgres{}
   218  		d, err := p.Open(addr)
   219  		if err != nil {
   220  			t.Fatal(err)
   221  		}
   222  		defer func() {
   223  			if err := d.Close(); err != nil {
   224  				t.Fatal(err)
   225  			}
   226  		}()
   227  
   228  		// create foobar schema
   229  		if err := d.Run(strings.NewReader("CREATE SCHEMA foobar AUTHORIZATION postgres")); err != nil {
   230  			t.Fatal(err)
   231  		}
   232  		if err := d.SetVersion(1, false); err != nil {
   233  			t.Fatal(err)
   234  		}
   235  
   236  		// re-connect using that schema
   237  		d2, err := p.Open(fmt.Sprintf("postgres://postgres:%s@%v:%v/postgres?sslmode=disable&search_path=foobar",
   238  			pgPassword, ip, port))
   239  		if err != nil {
   240  			t.Fatal(err)
   241  		}
   242  		defer func() {
   243  			if err := d2.Close(); err != nil {
   244  				t.Fatal(err)
   245  			}
   246  		}()
   247  
   248  		version, _, err := d2.Version()
   249  		if err != nil {
   250  			t.Fatal(err)
   251  		}
   252  		if version != database.NilVersion {
   253  			t.Fatal("expected NilVersion")
   254  		}
   255  
   256  		// now update version and compare
   257  		if err := d2.SetVersion(2, false); err != nil {
   258  			t.Fatal(err)
   259  		}
   260  		version, _, err = d2.Version()
   261  		if err != nil {
   262  			t.Fatal(err)
   263  		}
   264  		if version != 2 {
   265  			t.Fatal("expected version 2")
   266  		}
   267  
   268  		// meanwhile, the public schema still has the other version
   269  		version, _, err = d.Version()
   270  		if err != nil {
   271  			t.Fatal(err)
   272  		}
   273  		if version != 1 {
   274  			t.Fatal("expected version 2")
   275  		}
   276  	})
   277  }
   278  
   279  func TestParallelSchema(t *testing.T) {
   280  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   281  		ip, port, err := c.FirstPort()
   282  		if err != nil {
   283  			t.Fatal(err)
   284  		}
   285  
   286  		addr := pgConnectionString(ip, port)
   287  		p := &Postgres{}
   288  		d, err := p.Open(addr)
   289  		if err != nil {
   290  			t.Fatal(err)
   291  		}
   292  		defer func() {
   293  			if err := d.Close(); err != nil {
   294  				t.Error(err)
   295  			}
   296  		}()
   297  
   298  		// create foo and bar schemas
   299  		if err := d.Run(strings.NewReader("CREATE SCHEMA foo AUTHORIZATION postgres")); err != nil {
   300  			t.Fatal(err)
   301  		}
   302  		if err := d.Run(strings.NewReader("CREATE SCHEMA bar AUTHORIZATION postgres")); err != nil {
   303  			t.Fatal(err)
   304  		}
   305  
   306  		// re-connect using that schemas
   307  		dfoo, err := p.Open(fmt.Sprintf("postgres://postgres:%s@%v:%v/postgres?sslmode=disable&search_path=foo",
   308  			pgPassword, ip, port))
   309  		if err != nil {
   310  			t.Fatal(err)
   311  		}
   312  		defer func() {
   313  			if err := dfoo.Close(); err != nil {
   314  				t.Error(err)
   315  			}
   316  		}()
   317  
   318  		dbar, err := p.Open(fmt.Sprintf("postgres://postgres:%s@%v:%v/postgres?sslmode=disable&search_path=bar",
   319  			pgPassword, ip, port))
   320  		if err != nil {
   321  			t.Fatal(err)
   322  		}
   323  		defer func() {
   324  			if err := dbar.Close(); err != nil {
   325  				t.Error(err)
   326  			}
   327  		}()
   328  
   329  		if err := dfoo.Lock(); err != nil {
   330  			t.Fatal(err)
   331  		}
   332  
   333  		if err := dbar.Lock(); err != nil {
   334  			t.Fatal(err)
   335  		}
   336  
   337  		if err := dbar.Unlock(); err != nil {
   338  			t.Fatal(err)
   339  		}
   340  
   341  		if err := dfoo.Unlock(); err != nil {
   342  			t.Fatal(err)
   343  		}
   344  	})
   345  }
   346  
   347  func TestWithInstance(t *testing.T) {
   348  
   349  }
   350  
   351  func TestPostgres_Lock(t *testing.T) {
   352  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   353  		ip, port, err := c.FirstPort()
   354  		if err != nil {
   355  			t.Fatal(err)
   356  		}
   357  
   358  		addr := pgConnectionString(ip, port)
   359  		p := &Postgres{}
   360  		d, err := p.Open(addr)
   361  		if err != nil {
   362  			t.Fatal(err)
   363  		}
   364  
   365  		dt.Test(t, d, []byte("SELECT 1"))
   366  
   367  		ps := d.(*Postgres)
   368  
   369  		err = ps.Lock()
   370  		if err != nil {
   371  			t.Fatal(err)
   372  		}
   373  
   374  		err = ps.Unlock()
   375  		if err != nil {
   376  			t.Fatal(err)
   377  		}
   378  
   379  		err = ps.Lock()
   380  		if err != nil {
   381  			t.Fatal(err)
   382  		}
   383  
   384  		err = ps.Unlock()
   385  		if err != nil {
   386  			t.Fatal(err)
   387  		}
   388  	})
   389  }
   390  
   391  func TestWithInstance_Concurrent(t *testing.T) {
   392  	dktesting.ParallelTest(t, specs, func(t *testing.T, c dktest.ContainerInfo) {
   393  		ip, port, err := c.FirstPort()
   394  		if err != nil {
   395  			t.Fatal(err)
   396  		}
   397  
   398  		// The number of concurrent processes running WithInstance
   399  		const concurrency = 30
   400  
   401  		// We can instantiate a single database handle because it is
   402  		// actually a connection pool, and so, each of the below go
   403  		// routines will have a high probability of using a separate
   404  		// connection, which is something we want to exercise.
   405  		db, err := sql.Open("postgres", pgConnectionString(ip, port))
   406  		if err != nil {
   407  			t.Fatal(err)
   408  		}
   409  		defer func() {
   410  			if err := db.Close(); err != nil {
   411  				t.Error(err)
   412  			}
   413  		}()
   414  
   415  		db.SetMaxIdleConns(concurrency)
   416  		db.SetMaxOpenConns(concurrency)
   417  
   418  		var wg sync.WaitGroup
   419  		defer wg.Wait()
   420  
   421  		wg.Add(concurrency)
   422  		for i := 0; i < concurrency; i++ {
   423  			go func(i int) {
   424  				defer wg.Done()
   425  				_, err := WithInstance(db, &Config{})
   426  				if err != nil {
   427  					t.Errorf("process %d error: %s", i, err)
   428  				}
   429  			}(i)
   430  		}
   431  	})
   432  }
   433  func Test_computeLineFromPos(t *testing.T) {
   434  	testcases := []struct {
   435  		pos      int
   436  		wantLine uint
   437  		wantCol  uint
   438  		input    string
   439  		wantOk   bool
   440  	}{
   441  		{
   442  			15, 2, 6, "SELECT *\nFROM foo", true, // foo table does not exists
   443  		},
   444  		{
   445  			16, 3, 6, "SELECT *\n\nFROM foo", true, // foo table does not exists, empty line
   446  		},
   447  		{
   448  			25, 3, 7, "SELECT *\nFROM foo\nWHERE x", true, // x column error
   449  		},
   450  		{
   451  			27, 5, 7, "SELECT *\n\nFROM foo\n\nWHERE x", true, // x column error, empty lines
   452  		},
   453  		{
   454  			10, 2, 1, "SELECT *\nFROMM foo", true, // FROMM typo
   455  		},
   456  		{
   457  			11, 3, 1, "SELECT *\n\nFROMM foo", true, // FROMM typo, empty line
   458  		},
   459  		{
   460  			17, 2, 8, "SELECT *\nFROM foo", true, // last character
   461  		},
   462  		{
   463  			18, 0, 0, "SELECT *\nFROM foo", false, // invalid position
   464  		},
   465  	}
   466  	for i, tc := range testcases {
   467  		t.Run("tc"+strconv.Itoa(i), func(t *testing.T) {
   468  			run := func(crlf bool, nonASCII bool) {
   469  				var name string
   470  				if crlf {
   471  					name = "crlf"
   472  				} else {
   473  					name = "lf"
   474  				}
   475  				if nonASCII {
   476  					name += "-nonascii"
   477  				} else {
   478  					name += "-ascii"
   479  				}
   480  				t.Run(name, func(t *testing.T) {
   481  					input := tc.input
   482  					if crlf {
   483  						input = strings.Replace(input, "\n", "\r\n", -1)
   484  					}
   485  					if nonASCII {
   486  						input = strings.Replace(input, "FROM", "FRÖM", -1)
   487  					}
   488  					gotLine, gotCol, gotOK := computeLineFromPos(input, tc.pos)
   489  
   490  					if tc.wantOk {
   491  						t.Logf("pos %d, want %d:%d, %#v", tc.pos, tc.wantLine, tc.wantCol, input)
   492  					}
   493  
   494  					if gotOK != tc.wantOk {
   495  						t.Fatalf("expected ok %v but got %v", tc.wantOk, gotOK)
   496  					}
   497  					if gotLine != tc.wantLine {
   498  						t.Fatalf("expected line %d but got %d", tc.wantLine, gotLine)
   499  					}
   500  					if gotCol != tc.wantCol {
   501  						t.Fatalf("expected col %d but got %d", tc.wantCol, gotCol)
   502  					}
   503  				})
   504  			}
   505  			run(false, false)
   506  			run(true, false)
   507  			run(false, true)
   508  			run(true, true)
   509  		})
   510  	}
   511  
   512  }