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 }