PostgreSQL – how to UPSERT (Update or Insert into a table)

Most modern-day relational database systems use SQL MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records if a matching row already exists. UPSERT is a combination of Insert and Update, driven by a “PRIMARY KEY” on the table.

Why Upsert?

Say, we have a use case to insert all records from a text file sent by an external app every day into our database table that has a PRIMARY KEY defined. During the insert, if a duplicate record is found, entire insert batch will fail with “duplicate key violation error“. Annoying, right?!

Postgres 9.5 (and later) has introduced UPSERT (INSERT ON CONFLICT) operation, that can allow either updating the data for duplicate row in or just silently skipping the duplicate row, without any error.

Here, let me explain this using coffee shop scenario – coffee makes almost everything easy 😛

Coffee Shop Scenario ☕

I own a coffee shop, and I want to keep a record of drink preferences for all my customers. There’s one problem though, the preferences change over time. So how can I record all drink preference of for my customer in one place, without creating a duplicate row in database. Until now, to solve this, this would require writing a complex stored procedure in SQL, PL/pgSQL, C, Python, etc. In Postgres 9.5 (and later), this could be easily achieved using single line INSERT ON CONFLICT statement. Let’s see how…

Step 1. Create a test table

CREATE TABLE myCoffee (
	id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	preference VARCHAR NOT NULL);

myCoffee table consists of three columns: id, name, and preference. The name column has a unique constraint to guarantee the uniqueness of customer names.

Step 2. INSERT sample rows

INSERT INTO 
    myCoffee (name, preference)
VALUES 
    ('Tom', 'Cappuccino'),
    ('Joe', 'Espresso'),
    ('Varun', 'Frappé');
idnamepreference
1TomCappuccino
2JoeEspresso
3VarunFrappé

Step 3. UPSERT in action

Now suppose Joe visits my shop again on a hot summer day and asks for an ‘Iced Tea’ instead of his preferred drink. We can either:

A. Serve him the drink and skip updating his new drink preference (we already have his data in our table). The below INSERT statement has ON CONFLICT DO NOTHING. This will ensure that if a row already exists, it will be skipped. Else, a new row will be inserted.

INSERT INTO myCoffee (NAME, preference)
	VALUES('Joe','Iced Tea') 
	ON CONFLICT (name) 
	DO NOTHING;
idnamepreference
1TomCappuccino
2JoeEspresso
3VarunFrappé

B. Or else, we can record his new drink preference using ON CONFLICT DO UPDATE as in the below statement

INSERT INTO myCoffee (name, preference)
	VALUES('Joe','Iced Tea is my 2nd favorite') 
	ON CONFLICT (name) 
	DO UPDATE SET preference = EXCLUDED.preference || ';' || myCoffee.preference;
idnamepreference
1TomCappuccino
3VarunFrappé
2JoeIced Tea is my 2nd favorite; Espresso

Conclusion

So, we just learned that UPSERT is a combination of two different SQL statements UPDATE and INSERT. It works on a simple rule that if a new row being inserted does not have any duplicate then insert it, else if there are duplicate rows then either skip insert or update the new column value.

Reference – ON CONFLICT Clause 

UPSERT is a helpful feature to handle records that require to be updated frequently. I hope y’all found this post useful.

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 🙋‍♂️

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.

Connect to PostgreSQL in VS Code

VS Code has a rich extension API that let you add languages, debuggers, and tools to your installation to support easy development. PostgreSQL extension allows following:

  • Connect to PostgreSQL instances
  • View object DDL with ‘Go to Definition’ and ‘Peek Definition’
  • Write queries with IntelliSense
  • Run queries and save results as JSON, csv, or Excel

Download link: https://marketplace.visualstudio.com/items?itemName=ms-ossdata.vscode-postgresql

Using VS Code PostgreSQL extension

  1. Open the Command Palette Ctrl + Shift + P  (On mac use  ⌘ + Shift + P)

a

  1. Search and select PostgreSQL: New Query

  2. In the command palette, select Create Connection Profile. Follow the prompts to enter your Postgres instance hostname, database, username, and password.

a

 

 

You are now connected to your Postgres database. You can confirm this via the Status Bar (the ribbon at the bottom of the VS Code window). It will show your connected hostname, database, and user.

Now, let’s try to query database.

  1. Type a query ex. SELECT * FROM pg_stat_activity;

  2. Right-click, select Execute Query / keyboard shortcut [⌘M ⌘R] and the results will show in a new window.

6. You can also save the query results as JSONCSV or Excel.

So now, you can seamlessly code for PostgreSQL from Microsoft VS Code without switching screens, leverage powerful intellisense and execute queries.

Enjoy Coding!

IMP NOTE: Result windows from queries won´t show up again after being closed. This is bug with current version and is being worked by dev team. Workaround is either to keep the result window Open Or close / re-open the VS code window.

Azure Data Studio – Switching from Management Studio (SSMS) to Azure Data Studio (ADS)

Azure Data Studio (formerly SQL Operations Studio) is a free Cross-Platform DB management tool for for Windows, macOS and Linux. Azure Data Studio (ADS) initial release was only compatible for SQL Server, however recently Microsoft released a PostgreSQL extension for ADS – so now you can also manage your PostgreSQL instance using ADS. For more details on Azure Data Studio PostgreSQL Extension, refer to my earlier posts

Initially, I was apprehensive switching to ADS as I did not want to leave the comfort and ease of SSMS – after all I had been using it for more than a decade. My thoughts changed once I was on it.

Also, do you know ADS is built on Visual Studio code that has multiple options to ‘Customize’?And in this post I’ll take that feature and share how you could ‘Customize’ and ‘Personalize’ Azure Data Studio.

I. Change the Color theme

  1. Open Settings by clicking the gear on the bottom left and click on Settings
  2. Click on Color Theme
  3. Choose from the number of options (Choose between Light, Dark and High Contrast themes)

II. Change Keyboard Shortcuts

  1. Open Settings by clicking the gear on the bottom left and click on Settings
  2. Click on Keyboard Shortcuts

Change Run Query from Default to F5 And/Or Ctrl+E

// Place your key bindings in this file to overwrite the defaults
[
 {
  "key": "ctrl+e",
  "command": "runQueryKeyboardAction"
 },
 {
  "key": "f5",
  "command": "-runQueryKeyboardAction"
 }
]

III. Add Extensions

  1. Click the Extensions icon on the left
  2. Select the Install button on the extension you want from the list
  3. Click the Reload button to activate the installed extension

Refer to my previous post for  detailed step-by-step instructions for installing PostgreSQL extension.

IV. Get ‘Actual’ execution plan

  1. Highlight the query and Press [Ctrl] + [M] Or click Explain

You can modify the keyboard shortcut to your preference in user settings

V. Open an Integrated Terminal

  1. Use the Ctrl+` keyboard shortcut with the back tick character
  2. As a default, Terminal on my Windows 10 use Powershell, while Linux and macOS use $SHELL.
  3. You can customize and change the terminal by specifying the correct path for executable and update the settings. Below is the list of common shell executable and their default locations.
// Command Prompt
"terminal.integrated.shell.windows": "C:\\Windows\\System32\\cmd.exe"
// PowerShell
"terminal.integrated.shell.windows": "C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe"
// Git Bash
"terminal.integrated.shell.windows": "C:\\Program Files\\Git\\bin\\bash.exe"

Isn’t customizing on ADS easy? Let me know what you think.

I’ll share more about Azure Data Studio as I continue the journey – so stay tuned!