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

• Reduced connectivity of the database application
• Logging requests in debug mode
• Work with replicas

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

Area of ​​visibility

As a rule, large systems try to do weakly connected with a clear area of ​​responsibility of each component of the system. Therefore, publisher / subscriber type 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. For simplicity, the handler is represented by a single function (in real life it is more and 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 had to change the event signature. This solution is not clean and implies that handlers have knowledge of the context of the execution of a database request. A significantly cleaner solution would be to use a generic database interface and transaction - 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 involve 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 allows us to simplify the method signatures and increase the readability of the code. In our case, however, this cannot be avoided, since a reference to the transaction is required.
A fairly elegant solution is to put the reference to the transaction (scope) in the context, because the context is positioned as a pass-through parameter. Then we can still simplify our code:

 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) } } 

From this example, it is clear that we have retained the complete isolation of the managers, increased the readability of the code and achieved their joint work in a single field of view.

Replication support

The library also supports the use of replications. All exec type requests are sent to the master. Requests of the Slave type are transmitted to the randomly selected Slave. To support replication, it is enough to 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() ... } 

In the case of using a single data source when opening a database, it will be opened normally, without an additional overhead.

Metrics

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

Profiling and Logging Queries

Very part is necessary during debugging to log requests to the database. However, I have not met a quality logging mechanism with a zero overhead in production. The library allows you to elegantly solve this problem by wrapping the database. To profile a database, it is enough to transfer the corresponding 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 coherent and transparent, despite the increasing complexity of the application.