Unexpected "no such host error" when closing/reopening MySQL connection in Go

I'm in the early stages of learning Go, and I'm trying to build familiarity with the ways that Go can interact with SQL databases. I've worked extensively with SQL databases, but almost never with ORMs.

As a simple first exercise in inserting/deleting from a database, I've written the function addCardTest() to add a row to a table in my MySQL database, and the function listCardsTest() to print the results of a simple query on that table. In the main() function, I try to run the two functions in that order. However, I receive an error on the second function I run:

panic: dial tcp: lookup rpi.local on 172.24.32.1:53: no such host

goroutine 1 [running]:
main.listCardsTest()
        /home/tmiku/go/src/mtg/mtg.go:23 +0x325
main.main()
        /home/tmiku/go/src/mtg/mtg.go:12 +0x1c
exit status 2

I found that this error goes away if I remove the "defer db.Close()" line from the first function I call. I'm surprised that this worked, and it leaves me with many questions about what throws this error in the first place.

• Under the hood, is there some concurrent work happening (i.e. listCardsTest() running before addCardsTest() finishes)? Is this to be expected when working with external databases in Go?
• Can a single Go program only create a single connection pool to any given database? What happens when I try to open another one? This makes sense as a limitation, but the "no such host" error seems like a bizarre/unintuitive way to enforce that.
• Should functions that access databases be written with the assumption that the connection is already open, and accept the database info as a parameter? Is it bad form to wrap the opening/closing of a database connection into a function that does other things? It feels like the error is pushing me in this direction, but I had trouble finding that advice documented anywhere obvious.

Full code below. The hostname of the MySQL database is rpi.local, excuse the hardcoded credentials (I'm learning).

package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
addCardTest()
listCardsTest()
}
func listCardsTest() {
db, err := sql.Open("mysql", "tmiku:pass@tcp(rpi.local:3306)/mtg")
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query("select cardid, name, manacost, type from Cards")
if err != nil {
panic(err)
}
defer rows.Close()
var id int
var name, manacost, cardtype string // need to initialize these first...
for rows.Next() {
err := rows.Scan(&id, &name, &manacost, &cardtype) // ...so Scan() has somewhere to put them.
if err != nil {
panic(err)
}
//print the results
fmt.Println(id, name, manacost, cardtype)
}
}
func addCardTest() {
//open the database, db is a pointer to the db object
db, err := sql.Open("mysql", "tmiku:pass@tcp(rpi.local:3306)/mtg")
if err != nil {
panic(err)
}
defer db.Close() //REMOVED THIS LINE, FIXED ERROR
//run our insert
result, err := db.Exec("insert into mtg.Cards (name, manacost, ruletext, type, subtype, power, toughness) values ('Stormchaser Drake', '1U', 'Flying\nWhenever Stormchaser Drake becomes the target of a spell you control, draw a card.', 'Creature', 'Drake', 2, 1)")
if err != nil {
panic(err)
}
lastId, _ := result.LastInsertId()
fmt.Println("Added id ", lastId)
}

I've refactored a little bit your code to let you better understand how I usually write this piece of code. First, let me present the code, then, I'll walk you through all the relevant sections. For the sake of the demo, all the code has been written into a single file called main.go:

package main

import (
	"database/sql"
	"fmt"

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

type card struct {
	Id   int
	Name string
}

func addCardTest(sql *sql.DB, card card) error {
	res, err := sql.Exec(`INSERT INTO cards (name) VALUES (?)`, card.Name)
	if err != nil {
		return fmt.Errorf("err while inserting a card: %v", err)
	}
	lastId, err := res.LastInsertId()
	if err != nil {
		return fmt.Errorf("err while getting the last ID: %v", err)
	}
	fmt.Println("inserted card with id:", lastId)
	return nil
}

func listCardsTest(sql *sql.DB) ([]card, error) {
	rows, err := sql.Query("select id, name from cards")
	if err != nil {
		return nil, fmt.Errorf("err while getting cards: %v", err)
	}
	defer rows.Close()
	var currentCard card
	res := make([]card, 0)
	for rows.Next() {
		if err := rows.Scan(&currentCard.Id, &currentCard.Name); err != nil {
			return nil, fmt.Errorf("err while scanning a row: %v", err)
		}
		res = append(res, currentCard)
	}
	return res, nil
}

func main() {
	dsn := "root:root@tcp(127.0.0.1:3306)/card"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()
	if err := addCardTest(db, card{Name: "lorem ipsum"}); err != nil {
		panic(err)
	}
	if err := addCardTest(db, card{Name: "second card"}); err != nil {
		panic(err)
	}
	cards, err := listCardsTest(db)
	if err != nil {
		panic(err)
	}
	for _, v := range cards {
		fmt.Println(v)
	}

}

Now, let's take a closer look at each section.
> Please note that to run the MySQL instance through Docker, I used this command:
docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql.

The addCardTest function

Here, there are a couple of improvements:

• The function parameters. You should expect a pointer to an SQL client (more on that later) and a card to insert
• You should use the method Exec meant to be used for commands (e.g. the INSERT statement) instead of the Query
• You should not hard-code data even if it's a sample problem or a POC
• Always check for errors

Now, let's switch to the reading logic.

The listCardsTest function

Here, the code is more or less the same as before.

The main function

Within this function, you should instantiate a handle towards the MySQL DB that has to be used from now on. You only need to invoke the function sql.Open() once and defer a single invocation to the method Close(). All of this has to be done within the main function. Then, you should expect a parameter of this kind in your DB-related functions (e.g. the listCardsTest and the addCardTest mentioned above).

I strongly suggest you check this link that explains how the sql.DB struct is supposed to be used.

Let me know if my program helps you to solve the issue, thanks!