Inserting records into a PostgreSQL database with Go's database/sql package

Thus far in the Using PostgreSQL with Go series we have covered how to work with Postgres using raw SQL, and in the previous article in the series we explored connecting to a database using the database/sql package that ships with Go, along with the github.com/lib/pq Postgres driver.

In this post we are going to take everything we have learned a step further and we are going to look at how to insert records into an SQL database, along with how the database/sql package helps us avoid any unwanted SQL injection attacks by cleaning the inputs we provide before executing SQL statements. Don’t worry if you aren’t familiar with SQL injection - we will be covering what it is and what exactly the database/sql package does to prevent it within this post.

Before continuing, you need to make sure you understand how to connect to your database using database/sql. You can find instructions for this in the previous post - Connecting to a PostgreSQL database with Go's database/sql package - and the code in this post will build off of this, but you can easily adapt the code here for whatever connection code you currently have. The important thing is that we have a valid sql.DB object.

On top of having a connection, this tutorial assumes that you have a table named users with the following attributes:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

Inserting records with database/sql

Getting started we are going to jump right into creating records. When we want to create a user using raw SQL, we would use something like the code below.

INSERT INTO users (age, email, first_name, last_name)
VALUES (30, 'jon@calhoun.io', 'Jonathan', 'Calhoun');

The way we interact with SQL in Go is actually pretty similar to this. Rather than reinventing the wheel, we will mostly just be writing SQL statements and telling Go to run them with our database connection. For example, if you wanted you could run the SQL statement above by hard coding it into your application and calling db.Exec() on your sql.DB object.

sqlStatement := `
INSERT INTO users (age, email, first_name, last_name)
VALUES (30, 'jon@calhoun.io', 'Jonathan', 'Calhoun')`
_, err = db.Exec(sqlStatement)
if err != nil {
  panic(err)
}

The biggest issue with the approach above is that we have hard-coded values in our SQL statement; Surely that isn’t what we want, and the database/sql package provides a helpful way to fix this.

Rather than putting values into your SQL statement, you can instead replace them with $N where N is the index of the argument that is passed in as extra arguments to db.Exec(). Let’s look at an example.

sqlStatement := `
INSERT INTO users (age, email, first_name, last_name)
VALUES ($1, $2, $3, $4)`
_, err = db.Exec(sqlStatement, 30, "jon@calhoun.io", "Jonathan", "Calhoun")
if err != nil {
  panic(err)
}

What is SQL Injection

Now you might be wondering, “Why don’t I just create the SQL statement on my own using something like fmt.Sprintf()?“, but I assure you that is a very bad idea.

When you create strings using packages like fmt they don’t know anything about SQL, so all they know how to do is combine strings together. That means that if a user were to enter a value with nefarious intent, it could very easily create an SQL statement that doesn’t do what you intended.

Let’s imagine that you use the following code to create an SQL SELECT statement.

func buildSql(email string) string {
  return fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
}

While this might appear to work with an email address like jon@calhoun.io, what happens if a user attempts to sign in using an email address like '; DROP TABLE users;'? Go ahead and try it - https://play.golang.org/p/v9qXpK4IrQ

You should see an SQL statement that looks like the following.

SELECT * FROM users WHERE email=''; DROP TABLE users;'';

Now this might not look so bad at first, but if you take a closer look this statement doesn’t just look for a user, but it also adds an extra SQL statement to the end that drops the entire users table! Yikes! That would mean you would lose ALL of your user data if this were executed.

This is called SQL injection, and it happens when you let users input data that needs to be used in SQL statements and you don’t escape any special characters, like the single quote (') character. Unfortunately, this is probably one of the most common ways that “hackers” will attempt to attack your website, and while some SQL injection attacks can be used to gain data, a large chunk of them will simply destroy a large portion of your data, leaving you with an empty database and a lot of explaining to do to your users.

By using the database/sql package to create our SQL statements, we get a free layer of protection against this. The database/sql package is aware of all special SQL characters, so when you try to insert a string with a single quote (') into a statement being constructed by the database/sql package it will escape the special characters and prevent any nefarious SQL statements from ever being executed.

This is easier to understand with an example, so let’s go back to the previous example where we want to search for a user using an email address. Rather than executing the dangerous SQL above, the database/sql package would instead execute something like the SQL below.

SELECT * FROM users WHERE email='''; DROP TABLE users;''';

While this might look very similar, there is one very significant difference - the single quotes in the email address are doubled up, which is how you escape a single quote character in SQL. It would be the equivalent to putting a backslash before a quote in Go. eg fmt.Println("\"hi\", said the man") would output "hi", said the man, and '''; DROP TABLE users;''' is treated like the string '; DROP TABLE users;' in SQL, so rather than executing the dangerous DROP TABLE users; command, this statement would search for a user with the email address '; DROP TABLE users;'. This likely won’t return any users, but more importantly it wouldn’t delete all of your data!

So the short version of this story is *always use the database/sql package to construct SQL statements and insert values into them**. It will save you a lot of headaches down the road, I promise.

How do I Retrieve the ID of new records with Postgres and Go?

Okay, so we know to use the database/sql package to construct our SQL statements, and we saw an example of how to do this, but we still have one thing to cover - how to get the ID of a newly created record.

In nearly all cases of creating a new record, you are likely relying on your database to create the record’s ID, so before you can return the newly created record to an end user you are going to want to figure out the ID of that record.

If you check out the db.Exec() method you might notice that this method returns two values - a Result and an error. Upon further digging you might also notice that the Result type is an interface and requires the method LastInsertId(), which typically is used to get the ID of a newly created record, but unfortunately this does not work with the lib/pq driver for Postgres because PostgreSQL does not automatically return the last insert ID.

Instead, we get around this by making a few changes. First, we are going to update our SQL statement by adding RETURNING id to the end of the statement. This will tell our SQL database that we want it to return the id of the newly inserted record so that we can do something with it.

After doing that, we also need to update our Go code to use the db.QueryRow() method instead of the Exec() method. By using this method we can still execute the same SQL as before, but we are telling our code that we expect to get a single row of data back. That row will only contain one piece of data - the ID of our newly created record.

Finally, we Scan() the returned Row and provide an integer variable for the ID to be stored in. Putting it all together we get the code below.

sqlStatement := `
INSERT INTO users (age, email, first_name, last_name)
VALUES ($1, $2, $3, $4)
RETURNING id`
id := 0
err = db.QueryRow(sqlStatement, 30, "jon@calhoun.io", "Jonathan", "Calhoun").Scan(&id)
if err != nil {
  panic(err)
}
fmt.Println("New record ID is:", id)

If you have been following along with the series, here is the complete code example.

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/lib/pq"
)

const (
  host     = "localhost"
  port     = 5432
  user     = "postgres"
  password = "your-password"
  dbname   = "calhounio_demo"
)

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()

  sqlStatement := `
INSERT INTO users (age, email, first_name, last_name)
VALUES ($1, $2, $3, $4)
RETURNING id`
  id := 0
  err = db.QueryRow(sqlStatement, 30, "jon@calhoun.io", "Jonathan", "Calhoun").Scan(&id)
  if err != nil {
    panic(err)
  }
  fmt.Println("New record ID is:", id)
}

Now you can freely replace the variables passed into QueryRow() with whatever data you want and easily insert multiple records to your database!

Up Next…

You should now have a pretty basic idea of how to insert new records into a Postgres database using Go. If you haven’t noticed, a lot of this code is going to look pretty familiar to regular old SQL, and that is intended! Rather than reinventing the wheel, the database/sql package is intended to help you use SQL, not replace it, and as we continue to learn how to update and delete SQL records using Go you will notice that this remains true for pretty much all interactions you might end up having with an SQL DB.