This page discusses the creation of store (data access) packages. Complete examples can be seen in the Northwind example app.

As a database-first developer, the starting point is having a set of tables which model your business requirements. Let's say we have this:

CREATE TABLE core.category
(	
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE core.product
(	
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_fk bigint NOT NULL REFERENCES core.category (id),
  name text NOT NULL UNIQUE
);

Create views specific to each table if the table is part of a hierarchy.

In the example above, we might create a view for core.product so that we always get the category name when selecting the product:

CREATE OR REPLACE VIEW core.v_product AS
  SELECT 
    c_p.id,
    c_p.category_fk,
    c_c.name AS category,
    c_p.name
  FROM core.product c_p
  JOIN core.category c_c ON c_p.category_fk = c_c.id;

In internal/stores, create a subfolder per database schema, e.g. "core".

In the core folder, create a subfolder for each table, e.g. "corecategory" and "coreproduct".

In the coreproduct folder, create a file "store.go". In daily development, we would copy this file from the store of a table with a similar structure and make changes as needed.

We set the const values at the top to use our table and related view:

const (
  schemaName     string = "core"
  tableName      string = "product"
  viewName       string = "v_product"
  pkColName      string = "id"
  defaultOrderBy string = "name"
)

We also set the default result ordering, which is used when a GET request is made which does not contain specific ordering params.

If there is no view for this table, set viewName to the same value as tableName.

We list the fields needed when creating or modifying a product:

type Input struct {
  CategoryFk  int64   `db:"category_fk" json:"category_fk" validate:"required"`
  Name        string  `db:"name" json:"name,omitempty" validate:"required"`
}

Generated columns are excluded.

We set both the "db" and the "json" tags to match the column name. For now, these must be identical, and the init function will check this.

We use the validator package to indicate that both fields listed are required. This will ensure that incoming JSON will be validated on POST and PUT requests.

We list the fields returned when the core.v_product view is selected, excluding the Input fields, which are embedded:

type Model struct {
  Id        int64   `db:"id" json:"id"`
  Category  string  `db:"category" json:"category,omitempty"`
  Input
}

This will generally contain generated columns like "id", and JOINed columns like "category".

Unless there are special requirements, the remainder of the store package is boilerplate. It mainly consists of wrappers for generic lyspg functions, e.g.:

func (s Store) Insert(ctx context.Context, input Input) (newItem Model, stmt string, err error) {
  return lyspg.Insert[Input, Model](ctx, s.Db, schemaName, tableName, viewName, pkColName, gDbTags, input)
}

func (s Store) Select(ctx context.Context, params lyspg.SelectParams) (items []Model, unpagedCount lyspg.TotalCount, stmt string, err error) {
  return lyspg.Select[Model](ctx, s.Db, schemaName, tableName, viewName, defaultOrderBy, gDbTags, params)
}

The full version of the core.product store in Northwind is found here.

You can add more store functions as required. For example, if we need to select a product by name and not id, we can add this:

func (s Store) SelectByName(ctx context.Context, fields []string, name string) (item Model, stmt string, err error) {
  return lyspg.SelectUnique[Model](ctx, s.Db, schemaName, viewName, "name", fields, gDbTags, name)
}