こんにちは、KOUKIです。
Golang/ReactでMovie Appの開発をしています。
今回は、PostgreSQLを導入し、DB処理を実装していきます。
尚、Udemyの「Working with React and Go (Golang)」を参考にしているので、よかったら受講してみてください。
<目次>
前回
事前準備
フォルダ/ファイル
1 2 3 4 |
mkdir postgres touch postgres/go_movies.sql touch Dockerfile-postgres touch backend-app/models/movies-db.go |
モジュール
1 2 |
cd backend-app/ go get -u github.com/lib/pq@v1.10.0 |
PostgresSQLのDocker化
PostgreSQLの環境をDockerで導入します。
Dockerfile-postgres
Dockerfile-postgresファイルには、PostgreSQLの構成情報を記述します。
1 2 3 |
FROM postgres:12-alpine COPY ./postgres/*.sql /docker-entrypoint-initdb.d/ |
ここで注目なのが、「docker-entrypoint-initdb.d」フォルダ配下にsqlファイルを設置しておくとコンテナ作成時に、自動的にsqlを実行してくれるところです。DBの初期化に役立ちます。
docker-compose.yml
docker-compose.ymlにpostgresサービスを追加します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# docker-compose.yml version: "3.3" services: ... postgres: build: context: . dockerfile: ./Dockerfile-postgres environment: - POSTGRES_USER=postgres - POSTGRES_PASSWORD=secret - POSTGRES_DB=go_movies ports: - "5432:5432" container_name: postgres postgres-gui: image: donnex/pgweb command: -s --bind=0.0.0.0 --listen=8080 --url postgresql://postgres:secret@postgres/go_movies?sslmode=disable links: - postgres:postgres ports: - "9232:8080" depends_on: - postgres |
PostgreSQL本体とGUIを用意しました。
go-movies.sql
初期化SQLを以下に記載します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 |
-- go_movies.sql -- -- PostgreSQL database dump -- -- Dumped from database version 12.3 -- Dumped by pg_dump version 12.3 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: genres; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.genres ( id integer NOT NULL, genre_name character varying, created_at timestamp without time zone, updated_at timestamp without time zone ); -- -- Name: genres_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.genres_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: genres_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.genres_id_seq OWNED BY public.genres.id; -- -- Name: movies; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.movies ( id integer NOT NULL, title character varying, description text, year integer, release_date date, runtime integer, rating integer, mpaa_rating character varying, created_at timestamp without time zone, updated_at timestamp without time zone ); -- -- Name: movies_genres; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.movies_genres ( id integer NOT NULL, movie_id integer, genre_id integer, created_at timestamp without time zone, updated_at timestamp without time zone ); -- -- Name: movies_genres_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.movies_genres_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: movies_genres_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.movies_genres_id_seq OWNED BY public.movies_genres.id; -- -- Name: movies_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.movies_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: movies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.movies_id_seq OWNED BY public.movies.id; -- -- Name: genres id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.genres ALTER COLUMN id SET DEFAULT nextval('public.genres_id_seq'::regclass); -- -- Name: movies id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies ALTER COLUMN id SET DEFAULT nextval('public.movies_id_seq'::regclass); -- -- Name: movies_genres id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ALTER COLUMN id SET DEFAULT nextval('public.movies_genres_id_seq'::regclass); -- -- Data for Name: genres; Type: TABLE DATA; Schema: public; Owner: - -- COPY public.genres (id, genre_name, created_at, updated_at) FROM stdin; 1 Drama 2021-05-17 00:00:00 2021-05-17 00:00:00 2 Crime 2021-05-17 00:00:00 2021-05-17 00:00:00 3 Action 2021-05-17 00:00:00 2021-05-17 00:00:00 4 Comic Book 2021-05-17 00:00:00 2021-05-17 00:00:00 5 Sci-Fi 2021-05-17 00:00:00 2021-05-17 00:00:00 6 Mystery 2021-05-17 00:00:00 2021-05-17 00:00:00 7 Adventure 2021-05-17 00:00:00 2021-05-17 00:00:00 8 Comedy 2021-05-17 00:00:00 2021-05-17 00:00:00 9 Romance 2021-05-17 00:00:00 2021-05-17 00:00:00 \. -- -- Data for Name: movies; Type: TABLE DATA; Schema: public; Owner: - -- COPY public.movies (id, title, description, year, release_date, runtime, rating, mpaa_rating, created_at, updated_at) FROM stdin; 1 The Shawshank Redemption Two imprisoned men bond over a number of years 1994 1994-10-14 142 5 R 2021-05-17 00:00:00 2021-05-17 00:00:00 2 The Godfather The aging patriarch of an organized crime dynasty transfers control to his son 1972 1972-03-24 175 5 R 2021-05-17 00:00:00 2021-05-17 00:00:00 4 American Psycho A wealthy New York investment banking executive hides his alternate psychopathic ego 2000 2000-04-14 102 4 R 2021-05-17 00:00:00 2021-05-17 00:00:00 3 The Dark Knight The menace known as the Joker wreaks havoc on Gotham City 2008 2008-07-18 152 5 PG13 2021-05-17 00:00:00 2021-05-17 00:00:00 \. -- -- Data for Name: movies_genres; Type: TABLE DATA; Schema: public; Owner: - -- COPY public.movies_genres (id, movie_id, genre_id, created_at, updated_at) FROM stdin; \. -- -- Name: genres_id_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public.genres_id_seq', 9, true); -- -- Name: movies_genres_id_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public.movies_genres_id_seq', 1, false); -- -- Name: movies_id_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public.movies_id_seq', 4, true); -- -- Name: genres genres_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.genres ADD CONSTRAINT genres_pkey PRIMARY KEY (id); -- -- Name: movies_genres movies_genres_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ADD CONSTRAINT movies_genres_pkey PRIMARY KEY (id); -- -- Name: movies movies_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies ADD CONSTRAINT movies_pkey PRIMARY KEY (id); -- -- Name: movies_genres fk_movie_genries_genre_id; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ADD CONSTRAINT fk_movie_genries_genre_id FOREIGN KEY (genre_id) REFERENCES public.genres(id); -- -- Name: movies_genres fk_movie_genries_movie_id; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ADD CONSTRAINT fk_movie_genries_movie_id FOREIGN KEY (movie_id) REFERENCES public.movies(id); -- -- PostgreSQL database dump complete -- |
コンテナの立ち上げ
下記のコマンドで、PostgreSQLコンテナを立ち上げます。
確認
ブラウザから「http://localhost:9232/」にアクセスするとテーブルと導入されたデータが確認できます。

PostgreSQLへ接続
GolangからPostgreSQLへ接続しましょう。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
// cmd/api/main.go package main import ( "context" "database/sql" "flag" "fmt" "log" "net/http" "os" "time" _ "github.com/lib/pq" ) const ( version = "1.0.0" // 追加 dsnString = "postgres://postgres:secret@postgres/go_movies?sslmode=disable" ) type config struct { port int env string db struct { // 追加 dsn string } } .... func main() { var cfg config ... // postgres dsn追加 flag.StringVar(&cfg.db.dsn, "dsn", dsnString, "Postgres connection string") flag.Parse() logger := log.New(os.Stdout, "", log.Ldate|log.Ltime) // db接続 db, err := openDB(cfg) if err != nil { logger.Fatal(err) } defer db.Close() ... err = srv.ListenAndServe() if err != nil { log.Println(err) } } func openDB(cfg config) (*sql.DB, error) { db, err := sql.Open("postgres", cfg.db.dsn) if err != nil { return nil, err } ctx, cancle := context.WithTimeout(context.Background(), 5*time.Second) defer cancle() err = db.PingContext(ctx) if err != nil { return nil, err } return db, nil } |
PostgreSQLへ接続するDSNですが、docker-compose.ymlに設定したService名、ユーザー名、パスワード、テーブル名を使用しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
dsnString = "postgres://postgres:secret@postgres/go_movies?sslmode=disable" ↓ postgres: // service名 build: context: . dockerfile: ./Dockerfile-postgres environment: - POSTGRES_USER=postgres // ユーザー名 - POSTGRES_PASSWORD=secret // パスワード - POSTGRES_DB=go_movies // テーブル名 ports: - "5432:5432" container_name: postgres |
コンソールからエラーが出ていなければ、接続成功です。
1 2 3 |
backend_1 | building... backend_1 | running... backend_1 | 2021/08/04 20:45:38 Starting server on port 4000 |
DB処理
GolangからPostgreSQLを操作するコードを実装しましょう。
Modelとメソッド
movies-db.goには、Movieデータの取得処理を実装します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
// models/movies-db.go package models import "database/sql" type DBModel struct { DB *sql.DB } // Get retuns one movie and error, if any func (m *DBModel) Get(id int) (*Movie, error) { return nil, nil } // All retuns all movies and error, if any func (m *DBModel) All() ([]*Movie, error) { return nil, nil } |
中身は後で実装します。
DBModelのラッパーも実装しておきましょう。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// models/models.go package models import ( "database/sql" "time" ) // Models is the wrapper for database type Models struct { DB DBModel } // NewModels returns models with db pool func NewModels(db *sql.DB) Models { return Models{ DB: DBModel{DB: db}, } } |
Modelをapplicationに登録
main.goに実装したapplication構造体に、Modelを追加します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// cmd/api/main.go .... type application struct { config config logger *log.Logger models models.Models // 追加 } func main() { var cfg config flag.IntVar(&cfg.port, "port", 4000, "Server port to listen on") flag.StringVar(&cfg.env, "env", "development", "Application environment (development|production)") ... app := &application{ config: cfg, logger: logger, models: models.NewModels(db),, // 追加 } } |
これで、DBにアクセスしてデータを取得する枠組みを実装することができました。
Getメソッドの実装
Getメソッドは、利用者側から渡されたidをキーにDBを検索し、Movie情報を返します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
// models/movies-db.go ... // Get retuns one movie and error, if any func (m *DBModel) Get(id int) (*Movie, error) { ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second) defer cancel() query := `select id, title, description, year, release_date, rating, runtime, mpaa_rating, created_at, updated_at from movies where id = $1` row := m.DB.QueryRowContext(ctx, query, id) var movie Movie err := row.Scan( &movie.ID, &movie.Title, &movie.Description, &movie.Year, &movie.ReleaseDate, &movie.Rating, &movie.Runtime, &movie.MPAARating, &movie.CreatedAt, &movie.UpdatedAt, ) if err != nil { return nil, err } return &movie, nil } |
このメソッドは、前回実装したMovieハンドラーから呼び出します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// cmd/api/movie-handlers.go ... func (app *application) getOneMovie(w http.ResponseWriter, r *http.Request) { params := httprouter.ParamsFromContext(r.Context()) // urlからidを抜きだす id, err := strconv.Atoi(params.ByName("id")) if err != nil { app.logger.Print(errors.New("invalid id parameter")) app.errorJSON(w, err) return } app.logger.Println("id is", id) movie, err := app.models.DB.Get(id) // 追加 err = app.writeJSON(w, http.StatusOK, movie, "movie") } |
下記のパラメータで、テストをしましょう。
- URL: http://localhost:4000/v1/movie/1
- 形式: GET

OKですね。PostgreSQLに保存されたデータが取得できました。
Genres取得
Movie構造体には、MovieGenre
があります。これは、Movieのカテゴリーを示すものです。
これを取得する処理をGetメソッドに追加します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
// models/movies-db.go ... // Get retuns one movie and error, if any func (m *DBModel) Get(id int) (*Movie, error) { ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second) defer cancel() query := `select id, title, description, year, release_date, rating, runtime, mpaa_rating, created_at, updated_at from movies where id = $1` row := m.DB.QueryRowContext(ctx, query, id) var movie Movie err := row.Scan( &movie.ID, &movie.Title, &movie.Description, &movie.Year, &movie.ReleaseDate, &movie.Rating, &movie.Runtime, &movie.MPAARating, &movie.CreatedAt, &movie.UpdatedAt, ) if err != nil { return nil, err } // 追加 query = `select mg.id, mg.movie_id, mg.genre_id, g.genre_name from movies_genres mg left join genres g on (g.id = mg.genre_id) where mg.movie_id = $1 ` rows, _ := m.DB.QueryContext(ctx, query, id) defer rows.Close() var genres []MovieGenre for rows.Next() { var mg MovieGenre err := rows.Scan( &mg.ID, &mg.MovieID, &mg.GenreID, &mg.Genre.GenreName, ) if err != nil { return nil, err } genres = append(genres, mg) } movie.MovieGenre = genres return &movie, nil } |
さらに、Movie構造体のjsonタグも変更します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// models/models.go // Movie is the type for movies type Movie struct { ID int `json:"id"` Title string `json:"title"` Description string `json:"description"` Year int `json:"year"` ReleaseDate time.Time `json:"release_date"` Runtime int `json:"runtime"` Rating int `json:"rating"` MPAARating string `json:"mpaa_rating"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` MovieGenre []MovieGenre `json:"genres"` // 変更 } |
「json:"-"
」⇨ 「json:"genres"
」に変更しました。これで、呼び出し元でgenresが確認できます。
また、「http://localhost:9232/」へアクセスして、下記のデータを追加しましょう。
1 |
INSERT INTO movies_genres (movie_id, genre_id) VALUES (1, 1); |

データの投入に成功したら、下記のパラメータで、テストをしましょう。
- URL: http://localhost:4000/v1/movie/1
- 形式: GET

genres(カテゴリー)にデータが入っていることが確認できれば、OKです。
不要なデータの非表示
戻り値に不要なデータは、jsonキーワードに「-」を指定することで非表示にすることができます。
例えば、以下のような感じです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
// models/models.go // Movie is the type for movies type Movie struct { ID int `json:"id"` Title string `json:"title"` Description string `json:"description"` Year int `json:"year"` ReleaseDate time.Time `json:"release_date"` Runtime int `json:"runtime"` Rating int `json:"rating"` MPAARating string `json:"mpaa_rating"` CreatedAt time.Time `json:"-"` UpdatedAt time.Time `json:"-"` MovieGenre []MovieGenre `json:"genres"` } // Genre is the type for genre type Genre struct { ID int `json:"-"` GenreName string `json:"genre_name"` CreatedAt time.Time `json:"-"` UpdatedAt time.Time `json:"-"` } // MovieGenre is thee type for movie genre type MovieGenre struct { ID int `json:"-"` MovieID string `json:"-"` GenreID string `json:"-` Genre Genre `json:"genre"` CreatedAt time.Time `json:"-"` UpdateAt time.Time `json:"-"` } |

MovieGenreフィールドの修正
MovieGenreフィールドの構造が複雑なので、シンプルにします。
1 2 3 4 5 6 |
// models/models.go type Movie struct { ... // MovieGenre []Movie `json:"genres"` MovieGenre map[int]string `json:"genres"` } |
1 2 3 4 5 6 7 8 9 10 11 12 |
// models/movies-db.g func (m *DBModel) Get(id int) (*Movie, error) { .... // var genres []MovieGenre genres := make(map[int]string) for rows.Next() { .... // genres = append(genres, mg) genres[mg.ID] = mg.Genre.GenreName } ... } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// cmd/api/movie-handlers.go func (app *application) getAllMovie(w http.ResponseWriter, r *http.Request) { movies := []models.Movie{ // {1, "movie1", "movie1", 2021, time.Date(2021, 01, 01, 01, 0, 0, 0, time.Local), // 100, 5, "PG-13", time.Now(), time.Now(), []models.MovieGenre{}}, // {2, "movie2", "movie2", 2021, time.Date(2021, 01, 01, 01, 0, 0, 0, time.Local), // 100, 5, "PG-13", time.Now(), time.Now(), []models.MovieGenre{}}, // {3, "movie3", "movie3", 2021, time.Date(2021, 01, 01, 01, 0, 0, 0, time.Local), // 100, 5, "PG-13", time.Now(), time.Now(), []models.MovieGenre{}}, {1, "movie1", "movie1", 2021, time.Date(2021, 01, 01, 01, 0, 0, 0, time.Local), 100, 5, "PG-13", time.Now(), time.Now(), make(map[int]string)}, {2, "movie2", "movie2", 2021, time.Date(2021, 01, 01, 01, 0, 0, 0, time.Local), 100, 5, "PG-13", time.Now(), time.Now(), make(map[int]string)}, {3, "movie3", "movie3", 2021, time.Date(2021, 01, 01, 01, 0, 0, 0, time.Local), 100, 5, "PG-13", time.Now(), time.Now(), make(map[int]string)}, } app.writeJSON(w, http.StatusOK, movies, "movie") } |
これで、先ほどと同じ条件でテストします。

genresがシンプルになりました。
Allメソッドの実装
最後にAllメソッドを実装します。Getメソッドの処理とほぼ同じです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
// models/movies-db.go ... // Get retuns one movie and error, if any func (m *DBModel) Get(id int) (*Movie, error) { ... } // All retuns all movies and error, if any func (m *DBModel) All() ([]*Movie, error) { ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second) defer cancel() query := `select id, title, description, year, release_date, rating, runtime, mpaa_rating, created_at, updated_at from movies order by title` rows, err := m.DB.QueryContext(ctx, query) if err != nil { return nil, err } defer rows.Close() var movies []*Movie for rows.Next() { var movie Movie err := rows.Scan( &movie.ID, &movie.Title, &movie.Description, &movie.Year, &movie.ReleaseDate, &movie.Rating, &movie.Runtime, &movie.MPAARating, &movie.CreatedAt, &movie.UpdatedAt, ) if err != nil { return nil, err } genreQuery := `select mg.id, mg.movie_id, mg.genre_id, g.genre_name from movies_genres mg left join genres g on (g.id = mg.genre_id) where mg.movie_id = $1` genreRows, _ := m.DB.QueryContext(ctx, genreQuery, movie.ID) genres := make(map[int]string) for genreRows.Next() { var mg MovieGenre err := genreRows.Scan( &mg.ID, &mg.MovieID, &mg.GenreID, &mg.Genre.GenreName, ) if err != nil { return nil, err } genres[mg.ID] = mg.Genre.GenreName } genreRows.Close() movie.MovieGenre = genres movies = append(movies, &movie) } return movies, nil } |
これをハンドラーから呼び出します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// cmd/api/movie-handlers.go ... func (app *application) getOneMovie(w http.ResponseWriter, r *http.Request) { .... } func (app *application) getAllMovie(w http.ResponseWriter, r *http.Request) { movies, err := app.models.DB.All() if err != nil { app.errorJSON(w, err) return } err = app.writeJSON(w, http.StatusOK, movies, "movies") if err != nil { app.errorJSON(w, err) return } } |
下記のパラメータで、テストをしましょう。
- URL: http://localhost:4000/v1/movies
- 形式: GET

全てのデータが取得できているので、OKです。
次回
次回は、GoとReactの連携処理を実装します。
記事まとめ
参考書籍
ソースコード
ここまで実装したソースコードを下記に記載します。
Dockerfile-postgres
1 2 3 |
FROM postgres:12-alpine COPY ./postgres/*.sql /docker-entrypoint-initdb.d/ |
docker-compose.yml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
# docker-compose.yml version: "3.3" services: backend: build: context: . dockerfile: ./Dockerfile-golang ports: - 4000:4000 volumes: - ./backend-app:/app frontend: build: context: . dockerfile: ./Dockerfile-react volumes: - ./go-movies:/go-movies command: > sh -c "npm run start" ports: - "3000:3000" container_name: frontend postgres: build: context: . dockerfile: ./Dockerfile-postgres environment: - POSTGRES_USER=postgres - POSTGRES_PASSWORD=secret - POSTGRES_DB=go_movies ports: - "5432:5432" container_name: postgres postgres-gui: image: donnex/pgweb command: -s --bind=0.0.0.0 --listen=8080 --url postgresql://postgres:secret@postgres/go_movies?sslmode=disable links: - postgres:postgres ports: - "9232:8080" depends_on: - postgres |
go_movies.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 |
-- go_movies.sql -- -- PostgreSQL database dump -- -- Dumped from database version 12.3 -- Dumped by pg_dump version 12.3 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: genres; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.genres ( id integer NOT NULL, genre_name character varying, created_at timestamp without time zone, updated_at timestamp without time zone ); -- -- Name: genres_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.genres_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: genres_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.genres_id_seq OWNED BY public.genres.id; -- -- Name: movies; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.movies ( id integer NOT NULL, title character varying, description text, year integer, release_date date, runtime integer, rating integer, mpaa_rating character varying, created_at timestamp without time zone, updated_at timestamp without time zone ); -- -- Name: movies_genres; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.movies_genres ( id integer NOT NULL, movie_id integer, genre_id integer, created_at timestamp without time zone, updated_at timestamp without time zone ); -- -- Name: movies_genres_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.movies_genres_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: movies_genres_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.movies_genres_id_seq OWNED BY public.movies_genres.id; -- -- Name: movies_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.movies_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: movies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.movies_id_seq OWNED BY public.movies.id; -- -- Name: genres id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.genres ALTER COLUMN id SET DEFAULT nextval('public.genres_id_seq'::regclass); -- -- Name: movies id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies ALTER COLUMN id SET DEFAULT nextval('public.movies_id_seq'::regclass); -- -- Name: movies_genres id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ALTER COLUMN id SET DEFAULT nextval('public.movies_genres_id_seq'::regclass); -- -- Data for Name: genres; Type: TABLE DATA; Schema: public; Owner: - -- COPY public.genres (id, genre_name, created_at, updated_at) FROM stdin; 1 Drama 2021-05-17 00:00:00 2021-05-17 00:00:00 2 Crime 2021-05-17 00:00:00 2021-05-17 00:00:00 3 Action 2021-05-17 00:00:00 2021-05-17 00:00:00 4 Comic Book 2021-05-17 00:00:00 2021-05-17 00:00:00 5 Sci-Fi 2021-05-17 00:00:00 2021-05-17 00:00:00 6 Mystery 2021-05-17 00:00:00 2021-05-17 00:00:00 7 Adventure 2021-05-17 00:00:00 2021-05-17 00:00:00 8 Comedy 2021-05-17 00:00:00 2021-05-17 00:00:00 9 Romance 2021-05-17 00:00:00 2021-05-17 00:00:00 \. -- -- Data for Name: movies; Type: TABLE DATA; Schema: public; Owner: - -- COPY public.movies (id, title, description, year, release_date, runtime, rating, mpaa_rating, created_at, updated_at) FROM stdin; 1 The Shawshank Redemption Two imprisoned men bond over a number of years 1994 1994-10-14 142 5 R 2021-05-17 00:00:00 2021-05-17 00:00:00 2 The Godfather The aging patriarch of an organized crime dynasty transfers control to his son 1972 1972-03-24 175 5 R 2021-05-17 00:00:00 2021-05-17 00:00:00 4 American Psycho A wealthy New York investment banking executive hides his alternate psychopathic ego 2000 2000-04-14 102 4 R 2021-05-17 00:00:00 2021-05-17 00:00:00 3 The Dark Knight The menace known as the Joker wreaks havoc on Gotham City 2008 2008-07-18 152 5 PG13 2021-05-17 00:00:00 2021-05-17 00:00:00 \. -- -- Data for Name: movies_genres; Type: TABLE DATA; Schema: public; Owner: - -- COPY public.movies_genres (id, movie_id, genre_id, created_at, updated_at) FROM stdin; \. -- -- Name: genres_id_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public.genres_id_seq', 9, true); -- -- Name: movies_genres_id_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public.movies_genres_id_seq', 1, false); -- -- Name: movies_id_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public.movies_id_seq', 4, true); -- -- Name: genres genres_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.genres ADD CONSTRAINT genres_pkey PRIMARY KEY (id); -- -- Name: movies_genres movies_genres_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ADD CONSTRAINT movies_genres_pkey PRIMARY KEY (id); -- -- Name: movies movies_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies ADD CONSTRAINT movies_pkey PRIMARY KEY (id); -- -- Name: movies_genres fk_movie_genries_genre_id; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ADD CONSTRAINT fk_movie_genries_genre_id FOREIGN KEY (genre_id) REFERENCES public.genres(id); -- -- Name: movies_genres fk_movie_genries_movie_id; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.movies_genres ADD CONSTRAINT fk_movie_genries_movie_id FOREIGN KEY (movie_id) REFERENCES public.movies(id); -- -- PostgreSQL database dump complete -- |
main.go
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
// cmd/api/main.go package main import ( "backend/models" "context" "database/sql" "flag" "fmt" "log" "net/http" "os" "time" _ "github.com/lib/pq" ) const ( version = "1.0.0" dsnString = "postgres://postgres:secret@postgres/go_movies?sslmode=disable" ) type config struct { port int env string db struct { // 追加 dsn string } } type AppStatus struct { Status string `json:"status"` Environment string `json:"environment"` Version string `json:"version"` } type application struct { config config logger *log.Logger models models.Models } func main() { var cfg config flag.IntVar(&cfg.port, "port", 4000, "Server port to listen on") flag.StringVar(&cfg.env, "env", "development", "Application environment (development|production)") // postgres dsn追加 flag.StringVar(&cfg.db.dsn, "dsn", dsnString, "Postgres connection string") flag.Parse() logger := log.New(os.Stdout, "", log.Ldate|log.Ltime) // db接続 db, err := openDB(cfg) if err != nil { logger.Fatal(err) } defer db.Close() app := &application{ config: cfg, logger: logger, models: models.NewModels(db), } srv := &http.Server{ Addr: fmt.Sprintf(":%d", cfg.port), Handler: app.routes(), IdleTimeout: time.Minute, ReadTimeout: 10 * time.Second, WriteTimeout: 30 * time.Second, } logger.Println("Starting server on port", cfg.port) err = srv.ListenAndServe() if err != nil { log.Println(err) } } func openDB(cfg config) (*sql.DB, error) { db, err := sql.Open("postgres", cfg.db.dsn) if err != nil { return nil, err } ctx, cancle := context.WithTimeout(context.Background(), 5*time.Second) defer cancle() err = db.PingContext(ctx) if err != nil { return nil, err } return db, nil } |
movie-handlers.go
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
// cmd/api/movie-handlers.go package main import ( "errors" "net/http" "strconv" "github.com/julienschmidt/httprouter" ) func (app *application) getOneMovie(w http.ResponseWriter, r *http.Request) { params := httprouter.ParamsFromContext(r.Context()) // urlからidを抜きだす id, err := strconv.Atoi(params.ByName("id")) if err != nil { app.logger.Print(errors.New("invalid id parameter")) app.errorJSON(w, err) return } app.logger.Println("id is", id) movie, err := app.models.DB.Get(id) err = app.writeJSON(w, http.StatusOK, movie, "movie") if err != nil { app.errorJSON(w, err) return } } func (app *application) getAllMovie(w http.ResponseWriter, r *http.Request) { movies, err := app.models.DB.All() if err != nil { app.errorJSON(w, err) return } err = app.writeJSON(w, http.StatusOK, movies, "movies") if err != nil { app.errorJSON(w, err) return } } |
models.go
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
// models/models.go package models import ( "database/sql" "time" ) // Models is the wrapper for database type Models struct { DB DBModel } // NewModels returns models with db pool func NewModels(db *sql.DB) Models { return Models{ DB: DBModel{DB: db}, } } // Movie is the type for movies type Movie struct { ID int `json:"id"` Title string `json:"title"` Description string `json:"description"` Year int `json:"year"` ReleaseDate time.Time `json:"release_date"` Runtime int `json:"runtime"` Rating int `json:"rating"` MPAARating string `json:"mpaa_rating"` CreatedAt time.Time `json:"-"` UpdatedAt time.Time `json:"-"` MovieGenre map[int]string `json:"genres"` } // Genre is the type for genre type Genre struct { ID int `json:"-"` GenreName string `json:"genre_name"` CreatedAt time.Time `json:"-"` UpdatedAt time.Time `json:"-"` } // MovieGenre is thee type for movie genre type MovieGenre struct { ID int `json:"-"` MovieID string `json:"-"` GenreID string `json:"-` Genre Genre `json:"genre"` CreatedAt time.Time `json:"-"` UpdateAt time.Time `json:"-"` } |
movies-db.go
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
// models/movies-db.go package models import ( "context" "database/sql" "time" ) type DBModel struct { DB *sql.DB } // Get retuns one movie and error, if any func (m *DBModel) Get(id int) (*Movie, error) { ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second) defer cancel() query := `select id, title, description, year, release_date, rating, runtime, mpaa_rating, created_at, updated_at from movies where id = $1` row := m.DB.QueryRowContext(ctx, query, id) var movie Movie err := row.Scan( &movie.ID, &movie.Title, &movie.Description, &movie.Year, &movie.ReleaseDate, &movie.Rating, &movie.Runtime, &movie.MPAARating, &movie.CreatedAt, &movie.UpdatedAt, ) if err != nil { return nil, err } // get genres, if any query = `select mg.id, mg.movie_id, mg.genre_id, g.genre_name from movies_genres mg left join genres g on (g.id = mg.genre_id) where mg.movie_id = $1` rows, _ := m.DB.QueryContext(ctx, query, id) defer rows.Close() genres := make(map[int]string) for rows.Next() { var mg MovieGenre err := rows.Scan( &mg.ID, &mg.MovieID, &mg.GenreID, &mg.Genre.GenreName, ) if err != nil { return nil, err } genres[mg.ID] = mg.Genre.GenreName } movie.MovieGenre = genres return &movie, nil } // All retuns all movies and error, if any func (m *DBModel) All() ([]*Movie, error) { ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second) defer cancel() query := `select id, title, description, year, release_date, rating, runtime, mpaa_rating, created_at, updated_at from movies order by title` rows, err := m.DB.QueryContext(ctx, query) if err != nil { return nil, err } defer rows.Close() var movies []*Movie for rows.Next() { var movie Movie err := rows.Scan( &movie.ID, &movie.Title, &movie.Description, &movie.Year, &movie.ReleaseDate, &movie.Rating, &movie.Runtime, &movie.MPAARating, &movie.CreatedAt, &movie.UpdatedAt, ) if err != nil { return nil, err } genreQuery := `select mg.id, mg.movie_id, mg.genre_id, g.genre_name from movies_genres mg left join genres g on (g.id = mg.genre_id) where mg.movie_id = $1` genreRows, _ := m.DB.QueryContext(ctx, genreQuery, movie.ID) genres := make(map[int]string) for genreRows.Next() { var mg MovieGenre err := genreRows.Scan( &mg.ID, &mg.MovieID, &mg.GenreID, &mg.Genre.GenreName, ) if err != nil { return nil, err } genres[mg.ID] = mg.Genre.GenreName } genreRows.Close() movie.MovieGenre = genres movies = append(movies, &movie) } return movies, nil } |
コメントを残す
コメントを投稿するにはログインしてください。