Building Web Server with Go - Part 9

· ☕ 8 min read
This is part 9 of 15 part series on building web server with go.
Checkout https://www.gophersumit.com/series/web-server/ for more.

database

Connecting to SQL database one of the common task for web applications. Let's see how we can work with SQL databases in Go.

database/sql

Go standard library comes with database\sql package. This provides Generic interface around SQL database. For using any sql database with Go (see list here : https://github.com/golang/go/wiki/SQLDrivers), there are 2 packages required, Generic database/sql and database driver for SQL database we want to use.

setting up project for MySQL

Lets install MySQL if not already installed

$ sudo apt install mysql-server

Let's also create new database and table inside it.

Creating database

Run the script to create a new database named webdb

connect to MySQL

    $ sudo mysql

create database

1
2
3
-- Create a new `webdb` database.
CREATE DATABASE webdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE webdb;

create database user

1
2
3
4
CREATE USER 'webuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, CREATE, ALTER ON webdb.* TO 'webuser'@'localhost';

ALTER USER 'webuser'@'localhost' IDENTIFIED BY 'webpassword';

create table for storing a post

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Create a `posts` table.
CREATE TABLE posts (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
content TEXT NOT NULL,
created DATETIME NOT NULL,
expires DATETIME NOT NULL
);
-- Add an index on the created column.
CREATE INDEX idx_posts_created ON posts(created);

Let's also insert some data in posts

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
INSERT INTO posts (title, content, created, expires) VALUES (
'net http package',
'Package http provides HTTP client and server implementations.',
UTC_TIMESTAMP(),
DATE_ADD(UTC_TIMESTAMP(), INTERVAL 365 DAY)
);
INSERT INTO posts (title, content, created, expires) VALUES (
'database sql package',
'Package sql provides a generic interface around SQL (or SQL-like) databases.The sql package must be used in conjunction with a database driver',
UTC_TIMESTAMP(),
DATE_ADD(UTC_TIMESTAMP(), INTERVAL 365 DAY)
);

Let's verify we are able to login with new user setup for webdb database.

mysql -D webdb -u webuser -p
mysql> select id,title from posts;
+----+----------------------+
| id | title                |
+----+----------------------+
|  1 | net http package     |
|  2 | database sql package |
+----+----------------------+
2 rows in set (0.00 sec)

Our database is ready to be consumed from Go!

Setting up Go project

Let's create a new project dbproject.

$ mkdir dbproject
$ cd dbproject
$ touch main.go
$ go mod init gophersumit.com/dbproject

For working with MySQL, we will need mysql driver which is available at https://github.com/go-sql-driver/mysql/

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

We can see mysql is installed as our project dependency in module file.

$ cat go.mod 
module gophersumit.com/dbproject

go 1.13

require github.com/go-sql-driver/mysql v1.5.0 // indirect

Connecting to database

For connecting to a database, we need to use sql.Open() function.

func Open(driverName, dataSourceName string) (*DB, error)
    Open opens a database specified by its database driver name and a
    driver-specific data source name, usually consisting of at least a database
    name and connection information.

For MySQL server, we will need

1
2
3
db, err := sql.Open("mysql", "webuser:webpassword@/webdb?parseTime=true")
if err != nil {
}

But how will Go know we are using MySQL and how to load driver for it?
Before using MySQL, we need to register MySQL driver. Accepted practice for SQL driver registration is to use init() function for registration.

1
2
3
4
5
// source https://github.com/go-sql-driver/mysql/blob/b66d043e6c8986ca01241b990326db395f9c0afd/driver.go
func init() {
	sql.Register("mysql", &MySQLDriver{})
}

To make this function execute before main() we need to import github.com/go-sql-driver/mysql package. Go will not let us import package if we are not using it directly. We will need to use blank identifier to inform Go that we are importing this for side-effects (driver registration) and will not be using it directly.

Our main.go should look like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
package main

import (
	"database/sql"
	"fmt"
	"log"

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

func main() {
	db, err := sql.Open("mysql", "webuser:webpassword@/webdb")
	if err != nil {
		log.Fatalln(err)
	}
	err = db.Ping()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println("successfully connected to database")
	defer db.Close()
}

If we run this code, we should be able to see success message if everything was configured properly.

$ go run main.go 
successfully connected to database

Setting http server

Let's setup web server to work with MySQL over http.

Let's first define a struct type which we will use to store table data returned from Post. We are just interested in getting id, title and content.

1
2
3
4
5
6
7
//Post :  A record in Posts table
type Post struct {
	ID      int
	Title   string
	Content string
}

Let's also define type which should provide us with available database connection.

1
2
3
4
5
// PostDB type to wrap database connection
type PostDB struct {
	DB *sql.DB
}

Let's define a method which will return all the posts in MySQL.
We are executing select statement and scanning each row in our struct.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// GetAllPosts Get All Posts possible only top x in real world
func (p *PostDB) GetAllPosts() ([]*Post, error) {
	stmt := `SELECT id, title, content FROM posts`

	rows, err := p.DB.Query(stmt)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	posts := []*Post{}

	for rows.Next() {
		p := &Post{}

		err = rows.Scan(&p.ID, &p.Title, &p.Content)
		if err != nil {
			return nil, err
		}
		posts = append(posts, p)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return posts, nil
}

Now lets define our custom type which will have our http handlers as well as PostDB.

1
2
3
type application struct {
	db *PostDB
}

We need to provide db connection that we have created earlier to this type while creating a new type of application

1
2
3
4
5
6
7
db, err := sql.Open("mysql", "webuser:webpassword@/webdb")
// code omitted
app := application{
		&PostDB{
			DB: db,
		},
	}

And finally we need to define http handler on application type which will use db connection to get posts.

1
2
3
4
5
6
7
8
9
func (app *application) getPosts(w http.ResponseWriter, r *http.Request) {
	posts, err := app.db.GetAllPosts()
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
	}
	for _, post := range posts {
		fmt.Fprintf(w, "%v\n", post)
	}
}

Complete code :

go.mod

module gophersumit.com/dbproject

go 1.13

require github.com/go-sql-driver/mysql v1.5.0

main.go

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package main

import (
  "database/sql"
  "fmt"
  "log"
  "net/http"

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

type application struct {
  db *PostDB
}

func main() {
  db, err := sql.Open("mysql", "webuser:webpassword@/webdb")
  if err != nil {
      log.Fatalln(err)
  }
  err = db.Ping()
  if err != nil {
      log.Fatalln(err)
  }
  fmt.Println("successfully connected to database")
  defer db.Close()
  app := application{
      &PostDB{
          DB: db,
      },
  }
  mux := http.NewServeMux()
  mux.HandleFunc("/posts", app.getPosts)
  fmt.Println("starting server on port 3000")
  http.ListenAndServe(":3000", mux)
}

func (app *application) getPosts(w http.ResponseWriter, r *http.Request) {
  posts, err := app.db.GetAllPosts()
  if err != nil {
      w.WriteHeader(http.StatusInternalServerError)
  }
  for _, post := range posts {
      fmt.Fprintf(w, "%v\n", post)
  }
}

post.go

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package main

import "database/sql"

//Post :  A record in Posts table
type Post struct {
  ID      int
  Title   string
  Content string
}

// PostDB type to wrap database connection
type PostDB struct {
  DB *sql.DB
}

//GetAllPosts Get All Posts possible only top 10
func (p *PostDB) GetAllPosts() ([]*Post, error) {
  stmt := `SELECT id, title, content FROM posts`

  rows, err := p.DB.Query(stmt)
  if err != nil {
      return nil, err
  }
  defer rows.Close()

  posts := []*Post{}

  for rows.Next() {
      p := &Post{}

      err = rows.Scan(&p.ID, &p.Title, &p.Content)
      if err != nil {
          return nil, err
      }
      posts = append(posts, p)
  }

  if err = rows.Err(); err != nil {
      return nil, err
  }

  return posts, nil
}

curl

1
2
3
$ curl http://localhost:3000/posts
&{1 net http package Package http provides HTTP client and server implementations.}
&{2 database sql package Package sql provides a generic interface around SQL (or SQL-like) databases.The sql package must be used in conjunction with a database driver}

This is simple select operation. We can similarly implement insert, update and delete sql operations.


Sumit
WRITTEN BY
Sumit
Gopher


What's on this Page