2020 – My Year in Books 📚

I don’t think there has been a better year than 2020 to read. With most of us confined to our homes at some point during the year due to pandemic, books (and Netflix!) have become more popular than ever.

In this post, I’m reviewing all the books I have read in the year. Hopefully you’ll enjoy these quick reviews, plus I’ve also added a favorite quote from each.

In no particular order- let’s go!

Atomic Habits

Atomic Habits by far is the best book I’ve read in this year (hardcover). Author, James Clear, makes the argument that it’s the systems, not goals, that we should focus. Here’s his explanation “In professional sport both winning and losing teams usually share the same goal (to win!), however it is mostly about the system (routine) that each team follow, that makes the difference between winning and losing”. Here are my key takeaways

  • The concept of improving by 1% every day
  • Four laws of behavior change – make it obvious, make it attractive, make it easy, and make it satisfying
  • There are 3 layers of changing habits – changing outcomes, changing process, changing identity

As for me, I’m a huge believer in philosophy of “making tiny changes, for big results” and James Clear’s ideas made a lot of sense to me. I would 100% recommend this book to almost anyone, who wants to change their life without feeling overwhelmed.

“If you can get 1% better each day for one year, you’ll end up 37 times better by the time you’re done.”

Make Your Bed

Make Your Bed is a book based on General McRaven’s commencement speech in 2014 at University of Texas. While the the rules shared in the book are simple, short, clear, and straight forward, what’s captivating are the stories Admiral shares, including the anecdotes from his Navy Seal training that left me in awe. He narrates some key lessons from his life, learned the “hard-way” during seal trainings that almost anyone can apply to face challenges in their life. Also, if you haven’t seen the YouTube video of the speech, you can watch and listen it here. Overall, great stories on military training, coupled with excellent life lessons.

“Start each day with a task completed. Make your bed.”

Tribal Leadership

Tribal Leadership explains the various ways people function within an organization and teaches you how to lead change and improve your company’s culture. This book has an interesting take on social interactions and relationships. The premise is simple, there are 5 stages in which people exist, as follow:

1. Life sucks
2. My life sucks
3. I’m great
4. We’re great
5. Life is great

Overall, it’s great read on leadership. It’s crazy how accurately the 5 stages given in the book, reflect a lot about the companies I have worked. The author also shares practical tips that one can use to create successful teams and when the time comes you’ll know exactly how to motivate them. A must read.

“You don’t have to be in charge or powerful or pretty or most-connected to be a leader. All you need is to be COMMITTED.”

The Phoenix Project

Some people are lucky to find books that change their life. While I’m yet to find my life-changing novel, I do come across books that make me think, question my beliefs and push me to learn. The Project Phoenix is one such book for me.

Written by Gene Kim, George Spafford, and Kevin Behr, the book is about a large company’s transformation into a DevOps culture. Transformation driven not just to look cool, but as a necessity for the survival of the company. The Phoneix project is a gripping read that captures brilliantly the dilemmas faced by the organizations that depend on IT, and offers real-world solutions. The authors reminds us, ‘It is necessary to change for survival.’

“Improving daily work is even more important than doing daily work.”  

The Unicorn Project

The Unicorn Project is a sequel to The Phoenix Project (actually it’s not). Although, the two books fit together on the premise of digital transformation, they aren’t necessarily to be read together. The story this time dives deep into the developer’s world—Maxine, who’s a talented lead developer and architect, blamed for an outage and exiled on the Phoenix project. Throughout her journey, she partners with a team of corporate rebels, and together they confront their legacy and change-averse processes and apply the five ideals to lead a positive and lasting business, technology and cultural transformation. Not that the book is all about developers, debugging, continuous integration or unit tests, but it is very much the focus, at least for the first good half of the book. In the DevOps age, the core development topics were earlier left to developers are now in the center of discussion up to CIOs and sometimes CEOs. Gene Kim’s relatable writing style keeps the pages turning quite easily.

“Like all engineers, she secretly loves hearing disaster stories … as long as she doesn’t have the starring role.”

I must admit, these two books “The Project Phoenix” and “The Unicorn Project” had a profound impact on me during this year. I picked them while being stuck in abrupt travel ban (clearly the lowest point for me in the year). I read over #1000 pages in a week, that’s averaging 150 pages per/day, way above my usual reading rate. Also, these aren’t typical motivational books either, but for some reason, they just helped to stay focused on my work and reminded me, ‘This too shall pass‘.

Software Developer Life

Software Developer Life  is a refreshingly honest and personal book – pretty simple and to the point. This book offers good advice on how to be a good engineer in the real world. Author (David) shared technical and non-technical stories that his friends and he encountered in the past, and through these stories he proves that that only acquiring technical skill is not enough. One also needs to work on non-technical skills, such as collaboration, communication and empathy. He also shares real world tips on learning fundamentals, avoid arrogance, choosing your workplace, handling mid-career crisis and managing your boss. Overall, fun easy read and worth it if you’re considering a field as a software engineer.

“For any field, the people at the highest level are the ones who deeply understand FOUNDATION; that’s why they can break it sometimes.”

the Self-taught programmer

the self-taught programmer is the ideal book for anyone new to programming. I’ve read a few books on self-learn programming including Java & Python, even through with a decade long experience in technology, I would sometimes struggle to wrap my head around the code samples and exercises. Cory (the author) went above and beyond in providing examples throughout all the lessons and their real world application to complement the reading. His technique in breaking down complex technical topics in simple terms that anyone can understand, is what really makes this book shine. I highly recommend this book to anyone wanting to learn to code or looking for a great starter to Python.

“Life is too short to have insecurities about where we got our education. Your passion, curiosity and hard-work is all you need to be successful.”

If you made it this far, bravo! Thanks for reading through my reviews and I can’t wait to see you share what you’ve read. Leave a comment for your favorite books, podcasts, and reading goals for 2021.

So long 2020 😷

SQL Injection and Preventing them in your Golang app

SQL injection!… Is it really a thing?

SQL injection is a code injection technique that is capable of destroying your database. It is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. (e.g. to dump the database contents to the attacker)

How SQL Injection Works?

SQL injection is a hacking technique that’s been around since at least 1998. It takes advantage of two factors for success: First, web applications often ask users for data; Second, those applications tend to take the user-supplied data and pass it to the database as part of an instruction. Put them together with no code-based guardrails, and a criminal can run the application far off into the weeds.

(image from xkcd.com, with “copy and share” license described here:  License)

Like in above case, Bobby’s school lose all their student records when Bobby’s father decided to name their son as “Robert’); DROP TABLE students;” and used that as an input parameter in online school’s application form.

How can we avoid it in Golang?

In this post, we’ll learn how to avoid SQL Injection attack in our Golang code by sanitizing database inputs.

To be able to achieve our objective i.e. Avoid SQL Injection in Golang application, there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with some references

Install PostgreSQL, setup an instance and create a test database – https://www.postgresqltutorial.com/install-postgresql/

Install Go and configure workspace – https://www.callicoder.com/golang-installation-setup-gopath-workspace/

For this tutorial, I’m using Postgres 12 and Go 1.13.xx

Step 1. SQL Table definition

We’ll use test table EMP with some test data

-- create a sample table EMP
CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT CHECK (sal>0),
  email TEXT UNIQUE NOT NULL 
);

-- insert test data
INSERT INTO public.emp (ename, sal, email)
values
('Smith', 1400, 'smith@acme.com'),
('Allen', 2000, 'allen@acme.com'),
('Jones', 3000, 'jones@acme.com'),
('Blake', 4000, 'blake@acme.com');

Step 2. Basic select statement

Let’s just assume that we want to SELECT employee record from table EMP by passing EMPNO column filter in WHERE clause.

q := fmt.Sprintf("SELECT ename FROM emp where empno=%s;", "7")
row := db.QueryRow(q)

The empno WHERE clause is substituted with value 7 and all rows relating to that employee number are returned. No big deal. But what happens if someone like Bobby’s Father inputs 7); TRUNCATE TABLE emp;--sql_injection as variable input value.

q := fmt.Sprintf("SELECT ename FROM emp where empno=%s;", "7; Truncate Table emp;--sql_injection!")
row := db.QueryRow(q)

Here:

  • Since empno contain  ); the VALUES argument is closed
  • And postgres continues executing the next line i.e. the text that follows TRUNCATE TABLE emp and truncates the whole table emp
  • Finally the — at the end comments out the remaining SQL, essentially ignoring the rest of the original code and making sure no error occurs.

Step 3. How to deal with this in Golang

The database/sql package from the standard library provides methods for executing parameterized queries either as prepared statements or as one-off queries. For example, you might want to have code that looks roughly like this:

// this is for Postgres driver
// querying for a single record using Go's database/sql package
sqlStatement := `SELECT ename FROM emp WHERE empno=$1;`
row := db.QueryRow(sqlStatement, 7)

The key distinction here is that we aren’t trying to construct the SQL statement ourselves, but are instead providing arguments that can be easily escaped for us. The underlying driver for database/sql will ultimately be aware of what special characters it needs to handle and will escape them for us, preventing any dangerous SQL from running.

Step 4. Putting this all together

package main
// querying for a single record using Go's database/sql package
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)

const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "****"
	dbname = "connect"
)

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	var ename string
	sqlStatement := `SELECT ename FROM emp WHERE empno=$1;`
	row := db.QueryRow(sqlStatement, 46)
	switch err := row.Scan(&ename); err {
		case sql.ErrNoRows:
			fmt.Println("No rows were returned!")
		case nil:
			fmt.Println(ename)
		default:
			panic(err)
	}
}

Conclusion

You should always use the database/sql package to construct prepared statements. These prepared statements have parameters that will be passed while executing the statement. This is much better than concatenating strings (Avoiding SQL injection attack). In PostgreSQL, the parameter placeholder is $N, where N is a number. In MySQL it is ?. SQLite accepts either of these. For more best practices on preventing SQL Injection, please refer https://bobby-tables.com/go

If you find this post helpful, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook. Thank you

Implementing transactions in PostgreSQL using Go lang database/sql package

Introduction

A transaction is a unit of work that you want to treat as “a whole.” It has to either happen in full or not at all. In Go lang, a transaction is essentially an object that reserves a connection to the database. You begin a transaction with a call to db.Begin() and close it with a commit() or rollback() method on the resulting Tx variable.

In this post, we will learn how to implement database transactions in your Go lang application.

Prerequisites

To be able to achieve our objective i.e. Transactions in PostgreSQL from Go , there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with some references

Install PostgreSQL, setup an instance and create a test database – https://www.postgresqltutorial.com/install-postgresql/

Install Go and configure workspace – https://www.callicoder.com/golang-installation-setup-gopath-workspace/

For this tutorial, I’m using Postgres 12 and Go 1.13.xx

Objective – Transactions in PostgreSQL using Go lang database/sql package

Step 1. SQL Table definition

We’ll re-use our test table EMP that we created in the last post

-- create a sample table EMP
CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT CHECK (sal>0),
  email TEXT UNIQUE NOT NULL 
);

Step 2. Basic SQL transactions

For this example, let’s just assume that we want following SQL statements to be executed together

-- Stmt #1. INSERT a new employee record
INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com');

-- Stmt #2. UPDATE sal for this new employee
UPDATE emp SET sal = 800 WHERE ename = 'Smith';

Now think of a scenario where only the first SQL statement  succeeds, however, the second statement fails: you now have a new employee Smith, however, does not have a salary allocated,

  • Can we treat this as a success, since Smith does not have a salary allocated
  • Also, Is this a failure, since Smith employee record is created
    • If we consider this a failure and retry the transaction, then employee record of ‘Smith’ will be inserted twice

This is a classic situation and in relational systems, we handle them using database transactions i.e. allowing both the SQL statements to Pass or Fail together. Below is an example of database transactions to handle the above scenario.

BEGIN;
	INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com');
	UPDATE emp SET sal = 800 WHERE ename = 'Smith';
COMMIT;

Here

  • First, open a transaction by issuing the BEGIN command
  • Second, issue SQL statements to SELECT or INSERT or UPDATE data in the database. The SQL statements are executed one after the other
  • Third, commit the changes to the database by using the COMMIT statement
  • If you do not want to save the changes, you can roll back using the ROLLBACK statement

Step 3. Implementing Transactions in Go lang

You begin a transaction with a call to db.Begin(), and close it with a Commit() or Rollback() method on the resulting Tx variable. Under the covers, the Tx gets a connection from the pool, and reserves it for use only with that transaction. The methods on the Tx map one-for-one to methods you can call on the database itself, such as Query() and so forth.

// First You begin a transaction with a call to db.Begin()
ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
	log.Fatal(err)
}

While you are working inside a transaction you should be careful not to make calls to the db variable. Make all of your calls to the Tx variable that you created with db.Begin()db is not in a transaction, only the Tx object is. If you make further calls to db.Exec() or similar, those will happen outside the scope of your transaction, on other connections.

// Second, execute SQL queries against on the transaction instance. Note these are not applied to the database yet
_, err = tx.ExecContext(ctx, "INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com')")
if err != nil {
	// Incase we find any error in the query execution, rollback the transaction
	tx.Rollback()
return
}

// The next query is handled similarly
_, err = tx.ExecContext(ctx, "UPDATE emp SET sal = 0 WHERE ename = 'Smith'")
if err != nil {
	tx.Rollback()
	fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
	return
}

A Tx will maintain that single connection for its entire life cycle, releasing it only when Commit() or Rollback() is called. You should take care to call at least one of these, or else the connection will be held until garbage collection.

// close the transaction with a Commit() or Rollback() method on the resulting Tx variable. 
err = tx.Commit()
if err != nil {
	log.Fatal(err)
}

Step 4. Putting this all together

package main

// modifying data and using transactions
import (
	"context"
	"database/sql"
	"fmt"
	"log"
	
	_ "github.com/lib/pq"
)
const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "postgres"
	dbname = "connect"
)

var (
	ename string
	sal int
)

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
	"password=%s dbname=%s sslmode=disable",
	host, port, user, password, dbname)
	
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// First You begin a transaction with a call to db.Begin()
	// `tx` is an instance of `*sql.Tx` through which we can execute our queries
	ctx := context.Background()
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatal(err)
	}
	
	// Second, execute SQL queries against on the transaction instance. Note these are not applied to the database yet
	_, err = tx.ExecContext(ctx, "INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com')")
	if err != nil {
		// Incase we find any error in the query execution, rollback the transaction
		tx.Rollback()
		fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
		return
	}

	// The next query is handled similarly
	_, err = tx.ExecContext(ctx, "UPDATE emp SET sal = 0 WHERE ename = 'Smith'")
	if err != nil {
		tx.Rollback()
		fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
		return
	}

	// close the transaction with a Commit() or Rollback() method on the resulting Tx variable. 
	// this applies the above changes to our database
	err = tx.Commit()
	if err != nil {
		log.Fatal(err)
	} else {
		fmt.Println("....Transaction committed\n")
	}
}

Conclusion

In this post we learned how to implement database transactions in PostgreSQL using Go lang  database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. This is also concludes my blog post series for on Working with PostgreSQL in Go Lang. Below are all other post in this series:

1. My journey from DBA to DevOps and why I started learning Go lang

2. Connecting to PostgreSQL from Go lang project

3. Inserting rows in PostgreSQL db from Go lang project

4. Updating and Deleting rows in PostgreSQL from Go lang project

5. Querying rows from PostgreSQL from Go lang project

If you find my post helpful, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook.

Thank you 🙋‍♂️

The Unicorn Project – Book Review

A Novel about Developers, Digital Disruption, and Thriving in the Age of Data

Over a month ago, I read the book “The Phoenix Project” and published a review on my blog. The book was certainly amazing as it touched on so many important aspects of software development, DevOps and my own IT career. To my surprise, I found that the book also has a sequel The Unicorn Project.

So is it really a Sequel?

Although as per the author, there is absolutely no need to read The Phoenix Project prior reading The Unicorn Project, however based on my understanding of the books characters plot, I would totally recommend reading The Phoenix Project. There’s the same company – Parts Unlimited – same urge to change to survive, same challenges and same project – The Phoenix Project – on which the company is betting its future.

Developers

While in The Phoenix Project, the story revolves around Bill Palmer – VP of IT operations, The Unicorn Project, a brand new protagonist, Maxine Chambers – Developer Lead and Architect. Yes, you got it! the story this time deep dives into the developer’s world.

Maxine, is a talented lead developer and architect blamed for an outage and exiled on the Phoenix project. Throughout her journey, she partners with a team of corporate rebels, and together they confront their legacy and change-averse processes and apply the five ideals to lead a positive and lasting business, technology and cultural transformation.

Not that the book is all about developers, debugging, continuous integration or unit tests, but it is very much the focus, at least for the first good half of the book. In the DevOps age, the core development topics were earlier left to developers are now in the center of discussion up to CIOs and sometimes CEOs.

The Five Ideals

While author in The Phoenix Project  came up with “The Three Ways”, in this book he introduces “The Five Ideals” as follows:

  1. First Ideal: Locality and Simplicity

We need to design things so that we have locality in our systems and the organizations that build them. We need simplicity in everything we do, in our code, in our organization or in our processes. E.g. when you need to make a simple change and you need to change 15 files instead of 1, then you are probably violating this ideal. This is also known as the Single Responsibility principle.

  1. Second Ideal: Focus, Flow, and Joy

How does our daily work feel? This means not waiting for other people in order to get things done, get fast and continuous feedback. Also consider the lunch factor (similar to the truck factor): how many people do you have to pay lunch in order to execute a deployment?

  1. Third Ideal: Improvement of Daily Work

The Toyota Andon cord is a good example of this ideal. Sometimes, processes are improved by adding an extra step to the process every time something went wrong. This way a bloated process is created which negatively impacts flow. Often, when suggestions for improvement are made in a team working on a legacy application, the suggestion is killed by saying ‘We have always done it this way’. Of course, this is a non-argument, you must judge the suggested improvement and not express your feelings against change. This ideal is also about reducing technical debt which we discussed already. E.g. The Nokia Symbian builds lasted 48 hours when the iPhone was on the rise (somewhere around 2010). This way it is impossible to have fast feedback and it can have a devastating result for your company.

  1. Fourth Ideal: Psychological Safety

There should be no blaming culture, it should be safe to talk about problems and how to solve them. A culture of fear will make people hiding their mistakes, while a culture of safety will ensure that things will improve.

  1. Fifth Ideal: Customer Focus

Are we working on something the customer is willing to pay for or is this a feature which only will satisfy a functional silo? In other words, we need to focus on customer value.

Data is new currency 💲

Data in The Unicorn Project book, like in real life, takes the front seat. It’s with data that Parts Unlimited can now customize its marketing campaigns, optimize revenues and manage stocks like never before. It’s also with data that the teams operate their applications, make informed decisions on what to focus on and are able to react quickly when things go wrong. We often read that data is largely unused and can unlock immense value. The Unicorn Project gives practical examples of using this data effectively to generate favorable business outcome. Impressive!

Conclusion

Overall the book touches on a lot of different complex topics that are central to any engineering organization. For example, focusing on psychological safety of employees and the positive impact it can have on both a team and organization.

I love Gene’s style of writing that makes the entertaining to read about the struggles of a developer and how she (Maxine) overcomes those. It’s easy to relate to the frustration that comes from a company that wants to but is unable to change.

After reading through the book, I count myself lucky to work for a company that values its team members, their safety and follows a culture of openness & blameless post-mortems (believe me they do!). All in all the book made me reflect in a lot of ways, the same experience as the one I had with The Phoenix Project.

If you’re interested in DevOps, I highly recommend The Unicorn Project

🦄 Let your inner Unicorn shine!

Querying rows from PostgreSQL from Go lang project

Introduction

In the last post Updating/Deleting rows with Go, we learned to manipulate rows in PostgreSQL database in Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn how to query rows i.e. SELECT

Prerequisites

To be able to achieve our objective i.e. Select rows from PostgreSQL using Go, there are some prerequisites. Sticking to core subject of this blog, I won’t cover them, however I’m providing with some references

Install PostgreSQL, setup an instance and create a test database – https://www.postgresqltutorial.com/install-postgresql/

Install Go and configure workspace – https://www.callicoder.com/golang-installation-setup-gopath-workspace/

For purpose of this tutorial, I’m using Postgres 11 and Go 1.13.xx

Objective – Updating and  Deleting rows in a PostgreSQL table using Go lang database/sql package

In following steps, I’ll demonstrate how to query rows

Step 1. SQL Table definition

We’ll reuse our table EMP that has following columns

-- create a sample table EMP
CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT,
  email TEXT UNIQUE NOT NULL 
);

Step 2. SELECT statement in SQL (multiple rows)

Lets first write down our DML statements for update and delete

-- select row from table
SELECT ename, sal 
	FROM emp 
		ORDER BY sal desc;

Step 3. Connecting to PostgreSQL instance from Go lang

As always, we first need to make a connection to PostgreSQL instance. Since the code to make a connecting to PostgreSQL database is same, I’ll skip repeating it here, however if you need a refresher please visit the earlier post Connecting to PostgreSQL db from Go lang project

Step 4. Preparing Go lang code

Update row in EMP table

For reading rows, we will use db.Query() method, fetch ename and sal column from EMP table and output rows sorted by sal. Next, we will assign results to variables, one row at a time, with row.Scan().

// query rows from a table
var (
		ename string
		sal int
)


rows, err := db.Query("SELECT ename, sal FROM emp order by sal desc")
if err != nil {
			panic(err)
}
defer rows.Close()

for rows.Next() {
		err := rows.Scan(&ename, &sal)
		if err != nil {
					panic(err)
		}
		fmt.Println("\n", ename, sal)
}
err = rows.Err()
if err != nil {
			panic(err)
}

Lets understand the code above:

  • We’re using db.Query() to send the query to the database and checking for any error
  • Next we iterate over the rows with rows.Next().
  • Read the columns in each row into variables with rows.Scan() and print them to console with fmt.Println().
  • Finally, we check for errors after we’re done iterating over the rows.

Step 5. Putting this all together

package main

// querying all rows from the database with Go lang sql package
import (
	"database/sql"
	"fmt"

	_ "github.com/lib/pq"
)

const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "postgres"
	dbname = "connect-db"
)

var (
	ename string
	sal int
)

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)
	
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	rows, err := db.Query("SELECT ename, sal FROM emp order by sal desc")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		err := rows.Scan(&ename, &sal)
		if err != nil {
			panic(err)
		}
		fmt.Println("\n", ename, sal)
	}

	err = rows.Err()
	if err != nil {
		panic(err)
	}
}

What-If, you only want to query one single row?

There could be scenario when you would only want to query a single row, ex. lookup rows for a employee with highest salary. In this case, we can skip the for loop, instead use below shortcut approach.

var name string
err = db.QueryRow("SELECT ename FROM emp ORDER BY sal DESC LIMIT 1;").Scan(&name)
if err != nil {
		panic(err)
	}
fmt.Println(name)

Errors from the query are deferred until Scan() is called, and then are returned with variable output.

Conclusion

In this post we learned how to query rows (single or multiple) from a PostgreSQL table using Go lang  database/sql package that ships with Go, along with github.com/lib/pq Postgres driver.

Updating and Deleting rows in PostgreSQL from Go lang project

Introduction

In the previous post Inserting records into a database with Go, we learned how to insert rows to PostgreSQL database from Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn the remaining two DML (data manipulation language) operations i.e. UPDATE and DELETE.

Prerequisites

To be able to achieve our objective i.e. Updating and Deleting rows to PostgreSQL from Go, there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with some references

For purpose of this tutorial, I’m using Postgres 11 and Go 1.13.xx

Objective – Updating and  Deleting rows in a PostgreSQL table using Go lang database/sql package

In following steps, I’ll demonstrate how to write a simple command-line tool (CLI) using Go lang to update/delete rows in PostgreSQL database table

Step 1. SQL Table definition

We’ll re-use our test table EMP that we created in the last post while learning to insert rows. Below is table definition

CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT,
  email TEXT UNIQUE NOT NULL 
);

Step 2. UPDATE and DELETE statements in SQL

Lets first write down our DML statements for update and delete

-- SQL stmt to update a row
UPDATE emp
	SET sal = 1800
	WHERE ename = 'Allen';


-- SQL stmt to delete a row
DELETE FROM emp
	WHERE ename = 'Blake';

Step 3. Connecting to PostgreSQL instance from Go lang

As always, we first need to make a connection to PostgreSQL instance. Since the code to make a connecting to PostgreSQL database is same, I’ll skip repeating it here, however, if you need a refresher please visit the earlier post Inserting rows in PostgreSQL db from Go lang project

Step 4. Preparing Go lang code

In our Go code, we’ll use the same SQL statement and calling db.Exec() method on sql.db object. An imp thing to note is here is db.Exec() method returns two values – a Result and an Error. We’ll use Error for error handling, to generate panic() if anything goes wrong. And we’ll use Result to verify the count of rows that were updated or deleted. Let’s see

// update a row in table
sqlStatementUpdt := `
	UPDATE emp
	SET sal = 3000 WHERE ename = $1;`
	res, err := db.Exec(sqlStatementUpdt, "Smith")
	if err != nil {
		panic(err)
	}
	count, err := res.RowsAffected()
	
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows updated: %v\n", count)
// delete a rows in table
sqlStatementDel := `
	DELETE FROM emp
	WHERE ename = $1;`
	res1, err := db.Exec(sqlStatementDel, "Jones")
	if err != nil {
		panic(err)
	}
	count, err = res1.RowsAffected()
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows deleted: %v\n", count)

Step 5. Putting this all together

package main

	// updating PostgreSQL records using Go's database/sql package

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

const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "secure-password"
	dbname = "connect-db"
)

func main() {

	connStr := fmt.Sprintf("host=%s port=%d user=%s "+
	"password=%s dbname=%s sslmode=disable",
	host, port, user, password, dbname)
	
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// update row
	sqlStatementUpdt := `
	UPDATE emp
	SET sal = 3000 WHERE ename = $1;`
	res, err := db.Exec(sqlStatementUpdt, "Smith")
	if err != nil {
			panic(err)
	}
	count, err := res.RowsAffected()
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows updated: %v\n", count)

	// delete row
	sqlStatementDel := `
	DELETE FROM emp
	WHERE ename = $1;`
	res1, err := db.Exec(sqlStatementDel, "Jones")
	if err != nil {
		panic(err)
	}
	count, err = res1.RowsAffected()
	
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows deleted: %v\n", count)
}

Conclusion

In this post we learned how to UPDATE and DELETE records in PostgreSQL db table in Go lang project. In the next post, we’re going to learn how to read rows (aka SELECT) rows from the database table.

Inserting rows in PostgreSQL db from Go lang project

Introduction

In the previous post Connecting to PostgreSQL from Go lang project, we learned how to connect to the PostgreSQL database from Go lang project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn how to insert data (rows) in a PostgreSQL table.

Prerequisites

To be able to achieve our objective i.e. Inserting rows to PostgreSQL from Go , there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with useful references:

For purpose of this tutorial, I’m using Postgres 11 and Go 1.13.xx

Objective – Inserting rows to a PostgreSQL table from Go

In following steps, I’ll demonstrate how to write a simple command-line tool (CLI) using Go lang to insert rows in PostgreSQL database table

Step 1. SQL Table definition

Create test table EMP in PostgreSQL test database with the following columns

-- create a sample table EMP
CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT,
  email TEXT UNIQUE NOT NULL 
);

Step 2. SQL Insert statement

a basic SQL INSERT statement will look something like this

INSERT INTO public.emp (ename, sal, email)
VALUES('Smith', 800, 'smith@acme.com');

Step 3. Connecting to PostgreSQL instance

To be able to insert a row in the database, we first need to connect to the instance. In earlier post in this series we learned how to connect to PostgreSQL from Go lang project using database/sql package. I was originally using the same connection string, when one of the readers suggested me a better approach i.e. to use const() construct for declaring connection string variable

const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "secure-password"
	dbname = "connect-db"
)

Next in our main() function we are going to create a string variable connStr which contains all of the information required to connect to our Postgres database.

func main() {
	// connection string
	connStr := fmt.Sprintf("host=%s port=%d user=%s "+
	"password=%s dbname=%s sslmode=disable",
	host, port, user, password, dbname)
}

Step 4. Preparing Go lang code to insert row in EMP table

In our Go code, we’ll use same SQL statement and calling db.Exec() method on sql.db object. A thing to note is here is db.Exec() returns two values – a result and an error. We’re using the later for error handling, to generate a panic() if anything goes wrong.

sqlStatement := `INSERT INTO emp (ename, sal, email) 
VALUES ($1, $2, $3)`
_, err = db.Exec(sqlStatement, "Smith", 800, "smith@acme.com")
if err != nil {
panic(err)
}

Step 5. Putting this all together

package main

// inserting records into a PostgreSQL database with Go's database/sql package
import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

const (
	host = "pgslx1005"
	port = 5432
	user = "postgres"
	password = "postgres"
	dbname = "connect-db"
)

func main() {
	connStr := fmt.Sprintf("host=%s port=%d user=%s "+
	"password=%s dbname=%s sslmode=disable",
	host, port, user, password, dbname)
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		panic(err)
	}
	defer db.Close()
        
// insert a row
	sqlStatement := `INSERT INTO emp (ename, sal, email) 
	VALUES ($1, $2, $3)`
	_, err = db.Exec(sqlStatement, "Smith", 800, "smith@acme.com")
	if err != nil {
		panic(err)
	} else {
		fmt.Println("\nRow inserted successfully!")
	}
}

After inserting some additional rows, EMP table now looks like this

 | empno | ename | sal  | email          |
 |-------|-------|------|----------------|
 | 11    | Smith | 800  | smith@acme.com |
 | 12    | Allen | 1000 | allen@acme.com |
 | 13    | Jones | 1200 | jones@acme.com |
 | 14    | Blake | 1400 | blake@acme.com |

Conclusion

In this post we learned how to INSERT records (rows) in PostgreSQL db table via Go lang project. In the next post, I’ll explain about remaining two DML operations i.e. UPDATE and DELETE.

Connecting to PostgreSQL from Go lang project

Introduction

The Go programming language, sometimes referred to as Go lang, is making strong gains in popularity. Chances are if you are a Go developer, you will have to interact with SQL at some point in your project. This blog post will show how to connect to a PostgreSQL database from Go using database/sql package.

What is PostgreSQL?

PostgreSQL, is a free and open-source relational database management system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workload.  PostgreSQL allows user-defined functions to be written in other languages including C and Go. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database.

If you’re new to PostgreSQL project, I’ll recommend you to please refer the documentation

What is Go lang?

Go is an open source, statically typed, compiled programming language designed at Google. Its is  syntactically similar to C, but with overcomes lot of it’s limitations. It is often referred to as “Golang” because of its domain name, golang.org, but the proper name is Go. There are multiple areas where Go shines, like statically, strongly typed with a great way to handle errors, compiles down to one binary, super fast compilation, open source and above all simplicity.

To learn more about Go, please refer Go docs

Prerequisites

To be able to achieve our objective i.e. Connecting to PostgreSQL from Go , there are some prerequisites. Sticking to core subject of this blog, I won’t cover them, however I’m providing with some references

Install PostgreSQL and setup an instance – https://www.postgresqltutorial.com/install-postgresql/

Install Go and configure workspace – https://www.callicoder.com/golang-installation-setup-gopath-workspace/

For purpose of this tutorial, I’m using Postgres 11 and Go 1.13.xx

Objective – Connecting to PostgreSQL from Go

In next few steps, I’ll demonstrate how to build a simple command line tool (CLI) using Go lang that can connect to a PostgreSQL instance. So let’s Go (pun intended)

Step 1. Gather PostgreSQL Instance details

host     = "localhost"
port     =  5432
user     = "postgres"
password = "secure-password"
dbname   = "connect-db"

Step 2. Install the github.com/lib/pq package

go get -u github.com/lib/pq

Go’s standard library was not built to include any specific database drivers. So we need to install a third party package named lib/pq.

While Go provides us with the database/sql package that we will be utilizing to interact with our database, the standard libraries do not include drivers for every SQL database variant. Instead this is left up to the community, and from my experience the lib/pq package is the best driver for Postgres.

Step 3. Putting together our package pg-connect.go

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

Step 4. Configuring database connection string in our code

Inside main() function, we’re are going to create a connection string, containing all the information required to connect to our postgres database.

func main() {
  connStr := "user=postgres dbname=connect-db password=secure-password host=localhost sslmode=disable"
}

Depending on your specific setup, you may want to change sslmode to enable or disable (default value is enabled).

Step 5. Creating a connection to our database

At this time our code is ready for a handshake with database using the connection string connStr. To make this work, we will use sql.Open() function, that takes 2 arguments – a driver name and connecting string. At this step, we’ll also add error handling to ensure we signal a panic if anything goes wrong.

db, err := sql.Open("postgres", connStr )
if err != nil {
  panic(err)
}
defer db.Close()

Hoping our connection details are validated, next we’re going to call Ping() method on sql.DB object to test our connection. db.ping() will force open a database connection to confirm if we are successfully connected to the database.

err = db.Ping()
if err != nil {
  panic(err)
}

Step 6. Putting it all together

package main

// connecting to a PostgreSQL database with Go's database/sql package	
import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)
	
func main() {
	
/*
variables required for connection string: connStr

user= (using default user for postgres database)
dbname= (using default database that comes with postgres)
password = (password used during initial setup)
host = (hostname or IP Address of server)
sslmode = (must be set to disabled unless using SSL)
*/
	
  connStr := "user=postgres dbname=connect-db password=secure-password 
  host=localhost sslmode=disable"
  db, err := sql.Open("postgres", connStr)
  if err != nil {
	panic(err)
  }
  defer db.Close()
	
  err = db.Ping()
  if err != nil {
	panic(err)
  }
  fmt.Printf("\nSuccessfully connected to database!\n")
}

Next steps…

In the next post, we’ll see how to interact with the data within the database using Go lang.

My journey from DBA to DevOps

I’ve been working as a database engineer for over a decade, engineering enterprise data platforms. During the beginning of my career in early 2000, I chose the ‘safe’ path of being a DBA believing that relational systems being universal containers for storing critical data will never change. I started learning relational technologies like Oracle, MS-SQL, and eventually also learned Open Source systems including PostgreSQL & MySQL.

However contrary to my belief, enterprise technology did change a lot. Some things however remained constant like developers swinging by my cube to request a new database, performing DB refresh or tuning  a slow running query (my favorite). Sometimes these teams were frustrated with me when their app went down or when they couldn’t access their database. At times, my Dev and QA friends would come by my desk to learn what I was doing and even after my attempt to explain them how I am solving their production issue, they would walk away puzzled. I have grown up in this role realizing that a when things go wrong, the first one to be blamed is DBA (i.e. Default Blame Acceptor)

This was my life for long and as you might have guessed, it wasn’t very satisfying; professionally 😉

It was a vicious cycle of configuring environments, routine data refreshes, resolving outages, and late-night upgrades. The work which once gave meaning to my career, lately made me ask myself, “Is this it for a DBA?” , “Is it the time to change?”

The journey ahead…

I then started having discussions with my leaders, determined to find an answer to “What’s the future of my role?”.

During these discussions, I heard terms like Agile, Scrum, Continuous Delivery and DevOps. Since the last one was repeated more often than others, I started researching on “Skillset for DevOps”.

Initially I was all excited to learn a new skill set, maybe a new tool, however after multiple days of googling, I couldn’t find any specific Skill Set definition. During a follow-up discussion with my mentor, I learned something interesting,

“Think of DevOps as not a specific skill set, instead it’s a way of doing something.”

Wait, so all it means is Dev and Ops working together, that’s sounds weird! So now I have to learn all languages used by developers…C++, Java, Python…ugh? While I’ve been coding on PowerShell & SQL for infra automation, I never considered myself as a developer. Hmm…maybe that’s what needed to change.

Fast forward – Learning Go

In the past couple of years, there is a rise of a new programming language Go lang (or simply GO). Most developers in their code will have to interact with the database at some point in their project, and often that means working with PostgreSQL. I particularly got interested in learning Go’s integration with PostgreSQL. There are multiple Go libraries that allow Go PostgreSQL integration productive and fun. Excited about my journey and want to share whatever I’ve learned recently…so in the next few posts I’ll blog about PostgreSQL integration with Go.

Disclaimer: By NO means I claim to be a Go expert and I am not going to teach Go lang. There’s a lot of great online courses and material for that. I am only going to share my learnings on Postgres integration with Go.

The Phoenix Project – Book Review

A Novel about IT, DevOps, and Helping Your Business Win

Some people are lucky to find books that changes their life. While I’m yet to find my life-changing novel, I do come across books that make me think, question my beliefs and push me to learn. The Project Phoenix is that book for me.

Written by Gene Kim, George Spafford, and Kevin Behr, the book is about a large company’s transformation into a DevOps culture. Transformation driven not just to look cool, but as a necessity for the survival of the company.

The synopsis is simple. Bill, the protagonist, is the Director of Midrange Operations at Parts Unlimited, a US-based $4 billion per year manufacturing and retail company. Bill is swiftly pulled into the spotlight by the CEO and persuaded hoodwinked into taking up the post as VP of IT Operations. It soon becomes clear that among standard responsibilities, Bill and his team are responsible for making the launch of the risky doomed Project Phoenix a success. Project Phoenix not only seems to be hugely overscoped for its ambitious – and imminent – timelines, but it also faces enormous pressure elsewhere.

The characters and situations in the book are stereotypical, however that’s not a criticism. The intent is clearly for us to identify with the characters and events, to relate your workplace with the story . So here are my key learnings “spoiler-free

DevOps is a collaborative working relationship between Development and IT Operations 🤝

Outcome of this collaboration is fast flow of planned work, while increasing the reliability, stability of the production environment.

3️ Ways principle 📜

The First way – focuses on maximizing flow of work from left-to-right starting from business to development to IT operations to the end user.

The Second way – focuses on increasing the feedback loop from right to left. The focus is not only on getting feedback but also on how fast we can get the feedback in order to make necessary corrections/improvement quickly.

The Third way – The third way is all about developing and fostering a culture of continuous experimentation and learning.

Speed to Deliver is the key 🚀

Technology is life blood of all business today. It’s imperative that all business should strive to bring their applications to market more quickly so they don’t miss any opportunities and easily adjust to the market standards. To achieve these objectives, organizations must adopt the right DevOps practices in their software development processes to reduce time to market.

Overall, book does a great job at explaining all these ideas with examples and linking them together. It’s a super fun and easy read, and I would definitely recommend you.