Building a Simple CRUD Application with Go and PostgreSQL

Do you want to learn how to build a simple CRUD (Create Read Update Delete) application with Go and Postgres? Creating simple web applications is an important part of the development process – this blog post will explain the basics of using Go and Postgres to create a simple CRUD application.

Go is an increasingly popular programming language for web applications, with libraries for data storage and manipulation, making development fast and reliable. PostgreSQL is an open-source, ACID-compliant relational database system well-suited for applications needing to store data in complex structures. Together, they form a powerful combination.

Setting Up Your Environment

Before you can create your application, you'll need to set up the environment. First, you'll need to install Go and PostgreSQL. For this, you'll need to follow the instructions in our post How to Install Go and PostgreSQL for your specific system.

Next, you'll need to install a library for working with PostgreSQL. For this guide, we're using github.com/lib/pq. You can easily install it with the go get command.

go get -u github.com/lib/pq

Defining the Database Structure

Once your environment is set up, it's time to define the database structure for your application. PostgreSQL allows you to use SQL statements to define a database, so for this example, you'll create a table to store user information.

CREATE TABLE User (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

The id column will be the primary key and will be auto-generated. The username and password fields are required, so NOT NULL is set in the table definition. This simple table will store all the user information your application needs.

Establishing a Database Connection

Before you can work with the database, you'll need to establish a connection. To do this, you'll use the sql.Open() function from the github.com/lib/pq library and pass in the connection string.

db, err := sql.Open("postgres", "postgres://user:password@localhost/db_name?sslmode=disable")

if err != nil {
    log.Fatal(err)
}

defer db.Close()

This establishes a connection to the database and stores it in the db variable. The defer statement will make sure that the connection is properly closed when the program finishes execution.

Creating a User

Now that you have a working connection to the database, you can start writing code that interacts with it. To create a new user, you can use the INSERT INTO SQL statement and the Exec() function from the github.com/lib/pq library.

query := "INSERT INTO User(username, password) VALUES($1, $2)"

_, err := db.Exec(query, "example_user", "example_password")

if err != nil {
    log.Fatal(err)
}

This code will insert a new user with the username example_user and the password example_password. You can replace these with your own values to create different users.

Retrieving a User

Once you've created a user, you can retrieve it again using the SELECT SQL statement and the QueryRow() function from the github.com/lib/pq library.

query := "SELECT * FROM User WHERE username = $1"

user := &User{}

err := db.QueryRow(query, "example_user").Scan(&user.id, &user.username, &user.password)

if err != nil {
    log.Fatal(err)
}

fmt.Printf("%+v\n", user)

The code above will retrieve the user with the username example_user. It will store the user in the user variable and print it to the console.

Sure, here's the next section:

Updating a User

Once you have a user, you can update it using the UPDATE SQL statement and the Exec() function from the github.com/lib/pq library.

query := "UPDATE User SET password = $1 WHERE username = $2"

_, err := db.Exec(query, "new_example_password", "example_user")

if err != nil {
    log.Fatal(err)
}

This code will update the user with the username example_user and replace their password with new_example_password.

Deleting a User

Finally, you can delete a user using the DELETE SQL statement and the Exec() function from the github.com/lib/pq library.

query := "DELETE FROM User WHERE username = $1"

_, err := db.Exec(query, "example_user")

if err != nil {
    log.Fatal(err)
}

This code will delete the user with the username example_user.

Conclusion

In this post, you've learned how to create a simple CRUD application with Go and PostgreSQL. You've seen how to set up your environment, define a database, establish a connection, create, retrieve, update, and delete a user.

You can now use these steps to create your own web applications with Go and PostgreSQL.