Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

4 Background (product/size)

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

7 Application code structure which is easy to test with RDBMS

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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 }

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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 }

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

13 Test setup and teardown

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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) }

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

18 How to make tests repeatable and independent

Slide 19

Slide 19 text

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?”

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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.

Slide 24

Slide 24 text

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 )

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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) }

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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) }

Slide 30

Slide 30 text

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)

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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 }

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

35 How to make tests run in parallel

Slide 36

Slide 36 text

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