[GO] Todo list - postgresql

DB를 sqlite 에서 postgresql 로 변경하는 방법입니다.

최종소스는 여기서 참고하세요 ==> [heroku 배포용 최종소스]

 

여기서 다루는 내용은 

postgresql - heroku 에 postgresql 설치하는 방법

postgresql - 패키지 설치

postgresql 핸들러 만들기

model.go, main.go - 핸들러 및 DB 변경 코드 수정

 

 

1. heroku 에 postgresql 설치하는 방법

 

heroku_todos>heroku addons:create heroku-postgresql:hobby-dev
 »   Warning: heroku update available from 7.53.0 to 7.59.2.
Creating heroku-postgresql:hobby-dev on ⬢ fierce-sands-85340... free
Database has been created and is available
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pg:copy
Created postgresql-acute-27849 as DATABASE_URL
Use heroku addons:docs heroku-postgresql to view documentation


// 위에서 생성된 DB 주소가 환경변수(DATABASE_URL)로 저장된다
heroku_todos>heroku config
 »   Warning: heroku update available from 7.53.0 to 7.59.2.
=== fierce-sands-85340 Config Vars
DATABASE_URL:      postgres://vlpmujbesscrsq:23af877fb73bb3dc25f733213c08cb2bc4ee2a687490cb0b0a6bcbaeb3d2ff1d@ec2-44-199-85-33.compute-1.amazonaws.com:5432/d11ao4kq8bemh1
DOMAIN_NAME:       https://fierce-sands-85340.herokuapp.com
GOOGLE_CLIENT_ID:  개인키
GOOGLE_SECRET_KEY: 개인키
SESSION_KEY:       개인키

 

 

2. 패키지 설치

 

heroku_todos>go get github.com/lib/pq
go: downloading github.com/lib/pq v1.10.4
go get: added github.com/lib/pq v1.10.4

 

 

3. pqHandler.go - DB 변경으로 인한 함수 이름 수정 및 sql 문법 수정

 

package model

import (
	"database/sql"
	"time"

	_ "github.com/lib/pq"
)

type pqHandler struct {
	db *sql.DB
}

func (s *pqHandler) GetTodos(sessionId string) []*Todo {
	todos := []*Todo{}
	sql_string := "SELECT id, name, completed, createdAt From todos WHERE sessionId = $1"
	rows, err := s.db.Query(sql_string, sessionId)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var todo Todo
		rows.Scan(&todo.ID, &todo.Name, &todo.Completed, &todo.CreatedAt)
		todos = append(todos, &todo)
	}
	return todos
}

func (s *pqHandler) AddTodo(name, sessionId string) *Todo {
	sql_string := "INSERT INTO todos (sessionId, name, completed, createdAt) VALUES($1,$2,$3,now()) RETURNING id"
	stmt, err := s.db.Prepare(sql_string)
	if err != nil {
		panic(err)
	}

	var id int
	err = stmt.QueryRow(sessionId, name, false).Scan(&id)
	if err != nil {
		panic(err)
	}

	var todo Todo
	todo.ID = id
	todo.Name = name
	todo.Completed = false
	todo.CreatedAt = time.Now()

	return &todo
}

func (s *pqHandler) RemoveTodo(id int) bool {
	sql_string := "DELETE FROM todos WHERE id = $1"
	stmt, err := s.db.Prepare(sql_string)
	if err != nil {
		panic(err)
	}

	result, err := stmt.Exec(id)
	if err != nil {
		panic(err)
	}
	cnt, _ := result.RowsAffected()

	return cnt > 0
}

func (s *pqHandler) CompleteTodo(id int, complete bool) bool {
	sql_string := "UPDATE todos SET completed = $1 WHERE id = $2"
	stmt, err := s.db.Prepare(sql_string)
	if err != nil {
		panic(err)
	}

	result, err := stmt.Exec(complete, id)
	if err != nil {
		panic(err)
	}
	cnt, _ := result.RowsAffected()

	return cnt > 0
}

func (s *pqHandler) Close() {
	s.db.Close()
}

func newPQHandler(dbConn string) DBHandler {
	database, err := sql.Open("postgres", dbConn)
	if err != nil {
		panic(err)
	}
	statement, err := database.Prepare(
		`CREATE TABLE IF NOT EXISTS todos (
			id        SERIAL PRIMARY KEY,
			sessionId VARCHAR(256),
			name      TEXT,
			completed BOOLEAN,
			createdAt TIMESTAMP
		);`)
	if err != nil {
		panic(err)
	}
	_, err = statement.Exec()
	if err != nil {
		panic(err)
	}

	statement, err = database.Prepare(
		`CREATE INDEX IF NOT EXISTS sessionIdIndexOnTodos ON todos(sessionId ASC);`)
	if err != nil {
		panic(err)
	}
	_, err = statement.Exec()
	if err != nil {
		panic(err)
	}

	return &pqHandler{db: database}
}

 

 

4. model.go - 핸들러 변경

 

func NewDBHandler(dbConn string) DBHandler {
    handler := newPQHandler(dbConn)
    return handler
}

 

 

5. main.go

 

func main() {
	port := os.Getenv("PORT")

	mux := myapp.MakeNewHandler(os.Getenv("DATABASE_URL"))
	defer mux.Close()

	log.Println("Started App, portNo:", port)
	err := http.ListenAndServe(":"+port, mux)
	if err != nil {
		panic(err)
	}
}

 

 

서비스는 "https://fierce-sands-85340.herokuapp.com" 여기서 확인 가능함.

 

 

참고자료

[유투브 링크]