Go(lang) Todo App (Part 2)

This is part of a series. The database setup is here. With the database setup we will work on building access to the todo data. This is usually called DAO for Data Access Object or Repository. I’m old, so I’ll use DAO on this tutorial, but you can use whatever you like.

With the “todos” table already created let’s code the access to it. Open the todos project in VSCode. We’ll start by creating the todos “model” in a file called todos_model.go

package main

import "time"

type Todo struct {
  ID string
  Title string
  Completed bool
  CreatedAt time.Time
  UpdatedAt time.Time
}

We’ll use this “model” all over our code to transfer data between layers. Let’s create the DAO layer by creating a file called todos_dao.go

package main

type TodoDao interface {
  GetAll() ([]*Todo, error)
  Get(todoID string) (*Todo, error)
  Create(todo *Todo) error
  Update(todo *Todo) error
  Delete(todo *Todo) error
  Done(todoID string) error
}

Let’s build our PostgreSQL implementation by creating a file called todos_dao_pg.go

package main

import (
 "database/sql"

 _ "github.com/jackc/pgx/v5/stdlib"
)

type TodoDaoPGImpl struct {
 conn *sql.DB
}

func NewPGTodoDao(conn *sql.DB) TodoDao {
 return &TodoDaoPGImpl{conn: conn}
}

func (dao *TodoDaoPGImpl) GetAll() ([]*Todo, error) {
 rows, err := dao.conn.Query(`
  SELECT 
   t.id
   , t.title
   , t.completed
   , t.created_at
   , t.updated_at
  FROM todos t
  order by t.created_at desc
 `)
 if err != nil {
  return nil, err
 }
 defer rows.Close()

 todos := []*Todo{}
 for rows.Next() {
  todo := &Todo{}
  err := rows.Scan(&todo.ID, &todo.Title, &todo.Completed, &todo.CreatedAt, &todo.UpdatedAt)
  if err != nil {
   return nil, err
  }
  todos = append(todos, todo)
 }
 return todos, nil
}

func (dao *TodoDaoPGImpl) Get(id string) (*Todo, error) {
 todo := &Todo{}
 err := dao.conn.QueryRow(`
  SELECT 
   t.id
   , t.title
   , t.completed
   , t.created_at
   , t.updated_at
  FROM todos t
  WHERE t.id = $1
 `, id).Scan(&todo.ID, &todo.Title, &todo.Completed, &todo.CreatedAt, &todo.UpdatedAt)
 if err != nil {
  return nil, err
 }
 return todo, nil
}

func (dao *TodoDaoPGImpl) Create(todo *Todo) error {
 _, err := dao.conn.Exec("INSERT INTO todos (id, title, completed, created_at, updated_at) VALUES ($1, $2, $3, NOW(), NOW())", todo.ID, todo.Title, todo.Completed)
 if err != nil {
  return err
 }
 return nil
}

func (dao *TodoDaoPGImpl) Update(todo *Todo) error {
 _, err := dao.conn.Exec("UPDATE todos SET title = $1, completed = $2, updated_at = now() WHERE id = $3", todo.Title, todo.Completed, todo.ID)
 if err != nil {
  return err
 }
 return nil
}

func (dao *TodoDaoPGImpl) Delete(id string) error {
 _, err := dao.conn.Exec("DELETE FROM todos WHERE id = $1", id)
 if err != nil {
  return err
 }
 return nil
}

func (dao *TodoDaoPGImpl) Done(id string) error {
 _, err := dao.conn.Exec("UPDATE todos SET completed = true, updated_at = now() WHERE id = $1", id)
 if err != nil {
  return err
 }
 return nil
}

Now let’s test it to make sure this code works by creating a file called todos_dao_pg_test.go

package main

import (
 "database/sql"
 "log"
 "testing"

 _ "github.com/jackc/pgx/v5/stdlib"
)

func init() {
 log.SetFlags(log.LstdFlags | log.Lshortfile)
}

func TestPGGetAll(t *testing.T) {
 t.Log("Testing Postgres GetAll")
 conn, err := sql.Open("pgx", "postgres://todos:T0d05!@localhost:5432/todos?sslmode=disable")
 if err != nil {
  t.Fatal(err)
 }
 defer conn.Close()

 conn.Exec("insert into todos (id, title, completed, created_at, updated_at) values ('test', 'test', false, now(), now())")

 dao := NewPGTodoDao(conn)
 todos, err := dao.GetAll()
 if err != nil {
  cleanup(conn)
  t.Fatal(err)
 }

 if len(todos) == 0 {
  cleanup(conn)
  t.Fatal("expected at least one todo")
 }

 cleanup(conn)
}

func TestPGGet(t *testing.T) {
 t.Log("Testing Postgres Get")
 conn, err := sql.Open("pgx", "postgres://todos:T0d05!@localhost:5432/todos?sslmode=disable")
 if err != nil {
  t.Fatal(err)
 }
 defer conn.Close()

 conn.Exec("insert into todos (id, title, completed, created_at, updated_at) values ('test', 'test', false, now(), now())")

 dao := NewPGTodoDao(conn)
 todo, err := dao.Get("test")
 if err != nil {
  cleanup(conn)
  t.Fatal(err)
 }

 if todo == nil {
  cleanup(conn)
  t.Fatal("expected a todo")
 }

 if todo.Title != "test" {
  cleanup(conn)
  t.Fatal("expected todo title to be 'test'")
 }

 cleanup(conn)
}

func TestPGCreate(t *testing.T) {
 t.Logf("Testing Postgres Create")
 conn, err := sql.Open("pgx", "postgres://todos:T0d05!@localhost:5432/todos?sslmode=disable")
 if err != nil {
  t.Fatal(err)
 }
 defer conn.Close()

 conn.Exec("insert into owners (id, name) values ('test', 'test')")

 dao := NewPGTodoDao(conn)
 todo := &Todo{ID: "test", Title: "test", Completed: false}
 err = dao.Create(todo)
 if err != nil {
  cleanup(conn)
  t.Fatal(err)
 }

 cleanup(conn)
}

func TestPGUpdate(t *testing.T) {
 t.Logf("Testing Postgres Update")
 conn, err := sql.Open("pgx", "postgres://todos:T0d05!@localhost:5432/todos?sslmode=disable")
 if err != nil {
  t.Fatal(err)
 }
 defer conn.Close()

 conn.Exec("insert into todos (id, title, completed, created_at, updated_at) values ('test', 'test', false, now(), now())")

 dao := NewPGTodoDao(conn)
 todo := &Todo{ID: "test", Title: "test", Completed: true}
 err = dao.Update(todo)
 if err != nil {
  cleanup(conn)
  t.Fatal(err)
 }

 cleanup(conn)
}

func TestPGDelete(t *testing.T) {
 t.Logf("Testing Postgres Delete")
 conn, err := sql.Open("pgx", "postgres://todos:T0d05!@localhost:5432/todos?sslmode=disable")
 if err != nil {
  t.Fatal(err)
 }
 defer conn.Close()

 conn.Exec("insert into todos (id, title, completed, created_at, updated_at) values ('test', 'test', false, now(), now())")

 dao := NewPGTodoDao(conn)
 err = dao.Delete("test")
 if err != nil {
  cleanup(conn)
  t.Fatal(err)
 }

 cleanup(conn)
}

func cleanup(conn *sql.DB) {
 conn.Exec("delete from owners where name like 'test%'")
}

To test our code open a terminal and run the following:

go test

And you should be getting something like this: