Building an API with Go, PostgreSQL, Google Cloud and CockroachDB

I built an API with Go and PostgreSQL, set up a CI/CD pipeline with Google Cloud Run, Cloud Build, Secret Manager and Artifact Registry, and connected the Cloud Run instance to CockroachDB. The API is based on the game Crisis Core: Final Fantasy VII, to simulate “Materia Fusion”. This article’s intended audience is for developers who just want to know how to build and deploy the API. I have another article where I talk about everything I learnt while working on this project and what didn’t work. Links for easy reference GitHub repo and README Swagger (OpenAPI) documentation and testing Public Postman collection Source of domain model API Objective 3 endpoints — health check (GET), list of all materia (GET), and simulate materia fusion (POST). The Domain Model Materia (both singular and plural) is a crystal orb that serves as a source of magic. There are 144 distinct materia in the game, and they’re broadly classified into 4 categories: “Magic”, “Command”, “Support” and “Independent”. However, for the purpose of figuring out the rules of materia fusion, it was easier to have 32 internal categories based on their fusion behaviour, and 8 grades within those categories (see reference). A materia becomes ‘Mastered’ when it is used for a certain duration. The duration is not important here. Most importantly, 2 materia can be fused to produce a new materia. The rules governing fusion are influenced by: Whether either or both materia are mastered. Which materia comes first (as in X+Y is not necessarily equal to Y+X). Materia internal category. Materia grade. And there are a LOT of exceptions, with some rules having 3 levels of nested if-else logic. This eliminates the possibility of creating a simple table in the DB and persisting 1000+ rules into it, or coming up with One Formula To Rule Them All. In short, we need: A table materia with columns name(string), materia_type(ENUM) (the 32 internal categories), grade(integer), display_materia_type(ENUM) (the 4 categories used in the game), description(string) and id(integer) as an auto-incrementing primary key. A data structure to encapsulate the basic rules format MateriaTypeA + MateriaTypeB = MateriaTypeC. Code to use the basic and complex rules to determine the output Materia in terms of its internal category and grade. 1. Setting Up The Local PostgreSQL DB Ideally you can install the DB from the website itself. But the pgAdmin tool could not connect to the DB for some reason, so I used Homebrew. Installation brew install postgresql@17 This will install a whole bunch of CLI binary files to help use the DB. Optional: add /opt/homebrew/opt/postgresql@17/bin to $PATH variable. Next, create the DB: # create the DB createdb materiafusiondb # step into the DB to perform SQL commands psql materiafusiondb Create the user and permissions -- create an SQL user to be used by the Go server CREATE USER go_client WITH PASSWORD 'xxxxxxxx'; -- The Go server doesn't ever need to add data to the DB. -- So let's give it just read permission. CREATE ROLE readonly_role; GRANT USAGE ON SCHEMA public TO readonly_role; -- This command gives SELECT access to all future created tables. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role; -- If you want to be more strict and give access only to tables that already exist, use this: -- GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role; GRANT readonly_role TO go_client; Create the table CREATE TYPE display_materia_type AS ENUM ('Magic', 'Command', 'Support', 'Independent'); CREATE TYPE materia_type AS ENUM ('Fire', 'Ice', 'Lightning', 'Restore', 'Full Cure', 'Status Defense', 'Defense', 'Absorb Magic', 'Status Magic', 'Fire & Status', 'Ice & Status', 'Lightning & Status', 'Gravity', 'Ultimate', 'Quick Attack', 'Quick Attack & Status', 'Blade Arts', 'Blade Arts & Status', 'Fire Blade', 'Ice Blade', 'Lightning Blade', 'Absorb Blade', 'Item', 'Punch', 'SP Turbo', 'HP Up', 'AP Up', 'ATK Up', 'VIT Up', 'MAG Up', 'SPR Up', 'Dash', 'Dualcast', 'DMW', 'Libra', 'MP Up', 'Anything'); CREATE TABLE materia ( id integer NOT NULL, name character varying(50) NOT NULL, materia_type materia_type NOT NULL, grade integer NOT NULL, display_materia_type display_materia_type, description text, CONSTRAINT materia_pkey PRIMARY KEY (id) ); -- The primary key 'id' should auto-increment by 1 for every row entry.CREATE SEQUENCE materia_id_seqAS integerSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 1; ALTER SEQUENCE materia_id_seq OWNED BY materia.id; ALTER TABLE ONLY materia ALTER COLUMN id SET DEFAULT nextval('materia_id_seq'::REGCLASS); Add the data Create an Excel sheet with table header and data, and export it as a CSV file. Then run the command: COPY materia(name,materia_type,grade,display_materia_type,description) FROM ' /materiadata.csv' DELIMITER ',' CSV HEADER; 2. Creating the Go Server Create the boilerplate code using autostrada.dev. Add the options of api, postgresql, httprouter , env var config, tinted logging, git, live reload, makefile. We end up getting a file structure like this: 📦 codebase ├─ cmd │ └─ api │ ├─ errors.go │ ├─ handlers.go │ ├─ helpers.go │ ├─ main.go │ ├─ middleware.go │ └─ server.go ├─ internal │ ├─ database --- db.go │ ├─ env --- env.go │ ├─ request --- json.go │ ├─ response --- json.go │ └─ validator │ ├─ helpers.go │ └─ validators.go ├─ go.mod ├─ LICENSE ├─ Makefile ├─ README.md └─ README.html .env file The boilerplate generator has created code to fetch environment variables and add them to the code, but we can make it easier to track and update the values. Create /.env file. Add the following values: HTTP_PORT=4444 DB_DSN=go_client: @localhost:5432/materiafusiondb?sslmode=disable API_TIMEOUT_SECONDS=5 API_CALLS_ALLOWED_PER_SECOND=1 Add the godotenv library: go get github.com/joho/godotenv Add the following to main.go: // At the beginning of main(): err := godotenv.Load(".env") // Loads environment variables from .env file if err != nil { // This will be true in prod, but that's fine. fmt.Println("Error loading .env file") } // Modify config struct: type config struct { baseURL string db struct { dsn string } httpPort int apiTimeout int apiCallsAllowedPerSecond float64 } // Modify run() to use the new values from .env: cfg.httpPort = env.GetInt("HTTP_PORT") cfg.db.dsn = env.GetString("DB_DSN") cfg.apiTimeout = env.GetInt("API_TIMEOUT_SECONDS") cfg.apiCallsAllowedPerSecond = float64(env.GetInt("API_CALLS_ALLOWED_PER_SECOND")) // cfg.baseURL = env.GetString("BASE_URL") - not required Middleware and Routes The boilerplate already has a middleware to recover from panics. We will add 3 more: Content-Type checking, rate-limiting and API timeout protection. Add tollbooth library for rate-limiting: go get github.com/didip/tollbooth Update <rootfolder/api/middleware.go: func (app *application) contentTypeCheck(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { if r.Header.Get("Content-Type") != "application/json" { app.unsupportedMediaType(w, r) return } next.ServeHTTP(w, r) }) } func (app *application) rateLimiter(next http.Handler) http.Handler { limiter := tollbooth.NewLimiter(app.config.apiCallsAllowedPerSecond, nil) limiter.SetIPLookups([]string{"X-Real-IP", "X-Forwarded-For", "RemoteAddr"}) return tollbooth.LimitHandler(limiter, next) } func (app *application) apiTimeout(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { timeoutDuration := time.Duration(app.config.apiTimeout) * time.Second ctx, cancel := context.WithTimeout(r.Context(), timeoutDuration) defer cancel() r = r.WithContext(ctx) done := make(chan struct{}) go func() { next.ServeHTTP(w, r) close(done) }() select { case <-done: return case <-ctx.Done(): app.gatewayTimeout(w, r) return } }) } The middleware need to be added to the routes. They can be either added to all the routes, or to specific ones. In our case, Content-Type checking (that is, mandating the input headers to include Content-Type: application/json) is only needed for POST requests. So modify routes.go as follows: func (app *application) routes() http.Handler { mux := httprouter.New() mux.NotFound = http.HandlerFunc(app.notFound) mux.MethodNotAllowed = http.HandlerFunc(app.methodNotAllowed) // Serve the Swagger UI. Uncomment this line later // mux.Handler("GET", "/docs/*any", httpSwagger.WrapHandler) mux.HandlerFunc("GET", "/status", app.status) mux.HandlerFunc("GET", "/materia", app.getAllMateria) // Adding content-type check middleware to only the POST method mux.Handler("POST", "/fusion", app.contentTypeCheck(http.HandlerFunc(app.fuseMateria))) return app.chainMiddlewares(mux) } func (app *application) chainMiddlewares(next http.Handler) http.Handler { middlewares := []func(http.Handler) http.Handler{ app.recoverPanic, app.apiTimeout, app.rateLimiter, } for _, middleware := range middlewares { next = middleware(next) } return next } Error handling Add the following methods to /api/errors.go to help the middleware functions: func (app *application) unsupportedMediaType(w http.ResponseWriter, r *http.Request) { message := fmt.Sprintf("The %s Content-Type is not supported", r.Header.Get("Content-Type")) app.errorMessage(w, r, http.StatusUnsupportedMediaType, message, nil) } func (app *application) gatewayTimeout(w http.ResponseWriter, r *http.Request) { message := "Request timed out" app.errorMessage(w, r, http.StatusGatewayTimeout, message, nil) } Request and Response structure files /api/dtos.go for the responses of all 3 endpoints: package main // MateriaDTO provides Materia details - Name, Description and Type (Magic / Command / Support / Independent) type MateriaDTO struct { Name string `json:"name" example:"Thunder"` Type string `json:"type" example:"Magic"` Description string `json:"description" example:"Shoots lightning forward dealing thunder damage."`} // StatusDTO provides status of the server type StatusDTO struct { Status string `json:"Status" example:"OK"` } // ErrorResponseDTO provides Error message type ErrorResponseDTO struct { Error string `json:"Error" example:"The server encountered a problem and could not process your request"` } /api/requests.go for the POST \fusion endpoint: package main import ( "github.com/RayMathew/crisis-core-materia-fusion-api/internal/validator" ) // MateriaFusionRequest provides input Materia names and their Mastered states type MateriaFusionRequest struct { Materia1Mastered *bool `json:"materia1mastered" example:"true"` Materia2Mastered *bool `json:"materia2mastered" example:"false"` Materia1Name string `json:"materia1name" example:"Fire"` Materia2Name string `json:"materia2name" example:"Blizzard"` Validator validator.Validator `json:"-"` } Validator, from the generated code, will be used later to validate the input fields for \fusion endpoint. Data Structure for the combination rules Create the file /internal/crisis-core-materia-fusion/constants.go. Add the following: package crisiscoremateriafusion type MateriaType string const ( Fire MateriaType = "Fire" Ice MateriaType = "Ice" Lightning MateriaType = "Lightning" Restore MateriaType = "Restore" ... full list of 32 MateriaTypes can be found here. Create the file /internal/crisis-core-materia-fusion/models.go. Add the following: package crisiscoremateriafusion type Materia struct { Name string `json:"name"` Type string `json:"materia_type"` DisplayType string `json:"display_type"` Description string `json:"description"` Grade int `json:"grade"` } // Data structure to hold all basic combination rules type BasicCombinationRule struct { FirstMateriaType MateriaType SecondMateriaType MateriaType ResultantMateriaType MateriaType } var FILBasicRules = []BasicCombinationRule{ {Fire, Fire, Fire}, {Ice, Ice, Ice}, {Lightning, Lightning, Lightning}, {Fire, Ice, Lightning}, {Ice, Fire, Lightning}, {Fire, Lightning, Ice}, {Lightning, Fire, Ice}, {Lightning, Ice, Fire}, {Ice, Lightning, Fire}, } var ... full list of rules can be found here. Handler for GET \materia in api/handlers.go func (app *application) getAllMateria(w http.ResponseWriter, r *http.Request) { var allDisplayMateria []MateriaDTO var allMateria []ccmf.Materia var err error allMateria, err = app.getAllMateriaFromApprSource() if err != nil { app.serverError(w, r, err) } // Some materia have the same name but different grades. // We need to allow only unique names are sent in the response. seenMateriaNames := make(map[string]bool) for _, materia := range allMateria { if _, isDuplicate := seenMateriaNames[materia.Name]; !isDuplicate { seenMateriaNames[materia.Name] = true allDisplayMateria = append(allDisplayMateria, MateriaDTO{ Name: materia.Name, Type: materia.DisplayType, Description: materia.Description, }) } } err = response.JSON(w, http.StatusOK, allDisplayMateria) if err != nil { app.serverError(w, r, err) } } func (app *application) getAllMateriaFromApprSource() (allMateria []ccmf.Materia, err error) { // Check if allMateria data is in cache if data, found := app.getCachedData(string(ccmf.AllMateriaCacheKey)); found { // Type assertion: assert that data is of type []Materia if allMateriaCache, ok := data.([]ccmf.Materia); ok { allMateria = allMateriaCache app.logger.Debug("cache hit") } else { app.logger.Error("Failed to assert cached data as []Materia") return nil, errors.New("failed to assert cached data as []Materia") } } else { // allMateria data is not in cache. Get from DB app.logger.Debug("cache miss") allMateria, err = app.db.GetAllMateria() app.setCache(string(ccmf.AllMateriaCacheKey), allMateria) } return } In-server cache We are using an in-server cache because: The data fetched from the DB never changes. The same data is used by both \materia and \fusion endpoints. Update main.go: // declare a cache and a mutex. // the mutex is to ensure there is only one operation using the cache at a time. type application struct { db *database.DB logger *slog.Logger cache map[string]interface{} wg sync.WaitGroup mu sync.Mutex config config } // in run() initialize the cache: app := &application{ config: cfg, db: db, logger: logger, cache: make(map[string]interface{}), } Update api/helpers.go: // remove backgroundTask() // add getter and setter for the cache: func (app *application) getCachedData(key string) (interface{}, bool) { app.mu.Lock() defer app.mu.Unlock() data, found := app.cache[key] return data, found } func (app *application) setCache(key string, value interface{}) { app.mu.Lock() defer app.mu.Unlock() app.cache[key] = value } Handler for POST \fusion in api/handlers.go // showing only relevant parts of the code func (app *application) fuseMateria(w http.ResponseWriter, r *http.Request) { var fusionReq MateriaFusionRequest err := request.DecodeJSON(w, r, &fusionReq) if err != nil { app.badRequest(w, r, err) return } // Using the Validator we had defined in dtos.go fusionReq.Validator.CheckField(fusionReq.Materia1Name != "", "materia1name", "materia1name is required") fusionReq.Validator.CheckField(fusionReq.Materia2Name != "", "materia2name", "materia2name is required") fusionReq.Validator.CheckField(fusionReq.Materia1Mastered != nil, "materia1mastered", "materia1mastered is required") fusionReq.Validator.CheckField(fusionReq.Materia2Mastered != nil, "materia2mastered", "materia2mastered is required") if fusionReq.Validator.HasErrors() { app.failedValidation(w, r, fusionReq.Validator) return } var allMateria []ccmf.Materia allMateria, err = app.getAllMateriaFromApprSource() if err != nil { app.serverError(w, r, err) } var materia1Type string var materia1Grade int var materia2Type string var materia2Grade int // matching the request input with the categories in DB for _, materia := range allMateria { if materia1Type != "" && materia2Type != "" { break } if materia.Name == fusionReq.Materia1Name && materia1Type == "" { materia1Type = materia.Type materia1Grade = materia.Grade } if materia.Name == fusionReq.Materia2Name && materia2Type == "" { materia2Type = materia.Type materia2Grade = materia.Grade } } if materia1Type == "" || materia2Type == "" { app.badRequest(w, r, errors.New("one or both of the Materia names are not recognised")) return } // game rule - higher grade Materia moves to first position exchangePositionsIfNeeded(&fusionReq, &materia1Grade, &materia2Grade, &materia1Type, &materia2Type) relevantBasicRuleMap := ccmf.BasicRuleMap[ccmf.MateriaType(materia1Type)] var relevantBasicRule ccmf.BasicCombinationRule // finding the relevant combination rule for _, rule := range relevantBasicRuleMap { if (rule.FirstMateriaType == ccmf.MateriaType(materia1Type)) && (rule.SecondMateriaType == ccmf.MateriaType(materia2Type)) { relevantBasicRule = rule break } } var resultantMateria MateriaDTO // game rule - grade of resultant Materia depends on the input Materia as well as their Mastered state resultantMateriaGrade := determineGrade(fusionReq, materia1Grade) if relevantBasicRule.FirstMateriaType == "" { app.logger.Info("none of the basic rules satisfy the requirement.") // get final output using complex rules resultantMateria = useComplexRules(materia1Grade, materia2Grade, resultantMateriaGrade, materia1Type, materia2Type, *fusionReq.Materia1Mastered, *fusionReq.Materia2Mastered, &allMateria) } else { // get final output using basic rules resultantMateriaType := relevantBasicRule.ResultantMateriaType for _, materia := range allMateria { if materia.Grade == resultantMateriaGrade && materia.Type == string(resultantMateriaType) { resultantMateria.Name = materia.Name resultantMateria.Type = materia.DisplayType resultantMateria.Description = materia.Descriptionbreak } } } err = response.JSON(w, http.StatusOK, resultantMateria) if err != nil { app.serverError(w, r, err) } } // Combination rules which do not follow any pattern, and had to be coded separately func useComplexRules(materia1Grade, materia2Grade, resultantMateriaGrade int, materia1Type, materia2Type string, materia1Mastered, materia2Mastered bool, allMateria *[]ccmf.Materia) (resultantMateria MateriaDTO) { var resultantMateriaType string switch { // Complex Rule 1: FIL, Defense case (materia1Type == string(ccmf.Fire) || materia1Type == string(ccmf.Ice) || materia1Type == string(ccmf.Lightning)) && materia2Type == string(ccmf.Defense): if materia1Grade == 1 && materia2Grade == 1 { // output is Defense when grades are equal to 1 resultantMateriaType = string(ccmf.Defense) if materia1Mastered || materia2Mastered { // final Grade is increased when output is Defense increaseGrade(&resultantMateriaGrade) } } else { // output is FIL when grades are NOT equal to 1 resultantMateriaType = materia1Type } ... // prepare response DTO updateResultantMateriaData(allMateria, resultantMateriaGrade, resultantMateriaType, &resultantMateria) return resultantMateria } Complete handler code can be found here. Swagger UI and OpenAPI definition doc Add the Swagger library: go get -u github.com/swaggo/swag/cmd/swag go get github.com/swaggo/http-swagger go get github.com/swaggo/swag In routes.go uncomment the Swagger line, and add the import: httpSwagger "github.com/swaggo/http-swagger" In the handler, DTO and model files, add comments for Swagger documentation. Refer this for all options. In the terminal, run: cd api swag init -d . This creates an api/docs folder, with the definition available for Go, JSON and YAML. To test it, start the local server and open [http://localhost:4444/docs](http://localhost:4444/docs) . Final folder structure: 📦 crisis-core-materia-fusion-api .gitignore ├─ Dockerfile ├─ LICENSE ├─ Makefile ├─ README.md ├─ api │ ├─ docs │ │ ├─ docs.go │ │ ├─ swagger.json │ │ └─ swagger.yaml │ ├─ dtos.go │ ├─ errors.go │ ├─ handlers.go │ ├─ helpers.go │ ├─ main.go │ ├─ middleware.go │ ├─ requests.go │ ├─ routes.go │ └─ server.go ├─ go.mod ├─ go.sum └─ internal ├─ crisis-core-materia-fusion │ ├─ constants.go │ └─ models.go ├─ database -- db.go ├─ env -- env.go ├─ request -- json.go ├─ response -- json.go └─ validator ├─ helpers.go └─ validator.go 3. Setting up the remote PostgreSQL instance in CockroachDB Use the steps from here. After creating the certificate, create /certs/root.crt in the project and add the certificate there. We will make a reference to this file later in the Google Run configuration. CAUTION! We are NOT pushing this folder to the remote repository. Add certs/ folder to .gitignore. We are creating the certificate in local only to test the connection, if you wish. Now when you go to CockroachDB → Dashboard → Left Menu → Databases, you should be able to see the DB you created. Data Migration From your local DB instance, run: pg_dump --no-owner --no-privileges -U -d materiafusiondb > full_dump.sql Go to CockroachDB → Left Menu → Migrations → Add Schema → Drag the SQL file you just got. All the steps will run, except the table data insertion. It will also show you a list of steps that were executed. At the time of writing this article, the PostgreSQL instance in CockroachDB doesn’t support statements like IMPORT INTO . So I had to create an INSERT statement in a local SQL file for 270 rows (which we can derive from the pg_dump output we just got). Log into the remote instance, and run the SQL file. Logging in to the remote instance: psql -h -U -d materiafusiondb -p 4. Deploy a Google Cloud Run instance Create a Dockerfile like this. Go to Google Cloud Run and create a new project for the API. Create Service → Continuously deploy from a repo → SETUP WITH CLOUD BUILD → Repository Provider = Github → Select your repo → Build Type = Dockerfile → Save. Authentication = Allow unathenitcated invocations. Most of the defaults should be fine as is. Scroll down to Containers → Container Port = 4444. Select Variables and Secrets tab, and add the same environment variables as we have in our local .env file. Values: HTTP_PORT = 4444 DB_DSN = ?sslmode=verify-full&sslrootcert=/app/certs/root.crt API_TIMEOUT_SECONDS = 5 API_CALLS_ALLOWED_PER_SECOND = 1 Using Google Secret Manager for the certificate The last piece of the puzzle. Search for Secret Manager → Create Secret → Name = ‘DB_CERT’ → Upload the .crt certificate of the CockroachDB. In Cloud Run → (your service) → Click Edit Continuous Deployment → Scroll down to Configuration → Open Editor. Add this as the first step: - name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim' args: - '-c' - > mkdir -p certs gcloud secrets versions access latest --secret="DB_CERT" > certs/root.crt id: Fetch Secret This will make Cloud Build create the file certs/root.crt in our project before the build starts, so that the Dockerfile will have access to it even though we never pushed it to our Github repository. And that’s it. Try pushing a commit and check if the build triggers. The Cloud Run dashboard will show the URL of your hosted Go server. For questions related to “Why did you do X and not Y?” read this. For anything else that you want to know or discuss, go here. I built an API with Go and PostgreSQL, set up a CI/CD pipeline with Google Cloud Run, Cloud Build, Secret Manager and Artifact Registry, and connected the Cloud Run instance to CockroachDB. The API is based on the game Crisis Core: Final Fantasy VII , to simulate “Materia Fusion”. This article’s intended audience is for developers who just want to know how to build and deploy the API. I have another article where I talk about everything I learnt while working on this project and what didn’t work. Crisis Core: Final Fantasy VII article Links for easy reference GitHub repo and README Swagger (OpenAPI) documentation and testing Public Postman collection Source of domain model GitHub repo and README GitHub repo and README Swagger (OpenAPI) documentation and testing Swagger (OpenAPI) documentation and testing Public Postman collection Public Postman collection Source of domain model Source of domain model API Objective 3 endpoints — health check (GET), list of all materia (GET), and simulate materia fusion (POST). The Domain Model Materia (both singular and plural) is a crystal orb that serves as a source of magic. There are 144 distinct materia in the game, and they’re broadly classified into 4 categories: “Magic”, “Command”, “Support” and “Independent”. However, for the purpose of figuring out the rules of materia fusion, it was easier to have 32 internal categories based on their fusion behaviour, and 8 grades within those categories ( see reference ). 32 internal categories 8 grades see reference A materia becomes ‘Mastered’ when it is used for a certain duration. The duration is not important here. Most importantly, 2 materia can be fused to produce a new materia. The rules governing fusion are influenced by: Whether either or both materia are mastered. Which materia comes first (as in X+Y is not necessarily equal to Y+X). Materia internal category. Materia grade. Whether either or both materia are mastered. Which materia comes first (as in X+Y is not necessarily equal to Y+X ). X+Y Y+X Materia internal category. Materia grade. And there are a LOT of exceptions, with some rules having 3 levels of nested if-else logic. This eliminates the possibility of creating a simple table in the DB and persisting 1000+ rules into it, or coming up with One Formula To Rule Them All. if-else In short, we need: A table materia with columns name(string), materia_type(ENUM) (the 32 internal categories), grade(integer), display_materia_type(ENUM) (the 4 categories used in the game), description(string) and id(integer) as an auto-incrementing primary key. A data structure to encapsulate the basic rules format MateriaTypeA + MateriaTypeB = MateriaTypeC. Code to use the basic and complex rules to determine the output Materia in terms of its internal category and grade. A table materia with columns name(string), materia_type(ENUM) (the 32 internal categories), grade(integer), display_materia_type(ENUM) (the 4 categories used in the game), description(string) and id(integer) as an auto-incrementing primary key. A table materia with columns name(string) , materia_type(ENUM) (the 32 internal categories), grade(integer) , display_materia_type(ENUM) (the 4 categories used in the game), description(string) and id(integer) as an auto-incrementing primary key. materia name(string) materia_type(ENUM) grade(integer) display_materia_type(ENUM) description(string) id(integer) A data structure to encapsulate the basic rules format MateriaTypeA + MateriaTypeB = MateriaTypeC. A data structure to encapsulate the basic rules format MateriaTypeA + MateriaTypeB = MateriaTypeC . MateriaTypeA + MateriaTypeB = MateriaTypeC Code to use the basic and complex rules to determine the output Materia in terms of its internal category and grade. Code to use the basic and complex rules to determine the output Materia in terms of its internal category and grade. 1. Setting Up The Local PostgreSQL DB Ideally you can install the DB from the website itself. But the pgAdmin tool could not connect to the DB for some reason, so I used Homebrew. website Installation brew install postgresql@17 brew install postgresql@17 This will install a whole bunch of CLI binary files to help use the DB. Optional: add /opt/homebrew/opt/postgresql@17/bin to $PATH variable. Next, create the DB: /opt/homebrew/opt/postgresql@17/bin $PATH # create the DB createdb materiafusiondb # step into the DB to perform SQL commands psql materiafusiondb # create the DB createdb materiafusiondb # step into the DB to perform SQL commands psql materiafusiondb Create the user and permissions -- create an SQL user to be used by the Go server CREATE USER go_client WITH PASSWORD 'xxxxxxxx'; -- The Go server doesn't ever need to add data to the DB. -- So let's give it just read permission. CREATE ROLE readonly_role; GRANT USAGE ON SCHEMA public TO readonly_role; -- This command gives SELECT access to all future created tables. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role; -- If you want to be more strict and give access only to tables that already exist, use this: -- GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role; GRANT readonly_role TO go_client; -- create an SQL user to be used by the Go server CREATE USER go_client WITH PASSWORD 'xxxxxxxx'; -- The Go server doesn't ever need to add data to the DB. -- So let's give it just read permission. CREATE ROLE readonly_role; GRANT USAGE ON SCHEMA public TO readonly_role; -- This command gives SELECT access to all future created tables. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role; -- If you want to be more strict and give access only to tables that already exist, use this: -- GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role; GRANT readonly_role TO go_client; Create the table CREATE TYPE display_materia_type AS ENUM ('Magic', 'Command', 'Support', 'Independent'); CREATE TYPE materia_type AS ENUM ('Fire', 'Ice', 'Lightning', 'Restore', 'Full Cure', 'Status Defense', 'Defense', 'Absorb Magic', 'Status Magic', 'Fire & Status', 'Ice & Status', 'Lightning & Status', 'Gravity', 'Ultimate', 'Quick Attack', 'Quick Attack & Status', 'Blade Arts', 'Blade Arts & Status', 'Fire Blade', 'Ice Blade', 'Lightning Blade', 'Absorb Blade', 'Item', 'Punch', 'SP Turbo', 'HP Up', 'AP Up', 'ATK Up', 'VIT Up', 'MAG Up', 'SPR Up', 'Dash', 'Dualcast', 'DMW', 'Libra', 'MP Up', 'Anything'); CREATE TABLE materia ( id integer NOT NULL, name character varying(50) NOT NULL, materia_type materia_type NOT NULL, grade integer NOT NULL, display_materia_type display_materia_type, description text, CONSTRAINT materia_pkey PRIMARY KEY (id) ); -- The primary key 'id' should auto-increment by 1 for every row entry.CREATE SEQUENCE materia_id_seqAS integerSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 1; ALTER SEQUENCE materia_id_seq OWNED BY materia.id; ALTER TABLE ONLY materia ALTER COLUMN id SET DEFAULT nextval('materia_id_seq'::REGCLASS); CREATE TYPE display_materia_type AS ENUM ('Magic', 'Command', 'Support', 'Independent'); CREATE TYPE materia_type AS ENUM ('Fire', 'Ice', 'Lightning', 'Restore', 'Full Cure', 'Status Defense', 'Defense', 'Absorb Magic', 'Status Magic', 'Fire & Status', 'Ice & Status', 'Lightning & Status', 'Gravity', 'Ultimate', 'Quick Attack', 'Quick Attack & Status', 'Blade Arts', 'Blade Arts & Status', 'Fire Blade', 'Ice Blade', 'Lightning Blade', 'Absorb Blade', 'Item', 'Punch', 'SP Turbo', 'HP Up', 'AP Up', 'ATK Up', 'VIT Up', 'MAG Up', 'SPR Up', 'Dash', 'Dualcast', 'DMW', 'Libra', 'MP Up', 'Anything'); CREATE TABLE materia ( id integer NOT NULL, name character varying(50) NOT NULL, materia_type materia_type NOT NULL, grade integer NOT NULL, display_materia_type display_materia_type, description text, CONSTRAINT materia_pkey PRIMARY KEY (id) ); -- The primary key 'id' should auto-increment by 1 for every row entry.CREATE SEQUENCE materia_id_seqAS integerSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 1; ALTER SEQUENCE materia_id_seq OWNED BY materia.id; ALTER TABLE ONLY materia ALTER COLUMN id SET DEFAULT nextval('materia_id_seq'::REGCLASS); Add the data Create an Excel sheet with table header and data, and export it as a CSV file. Then run the command: COPY materia(name,materia_type,grade,display_materia_type,description) FROM ' /materiadata.csv' DELIMITER ',' CSV HEADER; COPY materia(name,materia_type,grade,display_materia_type,description) FROM ' /materiadata.csv' DELIMITER ',' CSV HEADER; 2. Creating the Go Server Create the boilerplate code using autostrada.dev . Add the options of api , postgresql , httprouter , env var config , tinted logging, git , live reload , makefile . We end up getting a file structure like this: autostrada.dev api postgresql httprouter env var config tinted git live reload makefile 📦 codebase ├─ cmd │ └─ api │ ├─ errors.go │ ├─ handlers.go │ ├─ helpers.go │ ├─ main.go │ ├─ middleware.go │ └─ server.go ├─ internal │ ├─ database --- db.go │ ├─ env --- env.go │ ├─ request --- json.go │ ├─ response --- json.go │ └─ validator │ ├─ helpers.go │ └─ validators.go ├─ go.mod ├─ LICENSE ├─ Makefile ├─ README.md └─ README.html 📦 codebase ├─ cmd │ └─ api │ ├─ errors.go │ ├─ handlers.go │ ├─ helpers.go │ ├─ main.go │ ├─ middleware.go │ └─ server.go ├─ internal │ ├─ database --- db.go │ ├─ env --- env.go │ ├─ request --- json.go │ ├─ response --- json.go │ └─ validator │ ├─ helpers.go │ └─ validators.go ├─ go.mod ├─ LICENSE ├─ Makefile ├─ README.md └─ README.html .env file The boilerplate generator has created code to fetch environment variables and add them to the code, but we can make it easier to track and update the values. Create /.env file. Add the following values: /.env HTTP_PORT=4444 DB_DSN=go_client: @localhost:5432/materiafusiondb?sslmode=disable API_TIMEOUT_SECONDS=5 API_CALLS_ALLOWED_PER_SECOND=1 HTTP_PORT=4444 DB_DSN=go_client: @localhost:5432/materiafusiondb?sslmode=disable API_TIMEOUT_SECONDS=5 API_CALLS_ALLOWED_PER_SECOND=1 Add the godotenv library: go get github.com/joho/godotenv go get github.com/joho/godotenv Add the following to main.go : main.go // At the beginning of main(): err := godotenv.Load(".env") // Loads environment variables from .env file if err != nil { // This will be true in prod, but that's fine. fmt.Println("Error loading .env file") } // Modify config struct: type config struct { baseURL string db struct { dsn string } httpPort int apiTimeout int apiCallsAllowedPerSecond float64 } // Modify run() to use the new values from .env: cfg.httpPort = env.GetInt("HTTP_PORT") cfg.db.dsn = env.GetString("DB_DSN") cfg.apiTimeout = env.GetInt("API_TIMEOUT_SECONDS") cfg.apiCallsAllowedPerSecond = float64(env.GetInt("API_CALLS_ALLOWED_PER_SECOND")) // cfg.baseURL = env.GetString("BASE_URL") - not required // At the beginning of main(): err := godotenv.Load(".env") // Loads environment variables from .env file if err != nil { // This will be true in prod, but that's fine. fmt.Println("Error loading .env file") } // Modify config struct: type config struct { baseURL string db struct { dsn string } httpPort int apiTimeout int apiCallsAllowedPerSecond float64 } // Modify run() to use the new values from .env: cfg.httpPort = env.GetInt("HTTP_PORT") cfg.db.dsn = env.GetString("DB_DSN") cfg.apiTimeout = env.GetInt("API_TIMEOUT_SECONDS") cfg.apiCallsAllowedPerSecond = float64(env.GetInt("API_CALLS_ALLOWED_PER_SECOND")) // cfg.baseURL = env.GetString("BASE_URL") - not required Middleware and Routes The boilerplate already has a middleware to recover from panics. We will add 3 more: Content-Type checking, rate-limiting and API timeout protection. Add tollbooth library for rate-limiting: tollbooth go get github.com/didip/tollbooth go get github.com/didip/tollbooth Update <rootfolder/api/middleware.go : <rootfolder/api/middleware.go func (app *application) contentTypeCheck(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { if r.Header.Get("Content-Type") != "application/json" { app.unsupportedMediaType(w, r) return } next.ServeHTTP(w, r) }) } func (app *application) rateLimiter(next http.Handler) http.Handler { limiter := tollbooth.NewLimiter(app.config.apiCallsAllowedPerSecond, nil) limiter.SetIPLookups([]string{"X-Real-IP", "X-Forwarded-For", "RemoteAddr"}) return tollbooth.LimitHandler(limiter, next) } func (app *application) apiTimeout(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { timeoutDuration := time.Duration(app.config.apiTimeout) * time.Second ctx, cancel := context.WithTimeout(r.Context(), timeoutDuration) defer cancel() r = r.WithContext(ctx) done := make(chan struct{}) go func() { next.ServeHTTP(w, r) close(done) }() select { case <-done: return case <-ctx.Done(): app.gatewayTimeout(w, r) return } }) } func (app *application) contentTypeCheck(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { if r.Header.Get("Content-Type") != "application/json" { app.unsupportedMediaType(w, r) return } next.ServeHTTP(w, r) }) } func (app *application) rateLimiter(next http.Handler) http.Handler { limiter := tollbooth.NewLimiter(app.config.apiCallsAllowedPerSecond, nil) limiter.SetIPLookups([]string{"X-Real-IP", "X-Forwarded-For", "RemoteAddr"}) return tollbooth.LimitHandler(limiter, next) } func (app *application) apiTimeout(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { timeoutDuration := time.Duration(app.config.apiTimeout) * time.Second ctx, cancel := context.WithTimeout(r.Context(), timeoutDuration) defer cancel() r = r.WithContext(ctx) done := make(chan struct{}) go func() { next.ServeHTTP(w, r) close(done) }() select { case <-done: return case <-ctx.Done(): app.gatewayTimeout(w, r) return } }) } The middleware need to be added to the routes. They can be either added to all the routes, or to specific ones. In our case, Content-Type checking (that is, mandating the input headers to include Content-Type: application/json ) is only needed for POST requests. So modify routes.go as follows: Content-Type: application/json routes.go func (app *application) routes() http.Handler { mux := httprouter.New() mux.NotFound = http.HandlerFunc(app.notFound) mux.MethodNotAllowed = http.HandlerFunc(app.methodNotAllowed) // Serve the Swagger UI. Uncomment this line later // mux.Handler("GET", "/docs/*any", httpSwagger.WrapHandler) mux.HandlerFunc("GET", "/status", app.status) mux.HandlerFunc("GET", "/materia", app.getAllMateria) // Adding content-type check middleware to only the POST method mux.Handler("POST", "/fusion", app.contentTypeCheck(http.HandlerFunc(app.fuseMateria))) return app.chainMiddlewares(mux) } func (app *application) chainMiddlewares(next http.Handler) http.Handler { middlewares := []func(http.Handler) http.Handler{ app.recoverPanic, app.apiTimeout, app.rateLimiter, } for _, middleware := range middlewares { next = middleware(next) } return next } func (app *application) routes() http.Handler { mux := httprouter.New() mux.NotFound = http.HandlerFunc(app.notFound) mux.MethodNotAllowed = http.HandlerFunc(app.methodNotAllowed) // Serve the Swagger UI. Uncomment this line later // mux.Handler("GET", "/docs/*any", httpSwagger.WrapHandler) mux.HandlerFunc("GET", "/status", app.status) mux.HandlerFunc("GET", "/materia", app.getAllMateria) // Adding content-type check middleware to only the POST method mux.Handler("POST", "/fusion", app.contentTypeCheck(http.HandlerFunc(app.fuseMateria))) return app.chainMiddlewares(mux) } func (app *application) chainMiddlewares(next http.Handler) http.Handler { middlewares := []func(http.Handler) http.Handler{ app.recoverPanic, app.apiTimeout, app.rateLimiter, } for _, middleware := range middlewares { next = middleware(next) } return next } Error handling Add the following methods to /api/errors.go to help the middleware functions: /api/errors.go func (app *application) unsupportedMediaType(w http.ResponseWriter, r *http.Request) { message := fmt.Sprintf("The %s Content-Type is not supported", r.Header.Get("Content-Type")) app.errorMessage(w, r, http.StatusUnsupportedMediaType, message, nil) } func (app *application) gatewayTimeout(w http.ResponseWriter, r *http.Request) { message := "Request timed out" app.errorMessage(w, r, http.StatusGatewayTimeout, message, nil) } func (app *application) unsupportedMediaType(w http.ResponseWriter, r *http.Request) { message := fmt.Sprintf("The %s Content-Type is not supported", r.Header.Get("Content-Type")) app.errorMessage(w, r, http.StatusUnsupportedMediaType, message, nil) } func (app *application) gatewayTimeout(w http.ResponseWriter, r *http.Request) { message := "Request timed out" app.errorMessage(w, r, http.StatusGatewayTimeout, message, nil) } Request and Response structure files /api/dtos.go for the responses of all 3 endpoints: /api/dtos.go package main // MateriaDTO provides Materia details - Name, Description and Type (Magic / Command / Support / Independent) type MateriaDTO struct { Name string `json:"name" example:"Thunder"` Type string `json:"type" example:"Magic"` Description string `json:"description" example:"Shoots lightning forward dealing thunder damage."`} // StatusDTO provides status of the server type StatusDTO struct { Status string `json:"Status" example:"OK"` } // ErrorResponseDTO provides Error message type ErrorResponseDTO struct { Error string `json:"Error" example:"The server encountered a problem and could not process your request"` } package main // MateriaDTO provides Materia details - Name, Description and Type (Magic / Command / Support / Independent) type MateriaDTO struct { Name string `json:"name" example:"Thunder"` Type string `json:"type" example:"Magic"` Description string `json:"description" example:"Shoots lightning forward dealing thunder damage."`} // StatusDTO provides status of the server type StatusDTO struct { Status string `json:"Status" example:"OK"` } // ErrorResponseDTO provides Error message type ErrorResponseDTO struct { Error string `json:"Error" example:"The server encountered a problem and could not process your request"` } /api/requests.go for the POST \fusion endpoint: /api/requests.go \fusion package main import ( "github.com/RayMathew/crisis-core-materia-fusion-api/internal/validator" ) // MateriaFusionRequest provides input Materia names and their Mastered states type MateriaFusionRequest struct { Materia1Mastered *bool `json:"materia1mastered" example:"true"` Materia2Mastered *bool `json:"materia2mastered" example:"false"` Materia1Name string `json:"materia1name" example:"Fire"` Materia2Name string `json:"materia2name" example:"Blizzard"` Validator validator.Validator `json:"-"` } package main import ( "github.com/RayMathew/crisis-core-materia-fusion-api/internal/validator" ) // MateriaFusionRequest provides input Materia names and their Mastered states type MateriaFusionRequest struct { Materia1Mastered *bool `json:"materia1mastered" example:"true"` Materia2Mastered *bool `json:"materia2mastered" example:"false"` Materia1Name string `json:"materia1name" example:"Fire"` Materia2Name string `json:"materia2name" example:"Blizzard"` Validator validator.Validator `json:"-"` } Validator , from the generated code, will be used later to validate the input fields for \fusion endpoint. Validator \fusion Data Structure for the combination rules Create the file /internal/crisis-core-materia-fusion/constants.go . Add the following: /internal/crisis-core-materia-fusion/constants.go package crisiscoremateriafusion type MateriaType string const ( Fire MateriaType = "Fire" Ice MateriaType = "Ice" Lightning MateriaType = "Lightning" Restore MateriaType = "Restore" ... package crisiscoremateriafusion type MateriaType string const ( Fire MateriaType = "Fire" Ice MateriaType = "Ice" Lightning MateriaType = "Lightning" Restore MateriaType = "Restore" ... full list of 32 MateriaType s can be found here . MateriaType here Create the file /internal/crisis-core-materia-fusion/models.go . Add the following: /internal/crisis-core-materia-fusion/models.go package crisiscoremateriafusion type Materia struct { Name string `json:"name"` Type string `json:"materia_type"` DisplayType string `json:"display_type"` Description string `json:"description"` Grade int `json:"grade"` } // Data structure to hold all basic combination rules type BasicCombinationRule struct { FirstMateriaType MateriaType SecondMateriaType MateriaType ResultantMateriaType MateriaType } var FILBasicRules = []BasicCombinationRule{ {Fire, Fire, Fire}, {Ice, Ice, Ice}, {Lightning, Lightning, Lightning}, {Fire, Ice, Lightning}, {Ice, Fire, Lightning}, {Fire, Lightning, Ice}, {Lightning, Fire, Ice}, {Lightning, Ice, Fire}, {Ice, Lightning, Fire}, } var ... package crisiscoremateriafusion type Materia struct { Name string `json:"name"` Type string `json:"materia_type"` DisplayType string `json:"display_type"` Description string `json:"description"` Grade int `json:"grade"` } // Data structure to hold all basic combination rules type BasicCombinationRule struct { FirstMateriaType MateriaType SecondMateriaType MateriaType ResultantMateriaType MateriaType } var FILBasicRules = []BasicCombinationRule{ {Fire, Fire, Fire}, {Ice, Ice, Ice}, {Lightning, Lightning, Lightning}, {Fire, Ice, Lightning}, {Ice, Fire, Lightning}, {Fire, Lightning, Ice}, {Lightning, Fire, Ice}, {Lightning, Ice, Fire}, {Ice, Lightning, Fire}, } var ... full list of rules can be found here . here Handler for GET \materia in api/handlers.go \materia api/handlers.go func (app *application) getAllMateria(w http.ResponseWriter, r *http.Request) { var allDisplayMateria []MateriaDTO var allMateria []ccmf.Materia var err error allMateria, err = app.getAllMateriaFromApprSource() if err != nil { app.serverError(w, r, err) } // Some materia have the same name but different grades. // We need to allow only unique names are sent in the response. seenMateriaNames := make(map[string]bool) for _, materia := range allMateria { if _, isDuplicate := seenMateriaNames[materia.Name]; !isDuplicate { seenMateriaNames[materia.Name] = true allDisplayMateria = append(allDisplayMateria, MateriaDTO{ Name: materia.Name, Type: materia.DisplayType, Description: materia.Description, }) } } err = response.JSON(w, http.StatusOK, allDisplayMateria) if err != nil { app.serverError(w, r, err) } } func (app *application) getAllMateriaFromApprSource() (allMateria []ccmf.Materia, err error) { // Check if allMateria data is in cache if data, found := app.getCachedData(string(ccmf.AllMateriaCacheKey)); found { // Type assertion: assert that data is of type []Materia if allMateriaCache, ok := data.([]ccmf.Materia); ok { allMateria = allMateriaCache app.logger.Debug("cache hit") } else { app.logger.Error("Failed to assert cached data as []Materia") return nil, errors.New("failed to assert cached data as []Materia") } } else { // allMateria data is not in cache. Get from DB app.logger.Debug("cache miss") allMateria, err = app.db.GetAllMateria() app.setCache(string(ccmf.AllMateriaCacheKey), allMateria) } return } func (app *application) getAllMateria(w http.ResponseWriter, r *http.Request) { var allDisplayMateria []MateriaDTO var allMateria []ccmf.Materia var err error allMateria, err = app.getAllMateriaFromApprSource() if err != nil { app.serverError(w, r, err) } // Some materia have the same name but different grades. // We need to allow only unique names are sent in the response. seenMateriaNames := make(map[string]bool) for _, materia := range allMateria { if _, isDuplicate := seenMateriaNames[materia.Name]; !isDuplicate { seenMateriaNames[materia.Name] = true allDisplayMateria = append(allDisplayMateria, MateriaDTO{ Name: materia.Name, Type: materia.DisplayType, Description: materia.Description, }) } } err = response.JSON(w, http.StatusOK, allDisplayMateria) if err != nil { app.serverError(w, r, err) } } func (app *application) getAllMateriaFromApprSource() (allMateria []ccmf.Materia, err error) { // Check if allMateria data is in cache if data, found := app.getCachedData(string(ccmf.AllMateriaCacheKey)); found { // Type assertion: assert that data is of type []Materia if allMateriaCache, ok := data.([]ccmf.Materia); ok { allMateria = allMateriaCache app.logger.Debug("cache hit") } else { app.logger.Error("Failed to assert cached data as []Materia") return nil, errors.New("failed to assert cached data as []Materia") } } else { // allMateria data is not in cache. Get from DB app.logger.Debug("cache miss") allMateria, err = app.db.GetAllMateria() app.setCache(string(ccmf.AllMateriaCacheKey), allMateria) } return } In-server cache We are using an in-server cache because: The data fetched from the DB never changes. The same data is used by both \materia and \fusion endpoints. The data fetched from the DB never changes. The same data is used by both \materia and \fusion endpoints. \materia \fusion Update main.go : main.go // declare a cache and a mutex. // the mutex is to ensure there is only one operation using the cache at a time. type application struct { db *database.DB logger *slog.Logger cache map[string]interface{} wg sync.WaitGroup mu sync.Mutex config config } // in run() initialize the cache: app := &application{ config: cfg, db: db, logger: logger, cache: make(map[string]interface{}), } // declare a cache and a mutex. // the mutex is to ensure there is only one operation using the cache at a time. type application struct { db *database.DB logger *slog.Logger cache map[string]interface{} wg sync.WaitGroup mu sync.Mutex config config } // in run() initialize the cache: app := &application{ config: cfg, db: db, logger: logger, cache: make(map[string]interface{}), } Update api/helpers.go : api/helpers.go // remove backgroundTask() // add getter and setter for the cache: func (app *application) getCachedData(key string) (interface{}, bool) { app.mu.Lock() defer app.mu.Unlock() data, found := app.cache[key] return data, found } func (app *application) setCache(key string, value interface{}) { app.mu.Lock() defer app.mu.Unlock() app.cache[key] = value } // remove backgroundTask() // add getter and setter for the cache: func (app *application) getCachedData(key string) (interface{}, bool) { app.mu.Lock() defer app.mu.Unlock() data, found := app.cache[key] return data, found } func (app *application) setCache(key string, value interface{}) { app.mu.Lock() defer app.mu.Unlock() app.cache[key] = value } Handler for POST \fusion in api/handlers.go \fusion api/handlers.go // showing only relevant parts of the code func (app *application) fuseMateria(w http.ResponseWriter, r *http.Request) { var fusionReq MateriaFusionRequest err := request.DecodeJSON(w, r, &fusionReq) if err != nil { app.badRequest(w, r, err) return } // Using the Validator we had defined in dtos.go fusionReq.Validator.CheckField(fusionReq.Materia1Name != "", "materia1name", "materia1name is required") fusionReq.Validator.CheckField(fusionReq.Materia2Name != "", "materia2name", "materia2name is required") fusionReq.Validator.CheckField(fusionReq.Materia1Mastered != nil, "materia1mastered", "materia1mastered is required") fusionReq.Validator.CheckField(fusionReq.Materia2Mastered != nil, "materia2mastered", "materia2mastered is required") if fusionReq.Validator.HasErrors() { app.failedValidation(w, r, fusionReq.Validator) return } var allMateria []ccmf.Materia allMateria, err = app.getAllMateriaFromApprSource() if err != nil { app.serverError(w, r, err) } var materia1Type string var materia1Grade int var materia2Type string var materia2Grade int // matching the request input with the categories in DB for _, materia := range allMateria { if materia1Type != "" && materia2Type != "" { break } if materia.Name == fusionReq.Materia1Name && materia1Type == "" { materia1Type = materia.Type materia1Grade = materia.Grade } if materia.Name == fusionReq.Materia2Name && materia2Type == "" { materia2Type = materia.Type materia2Grade = materia.Grade } } if materia1Type == "" || materia2Type == "" { app.badRequest(w, r, errors.New("one or both of the Materia names are not recognised")) return } // game rule - higher grade Materia moves to first position exchangePositionsIfNeeded(&fusionReq, &materia1Grade, &materia2Grade, &materia1Type, &materia2Type) relevantBasicRuleMap := ccmf.BasicRuleMap[ccmf.MateriaType(materia1Type)] var relevantBasicRule ccmf.BasicCombinationRule // finding the relevant combination rule for _, rule := range relevantBasicRuleMap { if (rule.FirstMateriaType == ccmf.MateriaType(materia1Type)) && (rule.SecondMateriaType == ccmf.MateriaType(materia2Type)) { relevantBasicRule = rule break } } var resultantMateria MateriaDTO // game rule - grade of resultant Materia depends on the input Materia as well as their Mastered state resultantMateriaGrade := determineGrade(fusionReq, materia1Grade) if relevantBasicRule.FirstMateriaType == "" { app.logger.Info("none of the basic rules satisfy the requirement.") // get final output using complex rules resultantMateria = useComplexRules(materia1Grade, materia2Grade, resultantMateriaGrade, materia1Type, materia2Type, *fusionReq.Materia1Mastered, *fusionReq.Materia2Mastered, &allMateria) } else { // get final output using basic rules resultantMateriaType := relevantBasicRule.ResultantMateriaType for _, materia := range allMateria { if materia.Grade == resultantMateriaGrade && materia.Type == string(resultantMateriaType) { resultantMateria.Name = materia.Name resultantMateria.Type = materia.DisplayType resultantMateria.Description = materia.Descriptionbreak } } } err = response.JSON(w, http.StatusOK, resultantMateria) if err != nil { app.serverError(w, r, err) } } // Combination rules which do not follow any pattern, and had to be coded separately func useComplexRules(materia1Grade, materia2Grade, resultantMateriaGrade int, materia1Type, materia2Type string, materia1Mastered, materia2Mastered bool, allMateria *[]ccmf.Materia) (resultantMateria MateriaDTO) { var resultantMateriaType string switch { // Complex Rule 1: FIL, Defense case (materia1Type == string(ccmf.Fire) || materia1Type == string(ccmf.Ice) || materia1Type == string(ccmf.Lightning)) && materia2Type == string(ccmf.Defense): if materia1Grade == 1 && materia2Grade == 1 { // output is Defense when grades are equal to 1 resultantMateriaType = string(ccmf.Defense) if materia1Mastered || materia2Mastered { // final Grade is increased when output is Defense increaseGrade(&resultantMateriaGrade) } } else { // output is FIL when grades are NOT equal to 1 resultantMateriaType = materia1Type } ... // prepare response DTO updateResultantMateriaData(allMateria, resultantMateriaGrade, resultantMateriaType, &resultantMateria) return resultantMateria } // showing only relevant parts of the code func (app *application) fuseMateria(w http.ResponseWriter, r *http.Request) { var fusionReq MateriaFusionRequest err := request.DecodeJSON(w, r, &fusionReq) if err != nil { app.badRequest(w, r, err) return } // Using the Validator we had defined in dtos.go fusionReq.Validator.CheckField(fusionReq.Materia1Name != "", "materia1name", "materia1name is required") fusionReq.Validator.CheckField(fusionReq.Materia2Name != "", "materia2name", "materia2name is required") fusionReq.Validator.CheckField(fusionReq.Materia1Mastered != nil, "materia1mastered", "materia1mastered is required") fusionReq.Validator.CheckField(fusionReq.Materia2Mastered != nil, "materia2mastered", "materia2mastered is required") if fusionReq.Validator.HasErrors() { app.failedValidation(w, r, fusionReq.Validator) return } var allMateria []ccmf.Materia allMateria, err = app.getAllMateriaFromApprSource() if err != nil { app.serverError(w, r, err) } var materia1Type string var materia1Grade int var materia2Type string var materia2Grade int // matching the request input with the categories in DB for _, materia := range allMateria { if materia1Type != "" && materia2Type != "" { break } if materia.Name == fusionReq.Materia1Name && materia1Type == "" { materia1Type = materia.Type materia1Grade = materia.Grade } if materia.Name == fusionReq.Materia2Name && materia2Type == "" { materia2Type = materia.Type materia2Grade = materia.Grade } } if materia1Type == "" || materia2Type == "" { app.badRequest(w, r, errors.New("one or both of the Materia names are not recognised")) return } // game rule - higher grade Materia moves to first position exchangePositionsIfNeeded(&fusionReq, &materia1Grade, &materia2Grade, &materia1Type, &materia2Type) relevantBasicRuleMap := ccmf.BasicRuleMap[ccmf.MateriaType(materia1Type)] var relevantBasicRule ccmf.BasicCombinationRule // finding the relevant combination rule for _, rule := range relevantBasicRuleMap { if (rule.FirstMateriaType == ccmf.MateriaType(materia1Type)) && (rule.SecondMateriaType == ccmf.MateriaType(materia2Type)) { relevantBasicRule = rule break } } var resultantMateria MateriaDTO // game rule - grade of resultant Materia depends on the input Materia as well as their Mastered state resultantMateriaGrade := determineGrade(fusionReq, materia1Grade) if relevantBasicRule.FirstMateriaType == "" { app.logger.Info("none of the basic rules satisfy the requirement.") // get final output using complex rules resultantMateria = useComplexRules(materia1Grade, materia2Grade, resultantMateriaGrade, materia1Type, materia2Type, *fusionReq.Materia1Mastered, *fusionReq.Materia2Mastered, &allMateria) } else { // get final output using basic rules resultantMateriaType := relevantBasicRule.ResultantMateriaType for _, materia := range allMateria { if materia.Grade == resultantMateriaGrade && materia.Type == string(resultantMateriaType) { resultantMateria.Name = materia.Name resultantMateria.Type = materia.DisplayType resultantMateria.Description = materia.Descriptionbreak } } } err = response.JSON(w, http.StatusOK, resultantMateria) if err != nil { app.serverError(w, r, err) } } // Combination rules which do not follow any pattern, and had to be coded separately func useComplexRules(materia1Grade, materia2Grade, resultantMateriaGrade int, materia1Type, materia2Type string, materia1Mastered, materia2Mastered bool, allMateria *[]ccmf.Materia) (resultantMateria MateriaDTO) { var resultantMateriaType string switch { // Complex Rule 1: FIL, Defense case (materia1Type == string(ccmf.Fire) || materia1Type == string(ccmf.Ice) || materia1Type == string(ccmf.Lightning)) && materia2Type == string(ccmf.Defense): if materia1Grade == 1 && materia2Grade == 1 { // output is Defense when grades are equal to 1 resultantMateriaType = string(ccmf.Defense) if materia1Mastered || materia2Mastered { // final Grade is increased when output is Defense increaseGrade(&resultantMateriaGrade) } } else { // output is FIL when grades are NOT equal to 1 resultantMateriaType = materia1Type } ... // prepare response DTO updateResultantMateriaData(allMateria, resultantMateriaGrade, resultantMateriaType, &resultantMateria) return resultantMateria } Complete handler code can be found here . here Swagger UI and OpenAPI definition doc Add the Swagger library: go get -u github.com/swaggo/swag/cmd/swag go get github.com/swaggo/http-swagger go get github.com/swaggo/swag go get -u github.com/swaggo/swag/cmd/swag go get github.com/swaggo/http-swagger go get github.com/swaggo/swag In routes.go uncomment the Swagger line, and add the import: routes.go httpSwagger "github.com/swaggo/http-swagger" httpSwagger "github.com/swaggo/http-swagger" In the handler, DTO and model files, add comments for Swagger documentation. Refer this for all options. this In the terminal, run: cd api swag init -d . cd api swag init -d . This creates an api/docs folder, with the definition available for Go, JSON and YAML. api/docs To test it, start the local server and open [http://localhost:4444/docs](http://localhost:4444/docs) . [http://localhost:4444/docs](http://localhost:4444/docs) Final folder structure: 📦 crisis-core-materia-fusion-api .gitignore ├─ Dockerfile ├─ LICENSE ├─ Makefile ├─ README.md ├─ api │ ├─ docs │ │ ├─ docs.go │ │ ├─ swagger.json │ │ └─ swagger.yaml │ ├─ dtos.go │ ├─ errors.go │ ├─ handlers.go │ ├─ helpers.go │ ├─ main.go │ ├─ middleware.go │ ├─ requests.go │ ├─ routes.go │ └─ server.go ├─ go.mod ├─ go.sum └─ internal ├─ crisis-core-materia-fusion │ ├─ constants.go │ └─ models.go ├─ database -- db.go ├─ env -- env.go ├─ request -- json.go ├─ response -- json.go └─ validator ├─ helpers.go └─ validator.go 📦 crisis-core-materia-fusion-api .gitignore ├─ Dockerfile ├─ LICENSE ├─ Makefile ├─ README.md ├─ api │ ├─ docs │ │ ├─ docs.go │ │ ├─ swagger.json │ │ └─ swagger.yaml │ ├─ dtos.go │ ├─ errors.go │ ├─ handlers.go │ ├─ helpers.go │ ├─ main.go │ ├─ middleware.go │ ├─ requests.go │ ├─ routes.go │ └─ server.go ├─ go.mod ├─ go.sum └─ internal ├─ crisis-core-materia-fusion │ ├─ constants.go │ └─ models.go ├─ database -- db.go ├─ env -- env.go ├─ request -- json.go ├─ response -- json.go └─ validator ├─ helpers.go └─ validator.go 3. Setting up the remote PostgreSQL instance in CockroachDB Use the steps from here. After creating the certificate, create /certs/root.crt in the project and add the certificate there. We will make a reference to this file later in the Google Run configuration. CAUTION! We are NOT pushing this folder to the remote repository. Add certs/ folder to .gitignore. We are creating the certificate in local only to test the connection, if you wish. Now when you go to CockroachDB → Dashboard → Left Menu → Databases, you should be able to see the DB you created. Use the steps from here . here After creating the certificate, create /certs/root.crt in the project and add the certificate there. We will make a reference to this file later in the Google Run configuration. /certs/root.crt CAUTION! We are NOT pushing this folder to the remote repository. Add certs/ folder to .gitignore . We are creating the certificate in local only to test the connection, if you wish. CAUTION! NOT certs/ .gitignore Now when you go to CockroachDB → Dashboard → Left Menu → Databases, you should be able to see the DB you created. Data Migration From your local DB instance, run: pg_dump --no-owner --no-privileges -U -d materiafusiondb > full_dump.sql pg_dump --no-owner --no-privileges -U -d materiafusiondb > full_dump.sql Go to CockroachDB → Left Menu → Migrations → Add Schema → Drag the SQL file you just got. All the steps will run, except the table data insertion. It will also show you a list of steps that were executed. At the time of writing this article, the PostgreSQL instance in CockroachDB doesn’t support statements like IMPORT INTO . So I had to create an INSERT statement in a local SQL file for 270 rows (which we can derive from the pg_dump output we just got). Log into the remote instance, and run the SQL file. Go to CockroachDB → Left Menu → Migrations → Add Schema → Drag the SQL file you just got. All the steps will run, except the table data insertion. It will also show you a list of steps that were executed. At the time of writing this article, the PostgreSQL instance in CockroachDB doesn’t support statements like IMPORT INTO . So I had to create an INSERT statement in a local SQL file for 270 rows (which we can derive from the pg_dump output we just got). IMPORT INTO INSERT pg_dump Log into the remote instance, and run the SQL file. Logging in to the remote instance: psql -h -U -d materiafusiondb -p psql -h -U -d materiafusiondb -p 4. Deploy a Google Cloud Run instance Create a Dockerfile like this. Go to Google Cloud Run and create a new project for the API. Create Service → Continuously deploy from a repo → SETUP WITH CLOUD BUILD → Repository Provider = Github → Select your repo → Build Type = Dockerfile → Save. Authentication = Allow unathenitcated invocations. Most of the defaults should be fine as is. Scroll down to Containers → Container Port = 4444. Select Variables and Secrets tab, and add the same environment variables as we have in our local .env file. Create a Dockerfile like this . this Go to Google Cloud Run and create a new project for the API. Google Cloud Run Create Service → Continuously deploy from a repo → SETUP WITH CLOUD BUILD → Repository Provider = Github → Select your repo → Build Type = Dockerfile → Save. Continuously deploy from a repo SETUP WITH CLOUD BUILD Repository Provider Build Type Dockerfile Authentication = Allow unathenitcated invocations . Authentication Allow unathenitcated invocations Most of the defaults should be fine as is. Scroll down to Containers → Container Port = 4444. Container Port Select Variables and Secrets tab, and add the same environment variables as we have in our local .env file. Variables and Secrets .env Values: HTTP_PORT = 4444 DB_DSN = ?sslmode=verify-full&sslrootcert=/app/certs/root.crt API_TIMEOUT_SECONDS = 5 API_CALLS_ALLOWED_PER_SECOND = 1 HTTP_PORT = 4444 DB_DSN = ?sslmode=verify-full&sslrootcert=/app/certs/root.crt ?sslmode=verify-full&sslrootcert=/app/certs/root.crt API_TIMEOUT_SECONDS = 5 API_CALLS_ALLOWED_PER_SECOND = 1 Using Google Secret Manager for the certificate The last piece of the puzzle. Search for Secret Manager → Create Secret → Name = ‘DB_CERT’ → Upload the .crt certificate of the CockroachDB. In Cloud Run → (your service) → Click Edit Continuous Deployment → Scroll down to Configuration → Open Editor. Add this as the first step: Search for Secret Manager → Create Secret → Name = ‘DB_CERT’ → Upload the .crt certificate of the CockroachDB. .crt In Cloud Run → (your service) → Click Edit Continuous Deployment → Scroll down to Configuration → Open Editor. Edit Continuous Deployment Configuration Add this as the first step: - name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim' args: - '-c' - > mkdir -p certs gcloud secrets versions access latest --secret="DB_CERT" > certs/root.crt id: Fetch Secret - name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim' args: - '-c' - > mkdir -p certs gcloud secrets versions access latest --secret="DB_CERT" > certs/root.crt id: Fetch Secret This will make Cloud Build create the file certs/root.crt in our project before the build starts, so that the Dockerfile will have access to it even though we never pushed it to our Github repository. certs/root.crt And that’s it. Try pushing a commit and check if the build triggers. The Cloud Run dashboard will show the URL of your hosted Go server. For questions related to “Why did you do X and not Y?” read this . this For anything else that you want to know or discuss, go here . here