setting up the environment

pocketbase can be used with no need for an enviroment setup by just downloading the executalble for the respective OS and using it's defaluts

but in our case we want to use as a framework

we'll need to setup a Go enviroment

credit to @Ben Davis who's one of the creators putting out some quality golang content on youtube , also check out his video on pocketbase

in my case i have a simple script that i use to install go on linux

#!/usr/bin/env bash
VER=1.19.3

sudo wget -c https://golang.org/dl/go$VER.linux-amd64.tar.gz
sudo rm -rf /usr/local/go
sudo tar -C /usr/local  -xvzf go$VER.linux-amd64.tar.gz
sudo rm -rf go$VER.linux-amd64.tar.gz

and then add the env variables to our .bashrc

~/.bashrc
LS_COLORS='ex=01;91:rs=0:di=1;33:ln=01;35:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=01;35;40:st=37;44:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;35:*.au=00;35:*.flac=00;35:*.mid=00;35:*.midi=00;35:*.mka=00;35:*.mp3=00;35:*.mpc=00;35:*.ogg=00;35:*.ra=00;35:*.wav=00;35:*.axa=00;35:*.oga=00;35:*.spx=00;35:*.xspf=00;35:';

# export LS_COLORS  ex
# PS1='\[\e]0;\u@\h: \w\a\]${debian_chroot:+($debian_chroot)}\[\033[01;32m\]\u@\h\[\033[0;39m\]:\[\033[01;35m\]\W\[\033[0;39m\]\$ '
 #   LS_COLORS=$LS_COLORS:'di=1;33:fi=0;97:ex=0;31:pi=1;35:no=1;35:do=1;35:' ;
  export LS_COLORS


# export GOROOT=/usr/local/go
# export GOPATH=$HOME/go
# export PATH=$GOPATH/bin:$GOROOT/bin:$PATH

export GOROOT=/usr/local/go
export PATH=$PATH:$GOROOT/bin

export GOPATH=/home/dennis/golibs
export PATH=$PATH:$GOPATH/bin

and run go --version to test it

then we'll clone pocketbase

git clone https://github.com/tigawanna/my-pocketbase-fork.git

• get the main.go file inside examples/base

• add it to another directory and run

go mod init "your new package name"
go mod tidy

custom posts route

define custom posts route logic in posts.go

package main

import (
    "fmt"
    "net/http"

    "github.com/labstack/echo/v5"
    "github.com/pocketbase/dbx"
    "github.com/pocketbase/pocketbase"
    "github.com/pocketbase/pocketbase/apis"
)

// CustomPostRoute  defines the HTTP route for getting custom posts
func CustomPostsRoute(app *pocketbase.PocketBase) echo.Route {
    return echo.Route{
        Method: http.MethodGet,
        Path:   "/custom_posts",
        Handler: func(c echo.Context) error {
            result := []*struct {
                CreatorId    string `db:"creator_id" json:"creator_id"`
                CreatorName  string `db:"creator_name" json:"creator_name"`
                CreatorImage string `db:"creator_image" json:"creator_image"`

                PostId       string `db:"post_id" json:"post_id"`
                PostBody     string `db:"post_body" json:"post_body"`
                PostMedia    string `db:"post_media" json:"post_media"`
                PostParent    string `db:"post_parent" json:"post_parent"`
                PostDepth    string `db:"post_depth" json:"post_depth"`

                CreatedAT    string `db:"created_at" json:"created_at"`
                Likes        int    `db:"likes" json:"likes"`
                MyLike       string `db:"mylike" json:"mylike"`

                ReactionId   string `db:"reaction_id" json:"reaction_id"`
                Replies       int `db:"replies" json:"replies"`
                MyReply   string `db:"myreply" json:"myreply"`
            }{}
            queryErr := app.Dao().DB().NewQuery(` 
SELECT 

pp.user creator_id,
dv.username creator_name,
dv.avatar creator_image,

pp.id post_id,
pp.body post_body,
pp.media post_media,
pp.created created_at,
pp.depth post_depth,
IFNULL(pp.parent,"op") post_parent,

(SELECT COUNT(*) FROM reactions WHERE liked = 'yes' AND post = pp.id) likes,
IFNULL((SELECT  liked FROM reactions WHERE user = {:user} AND post = pp.id),'virgin')mylike,
IFNULL((SELECT id FROM reactions WHERE user = {:user} AND post = pp.id),"virgin") reaction_id,

(SELECT COUNT(*) FROM posts WHERE parent = pp.id AND depth = pp.depth + 1) replies,
IFNULL((SELECT  id FROM posts WHERE pp.user = {:user} AND parent = pp.id AND depth = pp.depth + 1 ),'virgin')myreply

FROM posts pp
LEFT JOIN devs dv on dv.id = pp.user
WHERE (
    (pp.created < {:created} OR (pp.created = {:created} AND pp.id < {:id})) 
    AND pp.depth={:depth}
    AND (CASE WHEN {:profile} = 'general' THEN 1 ELSE pp.user = {:profile} END)
  )
ORDER BY pp.created DESC, pp.id DESC
LIMIT 10

`).Bind(dbx.Params{
"user": c.QueryParam("user"), 
"id": c.QueryParam("id"), 
"created": c.QueryParam("created"),
"depth": c.QueryParam("depth"),
"profile": c.QueryParam("profile"),
}).All(&result)

            if queryErr != nil {
                fmt.Print("\n")
                return apis.NewBadRequestError("Failed to fetch custom posts ", queryErr)
            }
            return c.JSON(200, result)
        },
        Middlewares: []echo.MiddlewareFunc{apis.ActivityLogger(app)},
        Name:        "",
    }
}

then add the route in main.go

main.go
    // ---------------------------------------------------------------
    // Plugins and hooks:
    // ---------------------------------------------------------------
    // Define the custom post route
    customPostRoute := CustomPostRoute(app)
    app.OnBeforeServe().Add(func(e *core.ServeEvent) error {
        e.Router.AddRoute(customPostRoute)

        return nil
    })

/custom_posts

The initial request requires user: the logged in user id ,created: depth : post depth 0 for the original post the latest date the rest can be sent as empty strings

const currentdate = dayjs(new Date()).format("[YYYYescape] YYYY-MM-DDTHH:mm:ssZ[Z]")

returns

export interface CustomPostType {
    creator_id: string;
    creator_name: string;
    creator_image: string;
    post_id: string;
    post_body: string;
    post_media: string;
    created_at: string;
    likes: number;
    mylike: "yes" | "no" | "virgin";
    myreply: string | "virgin";
    replies: number;
    reaction_id: string;
}

custom replies endpoint

define custom replies route logic in replies.go

package main



import (
    "fmt"
    "net/http"

    "github.com/labstack/echo/v5"
    "github.com/pocketbase/dbx"
    "github.com/pocketbase/pocketbase"
    "github.com/pocketbase/pocketbase/apis"
)

// CustomPostRoute  defines the HTTP route for getting custom posts
func CustomRepliesRoute(app *pocketbase.PocketBase) echo.Route {
    return echo.Route{
        Method: http.MethodGet,
        Path:   "/custom_replies",
        Handler: func(c echo.Context) error {

            result := []*struct {
                CreatorId    string `db:"creator_id" json:"creator_id"`
                CreatorName  string `db:"creator_name" json:"creator_name"`
                CreatorImage string `db:"creator_image" json:"creator_image"`

                PostId       string `db:"post_id" json:"post_id"`
                PostBody     string `db:"post_body" json:"post_body"`
                PostMedia    string `db:"post_media" json:"post_media"`
                PostParent    string `db:"post_parent" json:"post_parent"`
                PostDepth    string `db:"post_depth" json:"post_depth"`

                CreatedAT    string `db:"created_at" json:"created_at"`
                Likes        int    `db:"likes" json:"likes"`
                MyLike       string `db:"mylike" json:"mylike"`

                ReactionId   string `db:"reaction_id" json:"reaction_id"`
                Replies       int `db:"replies" json:"replies"`
                MyReply   string `db:"myreply" json:"myreply"`
            }{}
            queryErr := app.Dao().DB().NewQuery(` 
SELECT 

pp.user creator_id,
dv.username creator_name,
dv.avatar creator_image,

pp.id post_id,
pp.body post_body,
pp.media post_media,
pp.created created_at,
pp.depth post_depth,
IFNULL(pp.parent,"op") post_parent,

(SELECT COUNT(*) FROM reactions WHERE liked = 'yes' AND post = pp.id) likes,
IFNULL((SELECT  liked FROM reactions WHERE user = {:user} AND post = pp.id),'virgin')mylike,
IFNULL((SELECT id FROM reactions WHERE user = {:user} AND post = pp.id),"virgin") reaction_id,

(SELECT COUNT(*) FROM posts WHERE parent = pp.id AND depth = ({:depth} + 1)  ) replies,
IFNULL((SELECT  id FROM posts WHERE pp.user = {:user} AND parent = pp.id AND depth = ({:depth}  + 1) )
,'virgin')myreply

FROM posts pp
LEFT JOIN devs dv on dv.id = pp.user
WHERE (
    (pp.created < {:created} OR (pp.created = {:created} AND pp.id < {:id})) 
    AND pp.depth={:depth} 
    AND (CASE WHEN {:parent} = 'original' THEN 1 ELSE pp.parent={:parent} END) 

  )
ORDER BY pp.created DESC, pp.id DESC
LIMIT 10

`).Bind(dbx.Params{
"user": c.QueryParam("user"), 
"id": c.QueryParam("id"), 
"created": c.QueryParam("created"),
"depth": c.QueryParam("depth"),
"parent": c.QueryParam("parent"),
"profile": c.QueryParam("profile"),
}).All(&result)

            if queryErr != nil {
                fmt.Print("\n")
                return apis.NewBadRequestError("Failed to fetch custom replies ", queryErr)
            }
            return c.JSON(200, result)
        },
        Middlewares: []echo.MiddlewareFunc{apis.ActivityLogger(app)},
        Name:        "",
    }
}

and add the route in main.go

    // Define the custom post route
    customPostsRoute := CustomPostsRoute(app)
    customRepliesRoute := CustomRepliesRoute(app)
    app.OnBeforeServe().Add(func(e *core.ServeEvent) error {
        e.Router.AddRoute(customPostsRoute)
        e.Router.AddRoute(customRepliesRoute)

        return nil
    })

/custom_replies

Response

export interface CusromRepliesType {
    creator_id:    string;
    creator_name:  string;
    creator_image: string;
    reply_id:      string;
    reply_body:    string;
    reply_media:   string;
    replied_at:    Date;
    reply_depth:   string;
    replying_to:   string;
    likes:         number;
    mylike:        string;
    reaction_id:   string;
    replies:       number;
    myreply:       string;
}

⚠️⚠️

this method of having separate tables for posts and replies is getting hard to implement especially with mutations and aggregated fields , will switch to making replies in the posts table but with different depth levels

The initial request requires user: the logged in user id and created: the latest date the rest can be sent as empty strings

const currentdate = dayjs(new Date()).format("[YYYYescape] YYYY-MM-DDTHH:mm:ssZ[Z]")

deploying to fly.io

Add a Dockerfile

# syntax=docker/dockerfile:1
# stage 1 build
FROM golang:1.19-alpine AS build


RUN apk add -v build-base
RUN apk add -v ca-certificates
RUN apk add --no-cache \
 openssh

WORKDIR /pb

COPY go.mod ./
COPY go.sum ./
RUN go mod download

COPY *.go ./
# migrations to recreate your dev schemma when deploy
COPY pb_migrations/ /pb/pb_migrations
RUN go build -o pocketbase

# stage 2 build to cut down final image size
FROM alpine

WORKDIR /
COPY --from=build /pb /pb
EXPOSE 8080
CMD [ "/pb/pocketbase","serve", "--http=0.0.0.0:8080" ]

run

flyctl auth signup
flyctl auth login
fly launch

then add this to the fly.toml

[mounts]
  destination = "/pb/pb_data"
  source = "pb_data"

then you can run

fly deploy

if it takes too long you can configure docker locally and use

fly deploy --local-only

and now we have a deployed backeend next we do the react frontend