This document consists of review Golang libraries for communication with SQL database, as well as how to handle testing and database migration.
Three of the most popular libraries were evaluated:
- sqlx + Squirrel
- beego
- gorm
- Beego does not work correctly with Postgres, issue
- Odd filtering support:
qs.Filter("name__icontains", "slene")
- Very limited support for transactions:
err = d.ormer.Begin()
In contrast to other libraries, starting transaction does not create an explicit transaction object. If a developer forgets to commit or rollback transaction, it can interfere with another transaction.
- According to their documentation:
This framework is still under development so compatibility is not guaranteed.
- GORM has a lot of helper functions, such as:
- total count query
db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
- rollback transaction if needed:
tx.RollbackUnlessCommitted()
- GORM seems to be very error-prone, see those 2 quotes from their documentation:
NOTE When query with struct, GORM will only query with those fields has non-zero value, that means if your field’s value is 0, '', false or other zero values:
// it won’t be used to build query conditions, for example:
//db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
////// SELECT * FROM users WHERE name = "jinzhu";
WARNING When deleting a record, you need to ensure its primary field has value, and GORM will use the primary key to delete the record, if the primary key field is blank, GORM will delete all records for the model
- GORM uses unusual error handling, IMO overlooking errors can be more frequent with that approach:
if err := d.db.Limit(p.PageSize).Order("id").Find(&apps).Error; err != nil {
return nil, err
}
- Some people claim that GORM is not performant and too complex.
- Helper functions are very similar to the standard library
- Good documentation
- For building SQL Queries, squirrel can be used.
selBuilder := sq.Select("*").From("applications").OrderBy("id").Limit(uint64(p.PageSize))
str, args, err := selBuilder.ToSql()
if err != nil {
return nil, err
}
err = d.db.Select(&apps, str, args...)
- Sqlx or Squirrel don't use Go tags, so insert commands have to be created manually:
_, err = txx.NamedExecContext(ctx, "insert into applications(id,tenant,name,description,labels) values (:id, :tenant, :name, :description, :labels)", app)
No one library provides support for JSON queries.
Sqlx and Squirrel is our first-choice library because of its simplicity and explicitly. Before any developer starts working with DB, he should familiarize with these two excellent documents:
For mocking interactions with DB, we can use go-sqlmock
// a successful case
func TestShouldUpdateStats(t *testing.T) {
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
}
defer db.Close()
mock.ExpectBegin()
mock.ExpectExec("UPDATE products").WillReturnResult(sqlmock.NewResult(1, 1))
mock.ExpectExec("INSERT INTO product_viewers").WithArgs(2, 3).WillReturnResult(sqlmock.NewResult(1, 1))
mock.ExpectCommit()
// now we execute our method
if err = recordStats(db, 2, 3); err != nil {
t.Errorf("error was not expected while updating stats: %s", err)
}
// we make sure that all expectations were met
if err := mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}
}
For pre-populating DB with test data, we can use testfixtures or polluter. For code samples, see this article.
Schema updated can be performed using helm hooks pre-upgrade
and pre-install
.
For performing a migration, there are 2 interesting projects written in Go:
-
Golang-migrate with 2301 stars on Github
- for every migration, 2 files are created:
up
anddown
- supports only SQL
- creates additional table:
schema_migrations
- for every migration, 2 files are created:
-
Goose with 887 stars on Github
- creates only one file per migration, to distinguish
up
anddown
SQL statements, comments are used - supports SQL and Go binaries
- creates additional table:
goose_db_version
- creates only one file per migration, to distinguish
Go-migrate seems to be more popular, is easier (use file names, instead of custom comments). It does not support Go binaries but at the moment I don't see the advantages of them over plain SQL, so I suggest to use golang-migrate
.