First introduction of migrations using golang-migrate

8 files changed, 43 insertions, 7 deletions

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/resources/dbasik.sql
index b5bb6f9..b5bb6f9 100644
--- a/migrations/dbasik.sql
+++ b/resources/dbasik.sql
diff --git a/migrations/setup.sql b/resources/setup.sql
index 3d95044..dac150f 100644
--- a/migrations/setup.sql
+++ b/resources/setup.sql
@@ -3,4 +3,6 @@ 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;