gocon-2018-how-we-go-test-with-rdbms.pdf

Many of API servers tend to interact with RDBMS to serve structured data for frontend. Unlike other languages with full-featureed web application frameworks, it seems, at first glance, a little difficult to write tests for Go applications using RDBMS. However, knowing `database/sql` with a bit of RDBMS knowledge is just enough to write clean tests for RDBMS backed Go applications. In this talk, I'll describe how to write efficient RDBMS backed Go application tests.

Transcript

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

   View Slide

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

   View Slide

3. 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
   

   View Slide

4. 4
   Background
   (product/size)
   

   View Slide

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

   View Slide

6. 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
   

   View Slide

7. 7
   Application code structure
   which is easy to test with RDBMS
   

   View Slide

8. 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
   

   View Slide

9. 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
   }
   

   View Slide

10. 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
    

    View Slide

11. 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
    }
    

    View Slide

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

    View Slide

13. 13
    Test setup and teardown
    

    View Slide

14. 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
    

    View Slide

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

    View Slide

16. 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
    

    View Slide

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

    View Slide

18. 18
    How to make tests
    repeatable and independent
    

    View Slide

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

    View Slide

20. 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
    

    View Slide

21. 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
    

    View Slide

22. 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
    

    View Slide

23. 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.
    

    View Slide

24. 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
    )
    

    View Slide

25. 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
    

    View Slide

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

    View Slide

27. 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
    

    View Slide

28. 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
    

    View Slide

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

    View Slide

30. 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)
    

    View Slide

31. 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
    

    View Slide

32. 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
    

    View Slide

33. 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
    }
    

    View Slide

34. 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
    

    View Slide

35. 35
    How to make tests run
    in parallel
    

    View Slide

36. 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
    

    View Slide