[Golang]~MovieApp2: GoからPostgreSQLの処理を実装しよう!~

こんにちは、KOUKIです。

Golang/ReactでMovie Appの開発をしています。

今回は、PostgreSQLを導入し、DB処理を実装していきます。

尚、Udemyの「Working with React and Go (Golang)」を参考にしているので、よかったら受講してみてください。

[Golang/React]Movie App ハンズオン開発記録です!

前回

Webサーバーのルーティング処理を実装しました。

[Golang]~MovieApp1: ルーティング処理を実証しよう!~

事前準備

フォルダ/ファイル

mkdir postgres
touch postgres/go_movies.sql
touch Dockerfile-postgres
touch backend-app/models/movies-db.go

mkdir postgres

touch postgres/go_movies.sql

touch Dockerfile-postgres

touch backend-app/models/movies-db.go

モジュール

cd backend-app/
go get -u github.com/lib/pq@v1.10.0

1 2	cd backend-app/ go get -u github.com/lib/pq@v1.10.0

lib/pq

PostgresSQLのDocker化

PostgreSQLの環境をDockerで導入します。

Dockerfile-postgres

Dockerfile-postgresファイルには、PostgreSQLの構成情報を記述します。

FROM postgres:12-alpine

COPY ./postgres/*.sql /docker-entrypoint-initdb.d/

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サービスを追加します。

# 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

# 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を以下に記載します。

-- 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
--

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へ接続しましょう。

// 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
}

// 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名、ユーザー名、パスワード、テーブル名を使用しています。

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

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

コンソールからエラーが出ていなければ、接続成功です。

backend_1       | building...
backend_1       | running...
backend_1       | 2021/08/04 20:45:38 Starting server on port 4000

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データの取得処理を実装します。

// 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
}

// 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のラッパーも実装しておきましょう。

// 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},
	}
}

// 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を追加します。

// 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),, // 追加
	}
}

// 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情報を返します。

// 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
}

// 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ハンドラーから呼び出します。

// 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")
}

// 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メソッドに追加します。

// 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
}

// 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タグも変更します。

// 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"` // 変更
}

// 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/」へアクセスして、下記のデータを追加しましょう。

INSERT INTO movies_genres (movie_id, genre_id) VALUES (1, 1);

1	INSERT INTO movies_genres (movie_id, genre_id) VALUES (1, 1);

データの投入に成功したら、下記のパラメータで、テストをしましょう。

URL: http://localhost:4000/v1/movie/1
形式: GET

genres(カテゴリー)にデータが入っていることが確認できれば、OKです。

不要なデータの非表示

戻り値に不要なデータは、jsonキーワードに「-」を指定することで非表示にすることができます。

例えば、以下のような感じです。

// 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:"-"`
}

// 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フィールドの構造が複雑なので、シンプルにします。

// models/models.go
type Movie struct {
...
	// MovieGenre  []Movie `json:"genres"`
	MovieGenre map[int]string `json:"genres"`
}

// models/models.go

type Movie struct {

...

// MovieGenre []Movie `json:"genres"`

MovieGenre map[int]string `json:"genres"`

}

// 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
	}
...
}

// 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

}

...

}

// 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")
}

// 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メソッドの処理とほぼ同じです。

// 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
}

// 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

}

これをハンドラーから呼び出します。

// 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
	}
}

// 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の連携処理を実装します。

[Golang/React]~MovieApp1: アプリ連携~

記事まとめ

学習記事まとめです。

[Golang/React]Movie App ハンズオン開発記録です!

Go言語 ~ 記事まとめ ~

参考書籍

Head First Go【電子書籍】[ Jay McGavren ]

楽天で購入

入門Goプログラミング【電子書籍】[ Nathan Youngman ]

楽天で購入

ソースコード

ここまで実装したソースコードを下記に記載します。

Dockerfile-postgres

FROM postgres:12-alpine

COPY ./postgres/*.sql /docker-entrypoint-initdb.d/

FROM postgres:12-alpine

COPY ./postgres/*.sql /docker-entrypoint-initdb.d/

docker-compose.yml

# 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

# 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

-- 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
--