This article is part of a tutorial series on building GraphQL API with Go and PostgresQL. Please refer to it as this is the fourth part of the series.

Schema

A database schema is essentially a blueprint of how our stored data look like. A schema does not hold the data but rather shows the shape of the data and how different tables in the database relates to each other.

The Project

Creating the database

If you are a linux user like me, open your terminal, type in the following set of commands, follow through with the prompts on each of the input

su

su - postgres

psql

CREATE DATABASE your_preferred_database_name;

In the root of the project, create two files, .env (to store our configuration variables) and .gitignore (to tell git not to push selected files to github).

An environment variable is a variable whose value is set outside the program. It is made up of a name/value pair, and any number may be created and available for reference at a point in time. Storing our configuration variables in an env file instead of imputing the value directly into our application gives the advantage of editing only one file instead of an application wide editing whenever there's a need for changes in the configuration settings. It also helps in security as sensitive information will not be exposed in the application.

.gitignore

.env

.env

port = input_your_port
host = localhost
dbport = 5432
user = input_user
password = input_your_password
dbname = input_database_name
sslmode = disable

Creating and Running Migrations

A Go tool called migrate will be used for these purposes. At the root of your project, run

go get github.com/golang-migrate/migrate

go build -tags 'postgres' -ldflags="-X main.Version=1.0.0" -o $GOPATH/bin/migrate

You might need to add sudo if you are using linux.

Create another folder postgres/migrations/postgres, change directory into it and run the following commands one at at time to create the migration files.

migrate create -ext sql -dir postgres -seq create_users_table
migrate create -ext sql -dir postgres -seq create_posts_table
migrate create -ext sql -dir postgres -seq create_tags_table

The commands will create six files where you can define your database schema

postgres/migrations/postgres/000001_create_users_table.up.sql

BEGIN;

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE SCHEMA IF NOT EXISTS tech;

CREATE TABLE IF NOT EXISTS tech.users(
    id uuid NOT NULL DEFAULT gen_random_uuid () PRIMARY KEY,
    name VARCHAR (127) NOT NULL,
    email VARCHAR (127) NOT NULL UNIQUE,
    password VARCHAR (127) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp
);

END;

postgres/migrations/postgres/000002_create_posts_table.up.sql

BEGIN;

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;


CREATE SCHEMA IF NOT EXISTS tech;

CREATE TABLE IF NOT EXISTS tech.posts (
    id uuid NOT NULL DEFAULT gen_random_uuid () PRIMARY KEY,
    body VARCHAR (1027) NOT NULL,
    shared_body VARCHAR (1027),
    image VARCHAR (127),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
    update_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
    shared_at TIMESTAMP WITH TIME ZONE,
    author uuid NOT NULL REFERENCES tech.users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    shared_user uuid REFERENCES tech.users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    likes uuid REFERENCES tech.users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    Dislikes uuid REFERENCES tech.users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    Tags uuid REFERENCES tech.tags (id) ON DELETE CASCADE ON UPDATE CASCADE
);

END;

postgres/migrations/postgres/000003_create_tags_table.up.sql

BEGIN;

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;


CREATE SCHEMA IF NOT EXISTS tech;

CREATE TABLE IF NOT EXISTS tech.tags(
    id uuid NOT NULL DEFAULT gen_random_uuid () PRIMARY KEY UNIQUE,
    name VARCHAR (127) NOT NULL
);

END;

Since we are relying on postgres to automatically create random uuids for us with gen_random_uuid() function, we needed to install the extension pgcrypto as it does not ship with postgres sometimes.

Then to run the migrations, at the root of your project

migrate -database postgres://input_user:input_password@/input_database_name -path postgres/migrations/postgres up

Connecting Postgres with Go

In the spirit of keeping everything clean and easily, create a folder at the root of the project, name it utils. Utils will hold files containing pieces of code we'll need through out the application. Inside utils create two files called error.go and config.go. Config will hold the code needed for the project configuration while error will contain a fancy piece of code I modified after picking it up from stackoverflow for error handling and easy debugging. utils/error.go

package utils

import (
    "errors"
    "log"
    "os"
    "runtime"
)

// HandleError is a custom error function that prints to the terminal an error message, the function throwing the error, the filename and code line number responsible for the error. It takes two parameter: the error message and a boolean condition. When set to true, it replicates log.Fatal()
func HandleError(err error, fatal bool) {
    if err != nil {
        pc, filename, line, _ := runtime.Caller(1)
        log.Printf("[error] in %s :: [%s :line %d] %v", runtime.FuncForPC(pc).Name(), filename, line, err)
    }

    if fatal {
        os.Exit(1)
    }
}

You probably could achieve similar effect by setting log flags in package main.

log.SetFlags(log.LstdFlags | log.Lshortfile)

To load enviroment variables, we'll use a package called godotenv, so run

go get github.com/joho/godotenv

utils/config.go

package utils

import (
    "os"
    "strconv"

    "github.com/joho/godotenv"
)


func LoadEnv() (string, string, int, string, string, string, string) {
    err := godotenv.Load(".env")
    if err != nil {
        HandleError(err, true)
    }

    dbport, _ := strconv.Atoi(os.Getenv("dbport"))
    port := os.Getenv("port")
    host :=  os.Getenv("host")
    user := os.Getenv("user")
    password := os.Getenv("password")
    dbname := os.Getenv("dbname")
    sslmode := os.Getenv("sslmode")

    return port, host, dbport, user, password, dbname, sslmode
}

In postgres folder create a file called database.go which will contain the code for connecting to the database persistently as well as automatic migration

postgres/database.go

package database

import (
    "database/sql"
    "fmt"

    "github.com/DavidHODs/TechHUB-goGraph/utils"
    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
    _ "github.com/lib/pq"
)

var Db *sql.DB

func ConnectAndMigrate() {
// returns the info stored in env file
    _, host, dbport, user, password, dbname, sslmode := utils.LoadEnv()

    databaseInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=%s", host, dbport, user, password, dbname, sslmode)

    db, err := sql.Open("postgres", databaseInfo)
    if err != nil {
        utils.HandleError(err, true)
    }

    // checks if the connection to the database is still active and healthy
    err = db.Ping()
    if err != nil {
        utils.HandleError(err, true)
    }

    fmt.Println("database connection successful")

    Db = db

    err = Db.Ping()
    if err != nil {
        utils.HandleError(err, true)
    }

    // sets up the parameters for migration
    driver, _ := postgres.WithInstance(Db, &postgres.Config{})
    m, _ := migrate.NewWithDatabaseInstance(
        "file://postgres/migrations/postgres",
        fmt.Sprintf("postgres://%s:%s@/%s", user, password, dbname),
        driver,

    )
    // runs the migrations if new schema files have been added
    err = m.Up()
    if err != nil && err != migrate.ErrNoChange {
        utils.HandleError(err, true)
    }

    fmt.Println("database migration successful")
}

Finally in server.go which is our package main

package main

import (
    "log"
    "net/http"

    "github.com/99designs/gqlgen/graphql/handler"
    "github.com/99designs/gqlgen/graphql/playground"
    "github.com/DavidHODs/TechHUB-goGraph/graph"
    "github.com/DavidHODs/TechHUB-goGraph/graph/generated"
    database "github.com/DavidHODs/TechHUB-goGraph/postgres"
    "github.com/DavidHODs/TechHUB-goGraph/utils"
)


func main() {
    port, host, _, _, _, _, _ := utils.LoadEnv()

    database.ConnectAndMigrate()

    srv := handler.NewDefaultServer(generated.NewExecutableSchema(generated.Config{Resolvers: &graph.Resolver{}}))

    http.Handle("/", playground.Handler("GraphQL playground", "/query"))
    http.Handle("/query", srv)

    log.Printf("connect to http://%s:%s/ for GraphQL playground", host, port)
    log.Fatal(http.ListenAndServe(":"+port, nil))
}

And everything should be running fine

Up next is building of the resolvers so we can finally see GraphQL in action.