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.

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!

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 II – Declarative Partitioning

Starting Postgres 10.x and onward, it is now possible to create declarative partitions.

In my previous post ‘postgresql-table-partitioning-part-i-implementation-using-inheritance‘, I discussed about implementing Partitioning in PostgreSQL using ‘Inheritance’. Up until PostgreSQL 9, it was only way to partition tables in PostgreSQL. It was simple to implement, however had some limitations like:

  • Row INSERT does not automatically propagate data to a child tables (aka partition), instead it uses explicit ‘BEFORE INSERT’ trigger, making them slower
  • INDEXES and constraints have to be separately created on child tables
  • Significant manual work is required to create and maintain child tables ranges

‘Declarative’ partitioning released with Postgres 10 does not have these limitations and requires much less manual work to manage partitions.

Let’s see the implementation of ‘Declarative’ partitioning with example:

— Step 1.
Create a partitioned table using the PARTITION BY clause,                              — which includes the partitioning method (RANGE in this example) and the list of column(s) to use as the partition key

CREATE TABLE measurement (
 	city_id int not null,
 	logdate date not null,
 	peaktemp int,
 	unitsales int
) PARTITION BY RANGE (logdate);

— Step 2.
— Create Index on parent table
— Note: creation of seperate indexes on parent table is not required

CREATE INDEX measurement_indx_logdate ON measurement (logdate);

— Step 3.
— Create Default partition

CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;

— Create partitions with exclusive range and dfeualt partition (catch-all for out of range values)

CREATE TABLE measurement_y2006m02 PARTITION OF measurement 
             FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement 
             FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE measurement_y2006m04 PARTITION OF measurement 
             FOR VALUES FROM ('2006-04-01') TO ('2006-05-01');
CREATE TABLE measurement_y2007m11 PARTITION OF measurement 
             FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

Let’s review our schema now

— Step 4
— Insert sample rows

DO $DECLARE
  BEGIN
    FOR i in 1..10 loop
      INSERT INTO measurement VALUES (1,'2006-02-07',1,1);
    end loop;
  end $;

DO $DECLARE
  BEGIN
    FOR i in 1..1000000 loop
      INSERT INTO measurement VALUES (1,'2006-03-06',1,1);
    end loop;
  end $;

DO $DECLARE
  BEGIN
    FOR i in 1..1000000 loop
      INSERT INTO measurement VALUES (1,'2006-04-09',1,1);
    end loop;
  end $;

DO $DECLARE
  BEGIN
    FOR i in 1..1000000 loop
      INSERT INTO measurement VALUES (1,'2007-11-11',1,1);
    end loop;
  end $;

--Optional Step
ANALYZE measurement;

–Step 5
–Test partition elimination

  SELECT * FROM measurement
    WHERE logdate = '2007-11-11';

Let’s look at the Query Plan

As you can see, the ‘Declarative’ partitioning is much more intuitive and requires less manual steps in declaring  partitions compares to inheritance.

thanks for reading!

PostgreSQL Table Partitioning Part I – Implementation Using Inheritance

In earlier PostgreSQL versions, it was not possible to declare table partitions syntactically. Partitioning can be implemented using table inheritance. The inheritance approach involves creating a single parent table and multiple child tables (aka. Partitions) to hold data in each partition range.

In this post, I’ll discuss the implementation of table partitions using inheritance. However before proceeding, let’s first understand why do we need partitioning?

Why Partition?

The simple answer is to improve the scalability and manageability of large data sets and tables that have varying access patterns.

Typically, you create tables to store information about an entity, such as customers or sales, and each table has attributes that describe only that entity. While a single table for each entity is the easiest to design and understand, these tables are not necessarily optimized for performance, scalability, and manageability, particularly as the table grows larger.

How can partitioning help? 

  1. When tables and indexes become very large, partitioning can help by partitioning the data into smaller and more manageable sections.
  2. It allows you to speed up loading and archiving of data, so that you can perform maintenance operations on individual partitions instead of the whole table, and this in turn improves the query performance.

There is a ton of information published on partitioning, But if you new to partitioning in PostgreSQL, below are some great examples:

Now that we have some insight what table partitioning is, let’s do a real partitioning  using below scripts:

How to Partition a Table?

— Step 1.
— CREATE PARENT & CHILD TABLES

CREATE TABLE parent (
id int,
col_a varchar,
col_b varchar);
--Child Table 1
CREATE TABLE range1() INHERITS (parent);
--Child Table 2
CREATE TABLE range2() INHERITS (parent);
--Child Table 3
CREATE TABLE range3() INHERITS (parent);

Let’s review the schema now

— Step 2.
— CREATE PARTITION FUNCTION THAT WILL HOLD LOGIC OF ‘ON-INSERT’ TRIGGER

CREATE OR REPLACE FUNCTION partition_parent() RETURNS trigger as $$
  BEGIN
    IF (new.id < 10) THEN
         INSERT INTO range1 VALUES (new.*) ;
    ELSEIF (new.id >= 10 AND NEW.id < 20 ) then
         INSERT INTO range2 VALUES (new.*) ;
    ELSEIF (new.id >= 20 AND NEW.id < 30 ) then
         INSERT INTO range3 VALUES (new.*) ;
    ELSE
         RAISE EXCEPTION 'out of range';
END IF;

RETURN NULL;
END;
$$ language plpgsql;

— Step 3.
— CREATE TRIGGER AND ATTACH IT TO THE PARENT TABLE TO BE PARTITIONED

CREATE TRIGGER partition_parent_trigger
    BEFORE INSERT ON parent
        FOR EACH ROW EXECUTE PROCEDURE partition_parent();for each row execute PROCEDURE partition_parent();

— Step 4.
— INSERT SAMPLE ROWS

DO $$DECLARE
  BEGIN
     FOR i in 1..29 LOOP
          INSERT INTO parent(id, col_a, col_b) VALUES (i, 'a', 'b');
     END LOOP;
END $$;

— Optional Step (stats update)

ANALYZE parent;
-- Step 5.
-- TEST PARTITION ELIMINATION (PRUNING)

EXPLAIN ANALYZE
SELECT * from parent
   WHERE id = 5;

Query Plan 

In the next Post in this series, I’ll discuss the new ‘Declarative Partitioning‘ implementation

thanks for reading!