Immutable transaction isolated sql driver for golang

Build Status GoDoc

Single transaction based sql.Driver for GO

Package txdb is a single transaction based database sql driver. When the connection is opened, it starts a transaction and all operations performed on this sql.DB will be within that transaction. If concurrent actions are performed, the lock is acquired and connection is always released the statements and rows are not holding the connection.

Why is it useful. A very basic use case would be if you want to make functional tests you can prepare a test database and within each test you do not have to reload a database. All tests are isolated within transaction and though, performs fast. And you do not have to interface your sql.DB reference in your code, txdb is like a standard sql.Driver.

This driver supports any sql.Driver connection to be opened. 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.

Given, you have a mysql database called txdb_test and a table users with a username column.

    package main

    import (
        "database/sql"
        "log"

        "github.com/DATA-DOG/go-txdb"
        _ "github.com/go-sql-driver/mysql"
    )

    func init() {
        // we register an sql driver named "txdb"
        txdb.Register("txdb", "mysql", "root@/txdb_test")
    }

    func main() {
        // dsn serves as an unique identifier for connection pool
        db, err := sql.Open("txdb", "identifier")
        if err != nil {
            log.Fatal(err)
        }
        defer db.Close()

        if _, err := db.Exec(`INSERT INTO users(username) VALUES("gopher")`); err != nil {
            log.Fatal(err)
        }
    }

Every time you will run this application, it will remain in the same state as before.

Testing

Usage is mainly intended for testing purposes. See the db_test.go as an example. In order to run tests, you will need docker and docker-compose:

docker-compose up
make test

The tests are currently using postgres and mysql databases

Documentation

See godoc for general API details. See .travis.yml for supported go versions.

Contributions

Feel free to open a pull request. Note, if you wish to contribute an extension to public (exported methods or types) - please open an issue before to discuss whether these changes can be accepted. All backward incompatible changes are and will be treated cautiously.

The public API is locked since it is an sql.Driver and will not change.

License

txdb is licensed under the three clause BSD license

Owner
DATA-DOG
Happy awesome developers
DATA-DOG
Comments
  • Is it possible to run a transaction within txdb?

    Is it possible to run a transaction within txdb?

    Hello there,

    I'm trying to use your driver together with the underlying mysql driver, a mysql database and sqlx. Unfortunately whenever I create a transaction in my production code, it fails tx.Commit() or tx.Rollback(), usually with the error saying that the save point does not exist for tx_0 for example. Is it possible to run transactions within a (hidden) transaction started by your txdb driver?

    Thanks!

  • hangs when same primary key used in different tests

    hangs when same primary key used in different tests

    When you insert the same primary key in two different transactions your tests will hang.

    With the code below, the following happens

    A inserting
    A inserted
    A db closed
    B inserting <-- hangs here
    

    db schema

    -- CREATE DATABASE example;
    CREATE TABLE example_table (
      name text PRIMARY KEY
    );
    

    test file example_test.go

    package main
    
    import (
    	"database/sql"
    	"fmt"
    	"log"
    	"testing"
    	"time"
    
    	txdb "github.com/DATA-DOG/go-txdb"
    	_ "github.com/jackc/pgx/stdlib"
    )
    
    var (
    	dsn    = "postgres://postgres@localhost/example?sslmode=disable"
    	query  = "insert into example_table (name) values ('hello world')"
    	driver = "pgx"
    )
    
    func init() {
    	txdb.Register("txdb", driver, dsn)
    }
    
    func TestA(t *testing.T) {
    	// setup
    	db, err := sql.Open("txdb", "A")
    	if err != nil {
    		panic(err)
    	}
    	defer func() {
    		db.Close()
    		fmt.Println("A db closed")
    	}()
    
    	// insert
    	fmt.Println("A inserting")
    	_, err = db.Query(query)
    	if err != nil {
    		t.Fatal(err)
    	}
    	fmt.Println("A inserted")
    }
    
    func TestB(t *testing.T) {
    	// setup
    	db, err := sql.Open("txdb", "B")
    	if err != nil {
    		panic(err)
    	}
    	defer func() {
    		db.Close()
    		fmt.Println("B db closed")
    	}()
    
    	// insert
    	fmt.Println("B inserting")
    	_, err = db.Query(query)
    	if err != nil {
    		panic(err)
    	}
    	fmt.Println("B inserted")
    }
    

    I have tried this code with the postgres drivers _ "github.com/jackc/pgx/stdlib" and _ github.com/lib/pq

  • pq: syntax error at or near

    pq: syntax error at or near "," while using txdb driver. works with plain sqlx

    this is sqlx.NamedExec call which has problem while running with txdb

    sql := "INSERT INTO jobs(id, url_hash, created_at, job) VALUES (:id, :url_hash, :created_at, :job);" _, err := s.DB.NamedExecContext(ctx, sql, jobs)

    err is pq: syntax error at or near ","

    i have tried master branch as well as go mod

  • does this support orm?

    does this support orm?

    Hey, this library is working well with https://github.com/jmoiron/sqlx, but it seems not working with orm, such as https://github.com/gobuffalo/pop and https://github.com/jinzhu/gorm. Is there any workaround that developer can implement to use this library with these orms? Thanks!

  • DB CURRENT_TIMESTAMP is always the same

    DB CURRENT_TIMESTAMP is always the same

    Hello, thanks for the library!

    I have been using it in tests for some time but today I noticed a strange behaviour. In one of my test I insert 3 records one-by-one with created_at set by DB as DEFAULT CURRENT_TIMESTAMP. Later I select those records with ORDER BY created_at. And records order is always messed up. I investigated it a little bit and I noticed that all records have the same created_at value whereas with read DB it differs in few milliseconds.

    I ended up with code snippet which demonstrates the problem:

    func main() {
    	const dsn = "postgres://postgres@localhost/test_db?sslmode=disable"
    	txdb.Register("txdb", "postgres", dsn)
    
    	db, err := sql.Open("txdb", "anywordhere")
    	//db, err := sql.Open("postgres", dsn)
    	if err != nil {
    		log.Fatal(err)
    	}
    
    	probe(db)
    	time.Sleep(1 * time.Second)
    	probe(db)
    }
    
    func probe(db *sql.DB) {
    	var ts time.Time
    	err := db.QueryRow(`SELECT now()`).Scan(&ts)
    	if err != nil {
    		log.Fatal(err)
    	}
    
    	fmt.Println(time.Now(), ts)
    }
    
    # => 2019-09-24 19:13:08.429564 +0300 MSK m=+0.009807600 2019-09-24 16:13:08.432757 +0000 UTC
    # => 2019-09-24 19:13:09.436596 +0300 MSK m=+1.016829028 2019-09-24 16:13:08.432757 +0000 UTC
    

    As you can see DB time is always the same.

    Could you please help me to figure out what is going on? Is there any cache? Thank you.

  • Can´t get it to work with Gorm V2.

    Can´t get it to work with Gorm V2.

    Hello.

    I am trying to use txdb in my database integration tests together with Gorm V2, but it´s not going well.

    Here is my test code:

    func TestPgRepository_CreateTodo(t *testing.T) {
    	db := testutil.GetTestDBConnection()
    
    	defer testutil.CloseDb(db)
    
    	repo := todo.NewPgRepository(db)
    
    	td, err := repo.CreateTodo(todo.Todo{
    		Description: "some-todo",
    		CreatedAt: time.Now(),
    	})
    
    	assert.Nil(t, err)
    	assert.NotNil(t, td.ID)
    }
    
    func TestPgRepository_FindAll(t *testing.T) {
    	db := testutil.GetTestDBConnection()
    
    	defer testutil.CloseDb(db)
    
    	repo := todo.NewPgRepository(db)
    
    	// Create test data
    	r := db.Create(&todo.GormTodo{
    		Description: "TODO 1",
    		CreatedAt: time.Now(),
    	})
    
    	fmt.Println(r.Error)
    	result, err := repo.FindAll()
    
    	assert.NotNil(t, err)
    	assert.Len(t, result, 1)
    }
    

    When testing the FindAll method it returns 2 records, so the transaction is not being rollback.

    And here is the code of my GetTestDBConnection

    func GetTestDBConnection() *gorm.DB {
    
    	dbHost := os.Getenv("DB_HOST")
    	dbPort := os.Getenv("DB_PORT")
    	dbUser := os.Getenv("DB_USER")
    	dbPassword := os.Getenv("DB_PASSWORD")
    	dbName := GetTestDBName()
    
    	dsn := fmt.Sprintf("host=%v user=%v password=%v dbname=%v port=%v sslmode=disable",
    		dbHost,
    		dbUser,
    		dbPassword,
    		dbName,
    		dbPort,
    	)
    
    	once.Do(func() {
    		txdb.Register("tx_test", "postgres", dsn)
    	})
    
    	db, err := gorm.Open(gormPG.Open(dsn), &gorm.Config{})
    
    	if err != nil {
    		panic(err)
    	}
    
    	return db
    }
    

    Gorm changed the Open method signature, so now has to receive a Dialect struct instead the driver name. The only link between txdb and GORM in this example is the dsn. not sure if it´s enough.

    I tried to use a persistent connection instead of creating a new one for each est but then I can´t close the connection and thus the transaction are not rollback, otherwise the subsequence tests will complain the connection is closed

    I also tried to register a txdb for each test with a random name to avoid conflicts but it also doesnt seem to work. It should IMO, since each test is opening and closing a connection, so I guess it´s really GORM not using txdb correctly.

    Any ideas?

    Thank you

  • Suggestion: txdb.Close(name string) to close txdb.txDriver.db

    Suggestion: txdb.Close(name string) to close txdb.txDriver.db

    I’ve run into a small problem with txdb because it automatically opens a new connection here: https://github.com/DATA-DOG/go-txdb/blob/6ca798a10f4408c9d8c74527ff93da373e521ebf/db.go#L122-L128

    However, there is no way to call txdb.txDriver.db.Close(), which would be nice because I am spawning temporary test databases that are dropped after the tests are run. Unfortunately, because PostgreSQL doesn’t let you destroy an active database, having something hold the sql.DB connection makes the following SQL fail:

    db.Exec(`DROP DATABASE test_database`) pq: database "test_database" is being accessed by other users

    My suggestion is to create a new function called txdb.Reset:

    var drivers struct {
    	sync.Mutex
    	drvs map[string]*txDriver
    }
    
    func Reset(name string) error {
    	drivers.Lock()
    	defer drivers.Unlock()
    
    	d, ok := drivers.drv[name]
    	if !ok {
    		return ErrNotRegistered
    	}
    
    	if d.db == nil {
    		return nil
    	}
    
    	var err error
    	for _, c := range d.conns {
    		if cerr := c.Close(); cerr != nil && err == nil {
    			err = cerr
    		}
    	}
    	if err != nil {
    		return err
    	}
    
    	if err := d.db.Close(); err != nil {
    		return err
    	}
    
    	d.db = nil
    	return nil
    }
    

    This would also require changes to txdb.Register:

    func Register(name, drv, dsn string, options ...func(*conn) error) {
    	drv := &txDriver{
    		dsn:     dsn,
    		drv:     drv,
    		conns:   make(map[string]*conn),
    		options: options,
    	}
    	sql.Register(name, drv)
    
    	drivers.Lock()
    	defer drivers.Unlock()
    	drivers[name] = drv
    }
    

    Sorry I have not tested or documented this code.

  • Breaks go's sql driver semantics

    Breaks go's sql driver semantics

    https://golang.org/pkg/database/sql/driver/#Driver says that Open's returned connection is only used by one goroutine at a time, but because the txdb Driver returns the same connection multiple times, you can end up in a situation where the same connection is used in multiple concurrent threads of execution.

    The only correct thing to do is to return an error/panic if another connection is opened while the connection is already being used. It points to a programming error, for example an sql.Rows not being closed, or the *sql.DB being used while a transaction has started.

  • If Rollback fails, txdb keeps a bad connection in the pool

    If Rollback fails, txdb keeps a bad connection in the pool

    Not sure this is entirely the best fix just yet, but fixes the problem for us.

    We're seeing intermittent failure in our test suite when using txdb, if one test passes, and shuts down a query using a context - the next text often fails.

    I believe this is because when Close() is called, the Rollback will err (with something like "context canceled").

    Then when we try to open the DB (sql.Open) in the second test, it gets a stale connection and returns driver: bad connection. This change fixes the problem, but I'm not entirely sure why sql.Open will happily return a connection with a different DSN yet.

  • implement driver.RowsColumnTypeDatabaseTypeName for txdb rows and rowSets

    implement driver.RowsColumnTypeDatabaseTypeName for txdb rows and rowSets

    Problem: when txdb is registed and you try to get

    rows, err := ex.QueryContext(
    		ctx,
    		query,
    		queryParameters...,
    	)
    ...
    
    colTypes, err := rows.ColumnTypes()
    

    you will get empty colTypes[i].DatabaseTypeName()

    Resolution: implement interface driver.RowsColumnTypeDatabaseTypeName (from database/sql/driver package) that able to return database type name from the original driver

  • test with gorm return error: Error 1146: Table '*.users' doesn't exist

    test with gorm return error: Error 1146: Table '*.users' doesn't exist

    I'm trying to test gorm with go-txdb, but always get error.

    code

    type User struct {
    	gorm.Model
    	UserId int64  `json:"user_id,omitempty" gorm:"unique_index:uid_name_service_id"`
    	ServiceId   int64  `json:"service_id,omitempty" gorm:"unique_index:uid_name_service_id"`
    	UnitName    string `gorm:"unique_index:uid_name_service_id"`
    }
    
    func TestUserDao_GetUser(t *testing.T) {
    	// dsn serves as an unique identifier for connection pool
    	txdb.Register("txdb", "mysql", "root:123456@/infosec")
    	db, err := sql.Open("txdb", "root:123456@/infosec")
    	if err != nil {
    		log.Fatal(err)
    	}
    	defer db.Close()
    
    	DB, err := gorm.Open("mysql", db)
    	if err != nil {
    		t.Fatal(err)
    	}
    	var u User
    
    	findDb := DB.Model(&User{}).First(&u)
    	err = findDb.Error
    	t.Log(u, err)
    }
    

    log:

    === RUN   TestUserDao_GetUser
        TestUserDao_GetUser: user_test.go:30: {{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC <nil>} 0 0 } Error 1146: Table 'infosec.users' doesn't exist
    
    (/Users/snm/dev/gomod/infosec/model/user_test.go:28) 
    [2020-05-26 17:48:03]  Error 1146: Table 'infosec.users' doesn't exist 
    --- PASS: TestUserDao_GetUser (0.03s)
    PASS
    

    I don't understand why it keeps looking for Table 'infosec.users'.

  • Occasional sql: transaction has already been committed or rolled back errors

    Occasional sql: transaction has already been committed or rolled back errors

    We are seeing transaction has already been committed or rolled back errors if we try to use the database "too quickly" after initializing it, with a cancel context.

    Code looks like this:

    func init() {
    	dbURL := os.Getenv("DATABASE_URL")
    	if dbURL == "" {
    		log.Fatal("You must provide a DATABASE_URL environment variable")
    	}
    
    	txdb.Register("txdb", "postgres", dbURL)
    }
    
    
    func DBWithDefaultContext(db *gorm.DB, fc func(db *gorm.DB) error) error {
    	ctx, cancel := context.WithTimeout(context.Background(), 10 * time.Second)
    	defer cancel()
    	return fc(db.WithContext(ctx))
    }
    
    func Test_example(t *testing.T) {
    
    db, err := sql.Open("txdb", uuid.NewV4().String())
    require.NoError(t, err)
    t.Cleanup(func() { assert.NoError(t, db.Close()) })
    gormDB, err := gorm.Open(postgres.New(postgres.Config{
    	Conn: sqlDB,
    	DSN:  uuid.NewV4().String(),
    }), &gorm.Config{})
    require.NoError(t, err)
    
    err = DBWithDefaultContext(gormDB, func(db *gorm.DB) error {
    	return db.Order("created_at ASC, address ASC").Where("deleted_at IS NULL").Find(&keys).Error
    })
    
    err := db.Exec(`SELECT 1`).Error // ERROR: sql: transaction has already been committed or rolled back
    }
    

    Note that this error about "transaction already committed or rolled back" has nothing to do with postgres (actually the transaction never gets closed). The postgres logs look like this:

    2021-07-15 10:05:52.719 BST [63393] LOG:  statement: ;
    2021-07-15 10:05:52.720 BST [63393] LOG:  statement: BEGIN READ WRITE
    2021-07-15 10:05:52.720 BST [63393] LOG:  statement: SELECT * FROM "keys" WHERE deleted_at IS NULL AND "keys"."deleted_at" IS NULL ORDER BY created_at ASC, address ASC
    // ENDS HERE
    

    It must be a timing issue because it can be resolved by inserting a sleep or gosched here:

    db, err := sql.Open("txdb", uuid.NewV4().String())
    require.NoError(t, err)
    t.Cleanup(func() { assert.NoError(t, db.Close()) })
    runtime.Gosched() // FIXES THE TEST
    // or
    time.Sleep(1 * time.Millisecond) // FIXES THE TEST
    // ... rest of the test code
    

    Note that I was running this test with a parallelism flag of 1 - single threaded mode.

    UPDATE:

    It seems that calling runtime.Gosched() only sometimes fixes it - not always. Calling db.Exec('SELECT 1') in place of that seems to be a more reliable fix.

  • Is there any way to support intended rollback?

    Is there any way to support intended rollback?

    Background

    I am using go-txdb for unit tests in my project, and I expect that I can keep the same status of my database before running each test case. Currently it seems like that go-txdb rollback only when the sql.DB intends to close all driver connections so that it can not meet my requirement.

    I have read the code in this repository but can not find any function or method to solve my problem. Have I missed something? If not, I think I can provide a PR to support this.

    Proposal: manual rollback support

    Besides original rollback strategy, we can add a new method of txdb.txDriver, for example, txDriver.ManualRollback(dsn string), and extract a new method of txdb.conn, for example, conn.Rollback.

    txDriver.ManualRollback is responsible for searching the corresponding conn and then delegate the request to conn.Rollback method.

    In conn.Rollback, it just needs to do the below 2 steps:

    1. Rollback the underlying db connection;
    2. reset the tx field of conn. That is, it does exactly like the partial of the original conn.Close method, see https://github.com/DATA-DOG/go-txdb/blob/8216b38c0107cac8482c3d8a1b672295d162ae6d/db.go#L183-L186

    Besides, we need to make txdb.Register returns the txdb.txDriver instance to the caller so that the latter is possible to call its ManualRollback method.

  • Consider supporting the sql.Connector interface

    Consider supporting the sql.Connector interface

    If this package were to satisfy the driver.Connector interface, it would no longer be necessary to register a driver for every new instance, which could make certain test patterns much simpler.

    I'd be happy to submit a PR if it's likely to be accepted.

Sql mock driver for golang to test database interactions

Sql driver mock for Golang sqlmock is a mock library implementing sql/driver. Which has one and only purpose - to simulate any sql driver behavior in

Dec 31, 2022
Selenium Hub successor running browsers within containers. Scalable, immutable, self hosted Selenium-Grid on any platform with single binary.
Selenium Hub successor running browsers within containers. Scalable, immutable, self hosted Selenium-Grid on any platform with single binary.

Selenoid Selenoid is a powerful implementation of Selenium hub using Docker containers to launch browsers. Features One-command Installation Start bro

Jan 5, 2023
Trade Matching / Transaction System Load Testing Solution

Load Generation System for Trade Matching Systems Operation Users select one of the following options from thew Test Management Portal: Generate a new

Feb 25, 2022
A Devtools driver for web automation and scraping

Overview Documentation | API reference Rod is a high-level driver directly based on DevTools Protocol. It's designed for web automation and scraping.

Dec 30, 2022
This benchmark provides a overview of the different SQLite driver performances available in Go.

SQLite/HTTP Server Performance Benchmark This benchmark provides a overview of the different SQLite driver performances available in Go. For benchmark

Aug 8, 2022
A tool that integrates SQL, HTTP,interface,Redis mock

Mockit 目标:将mock变得简单,让代码维护变得容易 分支介绍 main 主分支,覆盖了单元测试 light 轻分支,去除了单元测试,简化了依赖项,方便其他团队使用 常见Mock难点 不同中间件,mock库设计模式不一致,学习代价高,差异化明显 mock方案强依赖服务端,无法灵活解耦 单元测试

Sep 22, 2022
Fortio load testing library, command line tool, advanced echo server and web UI in go (golang). Allows to specify a set query-per-second load and record latency histograms and other useful stats.
Fortio load testing library, command line tool, advanced echo server and web UI in go (golang). Allows to specify a set query-per-second load and record latency histograms and other useful stats.

Fortio Fortio (Φορτίο) started as, and is, Istio's load testing tool and now graduated to be its own project. Fortio is also used by, among others, Me

Jan 2, 2023
Golang HTTP client testing framework

flute Golang HTTP client testing framework Presentation https://speakerdeck.com/szksh/flute-golang-http-client-testing-framework Overview flute is the

Sep 27, 2022
Extremely flexible golang deep comparison, extends the go testing package and tests HTTP APIs
Extremely flexible golang deep comparison, extends the go testing package and tests HTTP APIs

go-testdeep Extremely flexible golang deep comparison, extends the go testing package. Latest news Synopsis Description Installation Functions Availab

Dec 22, 2022
Cucumber for golang
Cucumber for golang

Godog The API is likely to change a few times before we reach 1.0.0 Please read the full README, you may find it very useful. And do not forget to pee

Jan 7, 2023
Testing API Handler written in Golang.

Gofight API Handler Testing for Golang Web framework. Support Framework Http Handler Golang package http provides HTTP client and server implementatio

Dec 16, 2022
HTTP mock for Golang: record and replay HTTP/HTTPS interactions for offline testing

govcr A Word Of Warning I'm in the process of partly rewriting govcr to offer better support for cassette mutations. This is necessary because when I

Dec 28, 2022
HTTP mocking for Golang

httpmock Easy mocking of http responses from external resources. Install Currently supports Go 1.7 - 1.15. v1 branch has to be used instead of master.

Jan 3, 2023
A test-friendly replacement for golang's time package

timex timex is a test-friendly replacement for the time package. Usage Just replace your time.Now() by a timex.Now() call, etc. Mocking Use timex.Over

Dec 21, 2022
An implementation of failpoints for Golang.
An implementation of failpoints for Golang.

failpoint An implementation of failpoints for Golang. Fail points are used to add code points where errors may be injected in a user controlled fashio

Jan 2, 2023
mockery - A mock code autogenerator for Golang
mockery - A mock code autogenerator for Golang

mockery - A mock code autogenerator for Golang

Jan 8, 2023
Cucumber for golang
Cucumber for golang

Godog The API is likely to change a few times before we reach 1.0.0 Please read the full README, you may find it very useful. And do not forget to pee

Jan 4, 2023
Go (Golang) Fake Data Generator for Struct
Go (Golang)  Fake Data  Generator for Struct

Docs faker Struct Data Fake Generator Faker will generate you a fake data based on your Struct. Index Support Getting Started Example Limitation Contr

Dec 22, 2022
Check your internet speed right from your terminal. Built on GOlang using chromedp
Check your internet speed right from your terminal. Built on GOlang using chromedp

adhocore/fast A GO lang command line tool to check internet speed right from the terminal. Uses fast.com through headless chrome. Prerequistie Chrome

Dec 26, 2022