Summary

A database is used to store data.As long as it's not a toy project, a database is needed for each project.MySQL is still the most used. PostgreSQL Use is also growing rapidly. In Web development, databases are also required.This article describes how to operate a database in Go Language based on MySQL.This article assumes that you already have the basics of database and MySQL. There's a very detailed free tutorial on MySQL that I'll put on Reference resources Yes, you need to take it yourself.

The GoLanguage Standard Library, database/sql, provides only a set of interfaces for querying and manipulating databases without any implementation.Only third-party libraries can be used to operate databases in Go. All types of databases have corresponding third-party libraries.The most common MySQL-enabled drivers in Go are go-sql-driver/mysql. The library supports database/sql, all implemented in go.

Database Operation

Dead work

Create a database department to represent a department in your company. Two tables, employees and teams, are created in the library.Employees record employee information and teams record team information. Each employee belongs to a group with several employees in each group.

SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `department`
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

USE `department`;

CREATE TABLE IF NOT EXISTS `employees` (
  `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL DEFAULT '',
  `age` INT(11) NOT NULL DEFAULT 0,
  `salary` INT(11) NOT NULL DEFAULT 0,
  `team_id` INT(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `teams` (
  `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

INSERT INTO `teams`(`name`)
VALUES
  ('Plan'),
  ('Development'),
  ('Operate'),
  ('Operation and maintenance');

INSERT INTO `employees`(`name`, `age`, `salary`, `team_id`)
VALUES
  ('Zhang San', 28, 1200, 1),
  ('Li Si', 38, 4000, 1),
  ('King Five', 36, 3500, 1),
  ('Zhao Six', 31, 3100, 2),
  ('Pseudo-ginseng', 29, 2900, 2),
  ('Wu Ba', 27, 1500, 3),
  ('Zhu Jiu', 26, 1600, 3),
  ('Qian Ten', 27, 1800, 3),
  ('Tao 11', 28, 1900, 4),
  ('Wong Twelve', 25, 2000, 4),
  ('Sword Thirteen', 24, 30000, 4);

Insert some test data.Save this department.sql file to a directory and open the command line in that directory:

$ mysql -u root -p

Enter the password to connect to the database, and then enter the following command:

mysql> source department.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 4 warnings (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>

The database and tables are created.

Connect to database

go-sql-driver/mysql is a third-party library and needs to be installed:

$ go get github.com/go-sql-driver/mysql

Use:

package main

import (
  "database/sql"
  "log"

  _ "github.com/go-sql-driver/mysql"
)

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("connect database failed: ", err)
  }
  defer db.Close()
}

Instead of using the mysql library directly, we operate on the database through the database/sql interface.

import _ "github.com/go-sql-driver/mysql"

The above code imports mysql, but does not use it directly. Instead, it executes the init function of the MySQL library with the side effects of the import, registering the MySQL driver with the database/sql:

// go-sql-driver/mysql/driver.go
func init() {
  sql.Register("mysql", &MySQLDriver{})
}

Then sql.Open is used to create a sql.DB structure in the program. The first parameter is the name registered for the mysql library, and the second parameter is actually to specify the database connection information. Each database accepts different connection information.For MySQL, the connection information is actually a DSN (Data Source Name).The general format of a DSN is:

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

The example uses a DSN with a specified user name of root and password of 12345. Connect to the department database of MySQL with ip 127.0.0.1 and port 3306 through the tcp protocol.

After use is complete, you need to call db.Close to close sql.DB.

** It is important to note that sql.Open does not establish a connection to the database, nor does it detect driver connection parameters.It just creates a database abstraction layer for later use. Connections to databases are actually created lazily when needed.**So we use an illegal username or password to connect to a library that does not exist on the host and sql.Open will not fail. Change the DNN above to user:password@tcp(127.0.0.1:6666)/not_exist_department to run the program without error.

If you want to detect whether the database is accessible, you can use the db.Ping() function:

err = db.Ping()
if err != nil {
  log.Fatal("ping failed: ", err)
}

Connecting not_exist_department will result in an error:

2020/01/20 22:16:12 ping failed: Error 1049: Unknown database 'not_exist_department'
exit status 1

sql.DB objects generally survive for a long time as some form of global variable.Do not open or close this object frequently.This can have a very large impact on performance.

query

Let's start with a simple example:

package main

import (
  "database/sql"
  "log"

  _ "github.com/go-sql-driver/mysql"
)

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open database failed: ", err)
  }
  defer db.Close()

  var id int
  var name string
  var age int
  var salary int
  var teamId int

  rows, err := db.Query("select id, name, age, salary, team_id from employees where id = ?", 1)
  if err != nil {
    log.Fatal("query failed: ", err)
  }
  defer rows.Close()

  for rows.Next() {
    err := rows.Scan(&id, &name, &age, &salary, &teamId)
    if err != nil {
      log.Fatal("scan failed: ", err)
    }
    log.Printf("id: %d name:%s age:%d salary:%d teamId:%d\n", id, name, age, salary, teamId)
  }

  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }
}

Run the program, output:

2020/01/20 22:27:21 id: 1 name:Zhang San age:28 salary:1200 teamId:1

From the above program, we see the basic flow of a query operation:

• Query the database using db.Query();
• Traversing through the returned rows in a loop, rows.Scan() reads the values of columns, and rows.Next() moves the Pointer to the next row;
• When all rows are traversed, rows.Next() returns false and the loop exits.

Database operations can encounter a wide variety of errors, so error handling is important.For example, calling rows.Scan in a loop may produce an error.

Make sure rows are closed after traversal.Because it holds a connected pointer, not closing can cause a resource leak.rows.Next() returns an EOF error when it encounters the last row and closes the connection. In addition, rows.Next() will automatically close if it returns false due to an error.In other cases, if you exit the loop early, you may forget to close rows. So defer rows.Close() is generally used to ensure proper shutdown.

Tips:

When the Scan method is called, the corresponding data type conversion is performed internally based on the parameter type passed in.This feature allows you to simplify your code. For example, a column in MySQL is VARCHAR/CHAR or a similar text type, but we know it holds an integer. Then you can pass in a variable of type int, and Scan helps us turn strings into int.Remove the hassle of manually calling strconv related methods.

Functions in database/sql are particularly well named:

• Query* This function, which starts with Query, certainly returns several rows (possibly zero) of data;
• Statements that do not return row data, cannot use the Query* function, should use Exec.

Prepare

When we need to execute the same statement multiple times, the best practice is to create a PreparedStatement first.This PreparedStatement can contain parameter placeholders, which are then provided on subsequent execution.

Each database has its own parameter placeholder, what is MySQL using?One obvious benefit of using parameter placeholders is that they prevent SQL injection attacks.

When SQL needs to be executed, the incoming parameter calls the Query method of PreparedStatement:

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("select id, name, age, salary from employees where id = ?")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  rows, err := stmt.Query(2)
  if err != nil {
    log.Fatal("query failed: ", err)
  }
  defer rows.Close()

  var (
    id int
    name string
    age int
    salary int
  )
  for rows.Next() {
    err := rows.Scan(&id, &name, &age, &salary)
    if err != nil {
      log.Fatal("scan failed: ", err)
    }
    log.Printf("id:%d name:%s age:%d salary:%d\n", id, name, age, salary)
  }

  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }
}

In fact, inside the db.Query() function, a PreparedStatement is created, executed, and then closed.This will result in three communications with the database.So try to create a PreparedStatement before using it.

Single Line Query

If a query returns at most one row of data, we don't have to write a loop to simplify code writing using QueryRow.

Call db.QueryRow directly:

var name string
err = db.QueryRow("select name from employees where id = ?", 1).Scan(&name)
if err != nil {
  log.Fatal(err)
}
fmt.Println(name)

You can also call QueryRow on PreparedStatement:

stmt, err := db.Prepare("select name from employees where id = ?").Scan(&name)
if err != nil {
  log.Fatal(err)
}
defer stmt.Close()
var name string
err = stmt.QueryRow(1).Scan(&name)
if err != nil {
  log.Fatal(err)
}
fmt.Println(name)

Note that errors encountered by QueryRow are deferred until Scan is called.

Insert/Modify/Delete

INSERT/UPDATE/DELETE These operations, since no rows are returned, should use the Exec function.It is recommended that the PreparedStatement be created before execution.

Now the Planning Group has joined a new employee:

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("INSERT INTO employees(name, age, salary, team_id) VALUES(?,?,?,?)")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  res, err := stmt.Exec("Willow fourteen", 32, 5000, 1)
  if err != nil {
    log.Fatal("exec failed: ", err)
  }
  lastId, err := res.LastInsertId()
  if err != nil {
    log.Fatal("fetch last insert id failed: ", err)
  }
  rowCnt, err := res.RowsAffected()
  if err != nil {
    log.Fatal("fetch rows affected failed: ", err)
  }
  log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}

The Exec method returns a value of the sql.Result interface type:

// src/database/sql/sql.go
type Result interface {
  LastInsertId() (int64, error)
  RowsAffected() (int64, error)
}

Some tables have self-increasing IDs that do not need to be set when inserting, and the database automatically generates a return.LastInsertId() returns the id generated at insert time. RowsAffected() returns the number of rows affected.

Run the program, output:

2020/01/21 07:20:26 ID = 12, affected = 1

affair

In Go, a transaction is essentially an object that holds a connection to a database.When executing the method we described above through this object, All will use this same connection.Call db.Begin() to create a transaction object and execute the above method on it. Commit() was called successfully and Rollback() was called unsuccessfully to close the transaction.

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  tx, err := db.Begin()
  if err != nil {
    log.Fatal("begin failed: ", err)
  }
  defer tx.Rollback()


  stmt, err := tx.Prepare("UPDATE employees SET team_id=? WHERE id=?")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  _, err = stmt.Exec(2, 1)
  if err != nil {
    log.Fatal("exec failed: ", err)
  }

  tx.Commit()
}

Note that db's methods can no longer be called directly within a transaction because it uses a different connection to the transaction, which may result in inconsistent execution results.

error handling

The last return value for almost all operations in database/sql is an error type.There are all kinds of errors in the database, and we should always check to see if there are any.Errors due to several special circumstances are described below.

Traversing result set

for rows.Next() {
  // ...
}

if err = rows.Err(); err != nil {
}

``The error returned by rows.Err() may be multiple errors in the rows.Next() loop.The loop may have exited prematurely for some reason.We should check if the loop exits normally.When exiting abnormally, database/sql automatically calls rows.Close().When exiting early, we need to call rows.Close() manually.**rows.Close()`** can be called multiple times.

Close Result Set

In fact, rows.Close() also returns an error.But there is a limit to what we can do about this error.Usually it's logging. This error is usually ignored if logging is not required.

QueryRow

Consider the following code:

var name string
err = db.QueryRow("SELECT name FROM employees WHERE id = ?", 1).Scan(&name)
if err != nil {
  log.Fatal(err)
}
fmt.Println(name)

What would Scan() do if there were no employee with id = 1?

Go defines a special error constant, sql.ErrNoRows.QueryRow will return this error if no rows match the requirements. This error requires special handling in most cases because no results are generally not considered an error at the application level.

var name string
err = db.QueryRow("SELECT name FROM employees WHERE id = ?", 1).Scan(&name)
if err != nil {
  if err == sql.ErrNoRows {
  } else {
	log.Fatal(err)
  }
}
fmt.Println(name)

So why does QueryRow return an error when there are no rows that meet the requirements?

Because we want to distinguish whether rows are returned or not, if an empty result set is returned, since Scan() will not do anything, we cannot distinguish whether name reads an empty string or an initial value.

Specific database errors

To identify what errors occurred, one approach is to check for specific text in the error description:

rows, err := db.Query("SELECT someval FROM sometable")
if err != nil {
  if strings.Contains(err.Error(), "Access denied") {
  }
}

However, this is not recommended because these descriptions may not always be consistent across different database versions.

It is better to convert the errors into database-driven errors and then compare the error codes:

if driverErr, ok := err.(*mysql.MySQLError); ok {
  if driverErr.Number == 1045 {
  }
}

Different drivers may have different judgments.Also, writing the number 1045 directly is not good, VividCortex collates MySQL error codes, GitHub repository is mysqlerr .Use the library for subsequent modifications:

if driverErr, ok := err.(*mysql.MySQLError); ok {
  if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {
  }
}

Processing unknown columns

Sometimes, we may not be sure how many columns the query returns.However, Scan() requires the correct number of parameters to be passed in.To do this, we can first use rows.Columns() to return all column names, then create a string pointer slice of the same size to pass to the Scan() function:

func main() {
  db, err := sql.Open("mysql", "root:12345@tcp(127.0.0.1:3306)/department")
  if err != nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("SELECT * FROM employees")
  if err != nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  rows, err := stmt.Query()
  if err != nil {
    log.Fatal("exec failed: ", err)
  }
  defer rows.Close()

  cols, err := rows.Columns()
  if err != nil {
    log.Fatal("columns failed: ", err)
  }

  data := make([]interface{}, len(cols), len(cols))
  for i := range data {
    data[i] = new(string)
  }

  for rows.Next() {
    err = rows.Scan(data...)
    if err != nil {
      log.Fatal("scan failed: ", err)
    }

    for i := 0; i < len(cols); i++ {
      fmt.Printf("%s: %s ", cols[i], *(data[i].(*string)))
    }
    fmt.Println()
  }

  if err = rows.Err(); err != nil {
    log.Fatal(err)
  }
}

Run the program:

id: 1 name: Zhang San age: 28 salary: 1200 team_id: 2 
id: 2 name: Li Si age: 38 salary: 4000 team_id: 1
id: 3 name: King Five age: 36 salary: 3500 team_id: 1
id: 4 name: Zhao Six age: 31 salary: 3100 team_id: 2
id: 5 name: Pseudo-ginseng age: 29 salary: 2900 team_id: 2 
id: 6 name: Wu Ba age: 27 salary: 1500 team_id: 3
id: 7 name: Zhu Jiu age: 26 salary: 1600 team_id: 3
id: 8 name: Qian Ten age: 27 salary: 1800 team_id: 3
id: 9 name: Tao 11 age: 28 salary: 1900 team_id: 4
id: 10 name: Wong Twelve age: 25 salary: 2000 team_id: 4
id: 11 name: Sword Thirteen age: 24 salary: 30000 team_id: 4
id: 12 name: Willow fourteen age: 32 salary: 5000 team_id: 1

Connection Pool

database/sql implements a basic connection pool.Connection pools have some interesting features, so take a look to avoid treading holes:

• Execute two statements in succession against the same database, which may occur on different database connections.The results can be misleading.For example, LOCK TABLES followed by INSERT may be blocked;
• Create a new connection when a new connection is required and there are no idle connections in the pool;
• By default, there is no limit on the number of connections.If you do many things at once, you may create many connections at the same time.There may be too many connections errors in the database;
• Calling db.SetMaxIdleConns(N) limits the maximum number of idle connections in the pool; db.SetMaxOpenConns(N) limits the number of all open connections;
• Problems may occur if a connection is not used for a long time. If you encounter a connection timeout, try setting the maximum number of free connections to 0.
• Reusing long-lived connections can cause network problems, and you can call db.SetConnMaxLifeTime(duration) to set the maximum connection lifetime.

summary

This paper describes how to query and modify databases in Go, mainly the use of database/sql and go-sql-driver/mysql libraries.The database/sql interface is not complex, but many details require attention.A lack of attention can lead to a leak of resources.

Reference resources

1. MySQL Tutorial , very detailed tutorial
2. Go database/sql tutorial
3. Build Web Application with Golang

I

My Blog

Welcome to my WeChat Public Number GoUpUp to learn and progress together.

>This article is a multi-article blog platform OpenWrite Release!