From e69391d4f309f6268440632585bbddf3a2a5bd60 Mon Sep 17 00:00:00 2001 From: Matthew Lemon Date: Wed, 10 Apr 2024 19:33:21 +0100 Subject: First introduction of migrations using golang-migrate --- Dockerfile.postgres | 12 ++++++++ cmd/dbasik-api/datamaps.go | 10 ++++--- cmd/dbasik-api/main.go | 4 +-- compose.yaml | 5 +++- ...01_create_datamaps_datamap_line_tables.down.sql | 2 ++ ...0001_create_datamaps_datamap_line_tables.up.sql | 15 ++++++++++ migrations/dbasik.sql | 32 ---------------------- migrations/setup.sql | 6 ---- resources/dbasik.sql | 32 ++++++++++++++++++++++ resources/setup.sql | 8 ++++++ 10 files changed, 81 insertions(+), 45 deletions(-) create mode 100644 Dockerfile.postgres create mode 100644 migrations/000001_create_datamaps_datamap_line_tables.down.sql create mode 100644 migrations/000001_create_datamaps_datamap_line_tables.up.sql delete mode 100644 migrations/dbasik.sql delete mode 100644 migrations/setup.sql create mode 100644 resources/dbasik.sql create mode 100644 resources/setup.sql diff --git a/Dockerfile.postgres b/Dockerfile.postgres new file mode 100644 index 0000000..2131f51 --- /dev/null +++ b/Dockerfile.postgres @@ -0,0 +1,12 @@ +FROM postgres:16-alpine + +WORKDIR /dbasik + +COPY migrations/ . + +# Install required dependencies (e.g., curl) +RUN apk add --no-cache curl + +# Download and install the migrate binary +RUN curl -L https://github.com/golang-migrate/migrate/releases/download/v4.17.0/migrate.linux-amd64.tar.gz | tar xvz -C /usr/local/bin/ && chmod +x /usr/local/bin/migrate + diff --git a/cmd/dbasik-api/datamaps.go b/cmd/dbasik-api/datamaps.go index 8d371ab..1e668ef 100644 --- a/cmd/dbasik-api/datamaps.go +++ b/cmd/dbasik-api/datamaps.go @@ -30,6 +30,7 @@ import ( // The fields need to be exported otherwise they won't be included when encoding // the struct to json. type datamapLine struct { + ID int64 `json:"id"` Key string `json:"key"` Sheet string `json:"sheet"` DataType string `json:"datatype"` @@ -38,6 +39,7 @@ type datamapLine struct { // datamap includes a slice of datamapLine objects alongside header metadata type datamap struct { + ID int64 `json:"id"` Name string `json:"name"` Description string `json:"description"` Created time.Time `json:"created"` @@ -85,10 +87,10 @@ func (app *application) createDatamapHandler(w http.ResponseWriter, r *http.Requ } dmls = append(dmls, datamapLine{ - Key: line[0], - Sheet: line[1], - DataType: line[2], - Cellref: line[3], + Key: line[1], + Sheet: line[2], + DataType: line[3], + Cellref: line[4], }) } dm = datamap{Name: dmName, Description: dmDesc, Created: time.Now(), DMLs: dmls} diff --git a/cmd/dbasik-api/main.go b/cmd/dbasik-api/main.go index e86f91c..f132c50 100644 --- a/cmd/dbasik-api/main.go +++ b/cmd/dbasik-api/main.go @@ -73,8 +73,8 @@ func main() { //Read connection pool settings (explained in Configuring the pool in the book) flag.IntVar(&cfg.db.maxOpenConns, "db-max-open-conns", 25, "PostgreSQL max open connections") - flag.IntVar(&cfg.db.maxIdleConns, "db-max-idel-conns", 25, "PostgreSQL max idle connections") - flag.StringVar(&cfg.db.maxIdleTime, "db-max-idel-conns", "15m", "PostgreSQL max connection idle time") + flag.IntVar(&cfg.db.maxIdleConns, "db-max-idle-conns", 25, "PostgreSQL max idle connections") + flag.StringVar(&cfg.db.maxIdleTime, "db-max-idle-time", "15m", "PostgreSQL max connection idle time") flag.Parse() diff --git a/compose.yaml b/compose.yaml index 4206716..d1544a0 100644 --- a/compose.yaml +++ b/compose.yaml @@ -1,12 +1,15 @@ services: db: - image: postgres:16-alpine + build: + context: . + dockerfile: Dockerfile.postgres ports: - 5432:5432 environment: - POSTGRES_PASSWORD=secret volumes: - dbasik_data:/var/lib/postgresql/data + - ./migrations:/dbasik app: build: context: . diff --git a/migrations/000001_create_datamaps_datamap_line_tables.down.sql b/migrations/000001_create_datamaps_datamap_line_tables.down.sql new file mode 100644 index 0000000..86f243c --- /dev/null +++ b/migrations/000001_create_datamaps_datamap_line_tables.down.sql @@ -0,0 +1,2 @@ +DROP TABLE IF EXISTS datamaps; +DROP TABLE IF EXISTS datamap_lines; diff --git a/migrations/000001_create_datamaps_datamap_line_tables.up.sql b/migrations/000001_create_datamaps_datamap_line_tables.up.sql new file mode 100644 index 0000000..be96b96 --- /dev/null +++ b/migrations/000001_create_datamaps_datamap_line_tables.up.sql @@ -0,0 +1,15 @@ +CREATE TABLE IF NOT EXISTS datamaps ( + id bigserial PRIMARY KEY, + name text, + description text, + created timestamp(0) with time zone NOT NULL DEFAULT NOW() +); + +CREATE TABLE IF NOT EXISTS datamap_lines ( + datamap_line_id bigserial PRIMARY KEY, + datamap_id bigserial REFERENCES datamaps ON DELETE CASCADE, + key text, + sheet text, + data_type text, + cellref text +); diff --git a/migrations/dbasik.sql b/migrations/dbasik.sql deleted file mode 100644 index b5bb6f9..0000000 --- a/migrations/dbasik.sql +++ /dev/null @@ -1,32 +0,0 @@ -CREATE TABLE "datamap" ( - "id" int PRIMARY KEY, - "name" varchar, - "description" varchar -); - -CREATE TABLE "datamapline" ( - "id" uuid PRIMARY KEY, - "datamap_id" uuid, - "name" string, - "cellref" string, - "sheet" string, - "data_type" string -); - -CREATE TABLE "returnitem" ( - "id" uuid PRIMARY KEY, - "datamapline_id" uuid, - "return_id" int, - "value" string -); - -CREATE TABLE "return" ( - "id" int PRIMARY KEY, - "name" string -); - -ALTER TABLE "datamapline" ADD FOREIGN KEY ("datamap_id") REFERENCES "datamap" ("id"); - -ALTER TABLE "returnitem" ADD FOREIGN KEY ("datamapline_id") REFERENCES "datamapline" ("id"); - -ALTER TABLE "returnitem" ADD FOREIGN KEY ("return_id") REFERENCES "return" ("id"); diff --git a/migrations/setup.sql b/migrations/setup.sql deleted file mode 100644 index 3d95044..0000000 --- a/migrations/setup.sql +++ /dev/null @@ -1,6 +0,0 @@ -/* should be added manually at this point */ -CREATE DATABASE dbasik; -\c dbasik - -CREATE ROLE 'dbasik' WITH LOGIN PASSWORD 'dbasik'; -CREATE EXTENSION IF NOT EXISTS citext; diff --git a/resources/dbasik.sql b/resources/dbasik.sql new file mode 100644 index 0000000..b5bb6f9 --- /dev/null +++ b/resources/dbasik.sql @@ -0,0 +1,32 @@ +CREATE TABLE "datamap" ( + "id" int PRIMARY KEY, + "name" varchar, + "description" varchar +); + +CREATE TABLE "datamapline" ( + "id" uuid PRIMARY KEY, + "datamap_id" uuid, + "name" string, + "cellref" string, + "sheet" string, + "data_type" string +); + +CREATE TABLE "returnitem" ( + "id" uuid PRIMARY KEY, + "datamapline_id" uuid, + "return_id" int, + "value" string +); + +CREATE TABLE "return" ( + "id" int PRIMARY KEY, + "name" string +); + +ALTER TABLE "datamapline" ADD FOREIGN KEY ("datamap_id") REFERENCES "datamap" ("id"); + +ALTER TABLE "returnitem" ADD FOREIGN KEY ("datamapline_id") REFERENCES "datamapline" ("id"); + +ALTER TABLE "returnitem" ADD FOREIGN KEY ("return_id") REFERENCES "return" ("id"); diff --git a/resources/setup.sql b/resources/setup.sql new file mode 100644 index 0000000..dac150f --- /dev/null +++ b/resources/setup.sql @@ -0,0 +1,8 @@ +/* should be added manually at this point */ +CREATE DATABASE dbasik; +\c dbasik + +CREATE ROLE 'dbasik' WITH LOGIN PASSWORD 'dbasik'; +GRANT ALL PRIVILEGES ON DATABASE 'dbasik' TO 'dbasik'; +ALTER DATABASE 'dbasik' OWNER TO 'dbasik'; +CREATE EXTENSION IF NOT EXISTS citext; -- cgit v1.2.3