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