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.