Incubating Program: Use tidb-lite to integrate TiDB into your Golang application

[WangXiangUSTC]

Motivation

Built-in database

When developing applications using Golang, we often need to store data locally. It is a good choice to use a built-in database, so that we can use SQL to manage the data, greatly improving our development efficiency. However, there are not many built-in databases that are compatible with the MySQL protocol in Golang.

Database-related unit tests

If our Golang application involves database-related operations, we need to write unit tests for those functions. There are usually two ways.

• Deploy a database and the unit tests run with it. Unit testing relies on an external environment, which is obviously unfriendly.
• Mock SQL services in unit tests. The most commonly used project is go-sqlmock. However, this approach is often tedious, and you need to define the results of each database operation in advance.

tidb-lite

Implementation

Basically just need to modify TiDB's main.go file. The main method is not required, instead, modify it as an interface to be provided externally. Users can run a TiDB with mocktikv mode by using this interface in tidb-lite. In addition, tidb-lite also provides another interface to create a database connection, and users can access the database through this connection.

Usage

For example, we have the following codes:

package example

import (
    "context"
    "database/sql"
    "fmt"

    "github.com/pingcap/errors"
    "github.com/pingcap/log "
    "go.uber.org/zap"
)

// GetRowCount returns row count of the table.
// if not specify where condition, return total row count of the table.
func GetRowCount(ctx context.Context, db *sql.DB , schemaName string, tableName string, where string) (int64, error) {
    /*
        select count example result:
        mysql> SELECT count(1) cnt from `test`.`itest` where id> 0;
        +----- -+
        | cnt |
        +------+
        | 100 |
        +------+
    */

    query := fmt.Sprintf("SELECT COUNT(1) cnt FROM `%s`.`%s` ", schemaName, tableName)
    if len(where)> 0 {
        query += fmt.Sprintf(" WHERE %s", where)
    }
    log.Debug("get row count", zap.String("sql", query) )

    var cnt sql.NullInt64
    err := db.QueryRowContext(ctx, query).Scan(&cnt)
    if err != nil {
        return 0, errors.Trace(err)
    }
    if !cnt.Valid {
        return 0, errors.NotFoundf("table `%s`.`%s`", schemaName, tableName)
    }

    return cnt.Int64, nil
}

GetRowCount is used to get the number of eligible rows in the table, the unit test code of this function using tidb-lite is as follows:

package example

import (
    "context"
    "testing"
    "time"

    tidblite "github.com/WangXiangUSTC/tidb-lite"
    . "Github.com/pingcap /check"
)

func TestClient(t *testing.T) {
    TestingT(t)
}

var _ = Suite(&testExampleSuite{})

type testExampleSuite struct{}

func (t *testExampleSuite) TestGetRowCount(c *C) {
    tidbServer, err: = tidblite.NewTiDBServer(tidblite.NewOptions(c.MkDir()))
    c.Assert(err, IsNil)
    defer tidbServer.Close()

    dbConn, err := tidbServer.CreateConn()
    c.Assert(err, IsNil)
    defer dbConn.Close()

    ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer cancel()

    _, err = dbConn.ExecContext(ctx, "create database example_test")
    c.Assert(err, IsNil)
    _, err = dbConn.ExecContext(ctx , "create table example_test.t(id int primary key, name varchar(24))")
    c.Assert(err, IsNil)
    _, err = dbConn.ExecContext(ctx, "insert into example_test.t values(1, ' a'),(2,'b'),(3,'c')")
    c.Assert(err, IsNil)

    count, err := GetRowCount(ctx, dbConn, "example_test", "t", "id > 2")
    c.Assert(err, IsNil)
    c.Assert(count , Equals, int64(1))

    count, err = GetRowCount(ctx, dbConn, "example_test", "t", "")
    c.Assert(err, IsNil)
    c.Assert(count, Equals, int64(3))
}

First, we use NewTiDBServer to create a TiDB instance and use CreateConn to get a link to this database. And then you can use this link to access the database, generate test data, and verify the correctness of the function.

Advantage

Simplicity

One of the most important advantages of tidb-lite is simplicity. Run a TiDB directly in the code instead of running a MySQL/TiDB instance before running the unit test. This ensures that the unit test does not depend on the external environment; in addition, we don’t need to write a lot of redundancy and boring test code with go-sqlmock but focus on the correctness of the function.

Compatible with MySQL protocol

TiDB is highly compatible with MySQL protocol. Using tidb-lite can almost completely simulate the MySQL environment.

Plan

tidb-lite now relies on TiDB, but TiDB contains so many features, such as TiKV mode, binlog, dumping, br. In fact tidb-lite only needs the mocktikv mode. Maybe we can do a lite version based on TiDB, which is what lite means.

Reference

• tidb-lite repo
• tidb-lite: A Simpler Way to Unit Test Golang Database Code

Estimated Time

60 days

Team members

Only @WangXiangUSTC. Expect another team member and a mentor.

[sunxiaoguang]

LGTM

[winkyao]

LGTM

[winkyao]

what's the repo name you prefer?

[WangXiangUSTC]

what's the repo name you prefer?

tidb-lite

[zhangyangyu]

Currently there is another trend employing container techs(which is of course still external environments) into tests. A popular framework is testcontainer. It has already been used by many popular projects. It doesn't conflict with tidb-lite I think but provides another choice.

[WangXiangUSTC]

Currently there is another trend employing container techs(which is of course still external environments) into tests. A popular framework is testcontainer. It has already been used by many popular projects. It doesn't conflict with tidb-lite I think but provides another choice.

It seems that it works for Java applications.

[zhangyangyu]

It seems that it works for Java applications.

Also has other languages.