Generator client to database on Golang interface based

    Generator client to database on Golang based interface.



    To work with databases, Golang offers a package database/sqlthat is an abstraction over the software interface of a relational database. On the one hand, the package includes powerful functionality for managing a pool of connections, working with prepared statements, transactions, a database query interface. On the other hand, you have to write a considerable amount of the same type of code in a web application to interact with the database. The go-gad / sal library offers a solution in the form of generating the same type of code based on the described interface.


    Motivation


    Today, there are a sufficient number of libraries that offer solutions in the form of ORMs, helpers for building queries, generating helpers based on the database schema.



    When I switched to the Golang language a few years ago, I already had experience working with databases in different languages. Using ORM, for example, ActiveRecord, and without. Having gone from love to hate, having no problems with writing a few extra lines of code, interaction with the database in Golang came to something like a repository pattern. We describe the interface with the database, we implement using standard db.Query, row.Scan. To use additional wrappers simply did not make sense, it was opaque, forced to be on the alert.


    The SQL language itself is already an abstraction between your program and the data in the repository. It always seemed illogical to me to try to describe a data scheme, and then build complex queries. The structure of the response in this case differs from the data scheme. It turns out that the contract should not be described at the data schema level, but at the request and response level. We use this approach in web development when we describe the data structures of requests and API responses. When accessing the service via RESTful JSON or gRPC, we declare the contract at the request and response level using JSON Schema or Protobuf, not the entity data schema within the services.


    That is, the interaction with the database has been reduced to a similar method:


    type User struct {
    ID   int64
    Name string
}
type Store interface {
    FindUser(id int64) (*User, error)
}
type Postgres struct {
    DB *sql.DB
}
func(pg *Postgres)FindUser(id int64)(*User, error) {
    var resp User
    err := pg.DB.QueryRow("SELECT id, name FROM users WHERE id=$1", id).Scan(&resp.ID, &resp.Name)
    if err != nil {
        returnnil, err
    }
    return &resp, nil
}
funcHanlderFindUser(s Store, id int)(*User, error) {
    // logic of service object
    user, err := s.FindUser(id)
    //...
}

    This method makes your program predictable. But let's be honest, this is not a poet's dream. We want to reduce the number of template code to compose a query, fill data structures, use variable binding, and so on. I tried to formulate a list of requirements that the desired set of utilities should satisfy.


    Requirements


    • Description of interaction in the form of an interface.
    • The interface is described by methods and messages of requests and responses.
    • Support for linking variables and prepared expressions (prepared statements).
    • Named argument support.
    • Associate a database response with message data structure fields.
    • Support for atypical data structures (array, json).
    • Transparent work with transactions.
    • Built-in support for intermediate processors (middleware).

    Implementation of interaction with the database, we want to abstract using the interface. This will allow us to implement something similar to such a design pattern as a repository. In the example above, we described the Store interface. Now we can use it as an addiction. At the testing stage, we can pass the stub object generated on the basis of this interface, and in production we will use our implementation based on the Postgres structure.


    Each interface method describes one database request. Input and output parameters of the method should be part of the contract for the request. The query string must be able to format depending on the input parameters. This is especially true when compiling queries with a complex sample condition.


    When making a query, we want to use substitutions and variable bindings. For example, in PostgreSQL, instead of a value, you write $1, and together with the query you pass an array of arguments. The first argument will be used as the value in the converted query. Support for prepared expressions will allow you not to worry about organizing the storage of these expressions. The database / sql library provides a powerful tool for the support of prepared expressions, it takes care of the connection pool, closed connections. But on the part of the user, it is necessary to take an additional action to reuse the prepared expression in the transaction.


    Databases, such as PostgreSQL and MySQL, use different syntax for using substitution and variable bindings. PostgreSQL uses the format $1, $2... MySQL uses ?regardless of the location of the value. The database / sql library offered a universal format of named arguments https://golang.org/pkg/database/sql/#NamedArg . Usage example:


    db.ExecContext(ctx, `DELETE FROM orders WHERE created_at < @end`, sql.Named("end", endTime))

    Support for this format is preferable to use compared to PostgreSQL or MySQL solutions.


    The answer from the database that processes the software driver can be represented as follows:


    dev > SELECT * FROM rubrics;
 id |       created_at        | title | url
----+-------------------------+-------+------------
  1 | 2012-03-13 11:17:23.609 | Tech  | technology
  2 | 2015-07-21 18:05:43.412 | Style | fashion
(2 rows)

    From the user's point of view, at the interface level, it is convenient to describe the output parameter as an array of structures of the form:


    type GetRubricsResp struct {
    ID        int
    CreatedAt time.Time
    Title     string
    URL       string
}

    Next, project the value idonto resp.IDand so on. In general, this functionality covers most needs.


    When declaring messages through internal data structures, the question arises about how to support non-standard data types. For example an array. If you use the github.com/lib/pq driver when working with PostgreSQL, you can use auxiliary functions like when passing arguments to the query or scanning the response pq.Array(&x). Example from documentation:


    db.Query(`SELECT * FROM t WHERE id = ANY($1)`, pq.Array([]int{235, 401}))
var x []sql.NullInt64
db.QueryRow('SELECT ARRAY[235, 401]').Scan(pq.Array(&x))

    Accordingly, there should be ways to prepare data structures.


    When executing any of the interface methods, a connection to the database can be used as an object *sql.DB. If it is necessary to execute several methods within one transaction, I would like to use transparent functionality with a similar approach of working outside the transaction, not to pass additional arguments.


    When working with interface implementations, it is vital for us to be able to embed the toolkit. For example, logging all requests. The toolkit must access the request variables, response error, runtime, interface method name.


    For the most part, requirements were formulated as systematization of scenarios for working with a database.


    Solution: go-gad / sal


    One way to deal with template code is to generate it. Fortunately, Golang has tools and examples for this https://blog.golang.org/generate . The GoMock approach https://github.com/golang/mock was taken as an architectural solution for generation , where the analysis of the interface is carried out using reflection. Based on this approach, according to the requirements, the salgen utility and the sal library were written, which generate interface implementation code and provide a set of auxiliary functions.


    In order to start using this solution, it is necessary to describe the interface that describes the behavior of the interaction layer with the database. Specify a directive go:generatewith a set of arguments and start the generation. A constructor and a bunch of sample code will be received, ready to use.


    package repo
import"context"//go:generate salgen -destination=./postgres_client.go -package=dev/taxi/repo dev/taxi/repo Postgrestype Postgres interface {
    CreateDriver(ctx context.Context, r *CreateDriverReq) error
}
type CreateDriverReq struct {
    taxi.Driver
}
func(r *CreateDriverReq)Query()string {
    return`INSERT INTO drivers(id, name) VALUES(@id, @name)`
}

    Interface


    It all starts with the declaration of the interface and a special command for the utility go generate:


    //go:generate salgen -destination=./client.go -package=github.com/go-gad/sal/examples/profile/storage github.com/go-gad/sal/examples/profile/storage Storetype Store interface {
    ...

    Here it is described that for our interface Storefrom the package a console utility will be called salgen, with two options and two arguments. The first option -destinationdetermines in which file the generated code will be written. The second option -packagedefines the full path (import path) of the library for the generated implementation. The following are two arguments. The first one describes the complete package path ( github.com/go-gad/sal/examples/profile/storage) where the interface is located; the second one indicates the interface name itself. Note that the command for go generatecan be located anywhere, not necessarily next to the target interface.


    After executing the command, go generatewe will get a constructor, whose name is constructed by adding a prefix Newto the interface name. The constructor accepts a required parameter corresponding to the interface sal.QueryHandler:


    type QueryHandler interface {
    QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
    ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
    PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

    This interface corresponds to the object *sql.DB.


    connStr := "user=pqgotest dbname=pqgotest sslmode=verify-full"
db, err := sql.Open("postgres", connStr)
client := storage.NewStore(db)

    Methods


    Interface methods define a set of available database queries. 


    type Store interface {
    CreateAuthor(ctx context.Context, req CreateAuthorReq) (CreateAuthorResp, error)
    GetAuthors(ctx context.Context, req GetAuthorsReq) ([]*GetAuthorsResp, error)
    UpdateAuthor(ctx context.Context, req *UpdateAuthorReq) error
}

    • The number of arguments is always strictly two.
    • The first argument is the context.
    • The second argument contains the data to bind the variables and defines the query string.
    • The first output parameter can be an object, an array of objects, or missing.
    • The last output parameter is always an error.

    The first argument is always expected object context.Context. This context will be passed on to the database and toolkit calls. The second argument expects a parameter with a base type struct(or a pointer to struct). The parameter must satisfy the following interface:


    type Queryer interface {
    Query() string
}

    The method Query()will be called before performing a database query. The resulting string will be converted to a database specific format. That is, PostgreSQL @endwill be replaced by $1, and the value will be passed to the array of arguments&req.End


    Depending on the output parameters, it is determined which of the methods (Query / Exec) will be called:


    • If the first parameter with the base type struct(or pointer to struct), the method will be called QueryContext. If the response from the database does not contain any lines, then an error will be returned sql.ErrNoRows. That is, the behavior is similar to db.QueryRow.
    • If the first parameter with the base type slice, the method will be called QueryContext. If the response from the database does not contain rows, then an empty list will be returned. The base type of the list item must be stuct(or a pointer to struct).
    • If the output parameter is one, with type error, the method will be called ExecContext.

    Prepared statements


    The generated code supports prepared expressions. Prepared expressions are cached. After the first preparation of the expression, it is placed in the cache. The database / sql library itself ensures that prepared expressions are transparently applied to the desired connection to the database, including the processing of closed connections. In turn, the library go-gad/salcares about reusing the prepared expression in the context of a transaction. When the prepared expression is executed, the arguments are passed using variable binding, transparently to the developer.


    To support named arguments on the library side, the go-gad/salquery is converted to a form suitable for a database. Now there is a conversion support for PostgreSQL. The field names of the query object are used for substitutions in named arguments. To specify a different name instead of the object field name, you must use a tag sqlfor the structure fields. Consider an example:


    type DeleteOrdersRequest struct {
    UserID   int64`sql:"user_id"`
    CreateAt time.Time `sql:"created_at"`
}
func(r * DeleteOrdersRequest)Query()string {
    return`DELETE FROM orders WHERE user_id=@user_id AND created_at<@end`
}

    The query string will be converted, and the list will be passed to the query execution arguments using the matching table and variable binding:


    // generated code:
db.Query("DELETE FROM orders WHERE user_id=$1 AND created_at<$2", &req.UserID, &req.CreatedAt)

    Map structs to request arguments and response messages


    The library go-gad/salcares about linking database response lines with response structures, table columns with structure fields:


    type GetRubricsReq struct {}
func(r GetRubricReq)Query()string {
    return`SELECT * FROM rubrics`
}
type Rubric struct {
    ID       int64`sql:"id"`
    CreateAt time.Time `sql:"created_at"`
    Title    string`sql:"title"`
}
type GetRubricsResp []*Rubric
type Store interface {
    GetRubrics(ctx context.Context, req GetRubricsReq) (GetRubricsResp, error)
}

    And if the database response is:


    dev > SELECT * FROM rubrics;
 id |       created_at        | title 
----+-------------------------+-------
  1 | 2012-03-13 11:17:23.609 | Tech  
  2 | 2015-07-21 18:05:43.412 | Style 
(2 rows)

    Then the GetRubricsResp list will return to us, the elements of which will be pointers to Rubric, where the fields are filled with values ​​from the columns that correspond to the names of the tags.


    If the database response contains columns with the same name, the corresponding structure fields will be selected in the order of declaration.


    dev > select * from rubrics, subrubrics;
 id | title | id |  title
----+-------+----+----------
  1 | Tech  |  3 | Politics

    type Rubric struct {
    ID    int64`sql:"id"`
    Title string`sql:"title"`
}
type Subrubric struct {
    ID    int64`sql:"id"`
    Title string`sql:"title"`
}
type GetCategoryResp struct {
    Rubric
    Subrubric
}

    Non-standard data types


    The package database/sqlprovides support for basic data types (strings, numbers). In order to process such data types as an array or json in a request or response, it is necessary to support interfaces driver.Valuerand sql.Scanner. In various implementations of drivers there are special auxiliary functions. For example lib/pq.Array( https://godoc.org/github.com/lib/pq#Array ):


    funcArray(a interface{})interface {
    driver.Valuer
    sql.Scanner
}

    The default library go-gad/sqlfor fields of the structure of the form


    type DeleteAuthrosReq struct {
    Tags []int64`sql:"tags"`
}

    will use the value &req.Tags. If the structure satisfies the interface sal.ProcessRower,


    type ProcessRower interface {
    ProcessRow(rowMap RowMap)
}

    then the value used can be adjusted


    func(r *DeleteAuthorsReq)ProcessRow(rowMap sal.RowMap) {
    rowMap.Set("tags", pq.Array(r.Tags))
}
func(r *DeleteAuthorsReq)Query()string {
    return`DELETE FROM authors WHERE tags=ANY(@tags::UUID[])`
}

    This handler can be used for request and response arguments. In the case of a list in the response, the method must belong to the list item.


    Transactions


    To support transactions, the interface (Store) must be extended with the following methods:


    type Store interface {
    BeginTx(ctx context.Context, opts *sql.TxOptions) (Store, error)
    sal.Txer
    ...

    The implementation of the methods will be generated. The method BeginTxuses a connection from the current object sal.QueryHandlerand opens a transaction db.BeginTx(...); returns a new interface implementation object Store, but uses the resulting object as a handle*sql.Tx


    Middleware


    Hooks are provided for embedding tools.


    type BeforeQueryFunc func(ctx context.Context, query string, req interface{})(context.Context, FinalizerFunc)typeFinalizerFuncfunc(ctx context.Context, err error)

    The hook BeforeQueryFuncwill be called before executing db.PrepareContextor db.Query. That is, at the start of the program, when the cache of prepared expressions is empty, when called store.GetAuthors, the hook BeforeQueryFuncwill be called twice. A hook BeforeQueryFunccan return a hook FinalizerFuncthat will be called before leaving the user method, in our case store.GetAuthors, with defer.


    When the hooks are executed, the context is filled with service keys with the following values:


    • ctx.Value(sal.ContextKeyTxOpened) a boolean value determines whether the method is called in the context of a transaction or not.
    • ctx.Value(sal.ContextKeyOperationType), String-type surgery, "QueryRow", "Query", "Exec", "Commit"etc.
    • ctx.Value(sal.ContextKeyMethodName)the string value of the interface method, for example "GetAuthors".

    As arguments, the hook BeforeQueryFunctakes the sql string of the query and the argument of reqthe user query method. The hook FinalizerFunctakes a variable as an argument err.


    beforeHook := func(ctx context.Context, query string, req interface{})(context.Context, sal.FinalizerFunc) {
    start := time.Now()
    return ctx, func(ctx context.Context, err error) {
        log.Printf(
            "%q > Opeartion %q: %q with req %#v took [%v] inTx[%v] Error: %+v",
            ctx.Value(sal.ContextKeyMethodName),
            ctx.Value(sal.ContextKeyOperationType),
            query,
            req,
            time.Since(start),
            ctx.Value(sal.ContextKeyTxOpened),
            err,
        )
    }
}
client := NewStore(db, sal.BeforeQuery(beforeHook))

    Examples of output:


    "CreateAuthor" > Opeartion "Prepare": "INSERT INTO authors (Name, Desc, CreatedAt) VALUES($1, $2, now()) RETURNING ID, CreatedAt" with req <nil> took [50.819µs] inTx[false] Error: <nil>
"CreateAuthor" > Opeartion "QueryRow": "INSERT INTO authors (Name, Desc, CreatedAt) VALUES(@Name, @Desc, now()) RETURNING ID, CreatedAt" with req bookstore.CreateAuthorReq{BaseAuthor:bookstore.BaseAuthor{Name:"foo", Desc:"Bar"}} took [150.994µs] inTx[false] Error: <nil>

    What's next


    • Support for binding variables and prepared expressions for MySQL.
    • Hook RowAppender to adjust the response.
    • Return value Exec.Result.
    Tags:

    Also popular now: