DEV Community: Benny Yang

Integration test with Go and PostgreSQL

Benny Yang — Sat, 13 Apr 2024 15:21:19 +0000

Integration tests plays a crucial role in ensuring the reliability and functionality of our application by verifying the interactions between different components, such as communication between the application logic and the database.

In this article, I'll focus on setting up integration tests for a specific repository that interacts with a PostgreSQL database using Gorm.

Prerequisites:

Here's an example structure of MediaRepository method we'll be testing

type MediaRepository struct {
    db *gorm.DB
}

func NewMediaRepository(db *gorm.DB) *MediaRepository {
    return &MediaRepository{db: db}
}

func (repo *MediaRepository) CreateMedia(input *model.CreateMediaInput) (*model.Media, error) {
    media := model.Media{
        MediaType:    input.MediaType,
        UploadStatus: input.UploadStatus,
        MediaStatus:  "active",
    }
    result := repo.db.Create(&media)
    if result.Error != nil {
        return nil, result.Error
    }
    return &media, nil
}

func (repo *MediaRepository) UpdateMedia(id string, input *model.UpdateMediaInput) error {
    result := repo.db.Model(&model.Media{}).Where("id = ?", id).Updates(map[string]interface{}{
        "upload_status": input.UploadStatus,
    })
    return result.Error
}

In this code snippet we have:

MediaRepository: This class encapsulates all database interaction related to the 'Media' table, abstracting CRUD operations through methods that accepts input models and return either the result of the operation or an error.
Gorm Integration: Each method uses Gorm to execute database commands, such as inserting new records or updating existing ones.

In the following part, we'll be using the sqlmock a mock SQL driver from DataDog to simulate the behavior of database.

1. Initiating shared MockDB instance with TestMain function

The TestMain function in Go is a powerful tool used to set up and tear down global resources needed before and after running tests in a package, such as when initializing a mock database instance that can be used across multiple tests.
Here's a detailed breakdown of how it's used for setting up sqlmock:

package repositories_test

import (
    "os"
    "testing"
    "github.com/DATA-DOG/go-sqlmock"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

var db *gorm.DB
var mock sqlmock.Sqlmock

func TestMain(m *testing.M) {
    var err error
    db, mock, err = sqlmock.New() // Create a new instance of sqlmock
    if err != nil {
        panic("failed to create sqlmock")
    }

    gormDB, err := gorm.Open(postgres.New(postgres.Config{
        Conn: db,
    }), &gorm.Config{})
    if err != nil {
        panic("failed to open db")
    }

    db = gormDB
    code := m.Run() // Execute all the tests
    db.Close()      // Clean up after all tests are done
    os.Exit(code)
}

db, mock, err = sqlmock.New(): This line initializes a new instance of sqlmock, which effectively creates a simulated connection that can be used to mock SQL operations.
gormDB, err := grom.Open(...): At here, Gorm is configured to use the mock database connection created earlier, we could also specify some optional database configurations here.
db = gormDB: This assignment makes the gormDB instance globally available to the tests in this package by assigning it to the package-level db variable.
code := m.Run(): This will execute all the test functions in the package. It returns an exit code that indicates whether the tests passed(0) or failed(non-zero).
os.Exit(code): This ensures that the exit code is correctly propagated to the calling process, which is essential for CI/CD pipelines to detect test failures.

Basically we using TestMain to manage the lifecycle of a shared mock database, tests within the package can run in a controlled environment where database interactions are fully simulated.

2. Testing code with Repository

After setting up a shared mock database instance, the next step is to implement tests that interact with this mock database through the repository layer.

func TestCreateMedia(t *testing.T) {
    t.Run("CreateMediaSuccess", func(t *testing.T) {
        // Setup input data
        input := &input.CreateMedia{
            MediaType:    "image",
            UploadStatus: "pending",
        }

        // Expected ID returned from the mock query
        mockUUID := uuid.New()

        // Begin transaction
        mock.ExpectBegin()

        // Mock the SQL query execution
        mock.ExpectQuery(`INSERT INTO "media" \("deleted_at","media_type","upload_status","media_status"\) VALUES \(\$1,\$2,\$3,\$4\) RETURNING "id"`).
            WithArgs(sqlmock.AnyArg(), input.MediaType, input.UploadStatus, "active").
            WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(mockUUID.String()))

        // Expect transaction commit
        mock.ExpectCommit()

        // Create repository instance
        repo := repositories.NewMediaRepository(db)

        // Execute the test
        result, err := repo.CreateMedia(input)

        // Assertions
        assert.NoError(t, err)
        assert.Equal(t, mockUUID.String(), result.ID.String())
    })
}

Test Setup (TestCreateMedia): This function defines the test scenario for creating an entry. (Remember the function name must prefix with capital Test). It is structured using t.Run to allow for multiple sub-tests.
Mock Transaction Setup (ExpectBegin): Initiates the expectation of a transaction. Transactions are used in database operations that need to be atomic. This reflects the practice of managing database changes within a transaction to maintain data integrity.
Mock Query Execution (ExpectQuery): This line sets up the mock response for the SQL query executed by the CreateMedia method. It specifies the SQL statement, expected inputs, and the mocked return value (the UUID of the newly created media record). The RETURNING "id" clause in PostgreSQL queries is typical for immediately retrieving values of newly inserted rows, such as auto-generated IDs.
(Overrides default generation such as ID)
Commit Transaction (ExpectCommit): Ensures that a commit operation is expected as part of the transaction workflow. This mirrors the typical behavior in an application where changes are committed if all operations within the transaction succeed.
Repository Instance Creation: Instantiates the MediaRepository with the mocked database connection (db), allowing the repository to perform operations using Gorm as if it was interacting with a real database.
Execute the Test and Assertions: Calls the CreateMedia method with the predefined input and checks the outcomes. The assert.NoError verifies that the method did not return an error, indicating that the operation was successful. The assert.Equal ensures that the returned media ID matches the expected UUID, confirming that the method processes and returns the correct data.

Key Takeaways:

1. Use `.Debug()` to get the executed SQL statement

The .Debug() method in Gorm is straightforward to use and is particularly beneficial when paired with sqlmock during testing, as it allows you to verify that the queries generated by Gorm match the expectations set in your mock database configuration.

// media_repo
func (repo *MediaRepository) CreateMedia(input *model.CreateMediaInput) (*model.Media, error) {
    media := model.Media{
        MediaType:    input.MediaType,
        UploadStatus: input.UploadStatus,
        MediaStatus:  "active",
    }
    // Debugging the SQL query
    result := repo.db.Debug().Create(&media)
    if result.Error != nil {
        return nil, result.Error
    }
    return &media, nil
}

The SQL statement will be shown in terminal view:

2. Why `ExpectQuery` instead of `ExpectExec`?

In the context of SQL operations, particularly when interacting with databases like PostgreSQL, the distinction between using ExpectQuery and ExpectExec in sqlmock is crucial and is based on the nature of the SQL command being executed.

ExpectExec: This method is used in sqlmock to mock expectations for SQL commands that perform operations but do not return any rows. Common use cases include INSERT, UPDATE, and DELETE commands.
ExpectQuery: Conversely, ExpectQuery is used when the SQL command is expected to return one or more rows. This is commonly used with SELECT statements.

However!!!

In our testing scenario, ExpectQuery is used because the INSERT statement in PostgreSQL includes a RETURNING clause. The clause makes the INSERT operation return the generated id of the inserted row immediately, thus the operation needs to be treated as a query that fetches data.

3. SQL syntax of different databases

The setup for sqlmock generally remains consistent regardless of the database, but the SQL syntax and the expectations need to be adjusted according to the database-specific features and SQL dialect.

In our scenario, employing MySQL or PostgreSQL will necessitate distinct variations in the test code.

//  PostgreSQL
mock.ExpectQuery(regexp.QuoteMeta(`INSERT INTO "media" ("media_type", "upload_status") VALUES ($1, $2) RETURNING "id"`)).
    WithArgs("image", "pending").
    WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))

//  MySQL
mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO media (media_type, upload_status) VALUES (?, ?)`)).
    WithArgs("image", "pending").
    WillReturnResult(sqlmock.NewResult(1, 1)) // Assuming ID 1, 1 row affected
mock.ExpectQuery("SELECT LAST_INSERT_ID()").
    WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))

Thanks for Reading 🙌 🙌 🙌

Streamlining System Monitoring and Alerting with Prometheus

Benny Yang — Thu, 14 Mar 2024 02:46:50 +0000

Prometheus stands as a pivotal player in the monitoring and alerting landscape.
Its core strength lies in its ability to efficiently collect and query time-series data in real-time, making it indispensable for monitoring system health, performance metrics and alerting on anomalies.

Prometheus Alert Rules

Prometheus allows us to define alert rules in a dedicated .yml file based on PromQL expressions and dynamically import them in rule_files section in the configuration file, it supports wildcard directories to implement multiple files simultaneously.

// Prometheus.yml
# my global config
global:
  scrape_interval: 60s # Set the scrape interval to every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).


# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  - '/etc/prometheus/rules/*.yml'

# A scrape configuration containing exactly one endpoint to scrape:
scrape_configs:

With beyond configuration, the rules will be evaluated every 15 seconds, let's move on to the rule files :

groups:
  - name: b2ac9e71-589e-494c-af38-81760d4eeab9_..._rules
    rules:
      - alert: temp_high_warm_laser/101
        expr: 
          device_temperature_celsius{device="laser/101"} >= 30 and
          dfost_pu_luminis_device_temperature_celsius{device="laser/101"} < 80
        for: 1m
        labels:
          severity: temp_high_warm
          deviceId: test-device
          type: temperature
          operatingRange: 25-30
          value: "{{ $value }}"
        annotations:
          summary: Device temperature is between 30°C and 80°C
          description: "Device temperature is between 30°C and 80°C"

Let's breakdown each field one by one!

Groups: A collection of rules. Groups help organize rules by their purpose or by the services they monitor.
Rules: Defines the individual alerting or recording rules within a group. Each rule specifies conditions under which alerts should be fired or metrics should be recorded.
Alert: Name of the alert.'
for: The duration that the expr condition must be true for firing the alert to alert manager.
Expr: The PromQL expression that defines the condition triggering the alert.
Labels: Key-Value attached to the alert. Are used to categorize or add metadata with alerts, such as severity levels, alert types and actual metric value at the moment.
Annotations: Descriptive information attached to alerts that can include additional details or instructions such as summary and description.

Once the rule files are configured, locate them in the directory we've specified in Prometheus.yml, let's initiate the Prometheus server using docker compose

// docker-compose.yml
  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
      - ~/prometheus/rules:/etc/prometheus/rules
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--web.enable-remote-write-receiver'
      - '--web.enable-lifecycle'
      - '--storage.tsdb.retention.size=4GB'
    ports:
      - 9090:9090
    networks:
      - prom-net
    restart: always

Now visit the prometheus dashboard on port 9090, alert rules should appear under alert tab if they were correctly implemented.

Remember whenever a rule was added or modified, we have to actively reload the prometheus server, the configuration reload is triggered by sending a SIGHUP to the Prometheus process or sending a HTTP POST request to the /-/reload endpoint (that's why the --web.enable-lifecycle flag is enabled).

Alertmanager & Pushgateway

After successfully configured our alert rule, it's time to trigger an actual alert from it, that's when Prometheus Alertmanager and Pushgateway come in.

Alertmanager :

Alertmanager handles the next steps for alerts, deduplicating, grouping, and routing these alerts to the correct receivers like email, Slack, or webhooks.

Pushgateway :

PushGateway offers a solution for supporting Prometheus metrics from batch jobs or ephemeral jobs that cannot be scraped. It acts as an intermediary service allowing these jobs to push their metrics to PushGateway, which can be scraped by Prometheus.
It allows us to actively push custom metrics by curling endpoint which is useful for testing every kind of threshold scenario before Prometheus could actually scrape the real data.

Configuration & Test Steps

Step 1: Configure alertmanager.yml & prometheus.yml

// alertmanager.yml
global:
  resolve_timeout: 5m //After this time passes, declare as resolve

route:   // Define the routing logic for each alert
  group_by: ['alertname', 'instance'] 
  group_wait: 10s  // The time to wait before sending the first notification for a new group of alerts
  group_interval: 10s  // After the first notification for a group of alerts, this is the wait time before sending a subsequent notification for that group
  repeat_interval: 1m  // How long Alertmanager waits before sending out repeat notifications for a group of alerts that continues to be active (firing).
  receiver: 'webhook-receiver' // Name of receiver, defined in receivers section
  routes:
    - match: // Defines the condition of triggering webhook
        severity: 'disk_low_crit'
      receiver: 'webhook-receiver'

receivers:
  - name: 'webhook-receiver'
    webhook_configs:   // Webhook server endpoint
      - url: 'http://10.0.0.1:3000'
        send_resolved: true

// prometheus.yml  add alertmanager config
# my global config
global:
  scrape_interval: 60s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 60s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).

# Alertmanager configuration
alerting:
  alertmanagers:
    - static_configs:
        - targets:
            - alertmanager:9093

# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  - '/etc/prometheus/rules/*.yml'

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:

static_configs:
  - targets: ["localhost:9090"]

Step 2: Establish both instances with docker-compose, rebuild prometheus instance with new config as well

// docker-compose.yml
  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
      - ~/prometheus/rules:/etc/prometheus/rules
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--web.enable-remote-write-receiver'
      - '--web.enable-lifecycle'
      - '--storage.tsdb.retention.size=4GB'
    ports:
      - 9090:9090
    networks:
      - prom-net
    restart: always

  alertmanager:
    image: prom/alertmanager:latest
    volumes:
      - ./alertmanager.yml:/etc/alertmanager/alertmanager.yml
    command:
      - '--config.file=/etc/alertmanager/alertmanager.yml'
      - '--storage.path=/alertmanager'
    ports:
      - '9093:9093'
    networks:
      - prom-net

  pushgateway:
  image: prom/pushgateway
  ports:
    - "9091:9091"
  networks:
    - prom-net

Step 3: Pushing custom metrics to Pushgateway by running this command, in this case we're passing a temperature metric with value 75

echo "device_temperature_celsius{device=\"laser/101\", SN=\"18400138\", deviceId=\"3764b164-01e7-4940-b9e9-9cf26604534a\", instance=\"localhost:59900\", job=\"sysctl\"} 75" | curl --data-binary @- http://localhost:9091/metrics/job/temperature_monitoring

Step 4: Check Pushgateway dashboard on port 9091 to make sure if the metric was pushed

Step 5: Check if the alert status changed to active on Prometheus dashboard (9090), it should meet the threshold of the alert rule we just defined

Step 6: Check the alertmanager instance dashboard(9093) to make sure it receive the alert

Reference:

Prometheus Docs
Prometheus git book

Thanks for reading !!!

Unleash the searching power of MySQL by with Full-Text Search

Benny Yang — Wed, 24 Jan 2024 04:17:39 +0000

Recently I've been hooking up with Mysql full-text search, which is a compromised solution for building up a simple keyword search feature instead of utilizing external search engines such as ElasticSearch.

MySQL full-text search

MySQL supports for full-text searching, by creating an index with type FULLTEXT, after then, we can perform full-text search upon certain fields using MATCH(column) AGAINST (string) syntax.

Difference with `LIKE`

When it comes to fuzzy searching in MySQL, the first approach most people will come up with is applying LIKE operator.

SELECT * FROM wide_table WHERE column_A LIKE '%keyword%'

However, as the dataset grows, it could be resource-intensive when doing this, especially it will perform a full table scan when it comes to searching VARCHAR, char and TEXT field with a wildcard character % at the beginning.

In contrast, full-text searches are quicker in this case since it was indexed in advance and it provides additional features such as search with operators, data relevance score... etc.

Search Modes

MySQL does offer multiple search modes while performing full-text search by specifying it at the end of the query

MATCH (column) AGAINST (string) IN NATURAL LANGUAGE / BOOLEAN MODE

Natural Language Mode

Natural Language Mode is the default mode for full-text searches in MySQL. It looks for occurrences of the search terms within the indexed columns and calculates relevance based on factors like term frequency and proximity.

Let's dive into some examples

// Create table with full-text index
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=InnoDB;

// Search with "smartphone features"
SELECT * from articles where MATCH(title, body) AGAINST('smartphone features' IN NATURAL LANGUAGE MODE);

And we got the result with

| id  | title                                         | body                                                                                                                                                                                                                           |
| --- | --------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| 1   | Smartphone Evolution                          | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support. |
| 3   | Technological Advancements in Cellular Phones | Cellular phones have undergone tremendous technological advancements. The latest models boast features like facial recognition, augmented reality capabilities, and voice-activated assistants.                                |
| 4   | The Impact of Mobile Phones on Daily Life     | Mobile phones have drastically impacted our daily lives. They keep us aconnected, provide instant access to information, and offer an array of features like GPS navigation, digital wallets, and health monitoring apps.      |

Getting relevancy score from each row

SELECT *, MATCH(title, body) AGAINST('smartphone features' IN NATURAL LANGUAGE MODE) as score from articles;

| id  | title                                         | body                                                                                                                                                                                                                                    | score                |
| --- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------- |
| 1   | Smartphone Evolution                          | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support.          | 0.3780859112739563   |
| 2   | The Rise of Smart Devices                     | Smart devices, especially in the realm of telecommunication, have become ubiquitous. These devices, often fitting in the palm of our hand, offer functionalities ranging from high-quality video calls to seamless social media access. | 0                    |
| 3   | Technological Advancements in Cellular Phones | Cellular phones have undergone tremendous technological advancements. The latest models boast features like facial recognition, augmented reality capabilities, and voice-activated assistants.                                         | 0.015609688125550747 |
| 4   | The Impact of Mobile Phones on Daily Life     | Mobile phones have drastically impacted our daily lives. They keep us aconnected, provide instant access to information, and offer an array of features like GPS navigation, digital wallets, and health monitoring apps.               | 0.015609688125550747 |

As we saw from the example above, the result was automatically ordered by it's relevancy score and we can also retrieve the exact score by specifying it as the selected column.

The score is computed based on the number of words in the row (document), the number of unique words in the row, the total number of words in the collection, and the number of rows that contain a particular word.

Boolean Mode

Boolean Mode provides greater control and precision through the use of boolean operators like + (must include), - (exclude), and others. This allows for complex and specific search queries.

For example,

SELECT title, body 
    FROM articles 
    WHERE MATCH(title, body) AGAINST('+smartphone features' IN BOOLEAN MODE);

We got

| title                | body                                                                                                                                                                                                                           |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Smartphone Evolution | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support. |

by specifying "smartphone" as a must included string.

By utilizing these powerful operators, this mode is particularly useful when you need precise control over the search criteria, especially for complex queries or when filtering out specific terms is important.

Search with Query Expansion

Query Expansion can sometimes capture a broader range of related content, potentially addressing some aspects of context.
It works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search.

Let's test with previous example

SELECT *, MATCH(title, body) AGAINST('smartphone features' WITH QUERY EXPANSION) as score FROM articles WHERE MATCH(title, body) AGAINST('smartphone features' WITH QUERY EXPANSION);

| id  | title                                         | body                                                                                                                                                                                                                                    | score              |
| --- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------ |
| 4   | The Impact of Mobile Phones on Daily Life     | Mobile phones have drastically impacted our daily lives. They keep us connected, provide instant access to information, and offer an array of features like GPS navigation, digital wallets, and health monitoring apps.               | 8.474403381347656  |
| 1   | Smartphone Evolution                          | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support.          | 7.643221378326416  |
| 3   | Technological Advancements in Cellular Phones | Cellular phones have undergone tremendous technological advancements. The latest models boast features like facial recognition, augmented reality capabilities, and voice-activated assistants.                                         | 6.737030029296875  |
| 2   | The Rise of Smart Devices                     | Smart devices, especially in the realm of telecommunication, have become ubiquitous. These devices, often fitting in the palm of our hand, offer functionalities ranging from high-quality video calls to seamless social media access. | 0.4843146502971649 |

In this case, the relevance order of each row was changed compared to previous results we got since it searches again for the relevant concepts, the exact phrase doesn't appear in the fourth row but it was returned since it's content is related to the keyword.

Customization

Let's say we want to build a customize keyword search by prioritizing on certain fields. We could achieve this by putting weight upon their relevancy score using SELECT, for instance :

SELECT *, 
(MATCH(title) AGAINST('smartphone features' IN NATURAL LANGUAGE MODE) * 5) + 
(MATCH(body) AGAINST ('smartphone features' IN NATURAL LANGUAGE MODE) * 3) as score 
FROM articles ORDER by score;

In this case, if two rows of data have there body and title matching respectively, the row with matched title will be populated first.

Conclusion

In conclusion, MySQL full-text search provides a straightforward and accessible solution for keyword search functionality, presenting a simpler alternative to more complex systems like Elasticsearch in terms of less time-consuming to build and configure, it also offers clear advantages over the basic LIKE operator. Full-text search benefits from FULLTEXT indexing, enabling faster and more efficient searches. It supports diverse search modes such as Natural Language and Boolean Mode, but lacks Elasticsearch's advanced natural language processing and scalability. Although beneficial for basic to moderate search requirements. Furthermore, it can be challenging to fine-tune for specific needs, making it less suitable for highly complex or large-scale search scenarios.

References

MySQL documentation