Creating database migrations and adding a wrapper

To write a new migration you need to create new schema_migration_X.sql (where X is the number of the migration) file inside of /storage/sql/ and also bump const schemaVersion in /storage/migration.go to automatically apply them one after the other when you start vpub.

Example

Let’s say we want to add an ability to search by user, board and post contents.

Current structure of /storage/sql/ looks like this:

.
├── schema_version_1.sql
├── schema_version_2.sql
├── schema_version_3.sql
├── schema_version_4.sql
├── schema_version_5.sql
├── schema_version_6.sql
├── schema_version_7.sql
├── schema_version_8.sql
└── schema_version_9.sql

So let’s create schema_version_10.sql. Remember to also change schemaVersion in /storage/migration.go - const schemaVersion = 10.

In our migration we would add a new column to boards, posts and users tables called textsearchable_index_col which stores a tsvector of whatever we want to search by.

To learn more about full text search in PostgreSQL follow this link: https://www.postgresql.org/docs/17/textsearch.html

-- schema_version_10.sql
ALTER TABLE boards
    ADD COLUMN textsearchable_index_col tsvector
        GENERATED ALWAYS AS ( to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))) STORED;

CREATE INDEX textsearch_idx_boards ON boards USING GIN (textsearchable_index_col);

ALTER TABLE posts
    ADD COLUMN textsearchable_index_col tsvector
        GENERATED ALWAYS AS ( to_tsvector('english', coalesce(subject, '') || ' ' || coalesce(content, ''))) STORED;

CREATE INDEX textsearch_idx_posts ON posts USING GIN (textsearchable_index_col);

ALTER TABLE users
    ADD COLUMN textsearchable_index_col tsvector
        GENERATED ALWAYS AS ( to_tsvector('english', coalesce(name, '') || ' ' || coalesce(about, ''))) STORED;

CREATE INDEX textsearch_idx_users ON users USING GIN (textsearchable_index_col);

CREATE OR REPLACE VIEW search_items AS
    SELECT
        text 'users' AS origin_table,
        id,
        name AS title,
        about AS content,
        textsearchable_index_col AS searchable_element
    FROM
        users
UNION ALL
    SELECT
        text 'posts' AS origin_table,
        id,
        subject AS title,
        content,
        textsearchable_index_col AS searchable_element
    FROM
        posts
UNION ALL
    SELECT
        text 'boards' AS origin_table,
        id,
        name AS title,
        description AS content,
        textsearchable_index_col AS searchable_element
    FROM
        boards;

Save it, run vpub - it would automatically apply this migration. If something goes wrong - it will rollback.

Adding wrappers

Now we need to add a simple wrapper.

Let’s add a model so we can parse our query into a golang struct. Create /model/search.go:

// /model/search.go

package model

type Search struct {
	OriginTable        string
	ID                 string
	Title              string
	HighlightedTitle   string
	Content            string
	HighlightedContent string
	Rank               float64
}

And now let’s write a function that makes a query to postgres, then parses its response into our struct. Create search.go in /storage/:

// /storage/search.go
package storage

import (
	"database/sql"
	"log"
	"vpub/model"
)

func (s *Storage) Search(query string) ([]model.Search, error) {
	var searchResults []model.Search

	rows, err := s.db.Query(`
        SELECT
            origin_table, id, title, highlighted_title, highlighted_content, rank
        FROM
            search_with_highlights($1);
    `, query)

	if err != nil {
		return []model.Search{}, err
	}

	defer func(rows *sql.Rows) {
		err := rows.Close()
		if err != nil {
			log.Println(err)
		}
	}(rows)

	for rows.Next() {
		var search model.Search
		if err := rows.Scan(&search.OriginTable,
			&search.ID,
			&search.Title,
			&search.HighlightedTitle,
			&search.HighlightedContent,
			&search.Rank); err != nil {
			return []model.Search{}, err
		}
		searchResults = append(searchResults, search)
	}

	return searchResults, err
}

p.s. remember to run go generate!