github.phpd.cn/amacneil/dbmate@v1.4.1/pkg/dbmate/postgres_test.go (about) 1 package dbmate 2 3 import ( 4 "database/sql" 5 "net/url" 6 "testing" 7 8 "github.com/stretchr/testify/require" 9 ) 10 11 func postgresTestURL(t *testing.T) *url.URL { 12 u, err := url.Parse("postgres://postgres:postgres@postgres/dbmate?sslmode=disable") 13 require.NoError(t, err) 14 15 return u 16 } 17 18 func prepTestPostgresDB(t *testing.T) *sql.DB { 19 drv := PostgresDriver{} 20 u := postgresTestURL(t) 21 22 // drop any existing database 23 err := drv.DropDatabase(u) 24 require.NoError(t, err) 25 26 // create database 27 err = drv.CreateDatabase(u) 28 require.NoError(t, err) 29 30 // connect database 31 db, err := sql.Open("postgres", u.String()) 32 require.NoError(t, err) 33 34 return db 35 } 36 37 func TestPostgresCreateDropDatabase(t *testing.T) { 38 drv := PostgresDriver{} 39 u := postgresTestURL(t) 40 41 // drop any existing database 42 err := drv.DropDatabase(u) 43 require.NoError(t, err) 44 45 // create database 46 err = drv.CreateDatabase(u) 47 require.NoError(t, err) 48 49 // check that database exists and we can connect to it 50 func() { 51 db, err := sql.Open("postgres", u.String()) 52 require.NoError(t, err) 53 defer mustClose(db) 54 55 err = db.Ping() 56 require.NoError(t, err) 57 }() 58 59 // drop the database 60 err = drv.DropDatabase(u) 61 require.NoError(t, err) 62 63 // check that database no longer exists 64 func() { 65 db, err := sql.Open("postgres", u.String()) 66 require.NoError(t, err) 67 defer mustClose(db) 68 69 err = db.Ping() 70 require.NotNil(t, err) 71 require.Equal(t, "pq: database \"dbmate\" does not exist", err.Error()) 72 }() 73 } 74 75 func TestPostgresDumpSchema(t *testing.T) { 76 drv := PostgresDriver{} 77 u := postgresTestURL(t) 78 79 // prepare database 80 db := prepTestPostgresDB(t) 81 defer mustClose(db) 82 err := drv.CreateMigrationsTable(db) 83 require.NoError(t, err) 84 85 // insert migration 86 err = drv.InsertMigration(db, "abc1") 87 require.NoError(t, err) 88 err = drv.InsertMigration(db, "abc2") 89 require.NoError(t, err) 90 91 // DumpSchema should return schema 92 schema, err := drv.DumpSchema(u, db) 93 require.NoError(t, err) 94 require.Contains(t, string(schema), "CREATE TABLE public.schema_migrations") 95 require.Contains(t, string(schema), "\n--\n"+ 96 "-- PostgreSQL database dump complete\n"+ 97 "--\n\n\n"+ 98 "--\n"+ 99 "-- Dbmate schema migrations\n"+ 100 "--\n\n"+ 101 "INSERT INTO public.schema_migrations (version) VALUES\n"+ 102 " ('abc1'),\n"+ 103 " ('abc2');\n") 104 105 // DumpSchema should return error if command fails 106 u.Path = "/fakedb" 107 schema, err = drv.DumpSchema(u, db) 108 require.Nil(t, schema) 109 require.EqualError(t, err, "pg_dump: [archiver (db)] connection to database "+ 110 "\"fakedb\" failed: FATAL: database \"fakedb\" does not exist") 111 } 112 113 func TestPostgresDatabaseExists(t *testing.T) { 114 drv := PostgresDriver{} 115 u := postgresTestURL(t) 116 117 // drop any existing database 118 err := drv.DropDatabase(u) 119 require.NoError(t, err) 120 121 // DatabaseExists should return false 122 exists, err := drv.DatabaseExists(u) 123 require.NoError(t, err) 124 require.Equal(t, false, exists) 125 126 // create database 127 err = drv.CreateDatabase(u) 128 require.NoError(t, err) 129 130 // DatabaseExists should return true 131 exists, err = drv.DatabaseExists(u) 132 require.NoError(t, err) 133 require.Equal(t, true, exists) 134 } 135 136 func TestPostgresDatabaseExists_Error(t *testing.T) { 137 drv := PostgresDriver{} 138 u := postgresTestURL(t) 139 u.User = url.User("invalid") 140 141 exists, err := drv.DatabaseExists(u) 142 require.Equal(t, "pq: role \"invalid\" does not exist", err.Error()) 143 require.Equal(t, false, exists) 144 } 145 146 func TestPostgresCreateMigrationsTable(t *testing.T) { 147 drv := PostgresDriver{} 148 db := prepTestPostgresDB(t) 149 defer mustClose(db) 150 151 // migrations table should not exist 152 count := 0 153 err := db.QueryRow("select count(*) from public.schema_migrations").Scan(&count) 154 require.Equal(t, "pq: relation \"public.schema_migrations\" does not exist", err.Error()) 155 156 // create table 157 err = drv.CreateMigrationsTable(db) 158 require.NoError(t, err) 159 160 // migrations table should exist 161 err = db.QueryRow("select count(*) from public.schema_migrations").Scan(&count) 162 require.NoError(t, err) 163 164 // create table should be idempotent 165 err = drv.CreateMigrationsTable(db) 166 require.NoError(t, err) 167 } 168 169 func TestPostgresSelectMigrations(t *testing.T) { 170 drv := PostgresDriver{} 171 db := prepTestPostgresDB(t) 172 defer mustClose(db) 173 174 err := drv.CreateMigrationsTable(db) 175 require.NoError(t, err) 176 177 _, err = db.Exec(`insert into public.schema_migrations (version) 178 values ('abc2'), ('abc1'), ('abc3')`) 179 require.NoError(t, err) 180 181 migrations, err := drv.SelectMigrations(db, -1) 182 require.NoError(t, err) 183 require.Equal(t, true, migrations["abc1"]) 184 require.Equal(t, true, migrations["abc2"]) 185 require.Equal(t, true, migrations["abc2"]) 186 187 // test limit param 188 migrations, err = drv.SelectMigrations(db, 1) 189 require.NoError(t, err) 190 require.Equal(t, true, migrations["abc3"]) 191 require.Equal(t, false, migrations["abc1"]) 192 require.Equal(t, false, migrations["abc2"]) 193 } 194 195 func TestPostgresInsertMigration(t *testing.T) { 196 drv := PostgresDriver{} 197 db := prepTestPostgresDB(t) 198 defer mustClose(db) 199 200 err := drv.CreateMigrationsTable(db) 201 require.NoError(t, err) 202 203 count := 0 204 err = db.QueryRow("select count(*) from public.schema_migrations").Scan(&count) 205 require.NoError(t, err) 206 require.Equal(t, 0, count) 207 208 // insert migration 209 err = drv.InsertMigration(db, "abc1") 210 require.NoError(t, err) 211 212 err = db.QueryRow("select count(*) from public.schema_migrations where version = 'abc1'"). 213 Scan(&count) 214 require.NoError(t, err) 215 require.Equal(t, 1, count) 216 } 217 218 func TestPostgresDeleteMigration(t *testing.T) { 219 drv := PostgresDriver{} 220 db := prepTestPostgresDB(t) 221 defer mustClose(db) 222 223 err := drv.CreateMigrationsTable(db) 224 require.NoError(t, err) 225 226 _, err = db.Exec(`insert into public.schema_migrations (version) 227 values ('abc1'), ('abc2')`) 228 require.NoError(t, err) 229 230 err = drv.DeleteMigration(db, "abc2") 231 require.NoError(t, err) 232 233 count := 0 234 err = db.QueryRow("select count(*) from public.schema_migrations").Scan(&count) 235 require.NoError(t, err) 236 require.Equal(t, 1, count) 237 } 238 239 func TestPostgresPing(t *testing.T) { 240 drv := PostgresDriver{} 241 u := postgresTestURL(t) 242 243 // drop any existing database 244 err := drv.DropDatabase(u) 245 require.NoError(t, err) 246 247 // ping database 248 err = drv.Ping(u) 249 require.NoError(t, err) 250 251 // ping invalid host should return error 252 u.Host = "postgres:404" 253 err = drv.Ping(u) 254 require.Error(t, err) 255 require.Contains(t, err.Error(), "connect: connection refused") 256 }