Golang is something I’am experimenting these days. I find the argument that it has better performance (almost twice as ASP.NET Core) very appealing. So I started to implement an identity server for all my future projects in golang. Why not? After all, you can’t learn something without actually using it in the real world.

Golang sure is interesting. It has many good features. It’s very easy to work with, and writing go just has a certain elegance to it. However, this article is about database in golang, so all the opinion will be saved for another article.

Generic Driver

You may decide to use any kind of database for you next project. It could be SQL(relational), NO-SQL, Object(MongoDB) and so on(Actually, these are the only ones I know). Golang standard library has a genric driver for all of them. For my project, I use SQL.

import (
	"database/sql"
)

Implementation Driver

I did say this is genric didn’t I? Being a generic driver means it can’t do much except being there and telling the compiler that you chose SQL as your database. Don’t get me wrong here. It’s not like the generic driver is not important here. It does everything on a higher level. If you want a database object, you call this generic driver. If you want to execute a SQL command, you call this generic driver.

var statement = `
	INSERT INTO users(firstname, lastname, email, password) 
	VALUES($1, $2, $3, $4);
`
	db.Query(statement, "hello", "world", "email", "123456")

However, you still need a specific driver for the actually implementation of the SQl database. Here we will choose postgresql. There are a few choices, we will choose this one.

import (
	"database/sql"
	_ "github.com/lib/pq"
)

Now when ever you execute a query, the generic driver will know to call this implementation.

Initialize DB

After you imported all the libraries, you may actually want to write some code. Don’t just rush into creating the tables just yet. A DB needs to be initialized and configured.

Configs

Postgresql will need some minimal config to connect and run. You can totally hard code them, but in this case, let’s use environmental variables as it’s more safe. Put these in your bashrc or zshrc or whatever the shell you use. Of course the port number and the names can be whatever you want.

export DBHOST=localhost
export DBPORT=5432
export DBUSER=admin
export DBPASS=admin
export DBNAME=postgres

Now that we have our env, we just need to read them in go and pass them into the db driver. I’m skipping some null checks here as it is one of the least elegant thing in go and showing them in tutorial like this is just not helping the readability.

func InitializeDatabase() {
	config := getConfig()
	var err error
	psqlInfo := fmt.Sprintf(`
		host=%s port=%s user=%s 
		password=%s dbname=%s sslmode=disable`,
		config[dbhost], config[dbport],
		config[dbuser], config[dbpass], config[dbname])
	db, err = sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}
	fmt.Println("Connected to PostgreSQL")
}

func getConfig() map[string]string {
	conf := make(map[string]string)
	host, _ := os.LookupEnv("DBHOST")
	port, _ := os.LookupEnv("DBPORT")
	user, _ := os.LookupEnv("DBUSER")
	password, _ := os.LookupEnv("DBPASS")}
	name, _ := os.LookupEnv("DBNAME")
	conf["DBHOST"] = host
	conf["DBPORT"] = port
	conf["DBUSER"] = user
	conf["DBPASS"] = password
	conf["DBNAME"] = name
	return conf
}

Realize here that InitializeDatabase() has it’s first letter capitalized. In golang, this means we are exporting this functioin. No, it doesn’t this is a convention and you have to manually export all the functions that are like this. Go will do this for you. In fact, if you have a function that has a lower case for the first letter of it’s name, it will be considered private. As in, this function will not be usable outside this package’s scope.

What Now?

We are done, right? Now that we finally initialized the database, and gave it a config, we can finally go and create tables and insert rows right? Almost, but not quite. You can for sure do the following.

type User struct {
	Firstname string `json:"firstname"`
	Lastname  string `json:"lastname"`
	Email     string `json:"email"`
	Password  string `json:"password"`
}

func main() {
	initDb()
	var createTable = `
		CREATE TABLE IF NOT EXIST users (
			firstname varchar(255), 
			lastname varchar(255), 
			email varchar(255), 
			password varchar(255)
		);
	`
	var insertUser = `
		INSERT INTO users(firstname, lastname, email, password) 
		VALUES($1, $2, $3, $4);
	`
	var getUser = `
		SELECT * 
		FROM users
		WHERE email=$1;
	`
	var user User
	db.Query(createTable)
	db.Query(insertUser, "hello", "world", "email", "123456")
	row := db.QueryRow(getUser, "email")
	err := row.Scan(
		&user.Firstname,
		&user.Lastname,
		&user.Email,
		&user.Password,
	)
	if err != nil {
		fmt.Println("fail to get user", err)
	}
	fmt.Println("User is ", user.Firstname)
}

But have you realized that we have not talked about what the db variable is? Well, it’s very important. This variable is the database handler from the generic driver. We will need to “declare” it every time we need to use it. I put “declare” in quote because we don’t realy create this variable. This is a variable in the driver, we only use it’s pointer to point to the exact same handler every time we use it. So… Put this line in every file you want to access db.

var db *sql.DB

Conclusion

Here we have it. Now you know how to use sql database in golang. This tutorial included how to import a generic driver, a implementation, config and initialize a db and how to execute a query. This a one of my first blog here. It sure feels good writing about things I’m interested in. Looking forward to how this blog thing turn out for me.