Skip to content

Creating a PostgresQL model table with Repository

Jump to bottom
Akshay Katyal edited this page Jun 9, 2022 · 1 revision

Before moving forward, make sure you are familiar with the following tools:

Models Package

The models package inside the db folder consists of all the database models.

The base.go file

This file contains the base model for the models. It has id, created_at & updated_at fields which are injected into each model directly using struct composition. Any generic fields that need to be added to a model, should be added here

Individual Model file

Each model file consists of the following elements:

  • The model struct: This is where you define model attributes
    • TableName: This member method returns the name of the table that should be mapped in the Postgres DB
    • Serialize: This is the serializer method for the DB model to the graphql model. It is used in services to convert the model object to a graphql object.
  • init() method: This is the method that connects the model to beego ORM.
  • Query struct: This is the query object used in the repository queries (see repository below). All the queries like FindOne or FindAll should be done using this object

Example Model file

type Car struct {
  // compose the base model attributes
  Base
  Name      *string `json:"name" orm:"column(name)"`
  ChassisNo *string `json:"chassis_no"`
}

func (c *Car) TableName() string {
  return "cars"
}

func (c *Car) Serialize() *graphmodel.Car {
  res := &graphmodel.Car{
    ID: c.ID
    Name: c.Name
  }
  return res
}

func init() {
	orm.RegisterModel(new(Car))
}

type CarQuery struct {
  // used for easier querying of single items
  Car
  // used in FindAll for sorting. Good idea to have this
  SortBy       *string
  // used in FindAll for sorting. Good idea to have this
  SortOrder    *string
  // used in FindAll for pagination. Good idea to have this
  Limit        *int64
  // used in FindAll for pagination. Good idea to have this
  Page         *int64
  // maybe we need to find all cars IN IDs(array)? We can use this in that case.
  // You can add any number of fields like this depending on what you are querying
  IDs          []int64
  // .. other fields where you might have to query
}

Migrations Folder

The migrations folder consists of all the migrations that are generated to apply changes to the Database.

Just creation for models does nothing to create tables in the database we need to write migration for both up and down. Migrations are just SQL DDL or DML commands to create, alter or insert values in the database. We need to run migrations every time there is a change needed in the model so it reflects in the database. Up migrations are for new changes required and down for reversing that change.

Working with Migrations

  • Grab the Postgres connection URL from the .env file. Replace the service name (ex: postgres-auth) with localhost & export the env variable
export POSTGRESQL_URL=<postgres connection url>

  • Generate the migration file

    migrate create -ext sql -dir db/migrations <migration name (ex: create_users_table)>

  • This will create 2 files: <epoch timestamp>_<migration name>.up.sql &<epoch_timestamp>_<migration name>_.down.sql

  • Then add the SQL commands in the up file & add the commands that should be called when reverting the migrations in the down file.Ex: <>.up.sql

    CREATE TABLE IF NOT EXISTS "users" (
    "id" bigserial NOT NULL PRIMARY KEY,
    "first_name" text NOT NULL DEFAULT '' ,
    "last_name" text NOT NULL DEFAULT '' ,
    "email" text NOT NULL UNIQUE DEFAULT '' ,
    "password" text NOT NULL DEFAULT '' ,
    "is_inactive" bool NOT NULL DEFAUlT false ,
    "created_at" bigint NOT NULL DEFAULT extract(epoch from now()) ,
    "updated_at" bigint NOT NULL DEFAULT -62135596800 ,
    "deleted_at" bigint NOT NULL DEFAULT -62135596800
);

    <>.down.sql

    DROP TABLE "users";

  • Run the migrations using:

    migrate -database ${POSTGRESQL_URL} -path db/migrations up

  • If you want to revert/rollback your changes then, run:

    migrate -database ${POSTGRESQL_URL} -path db/migrations down
ℹ️ More docs [[here](https://github.com/golang-migrate/migrate/blob/master/database/postgres/TUTORIAL.md)](https://github.com/golang-migrate/migrate/blob/master/database/postgres/TUTORIAL.md)

Repository Package

The repository package is the package that acts as the abstraction layer of the querying of data for services. Any interaction which happens with the database should go inside this package. Every model has a separate repo file.

Methods in Each Repo

Each repo will have the following methods:

  • Save(ctx context.Context, doc *models.Car) error: Used to save a single instance of the model
  • BulkSave(ctx context.Context, docs []*models.Car) error: Used to save multiple instances of the model
  • FindOne(ctx context.Context, query models.CarQuery) (models.Car, error): Used to query a single object using the query object
  • FindAll(ctx context.Context, query models.CarQuery) ([]models.Car, int64, error): Used to query a list of objects using the query object. It supports sorting & pagination by default using the query object & returns the count as well as the second return parameter
  • Update(ctx context.Context, doc *models.Car, fieldsToUpdate []string) error: Used to update object using the doc supplied. If fieldsToUpdate is empty, then all fields are updated.
  • UpdateWithParams(ctx context.Context, query *models.CarQuery, ormParams orm.Params) error: This is a variation of update method that uses orm params. Generally used for updating with aggregations like set count = count + 1. [Example](https://beego.me/docs/mvc/model/query.md#update)
  • Delete(ctx context.Context, query models.CarQuery) (bool, error): Used to delete one or more instances using the query object

You can add more methods where you need to do complex queries like count, group by, etc.

type UserRepo interface {
	Save(ctx context.Context, doc *models.User) error
	FindByID(ctx context.Context, id int64) (*models.User, error)
}
type userRepo struct {
	db        orm.Ormer
}
// Save saves the user in the database
func (repo *userRepo) Save(ctx context.Context, doc *models.User) error {
	groupError := "CREATE_USER"

	doc.CreatedAt = time.Now().Unix()
	doc.UpdatedAt = utils.NilTime()

	logger.Log.Info("Inserting user in the db")
	id, err := repo.db.Insert(doc)
	if err != nil {
		logger.Log.WithError(err).Error(groupError)
		return err
	}
	logger.Log.Info("Done inserting user in the db")
	doc.ID = id
	return nil
}
// FindByID returns the user associated with the id parameter
func (repo *userRepo) FindByID(ctx context.Context, id int64) (*models.User, error) {
	groupError := "FIND_USER_BY_ID"
	// some logic
	return &user, nil
}
// NewUserRepo creates a new instance of the user repo
func NewUserRepo(db orm.Ormer, validator *govalidator.Validate) UserRepo {
	return &userRepo{
		db:        db,
	}
}

Adding a new model

Here is a checklist for adding a new model:

✅ Create a migration for the table/model

✅ Run the migration to see if the SQL works

✅ Run the down migration to see if the rollback SQL also works

✅ Add a file in the models package

✅ Add TableName and init methods in the file

✅ Add query model in the model file which will be used by the repository

✅ Add serializer if the graphmodel already exists

✅ Add a new file in the repository package for working with the model

Editing an existing model

Here is a checklist for editing an existing model:

✅ Create a migration for the change in the table using the https://github.com/golang-migrate/migratetool.

✅ Run the migration to see if the SQL works

✅ Run the down migration to see if the rollback SQL also works

✅ Edit the model file in models package

✅ Edit the query model in the model file if you have added a column that you need to query with

✅ Edit the serializer if you have added a new column that will be returned in the API

✅ Edit the FindOne & FindAll method to use the new query parameter if you added a new parameter in the query object

Clone this wiki locally