PostgreSQL, often simply referred to as Postgres, is a powerful, open-source object-relational database system. With more than 30 years of active development, it has gained a strong reputation for its robustness, advanced features, and substantial standards compliance.
It is designed to handle the extensive workloads, from single machines to data warehouses or web services with many concurrent users. It provides transactional integrity and durability and supports various advanced data types and powerful, flexible data manipulation language.
This tutorial teaches us how to configure and connect a PostgreSQL server with a Go application.
Requirements:
Go Programming Language – It ensures that the Go compiler is installed on your machine.
PostgreSQL Database – You also need to have the PostgreSQL installed. You can install it directly on your machine or use a Docker container for a more straightforward setup.
Development Tools – An Integrated Development Environment (IDE) that supports Go like Visual Studio Code, GoLand, etc.
Basic Knowledge of SQL and Go – To interact with the PostgreSQL database, you must understand SQL and the various queries that you need to carry out. You also need a basic understanding of the Go language.
With the given requirements met, we can proceed to the following steps.
Project Setup
Let us start by setting up our project directory structure. Create the main directory where you need to store the project source code.
Navigate into the directory and initialize a new Go module.
Install the Required Drivers
In order to interact with the PostgreSQL database, we need a PostgreSQL driver for the Go programing language.
For this tutorial, we use the pq driver which offers a wide array of features to work with the PostgreSQL database.
You can install it by running the following command:
Create a Database
The next step is to create a new PostgreSQL database. Again, we can do this in the PostgreSQL shell.
This command prompts you for the password of the postgres. Once logged, run the “create database” command to initialize a new database:
CREATE DATABASE
The given command creates a new database called “golang”. Feel free to replace the database name with your desired name.
You can then verify that the database is created successfully by connecting to it.
You are now connected to database "golang" as user "postgres".
Next, set up the table where you wish to store the data. We create a simple table for this tutorial that stores the session information.
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
session_time TIMESTAMP NOT NULL,
number_of_queries INT DEFAULT 0,
active_state BOOLEAN DEFAULT FALSE
);
The given query should create a new table called “db_clients” that stores an information about the clients that are logged into a given database server.
Connect the Go App with PostgreSQL
Once we setup the database and the database table, we can proceed and learn how to connect our Go application with the PostgreSQL server.
Start by creating a “main.go” file in the root of your project:
Next, edit the file with your text editor of choice:
In the “main.go” file, add the source code as shown in the following:
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
)
func main() {
connStr := "postgres://postgres:password@localhost/golang?sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
log.Fatal(err)
}
if err = db.Ping(); err != nil {
log.Fatal(err)
}
fmt.Println("Connected to database")
}
In the previous code, we start by importing the required modules. We then define the main function. Finally, in the main function, we define the connection string which allows us to specify the target host, database user, database password, and the actual target database.
The module supports the following parameters in the connection string:
- dbname – The name of the database to connect to.
- user – The user to sign in as.
- password – The user’s password.
- host – The host to connect to. The values that start with “/” are for Unix domain sockets (the default is localhost).
- port – The port to bind to (the default is 5432).
- sslmode – Whether or not to use SSL (the default is require; this is not the default for libpq).
- fallback_application_name – An application_name to fall back to if one isn’t provided.
- connect_timeout – The maximum wait for connection in seconds. Zero or not specified means to wait indefinitely.
- sslcert – The cert file location. The file must contain the PEM-encoded data.
- sslkey – The key file location. The file must contain the PEM-encoded data.
- sslrootcert – The location of the root certificate file. The file must contain the PEM-encoded data.
You can configure your target connection properties as necessary. To configure SSL and the accepted SSLMode parameters, check our tutorial on https://linuxhint.com/postgres-sslmode.
Once satisfied with the connection properties, you can run the previous code to test whether the connection is established.
Upon connection, you should get the following output:
PostgreSQL Insert Data Into the Table
The next step is to insert the sample data into the table that we created earlier. We can do this by defining the insert queries as a string literal and then use the db.Exec() function to run the target query.
The code is as follows:
insertStatement := `INSERT INTO db_clients (name, session_time, number_of_queries, active_state)
VALUES
('psql', '2023-05-26 10:15:00', 10, true),
('datagrip', '2023-05-26 09:30:00', 5, true),
('dbeaver', '2023-05-26 11:00:00', 20, true),
('workbench', '2023-05-26 14:45:00', 15, false),
('remote', '2023-05-26 13:20:00', 8, true);`
_, err = db.Exec(insertStatement)
if err != nil {
log.Fatal(err)
}
If you run the previous code, it should insert the specified records into the target table.
PostgreSQL Query Data
To query the rows from the table, we can define the select statement as a string and use the db.Query() to run it. We can iterate over the table rows using the Next() function and print them out as shown in the following:
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name string
var sessionTime time.Time
var numberOfQueries int
var activeState bool
err := rows.Scan(&id, &name, &sessionTime, &numberOfQueries, &activeState)
if err != nil {
log.Fatal(err)
}
fmt.Printf("ID: %d, Name: %s, Session Time: %s, Number of Queries: %d, Active State: %t\n", id, name, sessionTime, numberOfQueries, activeState)
}
if err = rows.Err(); err != nil {
log.Fatal(err)
}
Once we run the previous code, it should print all the rows from the db_clients table as shown in the following example output:
There you have it!
Conclusion
We explored how we can use the pq package to connect and query the PostgreSQL database using Golang.
