Golang CRUD MySQL

Table structure

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