Connecting with database
Tags: go, gin, postgres
Published: 2021-06-24 17:10:32

So far our backend is working very well. We can create user and login, but as soon we restart our server, that user data is lost, since it is saved only in memory while app is running. Of course that's not realistic case and we want to save all user data even if server is restarted. For that purpose all data will be saved to database. Database that we will use is PostgreSQL as mentioned in intro. We will not cover PostgreSQL installation as there is documentation about that on official page. For next steps we will assume that PostgreSQL is installed and configured. In all examples we will use default `postgres` user with password `postgres`.

Let's first connect to PostgreSQL to create new database. If PostgreSQL service is not running you will have to start it first, and then connect with `postgres` user:

```bash
sudo service postgresql start
sudo -u postgres psql
```

Once you are connected, create new database:

```sql
CREATE DATABASE rgb;
```

For database communication we will use [go-pg](https://github.com/go-pg/pg/) module. You can install it by running `go get github.com/go-pg/pg/v10`. This will install version 10 of `go-pg` module, which is latest version at the time of writing this guide. Now create new directory `internal/database/` and file `database.go` inside of it.

```go
package database

import (
  "github.com/go-pg/pg/v10"
)

func NewDBOptions() *pg.Options {
  return &pg.Options{
    Addr:     "localhost:5432",
    Database: "rgb",
    User:     "postgres",
    Password: "postgres",
  }
}
```

That will only create new options used for connecting to database. Create new file `internal/store/store.go` where we will setup database connection to be used by our `store` package which will be the only one communicating with database.

```go
package store

import (
  "log"

  "github.com/go-pg/pg/v10"
)

// Database connector
var db *pg.DB

func SetDBConnection(dbOpts *pg.Options) {
  if dbOpts == nil {
    log.Panicln("DB options can't be nil")
  } else {
    db = pg.Connect(dbOpts)
  }
}

func GetDBConnection() *pg.DB { return db }
```

Here we have one variable `db` which will serve as a database connector for store package. We also have two functions, one to set that database connector, and one to get it. Function `SetDBConnection()` will be used immediately because we need to set our database connector. As already said, only store package will connect to database and `db` variable is available in whole package. So, what do we need `GetDBConnection()` for? Well, we actually don't need it yet, but we will need it in next section to run migrations, because that will be happening in separate small app, as you will see soon. For now, let's only set database connector inside of `internal/server/server.go` file by adding line `store.SetDBConnection(database.NewDBOptions())`:

```go
package server

import (
  "rgb/internal/database"
  "rgb/internal/store"
)

func Start() {
  store.SetDBConnection(database.NewDBOptions())

  router := setRouter()

  // Start listening and serving requests
  router.Run(":8080")
}
```

With that in place, we are ready to implement methods for creating and authenticating users in `internal/store/users.go` file:

```go
package store

import "errors"

type User struct {
  ID       int
  Username string `binding:"required,min=5,max=30"`
  Password string `binding:"required,min=7,max=32"`
}

func AddUser(user *User) error {
  _, err := db.Model(user).Returning("*").Insert()
  if err != nil {
    return err
  }
  return nil
}

func Authenticate(username, password string) (*User, error) {
  user := new(User)
  if err := db.Model(user).Where(
    "username = ?", username).Select(); err != nil {
    return nil, err
  }
  if password != user.Password {
    return nil, errors.New("Password not valid.")
  }
  return user, nil
}
```

Functions above will be used in `internal/server/user.go`:

```go
package server

import (
  "net/http"
  "rgb/internal/store"

  "github.com/gin-gonic/gin"
)

func signUp(ctx *gin.Context) {
  user := new(store.User)
  if err := ctx.Bind(user); err != nil {
    ctx.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"error": err.Error()})
    return
  }
  if err := store.AddUser(user); err != nil {
    ctx.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"error": err.Error()})
    return
  }
  ctx.JSON(http.StatusOK, gin.H{
    "msg": "Signed up successfully.",
    "jwt": "123456789",
  })
}

func signIn(ctx *gin.Context) {
  user := new(store.User)
  if err := ctx.Bind(user); err != nil {
    ctx.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"error": err.Error()})
    return
  }
  user, err := store.Authenticate(user.Username, user.Password)
  if err != nil {
    ctx.AbortWithStatusJSON(http.StatusUnauthorized, gin.H{"error": "Sign in failed."})
    return
  }

  ctx.JSON(http.StatusOK, gin.H{
    "msg": "Signed in successfully.",
    "jwt": "123456789",
  })
}
```

If we try to create new account now we will get error. That's expected since we are trying to add our data to users table which doesn't exist yet. We will fix that in next section by using migrations.