Connect to PostgreSQL with Golang

Connecting to a database is a fundamental task in many applications, and PostgreSQL is a popular choice for storing and managing the data. In this tutorial, we will explore how to build a connection to a PostgreSQL database using the Go programming language (Golang). We will go over everything including setting up the required packages, running the SQL statements, and handling failures. By the end of this tutorial, we will have a solid foundation for working with PostgreSQL in the Golang applications.

Setting Up PostgreSQL in GO

Begin with the first step to set up PostgreSQL. In this case, we need to create the directory which is “go-access-postgres” using the “mkdir” command in the terminal or command prompt. Here, the command of creating the directory for PostgreSQL is given. The current location will receive a directory after this.

Next, to navigate to the “go-access-postgres” directory, we can use the “cd” command. Assuming that the directory exists in the current location, we can execute the following command:

Now, we are in the “go-access-postgres” directory as shown in the screen of the followingcommand prompt:

In the next step, a Go module called “example/gopostgressql” is initialized using the Go “mod init” command. Here’s a command for this:

The previous command creates a new Go module with the specified module path. The module path is typically the import path prefix for the package that is developed within this module. The command created with a “go.mod” file in the current directory which tracks the module’s dependencies. The command’s output has the following appearance:

After that, we install the package to work with the PostgreSQL database. For this, the “go get” command serves to download and install the packages, along with any dependencies that they may have, from the external repositories. In our scenario, we want to acquire the “github.com/lib/pq” package which is a popular Go package to work with PostgreSQL databases. There, we have the command to download the PostgreSQL package.

Here, the “github.com/lib/pq” package and its dependencies are downloaded and installed in the Go workspace. The -u flag that is used in the command is to update the package to the latest version if it already exists.

Build the PostgreSQL Database Connection in GO

We get the PostgreSQL driver in GO by following the previous steps. Now, we use that package here to connect to the database. We use the following code inside the Go file to build the database connection:

Here, we include the “database/sql” package which gives the users with a universal interface to interact with SQL databases. We also import the “github.com/lib/pq” as a blank import which means that only the package’s side effects are used. In this case, it registers the PostgreSQL driver with the “database/sql” package which allows us to use PostgreSQL as the underlying database.

After that, we define the main() function where the connectString is set up as a string variable that contains the connection string for the PostgreSQL database. It specifies the username, password, host, port, and database name to connect to.

Following that, we employ the sql.Open(“postgres”, connectString) function which tries to establish a connection to the PostgreSQL database using the supplied connection string. It returns a *sql.DB object (connection) and an error. The connection statement is printed if the connection with the given database is built successfully. Finally, we use the MyConn.Close() function to terminate the database connection.

The database is successfully connected as the console displays the following message:

Check the PostgreSQL Version in GO

Now, we all are good to go to run the PostgreSQL queries with Go. Here, we run a query to get the PostgreSQL version. The following code is defined in the “main.go” file:

We first import the necessary packages for this program. Then, we define the main() function where the “connStr” variable contains the connection string that is used to connect to the PostgreSQL database. It specifies the username, password, database name, and SSL mode.

Then, a connection is made to the PostgreSQL database using the sql.Open() function. It takes two parameters: the driver’s name (“postgres”) and the connection string. It returns a *sql.DB object that represents the database connection.

After that, an SQL query is then run on the database using the conn.Query() function. In this case, it executes the “SELECT version();”query which retrieves the version of the PostgreSQL server. Next, the rows.Next() method of the query is used to iterate through the result set using the “for” loop. Inside the loop, the rows.Scan() method retrieves the value from the query result and stores it in the version variable. Finally, the rows.Close() method is called to close the result set.

The following is a retrieval of the PostgreSQL’s version details:

Create and Insert the Table into the PostgreSQL Database with GO

Next, we attempt to create the table in PostgreSQL and insert the rows of the data into the table of that database within the Go file. The code is set up as follows:

Here, the program is the same as the previous code where we first add the required packages and then build the database connection. To create the table, we define the “createTable” variable which holds the SQL statement to create the “students” table if it doesn’t exist. It has three columns: “id” ( primary key), “name” (string), and “age” (integer).

We then pass the “createTable” SQL statement in the conn.Exec() function. If an error occurs, it panics and terminates the program. Next, we insert the rows of the data into the table by creating the “insertData” variable. The “insertData” variable contains the SQL statement to insert two rows of the data into the “students” table. It uses the parameterized queries with $1, $2, $3, and $4 as placeholders for values.

We can see the print statement in the output that the table is successfully created and the data is also inserted:

Conclusion

We discovered how to use Golang to establish a connection to a PostgreSQL database. We covered how to install the necessary packages, establish a connection, execute the SQL statements, and close the connection. Building reliable database-driven programs is made much easier by the simplicity of Golang and the power of PostgreSQL.