Working with Go

MySQL Database

This is an example file for working with databases. Go provides an abstract datalayer using the database/sql standard library. You need to load the specific driver your database first, than working with it is relatively the same.

This example uses the go-sql-driver/mysql.

Example Tables

You can create example table and data with the sql query below

DROP TABLE IF EXISTS `posts`;
 
CREATE TABLE `posts` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `posts` (`id`, `title`, `body`)
VALUES
    (1,'Hello World','The content of the hello world'),
    (2,'Hello Second World','The content of the hello second world');

Example Program

// standard main package
package main
 
// Necessary packages to work with mysql databases
import (
    "database/sql"
    "fmt"
 
    _ "github.com/go-sql-driver/mysql"
)
 
// dbConn connects to the database
func dbConn() (db *sql.DB) {
 
    dbDriver := "mysql"
    dbUser := "root"
    dbPass := "root"
    dbName := "godb"
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
        panic(err.Error())
    }
    return db
}
 
// Post is the key to connect database to the program
type Post struct {
    Id    int
    Title string
    Body  string
}
 
// getAll gets all the records from database
func getAll() {
 
    db := dbConn()
 
    selDB, err := db.Query("SELECT * FROM Posts ORDER BY id DESC")
    if err != nil {
        panic(err.Error())
    }
 
    post := Post{}
    posts := []Post{}
 
    for selDB.Next() {
 
        var id int
        var title, body string
 
        err = selDB.Scan(&id, &title, &body)
        if err != nil {
            panic(err.Error())
        }
 
        post.Id = id
        post.Title = title
        post.Body = body
 
        posts = append(posts, post)
    }
 
    for _, post := range posts {
        fmt.Println(post.Title)
    }
 
    defer db.Close()
}
 
// getOne gets only one record from database
func getOne(postId int) {
 
    db := dbConn()
 
    selDB, err := db.Query("SELECT * FROM Posts WHERE id=?", postId)
    if err != nil {
        panic(err.Error())
    }
 
    post := Post{}
 
    for selDB.Next() {
 
        var id int
        var title, body string
 
        err = selDB.Scan(&id, &title, &body)
        if err != nil {
            panic(err.Error())
        }
 
        post.Id = id
        post.Title = title
        post.Body = body
 
    }
 
    fmt.Println("Post Title : " + post.Title)
    fmt.Println("Post Body  : " + post.Body)
 
    defer db.Close()
}
 
// add helps you to add new record to database
func add() {
 
    db := dbConn()
 
    title := "Hello Second World"
    body := "The content of the hello second world"
    insertQuery, err := db.Prepare("INSERT INTO Posts(title, body) VALUES(?,?)")
    if err != nil {
        panic(err.Error())
    }
 
    insertQuery.Exec(title, body)
 
    fmt.Println("ADDED: title: " + title + " | Body: " + body)
 
    defer db.Close()
 
}
 
// update helps you to update an existing record in the database
func update(postId int) {
 
    db := dbConn()
 
    title := "Hello 1 World"
    body := "The content of the hello 1 world"
    updateQuery, err := db.Prepare("UPDATE Posts SET title=?, body=? WHERE id=?")
    if err != nil {
        panic(err.Error())
    }
 
    updateQuery.Exec(title, body, postId)
 
    fmt.Println("UPDATED: title: " + title + " | Body: " + body)
 
    defer db.Close()
 
}
 
// delete helps you to delete an existing record in the database
func delete(postId int) {
 
    db := dbConn()
 
    deleteQuery, err := db.Prepare("DELETE FROM Posts WHERE id=?")
    if err != nil {
        panic(err.Error())
    }
 
    deleteQuery.Exec(postId)
 
    fmt.Println("DELETED")
 
    defer db.Close()
 
}
 
func main() {
 
    //add()
    //update(1)
    //delete(1)
    //getOne(1)
    getAll()
 
}