add schemas to postgresql

steph-koopmanschap · Jun 22, 2023 · 95ce487 · 95ce487
1 parent 82bfc6c
commit 95ce487
Show file tree

Hide file tree

Showing 3 changed files with 37 additions and 2 deletions.
diff --git a/.env/.local/.postgres/.env b/.env/.local/.postgres/.env
@@ -1,6 +1,6 @@
 # POSTGRES DB
 PG_PORT=5432
 PG_HOST=jasma-pg-db   
-POSTGRES_DB=postgres
+POSTGRES_DB=jasma
 POSTGRES_USER=postgres
 POSTGRES_PASSWORD=postgres
diff --git a/compose/local/postgresql/init.sql b/compose/local/postgresql/init.sql
@@ -0,0 +1,34 @@
+-- Check if the version file exists
+DO $$ 
+BEGIN 
+  IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'jasma') THEN
+    -- The database should only be created on the first run / build of postgresql.
+    -- Create the jasma database
+    CREATE DATABASE jasma
+        WITH
+        OWNER = postgres
+        TEMPLATE = template0
+        ENCODING = 'UTF8'
+        LC_COLLATE = 'C'
+        LC_CTYPE = 'C'
+        TABLESPACE = pg_default
+        CONNECTION LIMIT = -1
+        IS_TEMPLATE = False;
+
+    COMMENT ON DATABASE jasma
+        IS 'Primary PS-SQL Database of JASMA';
+
+    -- Connect to the jasma database
+    \c jasma;
+
+    -- Create the app schema
+    CREATE SCHEMA app;
+
+    -- Create the analytics schema
+    CREATE SCHEMA analytics;
+
+    -- Create the version file indicating the initialization is done
+    CREATE TABLE IF NOT EXISTS init_version (version INT);
+    INSERT INTO init_version (version) VALUES (1);
+  END IF;
+END $$;
diff --git a/docker-compose-development.yml b/docker-compose-development.yml
@@ -33,7 +33,8 @@ services:
         networks:
             - jasma-network-dev
         volumes:
-            - jasma-storage-dev:/var/lib/postgresql/data #Check that this works
+            - jasma-storage-dev:/var/lib/postgresql/data #Check that this works 
+            - ./compose/local/postgresql:/docker-entrypoint-initdb.d # Init script for database
 
     # REDIS
     jasma-redis-db: