In this article I want to summarize the problems of working with databases running golang. When solving simple problems, these problems are usually not visible. As the project grows, so does the problem. The most topical of them:

• Decreasing connectivity of a database application
• Debug mode query logging
• Work with replicas

The article is based on the github.com/adverax/echo/database/sql package. The semantics of using this package are as close as possible to the standard database / sql package, so I don’t think that anyone will have problems using it.

Area of ​​visibility

As a rule, large systems try to make loosely connected with a clear area of ​​responsibility of each component of the system. Therefore, publisher / subscriber design patterns are widely practiced. Consider a small example of registering a new user in the system.

package main
import "database/sql"
type User struct {
    Id       int64
    Name     string
    Language string
}
type Manager struct {
    DB       *sql.DB
    OnSignup func(db *sql.DB, user *User) error
}
func (m *Manager) Signup(user *User) (id int64, err error) {
    id, err = m.insert(user)
    if err != nil {
        return
    }
    user.Id = id
    err = m.OnSignup(m.DB, user)
    return
}
func (m *Manager) insert(user *User) (int64, error) {
    res, err := m.DB.Exec("INSERT ...")
    if err != nil {
        return 0, err
    }
    id, err := res.LastInsertId()
    if err != nil {
        return 0, err
    }
    return id, err
}
func main() {
    manager := &Manager{
        // ...
        OnSignup: func(db *sql.DB, user *User) error {
        },
    }
    err := manager.Signup(&User{...})
    if err != nil {
        panic(err)
    }
}

In this example, we are primarily interested in the OnSignup event. To simplify, the handler is represented by a single function (in real life, everything is more complicated). In the event signature, we rigidly prescribe the type of the first parameter, which usually has far-reaching consequences.
Suppose that now we want to expand the functionality of our application and in case of successful user registration send a message to his personal account. Ideally, the message should be placed in the same transaction as the user registration.

type Manager struct {
    DB       *sql.DB
    OnSignup func(tx *sql.Tx, user *User) error
}
func (m *Manager) Signup(user *User) error {
    tx, err := m.DB.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()
    id, err := m.insert(user)
    if err != nil {
        return err
    }
    user.Id = id
    err = m.OnSignup(tx, id)
    if err != nil {
        return err
    }
    return tx.Commit()
}
func main() {
    manager := &Manager{
        // ...
        OnSignup: func(db *sql.Tx, user *User) error {
        },
    }
    err := manager.Signup(&User{...})
    if err != nil {
        panic(err)
    }
}

As you can see from the example, we were forced to change the signature of the event. This solution is not clean and implies that the handlers have knowledge of the context of the execution of the database query. A much cleaner solution would be to use a generic database and transaction interface — scope.

import "github.com/adverax/echo/database/sql"
type Manager struct {
    DB       sql.DB
    OnSignup func(scope sql.Scope, user *User) error
}
func (m *Manager) Signup(user *User) error {
    tx, err := m.DB.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()
    id, err := m.insert(user)
    if err != nil {
        return err
    }
    err = m.OnSignup(tx, id)
    if err != nil {
        return err
    }
    return tx.Commit()
}
func main() {
    manager := &Manager{
        // ...
        OnSignup: func(scope sql.Scope, user *User) error {
        },
    }
    err := manager.Signup(&User{...})
    if err != nil {
        panic(err)
    }
}

To implement this approach, we will need support for nested transactions, since the handler, in turn, can use transactions. Fortunately, this is not a problem, since most DBMSs support the SAVEPOINT mechanism.

Database and Context

In normal practice, the connection to the database is not passed as a parameter, as shown above, and each manager keeps a link to the connection to the database. This simplifies method signatures and improves code readability. In our case, it is impossible to avoid this, because you need to transfer a link to the transaction.
A rather elegant solution is to put the link to the transaction (scope) in the context, because the context is positioned as an end-to-end parameter. Then we can simplify our code further:

import (
    "context"
    "github.com/adverax/echo/database/sql"
)
type Manager struct {
    sql.Repository
    OnSignup func(ctx context.Context, user *User) error
}
func (m *Manager) Signup(ctx context.Context, user *User) error {
    return m.Transaction(
        ctx, func(ctx context.Context, scope sql.Scope) error {
            id, err := m.insert(user)
            if err != nil {
                return err
            }
            user.Id = id
            return m.OnSignup(ctx, user)
        },
    )
}
type Messenger struct {
    sql.Repository
}
func(messenger *Messenger) onSignupUser(ctx context.Context, user *User) error {
    _, err := messenger.Scope(ctx).Exec("INSERT ...")
    return err
}
func main() {
    db := ...
    messenger := &Messenger{
        Repository: sql.NewRepository(db),
    }
    manager := &Manager{
        Repository: sql.NewRepository(db),
        OnSignup:   messenger.onSignup,
    }
    err := manager.Signup(&User{...})
    if err != nil {
        panic(err)
    }
}

This example shows that we have maintained the complete isolation of managers, increased the readability of the code, and achieved their joint work in a single scope.

Replication support

The library also supports the use of replication. All requests of type Exec are sent to Master. Requests of the Slave type are transferred to a randomly selected Slave. To support replication, just specify several data sources:

func work() {
  dsc := &sql.DSC{
    Driver: "mysql",
    DSN: []*sql.DSN{
      {
        Host: "127.0.0.1",
        Database: "echo",
        Username: "root",
        Password: "password",
      },
      {
        Host: "192.168.44.01",
        Database: "echo",
        Username: "root",
        Password: "password",
      },
    },
  }
  db := dsc.Open(nil)
  defer db.Close()
  ...
}

If you use a single data source when opening a database, it will be opened in normal mode without additional overhead.

Metrics

As you know, metrics are cheap, and logs are expensive. Therefore, it was decided to add support for the default metrics.

Query Profiling and Logging

It is very necessary to log database queries during debugging. However, I have not seen a high-quality logging mechanism with zero overhead in production. The library allows you to elegantly solve this problem by wrapping the database. To profile the database, just pass the appropriate activator to it:

func openDatabase(dsc sql.DSC, debug bool) (sql.DB, error){
    if debug {
        return dsc.Open(sql.OpenWithProfiler(nil, "", nil))
    }
    return dsc.Open(nil)
}
func main() {
    dsc := ...
    db, err := openDatabase(dsc, true)
    if err != nil {
        panic(err)
    }
    defer db.Close()
    ...
}

Conclusion

The proposed package allows you to expand the possibilities of interaction with the database, while hiding unnecessary details. This allows you to improve the quality of the code, leaving it loosely connected and transparent, despite the increasing complexity of the application.