Geekly Articles each Day
⬆️ ⬇️

Generator client to database on Golang interface based

Generator client to database on Golang based interface.





To work with databases, Golang offers the database/sql package, which 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, and generating helpers based on the database schema.





When I switched to Golang several 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, and 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 { return nil, err } return &resp, nil } func HanlderFindUser(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





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 along 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 id value to resp.ID and 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 pq.Array(&x) when passing arguments to the query or scanning the response. 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 may 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 the go:generate directive with 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 Postgres type 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 declaring the interface and a special command for the go generate utility:



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


Here it is described that for our interface Store from the package, the console utility salgen will be called, with two options and two arguments. The first option -destination determines in which file the generated code will be written. The second option -package defines 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 generate can be located anywhere, not necessarily close to the target interface.



After the go generate command is executed, we will get a constructor, whose name is constructed by adding the prefix New to the interface name. The constructor takes a required parameter corresponding to the sal.QueryHandler interface:



 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 first argument always expects a context.Context object. This context will be passed on to the database and toolkit calls. The second argument expects a parameter with a base type of struct (or a pointer to a struct ). The parameter must satisfy the following interface:



 type Queryer interface { Query() string }


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



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





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 go-gad/sal library takes care of 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 go-gad/sal side of the library, the query is converted to a form suitable for the 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 the sql tag for 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 go-gad/sal library takes care of 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 tag names.



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 database/sql package provides support for basic data types (strings, numbers). In order to handle such data types as an array or json in a request or response, you must support the driver.Valuer and 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 ):



 func Array(a interface{}) interface { driver.Valuer sql.Scanner }


The default go-gad/sql library for view structure fields



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


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



 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 BeginTx method uses the connection from the current sal.QueryHandler object and opens the transaction db.BeginTx(...) ; returns a new implementation object for the Store interface, but uses the resulting *sql.Tx object as a *sql.Tx



Middleware



Hooks are provided for embedding tools.



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


The BeforeQueryFunc hook will be called before db.PrepareContext or db.Query . That is, at the start of the program, when the cache of prepared expressions is empty, when you call store.GetAuthors , the BeforeQueryFunc hook will be called twice. The BeforeQueryFunc hook can return the FinalizerFunc hook, which will be called before leaving the custom method, in our case store.GetAuthors , using defer .



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





As arguments, the BeforeQueryFunc hook takes the sql string of the query and the argument req the user query method. The FinalizerFunc hook accepts the err variable as an argument.



 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





')

Source: https://habr.com/ru/post/431984/

More articles:



All Articles