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.

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 JSON, CSV 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 PostgreSQL Extension – Custom insight dashboard


Azure Data Studio (formerly SQL Operations Studio) is a free Cross-Platform DB management tool for Windows, macOS and Linux.  Staying true to their promise of offering a unified data management experience for developers, Microsoft recently released PostgreSQL Extension for Azure Data Studio

So now developers can use same great GUI features for PostgreSQL database as they were for SQL Server, like IntelliSense, Multiple Query windows and Custom insight dashboard. In this blog post I’ll explain how to create a custom insight and add it to PostgreSQL dashboard as a widget

For this exercise I’ll use a simple query to display ‘active connections’ grouped by connection state

Step 1. Prepare your custom insight

1. Open a new Query Editor (Ctrl+N)

2. Copy / Paste below query

-- ADS custom dashboard - Get Active Vs Inactive connections
SELECT state, count(pid) 
    FROM pg_stat_activity 
     GROUP BY state, datname
     HAVING datname = '<app data>' --replace with your db name
          ORDER BY count(pid) DESC;

3. Save the query as “connection_stats.SQL” file and execute the query (F5)

4. Once you get the result-set, click on View as Chart

5. Customize the chart and click on Create Insight

Step 2. Add the insight to database dashboard

  1. Copy the insight configuration (the JSON data).
  2. Press Ctrl+Comma to open User Settings
  3. Type dashboard in Search Settings
  4. Click Edit for dashboard.database.widgets

  5. Paste the insight configuration JSON into dashboard.database.widgets. A formatted dashboard setting should look something like this
  6. Save the User Settings file
  7. In Server Explorer, right click on your database server name and click Manage
    Similar to above, you can also create more such “insights” to the default server dashboard. I have created one for checking top 5 tables by size (using below query)

    -- Get details of TOP `n` tables in database
    SELECT cl.relname AS objectname 
    ,pg_total_relation_size(cl.oid)/1024/1024/1024 AS size_in_GB
      FROM pg_class cl
         LEFT JOIN pg_namespace n ON (n.oid = cl.relnamespace)
         LEFT JOIN pg_stat_user_tables s ON (s.relid =cl.oid)
         WHERE nspname NOT IN ('pg_catalog', 'information_schema')
                 AND cl.relkind <> 'i' 
                 AND nspname !~ '^pg_toast' 
                   ORDER BY pg_total_relation_size(cl.oid) DESC 
                      LIMIT 5;

PostgreSQL Table Partitioning Part III ‚Äď Partition Elimination

Understanding Partition Elimination in PostgreSQL 11

This is Part-III for my series on Postgres Table partitioning. I’ll encourage you to also read Part-I and II

PostgreSQL Table Partitioning Part I ‚Äď Implementation Using¬†Inheritance

PostgreSQL Table Partitioning Part II ‚Äď Declarative¬†Partitioning

In this post, lets compare the READ performance between partitioned and an un-partitioned table. Before that lets first review our table schema and data distribution.

Un-Partitioned Data Set
-- Data Distribution un-partitioned tbl
SELECT logdate, COUNT (logdate)
FROM measurement_np
GROUP BY logdate;

Partitioned Data Set
-- Data Distribution partitioned tbl
SELECT logdate, COUNT (logdate)
FROM measurement
GROUP BY logdate;

Comparing Query Plan (EXPLAIN ANALYZE) and Partition Elimination

1st Execution – on cold cache
-- Un-Partitioned tbl
Index Scan using measurement_np_indx_logdate on measurement_np (cost=0.44..416.13 rows=10672 width=16) (actual time=0.031..40.625 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)

-- Partitioned tbl
Append (cost=0.43..4.46 rows=1 width=16) (actual time=0.028..2.316 rows=10000 loops=1)
-> Index Scan using measurement_y2006m04_logdate_idx on measurement_y2006m04 (cost=0.43..4.45 rows=1 width=16) (actual time=0.028..1.813 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)
2nd Execution – on hot cache
-- Un-Partitioned tbl
Index Scan using measurement_np_indx_logdate on measurement_np (cost=0.44..337.14 rows=8671 width=16) (actual time=0.040..1.750 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)

-- Partitioned tbl
Append (cost=0.43..405.61 rows=9519 width=16) (actual time=0.022..1.942 rows=10000 loops=1)
-> Index Scan using measurement_y2006m04_logdate_idx on measurement_y2006m04 (cost=0.43..358.01 rows=9519 width=16) (actual time=0.021..1.426 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)
Conclusion

On in both attempts (cold and hot cache), the data retrieval from¬† ‘partitioned’ tables was faster than the un-partitioned table.