Generator client to database on Golang based on

 
3r3-31. 3r33838. Client Generator 3r3631. to the database at Golang based interface. 3r33831. 3r33847.  
3r33838. Generator client to database on Golang based on 3r33831. 3r33847.  
3r33838. To work with databases Golang offers package 3r33858. database /sql which is an abstraction over the relational database software interface. 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. 3r33831.
3r3633. 3r33847.  
Motivation
3r33847.  
3r33838. 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. 3r33831. 3r33847.  
3r33849.  
3r33857.
https://github.com/jmoiron/sqlx 3r33860.  
3r33857. https://github.com/go-reform/reform 3r33860.  
3r33857. 3r3342. https://github.com/jinzhu/gorm
3r33860.  
3r33857. https://github.com/Masterminds/squirrel 3r33860.  
3r33857. https://github.com/volatiletech/sqlboiler 3r33860.  
3r33857. https://github.com/drone/sqlgen 3r33860.  
3r33857. 3r362. https://github.com/gocraft/dbr
3r33860.  
3r33857. https://github.com/go-gorp/gorp 3r33860.  
3r33857. https://github.com/doug-martin/goqu 3r33860.  
3r33857. https://github.com/src-d/go-kallax 3r33860.  
3r33857. https://github.com/go-pg/pg 3r33860.  
3r33862. 3r33847.  
3r33838. 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. 3r33831. 3r33847.  
3r33838. 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 be described not at the data schema level, but at the request and response level. We use this approach in web development when describing API request and response data structures. 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. 3r33831. 3r33847.  
3r33838. That is, the interaction with the database was reduced to a similar method:
3r33847.  
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)
//3r3747.}
3r33842. 3r33847.  
3r33838. 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. 3r33831. 3r33847.  
Requirements
3r33847.  
3r33849.  
3r33857. Description of interaction in the form of an interface. 3r33860.  
3r33857. The interface is described by methods and messages of requests and responses. 3r33860.  
3r33857. Support for linking variables and prepared expressions (prepared statements). 3r33860.  
3r33857. Named argument support. 3r33860.  
3r33857. Associate a database response with message data structure fields. 3r33860.  
3r33857. Support for atypical data structures (array, json). 3r33860.  
3r33857. Transparent work with transactions. 3r33860.  
3r33857. Built-in support for intermediate processors (middleware). 3r33860.  
3r33862. 3r33847.  
3r33838. 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. 3r33831. 3r33847.  
3r33838. 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. 3r33831. 3r33847.  
3r33838. When making a query, we want to use substitutions and variable bindings. For example, in PostgreSQL, instead of a value, you write 3r33858. $ 1 r3r3859. , and with the request, 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. 3r33831. 3r33847.  
3r33838. Databases, such as PostgreSQL and MySQL, use different syntax for using substitution and variable bindings. PostgreSQL uses the format 3r33858. $ 1 r3r3859. , 3r33858. $ 2 3r33859. , MySQL uses 3r33858. ? 3r33859. regardless of the location of the value. The database /sql library has proposed a generic format for named arguments 3r3191. https://golang.org/pkg/database/sql/#NamedArg
. Example of use:
3r33847.  
db.ExecContext (ctx, `DELETE FROM orders WHERE created_at < @end`, sql.Named("end", endTime))
 
3r33838. Support for this format is preferable to use compared to PostgreSQL or MySQL solutions. 3r33831. 3r33847.  
3r33838. The answer from the database, which processes the software driver, can be represented as follows: 3r33831. 3r33847.  
3r33590. dev> SELECT * FROM rubrics;
id | created_at | title | url
---- + ------------------------- + ------- + ----------- -
1 | 2012-03-???: 17: ??? | Tech | technology
2 | 2015-07-???: 05: ??? | Style | fashion
(2 rows) 3r33842. 3r33847.  
3r33838. 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:
3r33847.  
type GetRubricsResp struct {
Id int 3r3744 CreatedAt time.Time
Title string
URL string
}
3r33842. 3r33847.  
3r33838. Next, project the value 3r3r5858. id on 3r33858. resp. id 3r33859. and so on. In general, this functionality covers most needs. 3r33831. 3r33847.  
3r33838. 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, then you can use auxiliary functions, like 3r-385? when passing arguments to the query or scanning the response. pq.Array (& x) . Example from documentation:
3r33847.  
db.Query (`SELECT * FROM t WHERE id = ANY ($ 1)`, pq.Array ([]int {23? 401}))
var x[]sql.NullInt64
db.QueryRow ('SELECT ARRAY[235, 401]') .Scan (pq.Array (& x))
3r33842. 3r33847.  
3r33838. Accordingly, there should be ways to prepare data structures. 3r33831. 3r33847.  
3r33838. When executing any of the interface methods, a connection to the database can be used, in the form of an object 3r35858. * 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. 3r33831. 3r33847.  
3r33838. 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, the response error, the runtime, the interface method name. 3r33831. 3r33847.  
3r33838. For the most part, the requirements were formulated as a systematization of scenarios for working with a database. 3r33831. 3r33847.  
Solution: go-gad /sal
3r33847.  
3r33838. One way to deal with the template code is to generate it. Fortunately in Golang there are tools and examples for this https://blog.golang.org/generate . As an architectural solution for generation, the GoMock approach was adopted. https://github.com/golang/mock where the analysis of the interface is carried out with the help of 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. 3r33831. 3r33847.  
3r33838. 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 directive 3r33858. go: generate with a set of arguments and start the generation. A constructor and a bunch of template code will be received, ready to use. 3r33831. 3r33847.  
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)`
}
3r33842. 3r33847.  
3r3333317. Interface
3r33847.  
3r33838. It all starts with the declaration of the interface and a special command for the utility. go generate : 3r33847.  
    //go: generate salgen = //client.go-package = github.com /go-gad /sal /examples /profile /storage github.com/go-gad/sal/examples/profile/storage Store
type Store interface {
3r33859. 3r33842. 3r33847.  
3r33838. Here it is described that for our interface 3r3r5858. Store 3r33859. The 3r3r5858 console utility will be called from the package. salgen
, with two options and two arguments. The first option is 3r35858. -destination determines which file the generated code will be written to. The second option is 3r33858. -package defines the full path (import path) of the library for the generated implementation. The following are two arguments. The first describes the complete package path ( Github.com/go-gad/sal/examples/profile/storage ), Where the interface is located, the second indicates the interface name itself. Note that the command for go generate can be located anywhere, not necessarily near the target interface. 3r33831. 3r33847.  
3r33838. After running the command 3r33858. go generate we get a constructor whose name is built by adding the prefix New to the name of the interface. The constructor accepts a required parameter corresponding to the 3r35858 interface. sal.QueryHandler : 3r33847.  
    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)
}
3r33842. 3r33847.  
3r33838. Object 3r3r5858 corresponds to this interface. * sql.DB  . 3r33831. 3r33847.  
    connStr: = "user = pqgotest dbname = pqgotest sslmode = verify-full"
db, err: = sql.Open ("postgres", connStr)
client: = storage.NewStore (db)
3r33842. 3r33847.  

Methods

3r33847.  
3r33838. Interface methods define a set of available database queries. 3r33831. 3r33847.  
    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
}
3r33842. 3r33847.  
3r33849.  
3r33857. The number of arguments is always strictly two. 3r33860.  
3r33857. The first argument is the context. 3r33860.  
3r33857. The second argument contains the data to bind the variables and defines the query string. 3r33860.  
3r33857. The first output parameter can be an object, an array of objects, or missing. 3r33860.  
3r33857. The last output parameter is always an error. 3r33860.  
3r33862. 3r33847.  
3r33838. The first argument is always expected object 3r3r5858. context.Context . This context will be passed on to the database and toolkit calls. The second argument expects a parameter with a base type of 3r35858. struct 3r33859. (or pointer to 3r33858. struct 3r33859.). The parameter must satisfy the following interface: 3r33831. 3r33847.  
    type Queryer interface {
Query () string
}
3r33842. 3r33847.  
3r33838. Method 3r33858. Query () 3r33859. 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 by 3r33858. $ 1 r3r3859. , and the value of r3r3858 will be passed to the array of arguments. & req.End 3r33831. 3r33847.  
3r33838. Depending on the output parameters, which of the methods (Query /Exec) will be called is determined: 3r3-3311. 3r33847.  
3r33849.  
3r33857. If the first parameter with the base type struct 3r33859. (or a pointer to 3r33858. struct 3r33859.), then the method 3r3r5858 will be called. QueryContext . If the response from the database does not contain any lines, then error 3r3r5858 will be returned. sql.ErrNoRows . That is, the behavior is similar to db.QueryRow . 3r33860.  
3r33857. If the first parameter with the base type slice then the method will be called 3r3r5858. QueryContext . If the response from the database does not contain rows, then an empty list will be returned. The basic type of the list item should be stuct 3r33859. (or pointer to 3r33858. struct 3r33859.). 3r33860.  
3r33857. If the output parameter is one, with type error then the method will be called 3r3r5858. ExecContext
. 3r33860.  
3r33862. 3r33847.  
3r3494. Prepared statements 3r3726. 3r33847.  
3r33838. 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 database connection, including the processing of closed connections. In turn, the library 3r33858. go-gad /sal cares 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. 3r33831. 3r33847.  
3r33838. To support named arguments on the library side 3r3r5858. go-gad /sal The request is converted to a database suitable form. 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 need to use the 3r3r5858 tag. sql 3r33859. for structure fields. Consider an example: 3r33847.  
    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`
}
3r33842. 3r33847.  
3r33838. The query string will be converted, and using the matching table and variable bindings, the list will be passed to the query execution arguments: 3r33131. 3r33847.  
    //generated code:
db.Query ("DELETE FROM orders WHERE user_id = $ 1 AND created_at 3r33235. 3r33842.
 
3r33636. Map structs to request arguments and response messages 3r33847.  
3r33838. Library go-gad /sal cares about linking database response lines with response structures, columns of tables with fields of structures: 3r33847.  
    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)
}
3r33842. 3r33847.  
3r33838. And if the database response is: 3r33847.  
  3r33590. dev> SELECT * FROM rubrics;
id | created_at | title
---- + ------------------------- + -------
1 | 2012-03-???: 17: ??? | Tech
2 | 2015-07-???: 05: ??? | Style
(2 rows)
3r33842. 3r33847.  
3r33838. Then the GetRubricsResp list will return to us, 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. 3r33831. 3r33847.  
3r33838. If the database response contains columns with the same name, the corresponding structure fields will be selected in the order of the declaration. 3r33831. 3r33847.  
  3r33590. dev> select * from rubrics, subrubrics;
id | title | id | title
---- + ------- + ---- + ----------
1 | Tech | 3 | Politics 3r33859. 3r33842. 3r33847.  
    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 3r33874. Subrubric
}
3r33842. 3r33847.  
3r31717. Non-standard data types 3r33847.  
3r33838. Package 3r33858. database /sql 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, it is necessary to support the interfaces 3r33858. driver.Valuer and 3r33858. sql.Scanner . In various implementations of drivers there are special auxiliary functions. For example, 3r33858. lib /pq.Array (3r3-3630. [url]Https://godoc.org/github.com/lib/pq#Array[/url] ): 3r33847.  
    func Array (a interface {}) interface {3r33838. driver.Valuer
sql.Scanner
}
3r33842. 3r33847.  
3r33838. The default library is 3r33858. go-gad /sql for fields of the structure of the form 3r33847.  
    type DeleteAuthrosReq struct {
Tags[]int64 `sql:" tags "`
}
3r33842. 3r33847.  
3r33838. will use the value 3r33858. & req.Tags . If the structure will satisfy the interface 3r3r5858. sal.ProcessRower , 3r33847.  
    type ProcessRower interface {
ProcessRow (rowMap RowMap)
}
3r33842. 3r33847.  
3r33838. then the value used can be adjusted 3r33847.  
    func (r * DeleteAuthorsReq) ProcessRow (rowMap sal.RowMap) {
rowMap.Set ("tags", pq.Array (r.Tags))
}
func (r * DeleteAuthorsReq) Query () string {
return `DELETE FROM for WHERE tags = ANY (@tags :: UUID[])`
}
3r33842. 3r33847.  
3r33838. 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. 3r33831. 3r33847.  
3r3694. Transactions 3r33847.  
3r33838. To support transactions, the interface (Store) must be extended by the following methods: 3r33831. 3r33847.  
    type Store interface {
BeginTx (ctx context.Context, opts * sql.TxOptions) (Store, error)
sal.Txer
3r33859. 3r33842. 3r33847.  
3r33838. The implementation of the methods will be generated. Method 3r33858. BeginTx
uses connection from current object 3r3r5858. sal.QueryHandler and opens transaction 3r33858. db.BeginTx () ; returns a new interface implementation object 3r35858. Store 3r33859. , but as a handler uses the resulting object 3r3r5858. * sql.Tx 3r33831. 3r33847.  
3r33737. Middleware 3r33847.  
3r33838. Hooks are provided for embedding tools. 3r33831. 3r33847.  
    type BeforeQueryFunc func (ctx context.Context, query string, req interface {}) (context.Context, FinalizerFunc)
type FinalizerFunc func (ctx context.Context, err error)
3r33842. 3r33847.  
3r33838. Hook 3r33858. BeforeQueryFunc will be called before running r3r3858. db.PrepareContext or 3r3r5858. db.Query . That is, at the start of the program, when the cache of prepared expressions is empty, when calling 3r35858. store.GetAuthors , hook BeforeQueryFunc will be called twice. Hook 3r33858. BeforeQueryFunc can return the hook FinalizerFunc , which will be called before exiting the user method, in our case 3r3r5858. store.GetAuthors using r3r3858. defer . 3r33831. 3r33847.  
3r33838. When the hooks are executed, the context is filled with utility keys with the following values: 3r3-33831. 3r33847.  
3r33849.  
3r33857. 3r33858. ctx.Value (sal.ContextKeyTxOpened) a boolean value determines whether the method is called in the context of a transaction or not. 3r33860.  
3r33857. 3r33858. ctx.Value (sal.ContextKeyOperationType) , string value of the operation type, "QueryRow" , 3r33858. "Query" , 3r33858. "Exec" , 3r33858. "Commit" etc. 3r33860.  
3r33857. 3r33858. ctx.Value (sal.ContextKeyMethodName) the string value of the interface method, for example, 3r35858. "GetAuthors" . 3r33860.  
3r33862. 3r33847.  
3r33838. As arguments, hook BeforeQueryFunc takes the sql query string and argument 3r3r5858. req 3r33859. custom query method. Hook 3r33858. FinalizerFunc takes as an argument the variable err . 3r33831. 3r33847.  
    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 rt wt 2 w 28. inTx w2w 2 w 28 (sal.ContextKeyOperationType),
query,
        req,
time.Since (start),
ctx.Value (sal.ContextKeyTxOpened),
err,
) 3r33874.}
}
client: = NewStore (db, sal.BeforeQuery (beforeHook))
3r33842. 3r33847.  
3r33838. Examples of output: 3r33847.  
    "CreateAuthor"> Opeartion "Prepare": "INSERT INTO authors (Name, Desc, CreatedAt) VALUES ($ ? $ ? now ()) RETURNING ID, CreatedAt" with req    took[50.819µs]inTx[false]Error: 3r3408.
"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: 3r3408. 3r33859. 3r33842. 3r33847.  
3r33845. What's next 3r33847.  
3r33849.  
3r33857. Support for binding variables and prepared expressions for MySQL. 3r33860.  
3r33857. Hook RowAppender to adjust the response. 3r33860.  
3r33857. Returns the value 3r33858. Exec.Result
. 3r33860.  
3r33862. 3r33838.
3r33867. ! function (e) {function t (t, n) {if (! (n in e)) {for (var, a = e.document, i = a.scripts, o = i.length; o-- ;) if (-1! == i[o].src.indexOf (t)) {r = i[o]; break} if (! r) {r = a.createElement ("script"), r.type = "text /jаvascript", r.async =! ? r.defer =! ? r.src = t, r.charset = "UTF-8"; var d = function () {var e = a.getElementsByTagName ("script")[0]; e.parentNode.insertBefore (r, e)}; "[object Opera]" == e.opera? a.addEventListener? a.addEventListener ("DOMContentLoaded", d ): d ()}}} t ("//mediator.mail.ru/script/2820404/"""_mediator") () (); 3r33868.
3r33838.
+ 0 -

Add comment