Building a Simple CRUD Application with Go and PostgreSQL
Tags: go, postgres, beginners, tutorial
Published: 2023-05-06 12:31:49


# 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](https://dev.to/limaleandro1999/how-to-install-go-and-postgresql-5ef5) 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.

```sql
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.

```go
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.

```go
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.

```go
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.

```go
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.

```go
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.