Unit Testing Postgres db connection golang

Question

I am expected to have 80% test coverage even for pushing the basic project structure. I am a bit confused how do I write unit tests for the following code to Connect to postgres db and ping postgres for health check. Can someone help me please.

var postgres *sql.DB

// ConnectToPostgres func to connect to postgres
func ConnectToPostgres(connStr string) (*sql.DB, error) {

    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Println("postgres-client ", err)
        return nil, err

    }
    postgres = db
    return db, nil

}

// PostgresHealthCheck to ping database and check for errors
func PostgresHealthCheck() error {
    if err := postgres.Ping(); err != nil {
        return err
    }
    return nil
}

type PostgresRepo struct {
    db *sql.DB
}

// NewPostgresRepo constructor

func NewPostgresRepo(database *sql.DB) *PostgresRepo {
    return &PostgresRepo{
        db: database,
    }
}

Matias Barrios · Accepted Answer · 2020-12-07 19:11:57Z

You need to use this : https://github.com/DATA-DOG/go-sqlmock

Its very easy to use. Here is an example where a controller is getting tested using a mocked SQL :

Implementation

func (up UserProvider) GetUsers() ([]models.User, error) {
    var users = make([]models.User, 0, 10)
    rows, err := up.DatabaseProvider.Query("SELECT firstname, lastname, email, age FROM Users;")
    if err != nil {
        return nil, err
    }
    for rows.Next() {
        var u models.User = models.User{}
        err := rows.Scan(&u.Name, &u.Lastname, &u.Email, &u.Age)
        if err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return users, nil
}

Test

func TestGetUsersOk(t *testing.T) {
    db, mock := NewMock()
    mock.ExpectQuery("SELECT firstname, lastname, email, age FROM Users;").
        WillReturnRows(sqlmock.NewRows([]string{"firstname", "lastname", "email", "age"}).
            AddRow("pepe", "guerra", "[email protected]", 34))
    subject := UserProvider{
        DatabaseProvider: repositories.NewMockDBProvider(db, nil),
    }
    resp, err := subject.GetUsers()

    assert.Nil(t, err)
    assert.NotNil(t, resp)
    assert.Equal(t, 1, len(resp))
}

func NewMock() (*sql.DB, sqlmock.Sqlmock) {
    db, mock, err := sqlmock.New()
    if err != nil {
        log.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
    }

    return db, mock
}

Dylan Trotter · Accepted Answer · 2022-05-19 16:47:47Z

1

I find that writing tests against a live database makes for more high quality tests. The challenge with Postgres is that there's no good in-memory fake that you can substitute in.

What I came up with is standing up the postgres Docker container and creating temporary databases in there. The PostgresContainer type in the github.com/bitcomplete/sqltestutil package does exactly this:

# Postgres version is "12"
pg, _ := sqltestutil.StartPostgresContainer(context.Background(), "12")
defer pg.Shutdown(ctx)
db, err := sql.Open("postgres", pg.ConnectionString())
// ... execute SQL

Per the docs, it's a good idea to set up your tests so that the container is only started once, as it can take a few seconds to start up (more if the image needs to be downloaded). It suggests some approaches for mitigating that problem.

answered May 19, 2022 at 16:47

Dylan Trotter

113 bronze badges

1 Comment

havish Over a year ago

> The challenge with Postgres is that there's no good in-memory fake that you can substitute in. I think you can here's how, postgresqlco.nf/doc/en/param/fsync. While running your tests you just set fsync=off and it gives you in-memory fake behaviour by not writing on to the disk.

Collectives™ on Stack Overflow

Unit Testing Postgres db connection golang

2 Answers 2

Implementation

Test

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Implementation

Test

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related