In this article, we’re going to kickstart creating our business process code, namely the registration module API.

Registration API

Registration API involves 2 endpoints:

• POST /accounts/registration/
• GET /accounts/registration/email_confirmation?confirmation_id=abcdefghijklmnoprstu

From this take, it’s quite clear, we need 2 tables, account and account_email_confirmation.

Registration Database

Let’s create 2 database migration files. One for creating account table, and another one for account_email_confirmation.

For account table, we need to store some important informations, name, hashed password and surely email. Here we see that email will have UNIQUE constraint. We also really need to make sure that we DO NOT store account’s plaintext password. As for hashing the password, we’re going to use argon2 (scrypt will be ok too).

CREATE TABLE account (
	id bigserial NOT NULL,
	email varchar(200) NOT NULL,
	password_hash varchar(100) NOT NULL,
	"name" varchar(500) NOT NULL,
	created_at timestamp NOT NULL,
	email_verified bool NOT NULL,
	CONSTRAINT account_pk PRIMARY KEY (id),
	CONSTRAINT account_un UNIQUE (email)
);
CREATE INDEX account_email_idx ON public.account USING btree (email);

And here’s account_email_confirmation table. This is used for email verification, we generate a random hash, store it to confirmation_hash. This confirmation_hash will be sent to account’s email inside an API link. When this link get called, we will toggle email_verified field on account table as TRUE while deactivating the is_active below. created_at field below will also be used when verifying account’s email. Confirmation hash older than 6 hours is expired, account will need to request email verification link again.

CREATE TABLE account_email_confirmation (
	id bigserial NOT NULL,
	confirmation_hash varchar(64) NOT NULL,
	account_id int8 NOT NULL,
	created_at timestamp NOT NULL,
	is_active bool NOT NULL,
	CONSTRAINT account_email_confirmation_pk PRIMARY KEY (id)
);
CREATE INDEX account_email_confirmation_confirmation_hash_idx ON public.account_email_confirmation USING btree (confirmation_hash);


-- public.account_email_confirmation foreign keys

ALTER TABLE public.account_email_confirmation ADD CONSTRAINT account_email_confirmation_fk FOREIGN KEY (account_id) REFERENCES account(id);

We also create a foreign key, account_email_confirmation’s account_id refers to account’s id.

Finally, here’s our database diagram.

We can then run database migration tool in order to create those 2 tables, make sure that database server is up, and minicommerce database exists.

$ migrate -verbose -path 'schema/' -database 'postgres://user00:chocolatecake@0.0.0.0:5433/minicommerce?sslmode=disable' up

Registration Repo Package

In order for Go code to communicate with those 2 tables, we’re going to create 3 Go packages.

• accountrepo
• accountemailconfirmationrepo
• registrationrepo

Why 3 packages? accountrepo communicates with account table, accountemailconfirmationrepo communicates with account_email_confirmation table. registrationrepo is a cross-domain package, registration of new account involves both of those 2 tables at the same time, we’ll need to make sure they’re wrapped inside a database transaction, plus whenever we need to query that cross between tables (for example via JOIN) the queries should be placed on that cross-domain package. This is also done in order to prevent cyclic imports in Go. registrationrepo depends (does import) on accountrepo and accountemailconfirmationrepo.

Account Repo

We’ll put accountrepo package inside repo dir. For now, it contains these 4 files:

• account_repo.go contains interface and implementation struct.
• inject.go contains injection code for building the interface from a struct.
• pg_query.go contains queries related to PostgreSQL.
• pg_tx.go contains transaction (tx) methods. Usually has signature like this: (ctx context.Context, tx sql.Tx)

Here’s the accountrepo/pg_query.go, our usual queries should be placed here. If our codebase queries amount grows larger, we can split it to something like: pg_create_query.go, pg_report_query.go, etc.

We must keep our interfaces as small as possible. If we need to add another field to it, rethink if we need another interface for it.

package accountrepo

var (
	create = `
		INSERT INTO account (email, password_hash, name, created_at, email_verified)
		    VALUES ($1, $2, $3, NOW(), FALSE) RETURNING id;`
)

That query returns newly created account ID.

Before moving to our repo method, we need to create a form struct for creating the account. Here’s entity/account.go

package entity

type (
	RepoCreateAccount struct {
		Email        string `db:"email"`
		Name         string `db:"name"`
		Password     string `db:"-"`
		PasswordHash string `db:"password_hash"`
	}
)

We also realize that we put UNIQUE constraint for email field in account table, which means there’s a situation where user will try to register with duplicated email, we’re going to create a custom error for that. Here’s entity/error.go

package entity

import "fmt"

var (
	ErrUserEmailAlreadyExist = fmt.Errorf("email already used")
)

Here’s our very first method related to business process, accountrepo/pg_tx.go, utilizing the entity.RepoCreateAccount as input and ErrUserEmailAlreadyExist as error when email is already used. We should create a custom error, and must not utilize anything related to sql for the error messages (or response), because if someday we move implementation from PostgreSQL to API call, having response and error related to sql is kinda awkward.

ErrUserEmailAlreadyExist custom error will bubble up to the http handler, there we can return different kind of http response.

package accountrepo

import (
	"context"
	"database/sql"
	"errors"
	"go-mini-commerce/entity"

	"github.com/jackc/pgconn"
	"github.com/jackc/pgerrcode"
)

func (a *accountPg) CreateTx(
	ctx context.Context, tx sql.Tx, account entity.RepoCreateAccount,
) (int64, error) {
	var newAccountID int64

	err := tx.QueryRowContext(
		ctx, create, account.Email, account.PasswordHash, account.Name,
	).Scan(&newAccountID)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			if pgErr.Code == pgerrcode.UniqueViolation {
				return 0, entity.ErrUserEmailAlreadyExist
			}
		}
		return 0, err
	}

	return newAccountID, nil
}

This method uses QueryRowContext because we want to get the newly created account ID via RETURNING id query above. We also note that we check for specific type of error, because we’re using pgx, captured error should be *pgconn.PgError type, we check if such error is a specific type of code (23505 which is what the constant pgerrcode.UniqueViolation is), such code is from PostgreSQL error code. There are many other fields inside pgconn.PgError that give us more error informations, such as: which table fields cause the error, which line of query cause the error, etc, it’s a whole new discussions, but for now, let’s focus on email uniqueness only. This is why we put such code in a file named pg_tx.go, because this code right here is very specific to PostgreSQL because we’re using create variable above which contains query very specific to PostgreSQL in a file named pg_query.go which hopefully contains queries very specific to PostgreSQL as well.

In order to use such constant (pgerrcode.UniqueViolation, which is more readable than 23505), we need to import it first. There’s a package for that:

$ go get -u github.com/jackc/pgerrcode

Other notable information, as we can see here CreateTx receive tx sql.Tx, this is for the caller to provide. There are many other patterns that wrap transaction around Go sql methods, but that involves lots of moving parts, like we need to create an interface containing whole sql package interface.

We’re going to simpler path like above, CreateTx as it name implies, it is related to database transaction (tx).

Now, let’s move on to accountrepo/account_repo.go

package accountrepo

import (
	"context"
	"database/sql"
	"go-mini-commerce/entity"
	"go-mini-commerce/infra"
)

type (
	AccountRepo interface {
		CreateTx(context.Context, sql.Tx, entity.RepoCreateAccount) (int64, error)
	}

	accountPg struct {
		db infra.DB
	}
)

And finally accountrepo/inject.go

package accountrepo

import (
	"go-mini-commerce/infra"
	"sync"
)

var (
	accountRepoOnce sync.Once
	accountRepo     AccountRepo
)

func InjectNewAccountRepo(infra infra.Infra) AccountRepo {
	accountRepoOnce.Do(func() {
		accountRepo = &accountPg{db: infra.PG()}
	})

	return accountRepo
}

Account Email Confirmation Repo

The very same thing applies to account_email_confirmation_repo.go.

repo/accountemailconfirmationrepo/pg_query.go:

package accountemailconfirmationrepo

var (
	create = `
		INSERT INTO account_email_confirmation (confirmation_hash, account_id, created_at, is_active)
		    VALUES ($1, $2, NOW(), TRUE);`
)

entity/account_email_confirmation.go:

package entity

type (
	RepoCreateAccountEmailConfirmation struct {
		ConfirmationHash string `db:"confirmation_hash"`
		UserID           int64  `db:"account_id"`
	}
)

repo/accountemailconfirmationrepo/pg_tx.go:

package accountemailconfirmationrepo

import (
	"context"
	"database/sql"
	"go-mini-commerce/entity"
)

func (a *accountEmailConfirmationPg) CreateTx(
	ctx context.Context, tx sql.Tx, aec entity.RepoCreateAccountEmailConfirmation,
) error {
	_, err := tx.ExecContext(ctx, create, aec.ConfirmationHash, aec.UserID)
	if err != nil {
		return err
	}

	return nil
}

repo/accountemailconfirmationrepo/account_email_confirmation_repo.go:

package accountemailconfirmationrepo

import (
	"context"
	"database/sql"
	"go-mini-commerce/entity"
	"go-mini-commerce/infra"
)

type (
	AccountEmailConfirmationRepo interface {
		CreateTx(
			context.Context, sql.Tx, entity.RepoCreateAccountEmailConfirmation,
		) error
	}

	accountEmailConfirmationPg struct {
		db infra.DB
	}
)

repo/accountemailconfirmationrepo/inject.go:

package accountemailconfirmationrepo

import (
	"go-mini-commerce/infra"
	"sync"
)

var (
	accountEmailConfirmationRepoOnce sync.Once
	accountEmailConfirmationRepo     AccountEmailConfirmationRepo
)

func InjectNewAccountEmailConfirmationRepo(infra infra.Infra) AccountEmailConfirmationRepo {
	accountEmailConfirmationRepoOnce.Do(func() {
		accountEmailConfirmationRepo = &accountEmailConfirmationPg{
			db: infra.PG(),
		}
	})

	return accountEmailConfirmationRepo
}

repocommon Package

This repocommon package will be used to serve common codes related to repo activities. For now it contains only repo/repocommon/db.go:

package repocommon

import (
	"context"
	"database/sql"
)

func WithTransaction(ctx context.Context, db *sql.DB, fn func(sql.Tx) error) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	err = fn(*tx)
	if err != nil {
		if rollErr := tx.Rollback(); rollErr != nil {
			return rollErr
		}

		return err
	}

	return tx.Commit()
}

WithStmtTransaction above will be used to wrap a function with specified db (mostly write DB). This is a common minimal wrapping pattern if we want to use database transaction in Go.

Registration Domain Repo

This is where our accountrepo and accountemailconfirmationrepo are gathered and wrapped by database transaction. As we can see here RegistrationDomainRepo exposes RegisterUser method.

registrationDomain struct here contains

• db the instance of infra.DB, to be used for beginning db transactions or to query from multiple tables (cross repo packages).
• commonSvc our common code from previous part. In this code below, it will be used for generating password hash and email confirmation hash.
• both accountrepo.AccountRepo and accountemailconfirmationrepo.AccountEmailConfirmationRepo.

repo/registrationdomainrepo/registration_domain_repo.go:

package registrationdomainrepo

import (
	"context"
	"database/sql"
	"go-mini-commerce/entity"
	"go-mini-commerce/external/extcommon"
	"go-mini-commerce/infra"
	"go-mini-commerce/repo/accountemailconfirmationrepo"
	"go-mini-commerce/repo/accountrepo"
	"go-mini-commerce/repo/repocommon"
)

type (
	RegistrationDomainRepo interface {
		RegisterUser(ctx context.Context, form entity.RepoCreateAccount) (string, error)
	}

	registrationDomain struct {
		db                           infra.DB
		commonSvc                    extcommon.CommonService
		accountRepo                  accountrepo.AccountRepo
		accountEmailConfirmationRepo accountemailconfirmationrepo.AccountEmailConfirmationRepo
	}
)

func (r *registrationDomain) RegisterUser(
	ctx context.Context, form entity.RepoCreateAccount,
) (string, error) {
	var confirmationHash string

	err := repocommon.WithTransaction(ctx, r.db.Write.DB, func(tx sql.Tx) error {
		passwordHash, err := r.commonSvc.GenerateHash(form.Password)
		if err != nil {
			return err
		}
		form.PasswordHash = passwordHash

		newAccountID, err := r.accountRepo.CreateTx(ctx, tx, form)
		if err != nil {
			return err
		}

		confirmationHash = r.commonSvc.RandomString(48)
		err = r.accountEmailConfirmationRepo.CreateTx(
			ctx,
			tx,
			entity.RepoCreateAccountEmailConfirmation{
				UserID:           newAccountID,
				ConfirmationHash: confirmationHash,
			})
		if err != nil {
			return err
		}

		return nil
	})
	if err != nil {
		return "", err
	}

	return confirmationHash, nil
}

Here we see Registeruser method that uses repocommon.WithStmtTransaction from repocommon package made before. Hopefully this method can give clarity at what it does:

• Call common service’s GenerateHash to generate password hash from given password.
• Call accountrepo’s CreateTx to create account (along with password hash), this returns newAccountID, newly created account ID.
• If successful, generate confirmationHash random string.
• Then finally call accountemailconfirmationrepo’s CreateTx to create account email confirmation with newly account ID.
• By wrapping them inside a transaction, we can surely guarantee atomicity of both operations. Either full committed, or rolled-back when one of them failed (throws error).
• We avoid circular dependencies between accountrepo and accountemailconfirmationrepo.

Here’s our repo/registrationdomainrepo/inject.go:

package registrationdomainrepo

import (
	"go-mini-commerce/infra"
	"go-mini-commerce/repo/accountemailconfirmationrepo"
	"go-mini-commerce/repo/accountrepo"
	"sync"
)

var (
	registrationDomainRepoOnce sync.Once
	registrationDomainRepo     RegistrationDomainRepo
)

func InjectNewRegistrationDomainRepo(infra infra.Infra) RegistrationDomainRepo {
	registrationDomainRepoOnce.Do(func() {
		var (
			accountRepo                  = accountrepo.InjectNewAccountRepo(infra)
			accountEmailConfirmationRepo = accountemailconfirmationrepo.InjectNewAccountEmailConfirmationRepo(infra)
		)

		registrationDomainRepo = &registrationDomain{
			db:                           infra.PG(),
			commonSvc:                    infra.External().CommonSvc(),
			accountRepo:                  accountRepo,
			accountEmailConfirmationRepo: accountEmailConfirmationRepo,
		}
	})

	return registrationDomainRepo
}

• We inject infra so we can get db from it. This connections can be used to initiate transactions or do cross-domain queries.
• infra.External().CommonSvc() is from previous article.
• We gather both accountRepo and accountEmailConfirmationRepo.

We’ve completed our first domain repo, next we’ll create service code to call this domain method.