Gin Combat: Gin+mysql Simple restful-style API

This is a creation in Article, where the information may have evolved or changed.

We have learned about the gin framework of Golang. For WebService services, restful style is almost eminence. Gin also natural support for restful. The following is the use of gin write a simple service, small, perfectly formed. We start with a single file, and then gradually decompose the module into packages, organizing the code.

It Works

The premise of using gin is installation, we need to install gin and MySQL driver, the specific installation method is not to repeat. You can refer to the Golang Micro-framework gin Introduction and Golang persistence.

Create a folder to use for projects, create a new file Main.go:

  newland  tree.└── main.go

Main.go

package mainimport ( "gopkg.in/gin-gonic/gin.v1" "net/http")func main() { router := gin.Default() router.GET("/", func(c *gin.Context) {  c.String(http.StatusOK, "It works") }) router.Run(":8000")}

Compile run

  newland  go run main.go[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production. - using env: export GIN_MODE=release - using code: gin.SetMode(gin.ReleaseMode)[GIN-debug] GET    /                         --> main.main.func1 (3 handlers)[GIN-debug] Listening and serving HTTP on :8000

Access / to see our returned stringIt works

Database

After the framework has been installed, once the request response is completed. The next step is to install the database-driven and initialize-data-related operations. First, we need to create a new data table. One of its simple data tables:

CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `first_name` varchar(40) NOT NULL DEFAULT '',  `last_name` varchar(40) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After creating the data table, initialize the database connection pool:

func main() { db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true") defer db.Close() if err != nil{  log.Fatalln(err) } db.SetMaxIdleConns(20) db.SetMaxOpenConns(20) if err := db.Ping(); err != nil{  log.Fatalln(err) } router := gin.Default() router.GET("/", func(c *gin.Context) {  c.String(http.StatusOK, "It works") }) router.Run(":8000")}

Use SQL. The open method creates a database connection pool of DB. This db is not a database connection, it is a connection pool, and the connection is created only when the real database communicates. For example, here's the db.Ping operation. db.SetMaxIdleConns(20)and db.SetMaxOpenConns(20) set the free and maximum open connections for the database, which is the maximum number of connections that are made to the MySQL server.

If not set, the default is 0, which means there is no limit to the open connection. When I was testing, I found that there would be a lot of connections to the TIME_WAIT state, although the number of MySQL connections did not rise. After setting these two parameters, there is no connection with a large time_wait state. And there is no obvious change in the QPS, because of the protection of the database, it is best to set this even a parameter.

Curd additions and deletions to search

The basic of restful is the curd operation of resources. The following opens our first API interface, adding a resource.

Increase

func main() { ... router.POST("/person", func(c *gin.Context) {  firstName := c.Request.FormValue("first_name")  lastName := c.Request.FormValue("last_name")  rs, err := db.Exec("INSERT INTO person(first_name, last_name) VALUES (?, ?)", firstName, lastName)  if err != nil {   log.Fatalln(err)  }  id, err := rs.LastInsertId()  if err != nil {   log.Fatalln(err)  }  fmt.Println("insert person Id {}", id)  msg := fmt.Sprintf("insert successful %d", id)  c.JSON(http.StatusOK, gin.H{   "msg": msg,  }) }) ...}

Perform a non-query operation using the Exec method of the DB, which is used as a placeholder in MySQL ? . Finally, we return the inserted ID to the client. The results of the request are as follows:

  ~  curl -X POST http://127.0.0.1:8000/person -d "first_name=hello&last_name=world" | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100    62  100    30  100    32   5054   5391 --:--:-- --:--:-- --:--:--  6400{    "msg": "insert successful 1"}

You can add a few more records below.

Check

Querying list Query

Above we added a record, the following to get this record, check generally have two operations, one is the query list, followed by a query specific records. Two kinds of similarities.

In order to bind a query result to a variable or object of Golang, we need to define a struct to bind the object. Define the person structure above the main function:

type Person struct { Id        int    `json:"id" form:"id"` FirstName string `json:"first_name" form:"first_name"` LastName  string `json:"last_name" form:"last_name"`}

And then query our data list.

 router.GET("/persons", func(c *gin.Context) {  rows, err := db.Query("SELECT id, first_name, last_name FROM person")  defer rows.Close()  if err != nil {   log.Fatalln(err)  }  persons := make([]Person, 0)  for rows.Next() {   var person Person   rows.Scan(&person.Id, &person.FirstName, &person.LastName)   persons = append(persons, person)  }  if err = rows.Err(); err != nil {   log.Fatalln(err)  }  c.JSON(http.StatusOK, gin.H{   "persons": persons,  }) })

Reading MySQL data requires a binding process, db. The Query method returns a Rows object, and the database connection is transferred to the object, so we need to define row. Close operation. Then create a []Person slice.

Use make instead var persons []Person of using the declaration method directly. There is a difference, using make, when the array slice has no elements, the JSON returns [] . If declared directly, JSON is returned null .

The next step is to use the next method of the Rows object, traverse the queried data, bind to the person object, and finally append to the persons slice.

  ~  curl  http://127.0.0.1:8000/persons | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100   113  100   113    0     0   101k      0 --:--:-- --:--:-- --:--:--  110k{    "persons": [        {            "first_name": "hello",            "id": 1,            "last_name": "world"        },        {            "first_name": "vanyar",            "id": 2,            "last_name": "elves"        }    ]}

Querying a single record Queryrow

The query list needs to use an iteration of the rows object to query a single record, which is not so troublesome. Although you can also iterate over the result set of a record. Because the operation of querying a single record is so common, Golang's database/sql also specifically provides a query method

 router.GET("/person/:id", func(c *gin.Context) {  id := c.Param("id")  var person Person  err := db.QueryRow("SELECT id, first_name, last_name FROM person WHERE id=?", id).Scan(   &person.Id, &person.FirstName, &person.LastName,  )  if err != nil {   log.Println(err)   c.JSON(http.StatusOK, gin.H{    "person": nil,   })   return  }  c.JSON(http.StatusOK, gin.H{   "person": person,  }) })

The query results are:

  ~  curl  http://127.0.0.1:8000/person/1 | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100    60  100    60    0     0  20826      0 --:--:-- --:--:-- --:--:-- 30000{    "person": {        "first_name": "hello",        "id": 1,        "first_name": "world"    }}

Querying a single record has a small problem, and when the data does not exist, it also throws an error. Rough use of log exit is a bit inappropriate. Return a nil when, in case of a real error, such as a SQL error. How this situation is resolved. A specific scenario design program is also required.

Change

Additions and deletions, the following to update the operation. The previous add record we used the UrlEncode way to submit, updated API we automatically match binding Content-type

 router.PUT("/person/:id", func(c *gin.Context) {  cid := c.Param("id")  id, err := strconv.Atoi(cid)  person := Person{Id: id}  err = c.Bind(&person)  if err != nil {   log.Fatalln(err)  }  stmt, err := db.Prepare("UPDATE person SET first_name=?, last_name=? WHERE id=?")  defer stmt.Close()  if err != nil {   log.Fatalln(err)  }  rs, err := stmt.Exec(person.FirstName, person.LastName, person.Id)  if err != nil {   log.Fatalln(err)  }  ra, err := rs.RowsAffected()  if err != nil {   log.Fatalln(err)  }  msg := fmt.Sprintf("Update person %d successful %d", person.Id, ra)  c.JSON(http.StatusOK, gin.H{   "msg": msg,  }) })

Update by using UrlEncode:

  ~  curl -X PUT http://127.0.0.1:8000/person/2 -d "first_name=noldor&last_name=elves" | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100    72  100    39  100    33   3921   3317 --:--:-- --:--:-- --:--:--  4333{    "msg": "Update person 2 successful 1"}

Update by using JSON:

  ~  curl -X PUT http://127.0.0.1:8000/person/2 -H "Content-Type: application/json"  -d '{"first_name": "vanyar", "last_name": "elves"}' | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100    85  100    39  100    46   4306   5079 --:--:-- --:--:-- --:--:--  5750{    "msg": "Update person 2 successful 1"}

By deleting

The last action is to delete, delete the required features, the above examples are covered. The implementation of the deletion is particularly simple:

 router.DELETE("/person/:id", func(c *gin.Context) {  cid := c.Param("id")  id, err := strconv.Atoi(cid)  if err != nil {   log.Fatalln(err)  }  rs, err := db.Exec("DELETE FROM person WHERE id=?", id)  if err != nil {   log.Fatalln(err)  }  ra, err := rs.RowsAffected()  if err != nil {   log.Fatalln(err)  }  msg := fmt.Sprintf("Delete person %d successful %d", id, ra)  c.JSON(http.StatusOK, gin.H{   "msg": msg,  }) })

We can use the delete interface, the data are deleted, and then to verify the above post interface to get the list, when the record is not, the slice is JSON serialized [] ornull

  ~  curl  http://127.0.0.1:8000/persons | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100    15  100    15    0     0  11363      0 --:--:-- --:--:-- --:--:-- 15000{    "persons": []}

persons := make([]Person, 0)change to persons []Person . Compile run:

  ~  curl  http://127.0.0.1:8000/persons | python -m json.tool  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current                                 Dload  Upload   Total   Spent    Left  Speed100    17  100    17    0     0  13086      0 --:--:-- --:--:-- --:--:-- 17000{    "persons": null}

At this point, the basic curd operation of the RESTful style API has been completed. The content is actually not complicated, even quite simple. The complete code can be obtained through GIST.

Organization Code

Implement a basic point RESTful service, unfortunately our code is in a file. For a library, a single file may be good, for a slightly larger project, the single file is always a bit non-mainstream. Of course, there are more reasons for the program to be readable and maintainable, and we need to reorganize the code, split modules and packages.

Encapsulation Model method

Our handler comes out of the function, and the interaction between the request and the database is combined. First we create the data model based on the person structure created, and the method of the model. Split the database interactively.

Create a singleton database connection pool object:

var db *sql.DBfunc main() { var err error db, err = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true") if err != nil {  log.Fatalln(err) } defer db.Close() if err := db.Ping(); err != nil {  log.Fatalln(err) } ...}

This way, in the main package, the DB is free to use.

Next, we then encapsulate the function of adding the recorded functions into the person structure:

func (p *Person) AddPerson() (id int64, err error) { rs, err := db.Exec("INSERTs INTO person(first_name, last_name) VALUES (?, ?)", p.FirstName, p.LastName) if err != nil {  return } id, err = rs.LastInsertId() return}

The handler function is then modified to create an instance of the person structure and then invoke its method:

 router.POST("/person", func(c *gin.Context) {  firstName := c.Request.FormValue("first_name")  lastName := c.Request.FormValue("last_name")  person := Person{FirstName: firstName, LastName: lastName}  ra_rows, err := person.AddPerson()  if err != nil {   log.Fatalln(err)  }  msg := fmt.Sprintf("insert successful %d", ra_rows)  c.JSON(http.StatusOK, gin.H{   "msg": msg,  }) })

The model methods and handler functions for getting lists are also well-changed:

func (p *Person) GetPersons() (persons []Person, err error) { persons = make([]Person, 0) rows, err := db.Query("SELECT id, first_name, last_name FROM person") defer rows.Close() if err != nil {  return } for rows.Next() {  var person Person  rows.Scan(&person.Id, &person.FirstName, &person.LastName)  persons = append(persons, person) } if err = rows.Err(); err != nil {  return } return}

And

 router.POST("/person", func(c *gin.Context) {  firstName := c.Request.FormValue("first_name")  lastName := c.Request.FormValue("last_name")  person := Person{FirstName: firstName, LastName: lastName}  ra_rows, err := person.AddPerson()  if err != nil {   log.Fatalln(err)  }  msg := fmt.Sprintf("insert successful %d", ra_rows)  c.JSON(http.StatusOK, gin.H{   "msg": msg,  }) })

The rest of the functions and methods are no longer one by one examples.

In an interface that adds records, we use the client parameters and the person to create the instance, and then call its methods. In the interface that gets the list, we declare the person object directly. Both ways are possible.

handler function

router.Get(url, handler func)the format provided by the gin. First we can extract all the handler functions from the router.

For example, the handle of adding records and getting lists is extracted.

func AddPersonApi(c *gin.Context) { firstName := c.Request.FormValue("first_name") lastName := c.Request.FormValue("last_name") person := Person{FirstName: firstName, LastName: lastName} ra_rows, err := person.AddPerson() if err != nil {  log.Fatalln(err) } msg := fmt.Sprintf("insert successful %d", ra_rows) c.JSON(http.StatusOK, gin.H{  "msg": msg, })}func main(){ ... router.POST("/person", AddPersonApi) ... }

After Modle and handler are drawn out, our code structure becomes clearer, so refer to this gist

Organizing projects

With the separation of the model and handler above, the code structure becomes clearer, but we are still a single file. The next step is to encapsulate the different packages.

Database processing

Create the following three folders in the project root directory apis , databases and models , and create files within the folder. At this point our catalog results are as follows:

  newland  tree.├── apis│   └── person.go├── database│   └── mysql.go├── main.go├── models│   └── person.go└── router.go

The APIs folder holds our handler function, and the models folder is used to store our data model.

The package code for MYQL.GO is as follows:

package databaseimport ( "database/sql" _ "github.com/go-sql-driver/mysql" "log")var SqlDB *sql.DBfunc init() { var err error SqlDB, err = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true") if err != nil {  log.Fatal(err.Error()) } err = SqlDB.Ping() if err != nil {  log.Fatal(err.Error()) }}

Because we need to use the SQLDB variable in other places, the variable name must be capitalized, according to Golang's custom.

Data Model Package

Modify the Person.go in the Models folder to move the corresponding person structure and its methods here:

package modelsimport ( "log" db "newland/database")type Person struct { Id        int    `json:"id" form:"id"` FirstName string `json:"first_name" form:"first_name"` LastName  string `json:"last_name" form:"last_name"`}func (p *Person) AddPerson() (id int64, err error) { rs, err := db.SqlDB.Exec("INSERT INTO person(first_name, last_name) VALUES (?, ?)", p.FirstName, p.LastName) if err != nil {  return } id, err = rs.LastInsertId() return}func (p *Person) GetPersons() (persons []Person, err error) { persons = make([]Person, 0) rows, err := db.SqlDB.Query("SELECT id, first_name, last_name FROM person") defer rows.Close() if err != nil {  return } for rows.Next() {  var person Person  rows.Scan(&person.Id, &person.FirstName, &person.LastName)  persons = append(persons, person) } if err = rows.Err(); err != nil {  return } return}....

Handler

The specific handler function is then encapsulated in the API package because the handler function operates the database, so it references the model package

package apisimport ( "net/http" "log" "fmt" "strconv" "gopkg.in/gin-gonic/gin.v1" . "newland/models")func IndexApi(c *gin.Context) { c.String(http.StatusOK, "It works")}func AddPersonApi(c *gin.Context) { firstName := c.Request.FormValue("first_name") lastName := c.Request.FormValue("last_name") p := Person{FirstName: firstName, LastName: lastName} ra, err := p.AddPerson() if err != nil {  log.Fatalln(err) } msg := fmt.Sprintf("insert successful %d", ra) c.JSON(http.StatusOK, gin.H{  "msg": msg, })}...

Routing

Finally, we pull out the route, modify the Router.go, we encapsulate the routing function in the routing file.

package mainimport ( "gopkg.in/gin-gonic/gin.v1" . "newland/apis")func initRouter() *gin.Engine { router := gin.Default() router.GET("/", IndexApi) router.POST("/person", AddPersonApi) router.GET("/persons", GetPersonsApi) router.GET("/person/:id", GetPersonApi) router.PUT("/person/:id", ModPersonApi) router.DELETE("/person/:id", DelPersonApi) return router}

App Portal

Finally, the main function of the app portal, the route is imported, and we want to close the main function at the end of the global database connection pool:

Main.go

package mainimport ( db "newland/database")func main() { defer db.SqlDB.Close() router := initRouter() router.Run(":8000")}

At this point, we have a better organization of simple procedures. Of course, the Golang program is based on the package, not rigidly, according to the specific application scenario can be organized.

Running the project at this point cannot be as simple as before go run main.go , because the package main contains Main.go and router.go files, so you need to run the go run *.go command compilation run. If the binary project is eventually compiled, rungo build -o app

Summarize

Through the above practice, we understand the gin framework to create basic restful services. and learned how to organize Golang code packages. We discussed a lot of things, but only a lack of testing. It is important to test whether there are test files and test coverage when examining a framework or a three-party package. Because the test content is many, we do not do a separate test introduction here. Later, add the test code to Gin's API with Gofight.

In addition, more content can read others excellent open source projects, learn and practice to improve their coding ability.