$30 off During Our Annual Pro Sale. View Details »

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

Akira Chiku
November 25, 2018

 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.

Akira Chiku

November 25, 2018
Tweet

More Decks by Akira Chiku

Other Decks in Technology

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