github.com/bingtel/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  }