brief introduction

Gorm is one of the more ORM s used in the development of the Go language.It has a full range of functions:

• crud
• Association (contains one, contains many, belongs to, many-to-many, contains many)
• callback functions are available before and after CallBacks (create, save, update, delete, query find)
• Preload
• affair
• composite primary key
• Journal

database/sql package

However, Gorm's source code is not directly available in this article; we will start with a database/sql analysis.The reason is that Gorm also encapsulates some functionality based on this package.Therefore, the Gorm source code can only be better understood if you first understand the database/sql package.
Database/sql is also a top-level encapsulation for MySQL drivers."github.com/go-sql-driver/mysql" is a driver for mysql, on which database/sql is the basic encapsulation that includes the use of connection pools

Use examples

Here's the most basic add-delete check
The operation is divided into the following steps:

1. Introduce github.com/go-sql-driver/mysql package (the init method in the package initializes the registration of the MySQL driver)
2. Initialize a sql.DB structure using sql.Open
3. Call Prepare Exec to execute sql statement

==Note:==Use the Exec function to automatically release the connection after it has been called, putting it into the connection pool

  sql.rows returned using Query requires the connection rows.Close() to be manually released

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "strconv"
)

func main() {
    // open a connection
    db, err := sql.Open("mysql", "root:feg@125800@tcp(47.100.245.167:3306)/artifact?charset=utf8&loc=Asia%2FShanghai&parseTime=True")
    if err != nil {
        fmt.Println("err:", err)
    }
    // Set the maximum number of idle connections
    db.SetMaxIdleConns(1)
    // Set maximum number of links
    db.SetMaxOpenConns(1)
    query(db, 3)
}

//modify
func update(db *sql.DB, id int, user string) {
    stmt, err := db.Prepare("update user set UserName=? where Id =?")
    if err != nil {
        fmt.Println(err)
    }
    res, err := stmt.Exec(user, id)
    updateId, err := res.LastInsertId()
    fmt.Println(updateId)
}

//delete
func delete(db *sql.DB, id int) {
    stmt, err := db.Prepare("delete  from user where id = ?")
    if err != nil {
        fmt.Println(err)
    }
    res, err := stmt.Exec(1)
    updateId, err := res.LastInsertId()
    fmt.Println(updateId)
}

//query
func query(db *sql.DB, id int) {
    rows, err := db.Query("select * from user where  id = " + strconv.Itoa(id))
    if err != nil {
        fmt.Println(err)
        return
    }

    for rows.Next() {
        var id int
        var user string
        var pwd string
        rows.Scan(&id, &user, &pwd)
        fmt.Println("id:", id, "user:", user, "pwd:", pwd)
    }
    rows.Close()
}

//insert
func insert(db *sql.DB, user, pwd string) {
    stmt, err := db.Prepare("insert into user set UserName=?,Password=?")
    if err != nil {
        fmt.Println(err)
    }
    res, err := stmt.Exec("peter", "panlei")
    id, err := res.LastInsertId()
    fmt.Println(id)
}

Connection Pool

Since Gorm's connection pool uses the connection pool in the database/sql package, here we need to learn the source implementation of the connection pool in the package.In fact, the most important thing of all connection pools is the connection pool objects, get functions, and release functions.

DB Object

type DB struct {
    //Database Implementation Driver
    driver driver.Driver
    dsn    string
    numClosed uint64
    // lock
    mu           sync.Mutex // protects following fields
    // idle connection
    freeConn     []*driverConn
    //Block the request queue until the maximum number of connections is reached, subsequent requests will be inserted into the queue waiting for available connections
    connRequests map[uint64]chan connRequest
    // Record the next key used for the connRequests map
    nextRequest  uint64 // Next key to use in connRequests.
    numOpen      int    // number of opened and pending open connections
    
    openerCh    chan struct{}
    closed      bool
    dep         map[finalCloser]depSet
    lastPut     map[*driverConn]string 
    // Maximum number of idle connections
    maxIdle     int                    
    // Maximum Open Connections
    maxOpen     int  
    // Maximum Connection Lifetime
    maxLifetime time.Duration          
    cleanerCh   chan struct{}
}

Getting Method

func (db *DB) conn(ctx context.Context, strategy connReuseStrategy) (*driverConn, error) {
    db.mu.Lock()
    if db.closed {
        db.mu.Unlock()
        return nil, errDBClosed
    }
    // Check if the context is expired.
    select {
    default:
    case <-ctx.Done():
        db.mu.Unlock()
        return nil, ctx.Err()
    }
    lifetime := db.maxLifetime

    // Check to see if there are idle connections and if so, use them directly
    numFree := len(db.freeConn)
    if strategy == cachedOrNewConn && numFree > 0 {
        // Take out data first
        conn := db.freeConn[0]
        // Copy the array, remove the first connection
        copy(db.freeConn, db.freeConn[1:])
        db.freeConn = db.freeConn[:numFree-1]
        conn.inUse = true
        db.mu.Unlock()
        if conn.expired(lifetime) {
            conn.Close()
            return nil, driver.ErrBadConn
        }
        return conn, nil
    }

    // Determine if the maximum number of connections is exceeded 
    if db.maxOpen > 0 && db.numOpen >= db.maxOpen {
        // Create a chan 
        req := make(chan connRequest, 1)
        // Get the next request as the key in the map
        reqKey := db.nextRequestKeyLocked()
        db.connRequests[reqKey] = req
        db.mu.Unlock()

        // Timeout the connection request with the context.
        select {
        case <-ctx.Done():
            // Remove the connection request and ensure no value has been sent
            // on it after removing.
            db.mu.Lock()
            delete(db.connRequests, reqKey)
            db.mu.Unlock()
            select {
            default:
            case ret, ok := <-req:
                if ok {
                    db.putConn(ret.conn, ret.err)
                }
            }
            return nil, ctx.Err()
        // Get data from req chan if not cancelled Blocker has been waiting for conn data to come in
        case ret, ok := <-req:
            if !ok {
                return nil, errDBClosed
            }
            // Judgement timeout 
            if ret.err == nil && ret.conn.expired(lifetime) {
                ret.conn.Close()
                return nil, driver.ErrBadConn
            }
            return ret.conn, ret.err
        }
    }
    
    db.numOpen++ // optimistically
    db.mu.Unlock()
    // Call driver's Open method to establish a connection
    ci, err := db.driver.Open(db.dsn)
    if err != nil {
        db.mu.Lock()
        db.numOpen-- // correct for earlier optimism
        db.maybeOpenNewConnections()
        db.mu.Unlock()
        return nil, err
    }
    db.mu.Lock()
    dc := &driverConn{
        db:        db,
        createdAt: nowFunc(),
        ci:        ci,
        inUse:     true,
    }
    db.addDepLocked(dc, dc)
    db.mu.Unlock()
    return dc, nil
}

Release Connection Method

// Release Connection
func (db *DB) putConn(dc *driverConn, err error) {
    db.mu.Lock()
    if !dc.inUse {
        if debugGetPut {
            fmt.Printf("putConn(%v) DUPLICATE was: %s\n\nPREVIOUS was: %s", dc, stack(), db.lastPut[dc])
        }
        panic("sql: connection returned that was never out")
    }
    if debugGetPut {
        db.lastPut[dc] = stack()
    }
    // Settings are already in use
    dc.inUse = false

    for _, fn := range dc.onPut {
        fn()
    }
    dc.onPut = nil
    // Determine if there is a connection error 
    if err == driver.ErrBadConn {
        db.maybeOpenNewConnections()
        db.mu.Unlock()
        dc.Close()
        return
    }
    if putConnHook != nil {
        putConnHook(db, dc)
    }
    // Call method to release connection
    added := db.putConnDBLocked(dc, nil)
    db.mu.Unlock()
    // Determine if dc is off if not added to idle list
    if !added {
        dc.Close()
    }
}

func (db *DB) putConnDBLocked(dc *driverConn, err error) bool {
    if db.closed {
        return false
    }
    if db.maxOpen > 0 && db.numOpen > db.maxOpen {
        return false
    }
    // If the waiting chan list is greater than 0 
    if c := len(db.connRequests); c > 0 {
        var req chan connRequest
        var reqKey uint64
        // Get chan and key in map
        for reqKey, req = range db.connRequests {
            break
        }
        // Remove chan from list 
        delete(db.connRequests, reqKey) // Remove from pending requests.
        if err == nil {
            dc.inUse = true
        }
        // Pass the connection into chan to continue with the previously blocked fetch function
        req <- connRequest{
            conn: dc,
            err:  err,
        }
        return true
    } else if err == nil && !db.closed && db.maxIdleConnsLocked() > len(db.freeConn) {
        // If there is no waiting list, put the connection in the free list
        db.freeConn = append(db.freeConn, dc)
        db.startCleanerLocked()
        return true
    }
    return false
}

There are many ways to implement connection pooling, using chan in the database/sql package to block waiting lists using map records and wait until a connection is released before passing chan into the waiting list to return the connection without blocking it.
Previously, we saw Edgo blocking with a chan, then releasing it into the free list, passing in struct {}{} into the chan, and getting it from the free list when the program continues to get it.It also uses a chain table structure to store the free list.

summary

Database/sql is the encapsulation of mysql drivers, whereas Gorm is the re-encapsulation of database/sql.Let's make it easier to implement operations on mysql databases.