Hi everyone.
for this tutorial, we will try to explain how to connect to a database and do CRUD operations on your database, meaning you will be able to create, read, update, and delete data from a database using Go.
Plan:
Environment Set-up and Requirements:
- we will be using Visual Studio Code Editor but you can use whatever you want.
- we will be connecting to a MySQL database so make sure that is all set up.
- you also need to have to go installed on your PC, if not download it here https://golang.org/dl/.
- now all we need is your Focus.
**Now that the environment is all set up. Let's make our server.** That's it, we will keep things very simple without extra libraries or frameworks.
1-making a simple Hello World to test if it’s working:
then add a file named server.go to the root of your folder.
now open the terminal to create our go.mod file.
It is much like package.json in javaScript to handle and organize your packages so don’t get scared(this is optional).
run this command to create the go.mod file:
go mod init my-server
now you will see the file go.mod created and initiated for you.
- Let’s test if we are all set up with a simple Hello World! app.
add this code to your server.go file:
package main import ( "fmt" ) func main(){ /// remember imported functions start with capital letter! fmt.Println("Hello World!") }
GREAT!
let’s test that.
back to the terminal run this command:
go run server.go
and you will get that nice Hello world! message in the terminal
2-making a go server:
now let us go with the advanced stuff.
We need to get our server to listen on a port of our choice.
note: if you are trying to deploy your app u need to set the port to the os environment port:
port := os.Getenv("PORT")
http.ListenAndServe(":" +port, nil)
Here is the code to get our server to listen on port 8085:
package main import ( "fmt" ) func main() { ///nil is Null in go '_' I know! http.ListenAndServe(":8085", nil) }
and that’s it ur server Is listening on port 8085, pretty simple!
to check that u need to go back to the terminal and run:
go run server.go
now navigate to http://localhost:8080/
you will see that the server is running.
The next step would be to create our home page:
to do so we need to create a handler to handle the home endpoint.
func home(w http.ResponseWriter, r *http.Request){ /// print this using the response writer. fmt.Fprintf(w, "Hello Home Page!") }
now in the main function write before http.ListenAndServe we call our home Handler function.
func main(){ /**///set home function to handle (home endpoint). http.HnaldeFunc("/", home) http.ListenAndServe("8085",nil) }
the whole would look like this:
Now if you check the “not found message” is gone and your nice message appears.
3- Connecting to a database in go:
→ for this tutorial we will use this table “users”, and the database name is “world”.
First, we need to import our go-sql driver from github.com/go-sql-driver/mysql:
Here is the whole code to connect to a MySql database in go:
we declare a function connect which establishes the connection to our MySql database.
and we use
defer db.Close() to postpone closing the database tell current function’s ending.
sql.Open() takes two arguments 1-sql driver name 2-database credentials as a string.
in the example above:
- root is the user.
- 8520 is the password.
- 127.0.0.1:3306 is the port of our server (default).
-
and world is the name of our database.
now you can try:
go run server.go
to see that you are successfully connected to the database.
a- inserting to a database in go:
now will make another handler to insert data into our database table.
now we add this function to handle our “insert” endpoint in the main function.
now just run the server again with
go run server.go in the terminal
→ now navigate to http://localhost:8085/ you will see the home page.
→ Then navigate to http://localhost:8085/insert and check your database.
Checking your database you will find the values are inserted:
b- deleting in a database in go:
now will make another handler to delete data from our database table.
now we again add this function to handle our “delete” endpoint in the main function.
after hitting the http://localhost:8085/delete Endpoint we will see that id 1 is deleted 
c- retreiving data from a database in golang:
again we will make another handler to get/ retrieve data from our database table.
this time we have to declare a struct of type users containing all our table columns as fields.
Then in our handler, we use db.Query() which returns more than a row than our rows.Next() to iterate over rows returned by db.Query() and scan them in myUser variable. rows.Next keeps returning true till the last row, then it returns False.
and we finally break out of the loop.
if you rerun the app again and visit: http://localhost:8085/retreive
you will see such results:
note: to retrieve a single row it would much simpler to use db.QueryRow(“select single row”), followed by using .Scan() and scan it to your struct variable.
d- Updating data in a database table in go:
Deleting registrations from your database is as simple as deleting from it.
again we will declare our update handler and call it in the main function.
if you run this program and hit the Update endpoint “http://localhost:8085/update”, the user with id=2 gets updated.
then we check using the retrieve endpoint “http://localhost:8085/retreive”, and we get new values for the user with id 2.
FULL Code:
package main import ( "database/sql" "encoding/json" "fmt" "log" "net/http" _ "github.com/go-sql-driver/mysql" ) var db *sql.DB var err error func home(w http.ResponseWriter, r *http.Request) { fmt.Fprintf(w, "Hello Home Page!") } func insert(w http.ResponseWriter, r *http.Request) { fmt.Fprintf(w, "isnert endpoint got hit!") db.Exec("insert into users values(default, 'xxx','xxx@xxx','xxxpassword','user')") // for fixed exection name := "myname" email := "myemail@xxx" password := "mypassword" accType := "admin" //you can also use variables for a custom execution db.Exec("insert into users values(default, ?,?,?,?)", name, email, password, accType) } func delete(w http.ResponseWriter, r *http.Request) { fmt.Fprintf(w, "delete endpoint got hit!") id := 1 db.Exec("delete from users where id = ?", id) } type users struct { ID int Name string Email string Password string AccType string } func retreive(w http.ResponseWriter, r *http.Request) { var myUser users id, id2 := 2, 3 rows, err := db.Query("select * from users where id=? or id = ?", id, id2) if err != nil { log.Fatal(err) } defer rows.Close() for rows.Next() { rows.Scan(&myUser.ID, &myUser.Name, &myUser.Email, &myUser.Password, &myUser.AccType) json.NewEncoder(w).Encode(myUser) } } func update(w http.ResponseWriter, r *http.Request) { fmt.Fprintf(w, "update endpoint got hit!") chosenID := 2 newUserName := "New User" newUserPassword := "New Password" db.Exec("update users set user_name=?, user_Password=? where id=?", newUserName, newUserPassword, chosenID) } func connect() { db, err = sql.Open("mysql", "root:8520@tcp(127.0.0.1:3306)/world") if err != nil { fmt.Println(err.Error()) } else { fmt.Println("successfully connected to database.") } } func main() { defer db.Close() connect() http.HandleFunc("/", home) http.HandleFunc("/insert", insert) http.HandleFunc("/delete", delete) http.HandleFunc("/retreive", retreive) http.HandleFunc("/update", update) fmt.Println("listening on port 8085") http.ListenAndServe(":8085", nil) }
Hope this was helpful 
The next lesson would be getting values from the client-side using URL params, and getting them from HTML forms, so stay tuned!
