initial

1 files changed, 52 insertions, 0 deletions

diff --git a/build/db-init/README.md b/build/db-init/README.md
new file mode 100644
index 0000000..942090a
--- /dev/null
+++ b/build/db-init/README.md
@@ -0,0 +1,52 @@
+## db-init
+
+This script setups of the databse with the requrired baseline, runs migrations,
+and loads the api schema (if enabled).
+
+#### Migration script
+
+All migrations scrips MUST do ALL of the following:
+
+    - Placed in src/db/migrations
+    - Named with its migration number (0 indexed), and have four
+      numbers of padding. i.e. `0000.sql`, `0030.sql`, or `9999.sql`
+    - In numerical order with all other migrations (cannot go from migration
+      0 to 2).
+    - A postgres transaction. `BEGIN TRANSACTION ... COMMIT TRANSACTION`.
+    - End with the following before COMMIT, where <rev> is the NEXT
+      revision number. (i.e. in `0000.sql` <rev> MUST be 1).
+
+```
+UPDATE sys.database_info SET curr_revision = <rev> WHERE name = current_database();
+```
+
+Example `0000.sql`:
+```sql
+BEGIN TRANSACTION;
+
+CREATE SCHEMA website;
+
+UPDATE sys.database_info SET curr_revision = 1 WHERE name = current_database();
+
+COMMIT TRANSACTION;
+```
+
+Migrations will ONLY EVER be ONCE, and will ALLWAYS be run in order. This means
+that you can assume all previous migrations have run successfully in any current
+migration, and there is NO other possible state in the database.
+
+### API
+
+Once all migrations have been completed, the api will be initalized (if enabled.
+
+If you opt to use postgrest which is builtin into crimson, you must create a
+sql file loads the api schema that MUST do ALL of the following:
+
+    - Placed in src/db/rest and named `rest.sql`
+    - A postgres transaction. `BEGIN TRANSACTION ... COMMIT TRANSACTION`.
+
+Within that transaction you can setup postgres with the api schema you want.
+See https://docs.postgrest.org/en/v12/. (crimson currently uses postgres 12).
+
+NOTE: If you want to load any sql file though an absolute path, src/db will be
+mounted as READ ONLY to /db. (i.e. src/db/rest/rest.sql => /db/rest/rest.sql).