1 Beloved database/sql How we go test with RDBMS GoCon JP Nov 25, 2018 Akira Chiku

me 2 Name: Akira Chiku Twitter: @_achiku GitHub: @achiku Google Search -> Akira Chiku Fire

agenda 3 Ø Background(product/size) Ø Application code structure which is easy test with RDBMS Ø Test setup and teardown Ø How to make tests repeatable and independent Ø How to make tests run in parallel

4 Background (product/size)

background (product) 5 Ø product Ø App based instant, and reloadable virtual/plastic Visa prepaid card for everyone Ø iOS, Android Ø https://vandle.jp/

background (size) 6 Ø size (as of Nov, 2018) Ø # of endpoints (internal API): 110 Ø # of external services: 9 Ø # of tables (PostgreSQL): 192 Ø # of lines (Go code without vendor) Ø application + generated code: 66,064 Ø test + public test api code: 41,109

7 Application code structure which is easy to test with RDBMS

use driver interface 8 Ø It makes easy to switch DB implementation while testing Ø Use interface to define database Ø It also makes arguments more explicit Ø e.g.) This function only takes Tx like object, etc

9 // Queryer database/sql compatible query interface type Queryer interface { Exec(string, ...interface{}) (sql.Result, error) Query(string, ...interface{}) (*sql.Rows, error) QueryRow(string, ...interface{}) *sql.Row } // Txer database/sql transaction interface type Txer interface { Queryer Commit() error Rollback() error } // DBer database/sql type DBer interface { Queryer Begin() (*sql.Tx, error) Close() error Ping() error }

avoid package global variables 10 Ø Let app global struct to have DB connection pool Ø This allows tests to switch actual implementation of DB connection pool

11 // App application global type App struct { DB txmodel.DBer Logger *log.Logger } // NewApp creates new app func NewApp(v bool) (*App, error) { constr := os.Getenv("TODOCLI_CONSTR") db, err := txmodel.NewDB(constr) if err != nil { return nil, err } var logger *log.Logger if v { logger = log.New(os.Stdout, "[app]", log.Lmicroseconds) } else { logger = log.New(ioutil.Discard, "[app]", log.Lmicroseconds) } app := &App{ DB: db, Logger: logger, } return app, nil }

12 func main() { flag.Parse() app, err := NewApp(*verbose) if err != nil { log.Fatal(err) } .. }

13 Test setup and teardown

Test setup and teardown 14 Ø use testing.M for setup/teardown Ø This is in main_test.go Ø create test schema (PostgreSQL) Ø create tables in the test schema Ø run tests Ø drop test schema with all the tables

15 // TestMain model package setup/teardonw func TestMain(m *testing.M) { flag.Parse() .. TestDropSchema(dbSetupCfg, testSchema) if err := TestCreateSchema(dbSetupCfg, testSchema, testUser); err != nil { log.Println(err) os.Exit(1) } if err := TestCreateTables(tblSetupCfg, "../schema"); err != nil { log.Println(err) os.Exit(1) } code := m.Run() if err := TestDropSchema(dbSetupCfg, testSchema); err != nil { log.Println(err) os.Exit(1) } os.Exit(code) }

Make them Public Test API 16 Ø Public Test API allow other packages using our package to test itself without reinventing components Ø “Advanced Testing with Go” Ø https://speakerdeck.com/mitchellh/advanced-testing- with-go?slide=53 Ø The database related package is intended to be used in other packages to integrate functions inside Ø Since database related layer tends to have dependent packages, it saves a lot of time

17 https://speakerdeck.com/mitchellh/advanced-testing-with-go?slide=53

18 How to make tests repeatable and independent

Repeatable and independent tests 19 Ø Good tests are repeatable, and independent from each other even they have to interact with RDBMS Ø not sufficient for good tests, but necessary Ø Why do tests need to be independent from each other? Ø order of tests should not change test results Ø the order in which tests are executed is not defined Ø Developers can specify how to run tests using command line options Ø It boils down to a question “How can we clear data modifications made during a test?”

Learning from pioneers 20 Ø Django, full-featured Python web application framework, as an example Ø I don't know much about Ruby on Rails, but it seems like Django, and Rails have similar approaches to independent tests Ø https://github.com/DatabaseCleaner/database_cleaner

Approaches 21 Ø Wrap test in a transaction Ø In Django context, it is TestCase class Ø it encloses the test code in a database transaction that is rolled back at the end of the test. This guarantees that the rollback at the end of the test restores the database to its initial state Ø Truncate all tables after a test Ø In Django context, it is TransactionTestCase class Ø A TransactionTestCase resets the database after the test runs by truncating all tables. A TransactionTestCase may call commit and rollback and observe the effects of these calls on the database Ø Wrap test in transaction, and partially mock it Ø Django doesn’t have this type of strategy Ø Begin -> savepoint, Commit -> do nothing, Rollback -> rollback to savepoint

Wrap test in a transaction 22 Ø Pros Ø It clears up all data modifications in a test Ø It isolates data modifications from other tests running at the same time Ø It is fast compared to truncating all tables or deleting all data from all tables Ø Cons Ø If a function uses transactions, this strategy doesn’t work

Single transaction SQL driver 23 Ø Single Transaction SQL Driver Ø https://github.com/DATA-DOG/go-txdb Ø When the connection is opened, it starts a transaction and all operations performed on this sql.DB will be within that transaction. Ø You can register txdb for different sql drivers and have it under different driver names. Under the hood whenever a txdb driver is opened, it attempts to open a real connection and starts transaction. When close is called, it rollbacks transaction leaving your prepared test database in the same state as before.

24 func init() { txdb.Register("txdb", "pgx", "postgres://gotodo_api_test@localhost:5432/gotodo?sslmode=disable") } import ( "database/sql" "testing" txdb "github.com/achiku/pgtxdb" "github.com/jackc/pgx" _ "github.com/jackc/pgx/stdlib" // pgx )

25 // TestSetupTx create tx and cleanup func for test func TestSetupTx(t *testing.T) (Txer, func()) { db, err := sql.Open("txdb", uuid.NewV4().String()) if err != nil { t.Fatal(err) } tx, err := db.Begin() if err != nil { t.Fatal(err) } cleanup := func() { tx.Rollback() db.Close() } return tx, cleanup } Ø db is already a transaction Ø db.Begin() returns itself, not really starting new transaction

26 func TestGetToDoByID(t *testing.T) { t.Parallel() tx, cleanup := TestSetupTx(t) defer cleanup() testShowCount(t, tx) td := TestCreateToDo(t, tx, &ToDo{ Description: "test", Status: "created", }) cases := []struct { id int64 found bool }{ {id: td.ID, found: true}, {id: 0, found: false}, } for _, c := range cases { td, found, err := GetToDoByID(tx, c.id) if err != nil { t.Fatal(err) } if found != c.found { t.Errorf("want %t got %t", c.found, found) } if found && td.ID != c.id { t.Errorf("want %d got %d", c.id, td.ID) } } testShowCount(t, tx) }

Truncate all tables after a test 27 Ø Pros Ø A test using this strategy can have more than one transactions in the code tested Ø Real begin/commit/rollback Ø Cons Ø It takes time to truncate all tables Ø It affects other tests running at the same time on the same schema

28 // TestSetupDB create db func TestSetupDB(t *testing.T) (DBer, func()) { db, err := sql.Open("pgx", "postgres://gotodo_api_test@localhost:5432/gotodo?sslmode=disable") if err != nil { t.Fatal(err) } cleanup := func() { _, err := db.Exec(` truncate table todo cascade; truncate table action cascade; `) if err != nil { t.Fatal(err) } db.Close() } return db, cleanup } Ø Truncate tables in an cleanup function

29 func TestGetToDoByID(t *testing.T) { db, cleanup := TestSetupDB(t) defer cleanup() testShowCount(t, db) td := TestCreateToDo(t, db, &ToDo{ Description: "test", Status: "created", }) cases := []struct { id int64 found bool }{ {id: td.ID, found: true}, {id: 0, found: false}, } for _, c := range cases { td, found, err := GetToDoByID(db, c.id) if err != nil { t.Fatal(err) } if found != c.found { t.Errorf("want %t got %t", c.found, found) } if found && td.ID != c.id { t.Errorf("want %d got %d", c.id, td.ID) } } testShowCount(t, db) }

Wrap test in a transaction, and partially mock it 30 Ø Pros Ø It clears up all data modifications in a test once it’s done Ø It isolates data modifications from other tests running at the same time Ø It is fast compared to truncating all tables or deleting all data from all tables Ø Application code can have more than on transactions, and still works Ø Cons Ø It mocks some parts of transaction (begin, commit, and rollback) Ø Application code might behave differently in certain circumstances (e.g. trigger)

Single transaction SQL driver with mock 31 Ø Single Transaction SQL Driver with savepoint Ø https://github.com/achiku/pgtxdb Ø When conn.Begin() is called, this library executes SAVEPOINT pgtxdb_xxx; instead of actually begins transaction Ø tx.Commit() does nothing Ø ROLLBACK TO SAVEPOINT pgtxdb_xxx; will be executed upon tx.Rollback() is call so that it can emulate transaction rollback

32 begin op1 if err then rollback else commit and return begin op2 commit begin savepoint (instead of begin) op1 if err then rollback to savepoint (instead of rollback) else do nothing (instead of commit) and return savepoint (instead of begin) op2 do nothing (instead of commit) rollback Multiple transactions with mock tx Production Testing

33 func (c *conn) Begin() (driver.Tx, error) { savepointID := len(c.savepoints) c.savepoints = append(c.savepoints, savepointID) sql := fmt.Sprintf("SAVEPOINT pgtxdb_%d", savepointID) _, err := c.tx.Exec(sql) if err != nil { return nil, errors.Wrap(err, "failed to create savepoint") } return c, nil } Ø Inside pgtxdb, Begin executes SAVEPOINT, Commit does nothing, and Rollback executes ROLLBACK TO SAVEPOINT func (c *conn) Commit() error { return nil } func (c *conn) Rollback() error { savepointID := c.savepoints[len(c.savepoints)-1] c.savepoints = c.savepoints[:len(c.savepoints)-1] sql := fmt.Sprintf("ROLLBACK TO SAVEPOINT pgtxdb_%d", savepointID) _, err := c.tx.Exec(sql) if err != nil { return errors.Wrap(err, "failed to rollback to savepoint") } return nil }

Test performance 34 Ø Spec Ø MacBook Pro (13-inch, 2017) Ø CPU 2.3 GHz Intel Core i5 Ø RAM 16GB Ø Go 1.11.2 Ø https://github.com/achiku/gotodocli Ø $ go test -count 1000 Ø 5 tries average Ø Transaction Ø 3.12 sec Ø Truncation Ø 47.38 sec

35 How to make tests run in parallel

If transaction is used 36 Ø Since all tests are isolated from each other, and modifications can’t be seen from outside of tx, we can use t.Parallel() in tests $ go test -count 1000 -parallel 2 2018/11/25 12:40:41 failed to create test schema: gotodo_api_test PASS ok github.com/achiku/gotodocli/txmodel 3.542s