sql.Open() doesn't open anything

#beginners #database #go #sql

We all know the basic drill of connecting a db in Go:

func main(){
//...
dsn := flag.String("dsn", "web:password@/demoProject", "MySQL data source name")
    flag.Parse()
db, err := openDB(*dsn)
    if err != nil {
        errorLog.Fatal(err)
    }
defer db.Close()
//...
}
 func openDB(dsn string) (*sql.DB, error) {
        db, err := sql.Open("mysql", dsn)
        if err != nil {
            return nil, err
        }
        if err = db.Ping(); err != nil {
            return nil, err
        }
        return db, nil
    }

but do u know what happens under the hood, u might intuitively think its pretty much clear that the sql.Open opens the connection and Ping() just sends a request at db to verify it , right? no- not even close..

Lets log the results at each step and understand what actually happens at each step,

package main

import(...)

func main(){
    dsn := flag.String("dsn", "web:password@/demoProject", "MySQL DSN")
    flag.Parse()

    db, err := openDB(*dsn)
    if err != nil {
        errorLog.Fatal(err)
    }

    defer db.Close()

//...
}
func openDB(dsn string) (*sql.DB, error) {

    db, err := sql.Open("mysql", dsn)
    fmt.Println("After sql.Open — db stats:", db.Stats())
    if err != nil {
        return nil, err
    }

    fmt.Println("Calling db.Ping()...")
    if err = db.Ping(); err != nil {
        fmt.Println("Ping failed:", err)
        return nil, err
    }
    fmt.Println("Ping successful!")
    fmt.Println("After Ping — db stats:", db.Stats())

    return db, nil
}

func openDB(dsn string) (*sql.DB, error)

it accepts a string, and returns:

1. a pointer to the DB struct

2.error

Now I have used db.Stats() native method on *sql.DB struct to log statistics of the db at each step:

func openDB(...)(...){

    db, err := sql.Open("mysql", dsn)
    fmt.Println("After sql.Open — db stats:", db.Stats())
    if err != nil {
        return nil, err
    }

    fmt.Println("Calling db.Ping()...")
    if err = db.Ping(); err != nil {
        fmt.Println("Ping failed:", err)
        return nil, err
    }
    fmt.Println("Ping successful!")
    fmt.Println("After Ping — db stats:", db.Stats())
}

db.Stats() returns a snapshot of the statistics using the DBStats struct

so lets run our server and .....we get this:

Notice how even after the sql.Open() function, all the fields of the DBStats struct remains 0,

and after the db.Ping() is successful:

we see two 1's , {0 1 0 1 0 0s 0 0 0} ,

the first 1 denotes the OpenConnections field and the second 1 denotes the Idle field of the DBStats struct - Idle - meaning 1 connection is open and is sitting idle in the pool, waiting for the first query..

So question arises: "Whats the use of Open() function?", well it does the most important things:

1. Validates the DSN (connection string)

2. Checks if the database driver has been registered or not.

So to sum up this is the flow diagram:

Thanks! See you in the next one:)

DEV Community

sql.Open() doesn't open anything

Top comments (0)