Golang CRUD MySQL

Installation.

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

Connection string. I recommend using utf8mb4 else you will not be able to save emoji characters.

func my_db() (db *sql.DB) {
username := "root"
password := "password"
hostname := "db.mysql.com"
database := "demo"
charset := "charset=utf8mb4"
collation := "collation=utf8mb4_general_ci"
db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?%s&%s",username,password,hostname,database,charset,collation))
check(err)
return db
}

Create.

func create(db *sql.DB, name, address, phone string) {
sql, e := db.Prepare("INSERT INTO contacts(name, address, phone) VALUES (?, ?, ?)")
check(e)

res, e := sql.Exec(name, address, phone)
check(e)

id, e := res.LastInsertId()
check(e)

log.Println("Created. ID=", id)
}

The prepare statement uses DB resources to escape the values, it is heavier but safer. If you know that the values does not require escaping, you can do it with the lighter method.

func create(db *sql.DB, name, address, phone string) {
sql := fmt.Sprintf("INSERT INTO contacts(name, address, phone) VALUES ('%s', '%s', '%s')", name, address, phone)
res, e := db.Exec(sql)
check(e)

id, e := res.LastInsertId()
check(e)

log.Println("Created. ID=", id)
}

Read.

func read(db *sql.DB) []Contact {
var cs []Contact
rows, e := db.Query("select * from contacts")
check(e)

for rows.Next() {
var c Contact
e = rows.Scan(&c.Id, &c.Name, &c.Address, &c.Phone)
check(e)
cs = append(cs, c)
}
return cs
}

Update.

func update(db *sql.DB, id int, name string) {
sql, e := db.Prepare("UPDATE contacts SET name=? WHERE id=?")
check(e)

res, e := sql.Exec(name, id)
check(e)

a, e := res.RowsAffected()
check(e)

log.Println("Updated. Rows affected=", a)
}

Delete.

func delete(db *sql.DB, id int) {
sql, e := db.Prepare("DELETE FROM contacts WHERE id=?")
check(e)

res, e := sql.Exec(id)
check(e)

a, e := res.RowsAffected()
check(e)

log.Println("Deleted. Rows affected=", a)
}

Main.

func main() {
db := my_db()
defer db.Close()

e := db.Ping()
check(e)

create(db, "Bill Gates", "Seattle", "+1 425-882-8080")
create(db, "Elon Musk", "Texas", "+1 512-833-6321")

contacts := read(db)
for z:=0; z<len(contacts); z++ {
c := contacts[z]
log.Println(c.Id, c.Name, c.Address, c.Phone)
}

update(db, 1, "Jeff Bezos")
delete(db, 2)

contacts = read(db)
for y:=0; y<len(contacts); y++ {
c := contacts[y]
log.Println(c.Id, c.Name, c.Address, c.Phone)
}
}

What the main function is doing.
1. Initialize the DB connection.
2. Ping the DB to check if the connection is valid.
3. Insert 2 rows into the table.
4. List all the rows in the table.
5. Update row 1.
6. Delete row 2.
7. List all the rows in the table.

Output.

The full source.

< Back to all the stories I had written

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Kevin FOO

Kevin FOO

A software engineer, a rock climbing, inline skating enthusiast, a husband, a father.