Connect to PostgreSQL and Run a Query

· 303 words · 2 minutes read
#sql #postgre #connect #query #select #queryrow #close #pq #interface #database

Sometimes getting a database connection up and running can be a bit fiddly, we’ve all been there, and it can help to have an example to work from. This post aims to show you the complete basics of creating a database connection, forming a query to run and populating a struct with our resulting data.

To do with we use the database/sql interface and load in the pq driver/library to actually do the work for us. We’re using QueryRow() which will only return the single row. If you need more than one row you can use Query() which returns multiple rows for you to turn each into a struct.

Note: This should be very similar to connecting to CockroachDB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

const (
	// TODO fill this in directly or through environment variable
	// Build a DSN e.g. postgres://username:[email protected]:5432/dbName
	DB_DSN = ""
)

type User struct {
	ID       int
	Email    string
	Password string
}

func main() {

	// Create DB pool
	db, err := sql.Open("postgres", DB_DSN)
	if err != nil {
		log.Fatal("Failed to open a DB connection: ", err)
	}
	defer db.Close()

	// Create an empty user and make the sql query (using $1 for the parameter)
	var myUser User
	userSql := "SELECT id, email, password FROM users WHERE id = $1"

	err = db.QueryRow(userSql, 1).Scan(&myUser.ID, &myUser.Email, &myUser.Password)
	if err != nil {
		log.Fatal("Failed to execute query: ", err)
	}

	fmt.Printf("Hi %s, welcome back!\n", myUser.Email)
}

As a reference, this code uses a very basic schema structure:

1
2
3
4
5

CREATE TABLE users (
    id serial PRIMARY KEY,
    email VARCHAR (355) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL
);

postgreSQL example in go

Related Posts

Image of Author Edd Turtle

Author:  Edd Turtle

Edd is the Lead Developer at Hoowla, a prop-tech startup, where he spends much of his time working on production-ready Go and PHP code. He loves coding, but also enjoys cycling and camping in his spare time.

See something which isn't right? You can contribute to this page on GitHub or just let us know in the comments below - Thanks for reading!