github.com/bingtel/dbmate@v1.4.1/pkg/dbmate/mysql_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 mySQLTestURL(t *testing.T) *url.URL {
    12  	u, err := url.Parse("mysql://root:root@mysql/dbmate")
    13  	require.NoError(t, err)
    14  
    15  	return u
    16  }
    17  
    18  func prepTestMySQLDB(t *testing.T) *sql.DB {
    19  	drv := MySQLDriver{}
    20  	u := mySQLTestURL(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 := drv.Open(u)
    32  	require.NoError(t, err)
    33  
    34  	return db
    35  }
    36  
    37  func TestNormalizeMySQLURLDefaults(t *testing.T) {
    38  	u, err := url.Parse("mysql://host/foo")
    39  	require.NoError(t, err)
    40  	require.Equal(t, "", u.Port())
    41  
    42  	s := normalizeMySQLURL(u)
    43  	require.Equal(t, "tcp(host:3306)/foo?multiStatements=true", s)
    44  }
    45  
    46  func TestNormalizeMySQLURLCustom(t *testing.T) {
    47  	u, err := url.Parse("mysql://bob:secret@host:123/foo?flag=on")
    48  	require.NoError(t, err)
    49  	require.Equal(t, "123", u.Port())
    50  
    51  	s := normalizeMySQLURL(u)
    52  	require.Equal(t, "bob:secret@tcp(host:123)/foo?flag=on&multiStatements=true", s)
    53  }
    54  
    55  func TestMySQLCreateDropDatabase(t *testing.T) {
    56  	drv := MySQLDriver{}
    57  	u := mySQLTestURL(t)
    58  
    59  	// drop any existing database
    60  	err := drv.DropDatabase(u)
    61  	require.NoError(t, err)
    62  
    63  	// create database
    64  	err = drv.CreateDatabase(u)
    65  	require.NoError(t, err)
    66  
    67  	// check that database exists and we can connect to it
    68  	func() {
    69  		db, err := drv.Open(u)
    70  		require.NoError(t, err)
    71  		defer mustClose(db)
    72  
    73  		err = db.Ping()
    74  		require.NoError(t, err)
    75  	}()
    76  
    77  	// drop the database
    78  	err = drv.DropDatabase(u)
    79  	require.NoError(t, err)
    80  
    81  	// check that database no longer exists
    82  	func() {
    83  		db, err := drv.Open(u)
    84  		require.NoError(t, err)
    85  		defer mustClose(db)
    86  
    87  		err = db.Ping()
    88  		require.NotNil(t, err)
    89  		require.Regexp(t, "Unknown database 'dbmate'", err.Error())
    90  	}()
    91  }
    92  
    93  func TestMySQLDumpSchema(t *testing.T) {
    94  	drv := MySQLDriver{}
    95  	u := mySQLTestURL(t)
    96  
    97  	// prepare database
    98  	db := prepTestMySQLDB(t)
    99  	defer mustClose(db)
   100  	err := drv.CreateMigrationsTable(db)
   101  	require.NoError(t, err)
   102  
   103  	// insert migration
   104  	err = drv.InsertMigration(db, "abc1")
   105  	require.NoError(t, err)
   106  	err = drv.InsertMigration(db, "abc2")
   107  	require.NoError(t, err)
   108  
   109  	// DumpSchema should return schema
   110  	schema, err := drv.DumpSchema(u, db)
   111  	require.NoError(t, err)
   112  	require.Contains(t, string(schema), "CREATE TABLE `schema_migrations`")
   113  	require.Contains(t, string(schema), "\n-- Dump completed\n\n"+
   114  		"--\n"+
   115  		"-- Dbmate schema migrations\n"+
   116  		"--\n\n"+
   117  		"LOCK TABLES `schema_migrations` WRITE;\n"+
   118  		"INSERT INTO `schema_migrations` (version) VALUES\n"+
   119  		"  ('abc1'),\n"+
   120  		"  ('abc2');\n"+
   121  		"UNLOCK TABLES;\n")
   122  
   123  	// DumpSchema should return error if command fails
   124  	u.Path = "/fakedb"
   125  	schema, err = drv.DumpSchema(u, db)
   126  	require.Nil(t, schema)
   127  	require.EqualError(t, err, "mysqldump: Got error: 1049: "+
   128  		"\"Unknown database 'fakedb'\" when selecting the database")
   129  }
   130  
   131  func TestMySQLDatabaseExists(t *testing.T) {
   132  	drv := MySQLDriver{}
   133  	u := mySQLTestURL(t)
   134  
   135  	// drop any existing database
   136  	err := drv.DropDatabase(u)
   137  	require.NoError(t, err)
   138  
   139  	// DatabaseExists should return false
   140  	exists, err := drv.DatabaseExists(u)
   141  	require.NoError(t, err)
   142  	require.Equal(t, false, exists)
   143  
   144  	// create database
   145  	err = drv.CreateDatabase(u)
   146  	require.NoError(t, err)
   147  
   148  	// DatabaseExists should return true
   149  	exists, err = drv.DatabaseExists(u)
   150  	require.NoError(t, err)
   151  	require.Equal(t, true, exists)
   152  }
   153  
   154  func TestMySQLDatabaseExists_Error(t *testing.T) {
   155  	drv := MySQLDriver{}
   156  	u := mySQLTestURL(t)
   157  	u.User = url.User("invalid")
   158  
   159  	exists, err := drv.DatabaseExists(u)
   160  	require.Regexp(t, "Access denied for user 'invalid'@", err.Error())
   161  	require.Equal(t, false, exists)
   162  }
   163  
   164  func TestMySQLCreateMigrationsTable(t *testing.T) {
   165  	drv := MySQLDriver{}
   166  	db := prepTestMySQLDB(t)
   167  	defer mustClose(db)
   168  
   169  	// migrations table should not exist
   170  	count := 0
   171  	err := db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   172  	require.Regexp(t, "Table 'dbmate.schema_migrations' doesn't exist", err.Error())
   173  
   174  	// create table
   175  	err = drv.CreateMigrationsTable(db)
   176  	require.NoError(t, err)
   177  
   178  	// migrations table should exist
   179  	err = db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   180  	require.NoError(t, err)
   181  
   182  	// create table should be idempotent
   183  	err = drv.CreateMigrationsTable(db)
   184  	require.NoError(t, err)
   185  }
   186  
   187  func TestMySQLSelectMigrations(t *testing.T) {
   188  	drv := MySQLDriver{}
   189  	db := prepTestMySQLDB(t)
   190  	defer mustClose(db)
   191  
   192  	err := drv.CreateMigrationsTable(db)
   193  	require.NoError(t, err)
   194  
   195  	_, err = db.Exec(`insert into schema_migrations (version)
   196  		values ('abc2'), ('abc1'), ('abc3')`)
   197  	require.NoError(t, err)
   198  
   199  	migrations, err := drv.SelectMigrations(db, -1)
   200  	require.NoError(t, err)
   201  	require.Equal(t, true, migrations["abc1"])
   202  	require.Equal(t, true, migrations["abc2"])
   203  	require.Equal(t, true, migrations["abc2"])
   204  
   205  	// test limit param
   206  	migrations, err = drv.SelectMigrations(db, 1)
   207  	require.NoError(t, err)
   208  	require.Equal(t, true, migrations["abc3"])
   209  	require.Equal(t, false, migrations["abc1"])
   210  	require.Equal(t, false, migrations["abc2"])
   211  }
   212  
   213  func TestMySQLInsertMigration(t *testing.T) {
   214  	drv := MySQLDriver{}
   215  	db := prepTestMySQLDB(t)
   216  	defer mustClose(db)
   217  
   218  	err := drv.CreateMigrationsTable(db)
   219  	require.NoError(t, err)
   220  
   221  	count := 0
   222  	err = db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   223  	require.NoError(t, err)
   224  	require.Equal(t, 0, count)
   225  
   226  	// insert migration
   227  	err = drv.InsertMigration(db, "abc1")
   228  	require.NoError(t, err)
   229  
   230  	err = db.QueryRow("select count(*) from schema_migrations where version = 'abc1'").
   231  		Scan(&count)
   232  	require.NoError(t, err)
   233  	require.Equal(t, 1, count)
   234  }
   235  
   236  func TestMySQLDeleteMigration(t *testing.T) {
   237  	drv := MySQLDriver{}
   238  	db := prepTestMySQLDB(t)
   239  	defer mustClose(db)
   240  
   241  	err := drv.CreateMigrationsTable(db)
   242  	require.NoError(t, err)
   243  
   244  	_, err = db.Exec(`insert into schema_migrations (version)
   245  		values ('abc1'), ('abc2')`)
   246  	require.NoError(t, err)
   247  
   248  	err = drv.DeleteMigration(db, "abc2")
   249  	require.NoError(t, err)
   250  
   251  	count := 0
   252  	err = db.QueryRow("select count(*) from schema_migrations").Scan(&count)
   253  	require.NoError(t, err)
   254  	require.Equal(t, 1, count)
   255  }
   256  
   257  func TestMySQLPing(t *testing.T) {
   258  	drv := MySQLDriver{}
   259  	u := mySQLTestURL(t)
   260  
   261  	// drop any existing database
   262  	err := drv.DropDatabase(u)
   263  	require.NoError(t, err)
   264  
   265  	// ping database
   266  	err = drv.Ping(u)
   267  	require.NoError(t, err)
   268  
   269  	// ping invalid host should return error
   270  	u.Host = "mysql:404"
   271  	err = drv.Ping(u)
   272  	require.Error(t, err)
   273  	require.Contains(t, err.Error(), "connect: connection refused")
   274  }