方言を話すおしゃべり猫型ロボット『ミーア』をリリースしました（こちらをクリック）

[Golang] Practice of Test-Driven Development: From Migration File Creation to SQL Injection Countermeasures

golang-test-driven-development
This article can be read in about 23 minutes.

Introduction.

I am now developing “Mia,” a talking cat-shaped robot that speaks in various dialects.

Mia
The talking cat-shaped robot Mia shares sadness and joy with more than 100 different rich expressions. It also speaks in...
mia-cat.com

This time, the next major feature is

Arbitrary text voice playback function
When the user enters a phrase that he/she wants Mia to speak, along with the playback time, the app will play back the phrase at the specified time.

I want to develop a new database. Since it will be a fairly large function, we will first design the DB and try test-driven development for SQL statements.

Creating a Migration File

First, create a database migration file.

This time, create two new tables

user_phrases table: which manages all phrases created by users and controls whether they are public or private
phrase_schedules table: which manages phrase playback schedules for each user

Text phrases or recorded audio files entered by the user in the app are stored in the user_phrases table. The user can control whether the phrase is public or private (is_private field)

Phrases and recorded audio files made public by users are visible to other users, and other users can say, “This phrase is interesting! and other users can copy the phrase and incorporate it as their own phrase playback.

Migration file creation command

ShellScript
$ cd migrations
$ migrate create -ext sql -format 2006010215 create_user_phrases_table
$ migrate create -ext sql -format 2006010215 create_phrase_schedules_table

Migration file contents

2024071305_create_user_phrases_table.up.sql

SQL
CREATE TABLE IF NOT EXISTS user_phrases (
    id INT NOT NULL AUTO_INCREMENT COMMENT 'フレーズID',
    user_id BIGINT UNSIGNED COMMENT 'ユーザーID',
    phrase TEXT NOT NULL COMMENT 'フレーズテキスト',
    voice_path VARCHAR(255) NOT NULL COMMENT 'フレーズ音声ファイルへのパス',
    recorded BOOLEAN NOT NULL DEFAULT FALSE COMMENT '録音データか否か',
    is_private BOOLEAN NOT NULL DEFAULT FALSE COMMENT '公開or非公開',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2024071305_create_user_phrases_table.down.sql

ShellScript
DROP TABLE IF EXISTS user_phrases;

2024071306_create_phrase_schedules_table.up.sql

SQL
CREATE TABLE IF NOT EXISTS phrase_schedules (
    id INT NOT NULL AUTO_INCREMENT COMMENT 'スケジュールID',
    user_id BIGINT UNSIGNED COMMENT 'ユーザーID',
    phrase_id INT COMMENT 'フレーズID',
    time TIME COMMENT '再生時間',
    days VARCHAR(255) COMMENT '再生曜日',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (phrase_id) REFERENCES user_phrases(id)
);

2024071306_create_phrase_schedules_table.down.sql

ShellScript
DROP TABLE IF EXISTS phrase_schedules;

Creating Test Codes

Next, the test code is created. The test code is used to verify that the expected functionality is implemented correctly.

Connect to production DB with dockertest

Processing around DB makes it easy to verify if dockertest can connect to a real DB for testing.

  • CallingRunMySQLContainer: Call the testutils.RunMySQLContainer function to start the MySQL container and connect to the database. If successful, an instance MySQLContainer is returned in container.
  • Database connection setup: db = container.DB causes the db variable to hold the database connection. This allows us to use db in our test code to access the database.
  • Running tests: Call m.Run() to run all tests.
  • Cleanup: After the test is finished, call container.Close() to clean up the database connection and Docker resources.
Go
package clocky_be_test

import (
	"log"
	"os"
	"testing"

	"github.com/EarEEG-dev/clocky_be/testutils"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func TestMain(m *testing.M) {
	container, err := testutils.RunMySQLContainer()
	if err != nil {
		if container != nil {
			container.Close()
		}
		log.Fatal(err)
	}
	db = container.DB
	code := m.Run()
	container.Close()
	os.Exit(code)
}

Test code creation in user_phrases_db_test.go

This setup is used to create concrete test code. The following is an example of a test code in user_phrases_db_test.go (only Create and Get out of CRUD).

user_phrases_db_test.go

Go
package clocky_be_test

import (
	"errors"
	"testing"

	"github.com/EarEEG-dev/clocky_be"
	"github.com/jmoiron/sqlx"
)

func resetDBForUserPhrases(db *sqlx.DB) {
	db.MustExec(`DELETE FROM phrase_schedules`)
	db.MustExec(`DELETE FROM user_phrases`)
	db.MustExec(`DELETE FROM users`)
}

func TestCreateUserPhrase(t *testing.T) {
	t.Run("Create new user phrase", func(t *testing.T) {
		resetDBForUserPhrases(db)
		uid := "unique_user_id"
		user, err := clocky_be.CreateUser(db, uid)
		if err != nil {
			t.Fatalf("failed to create user: %v", err)
		}

		up, err := clocky_be.CreateUserPhrase(db, user.ID, "sample phrase", "sample/path", false, true)
		if err != nil {
			t.Fatalf("failed to create user phrase: %v", err)
		}

		if up.UserID != user.ID || up.Phrase != "sample phrase" || up.VoicePath != "sample/path" || up.Recorded != false || up.IsPrivate != true {
			t.Errorf("unexpected user phrase data: %+v", up)
		}
	})
}

func TestGetUserPhrase(t *testing.T) {
	t.Run("Get specific user phrase", func(t *testing.T) {
		resetDBForUserPhrases(db)
		uid := "existing_user_id"
		user, err := clocky_be.CreateUser(db, uid)
		if err != nil {
			t.Fatalf("failed to create user: %v", err)
		}

		up, err := clocky_be.CreateUserPhrase(db, user.ID, "sample phrase", "sample/path", false, true)
		if err != nil {
			t.Fatalf("failed to create user phrase: %v", err)
		}

		gotUP, err := clocky_be.GetUserPhrase(db, up.ID, user.ID)
		if err != nil {
			t.Fatalf("failed to get user phrase: %v", err)
		}

		if gotUP.ID != up.ID || gotUP.UserID != user.ID || gotUP.Phrase != up.Phrase || gotUP.VoicePath != up.VoicePath {
			t.Errorf("unexpected user phrase data: %+v", gotUP)
		}
	})
}

How to create an SQL query

When creating SQL queries, it is important to protect against SQL injection. Therefore, use placeholders instead of string concatenation. Use the lightweight query builder called squirrel.

Using the squirrel package

squirrel is a library for safely and concisely assembling SQL queries.

https://github.com/Masterminds/squirrel

user_phrases_db.go

Go
package clocky_be

import (
	"github.com/Masterminds/squirrel"
	"github.com/jmoiron/sqlx"
)

type UserPhrase struct {
	ID        uint64 `db:"id" json:"id"`
	UserID    uint64 `db:"user_id" json:"user_id"`
	Phrase    string `db:"phrase" json:"phrase"`
	VoicePath string `db:"voice_path" json:"voice_path"`
	Recorded  bool   `db:"recorded" json:"recorded"`
	IsPrivate bool   `db:"is_private" json:"is_private"`
	CreatedAt string `db:"created_at" json:"created_at"`
	UpdatedAt string `db:"updated_at" json:"updated_at"`
}

func CreateUserPhrase(db *sqlx.DB, userID uint64, phrase, voicePath string, recorded, isPrivate bool) (*UserPhrase, error) {
	query := squirrel.Insert("user_phrases").
		Columns("user_id", "phrase", "voice_path", "recorded", "is_private", "created_at", "updated_at").
		Values(userID, phrase, voicePath, recorded, isPrivate, squirrel.Expr("CURRENT_TIMESTAMP"), squirrel.Expr("CURRENT_TIMESTAMP")).
		PlaceholderFormat(squirrel.Question)

	sql, args, err := query.ToSql()
	if err != nil {
		return nil, err
	}

	result, err := db.Exec(sql, args...)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	return GetUserPhrase(db, uint64(id), userID)
}

func GetUserPhrase(db *sqlx.DB, id, userID uint64) (*UserPhrase, error) {
	query := squirrel.Select("id", "user_id", "phrase", "voice_path", "recorded", "is_private", "created_at", "updated_at").
		From("user_phrases").
		Where(squirrel.Eq{"id": id, "user_id": userID}).
		PlaceholderFormat(squirrel.Question)

	sql, args, err := query.ToSql()
	if err != nil {
		return nil, err
	}

	var up UserPhrase
	err = db.Get(&up, sql, args...)
	if err != nil {
		return nil, err
	}

	return &up, nil
}

Running tests with the go test command

Use the go test command to run the test.

ShellScript
go test -v
  • The -v option is for displaying detailed output. The output is displayed for both successful and unsuccessful tests.

It was executed like this

ShellScript
2024/07/13 20:14:48 success connecting to DB🚀
2024/07/13 20:14:48 mysql container start🐳
=== RUN   TestCreatePhraseSchedule
=== RUN   TestCreatePhraseSchedule/Create_new_phrase_schedule
=== RUN   TestCreatePhraseSchedule/Create_phrase_schedule_for_private_phrase_with_mismatched_user_id
--- PASS: TestCreatePhraseSchedule (0.05s)
    --- PASS: TestCreatePhraseSchedule/Create_new_phrase_schedule (0.03s)
    --- PASS: TestCreatePhraseSchedule/Create_phrase_schedule_for_private_phrase_with_mismatched_user_id (0.02s)
=== RUN   TestUpdatePhraseSchedule
=== RUN   TestUpdatePhraseSchedule/Update_existing_phrase_schedule
--- PASS: TestUpdatePhraseSchedule (0.01s)
    --- PASS: TestUpdatePhraseSchedule/Update_existing_phrase_schedule (0.01s)
=== RUN   TestDeletePhraseSchedule
=== RUN   TestDeletePhraseSchedule/Delete_existing_phrase_schedule
--- PASS: TestDeletePhraseSchedule (0.01s)
    --- PASS: TestDeletePhraseSchedule/Delete_existing_phrase_schedule (0.01s)
=== RUN   TestCreateUser
=== RUN   TestCreateUser/Create_unique_user
=== RUN   TestCreateUser/Create_duplicate_user
--- PASS: TestCreateUser (0.01s)
    --- PASS: TestCreateUser/Create_unique_user (0.01s)
    --- PASS: TestCreateUser/Create_duplicate_user (0.01s)
=== RUN   TestGetUser
=== RUN   TestGetUser/Get_existing_user
=== RUN   TestGetUser/Get_non-existing_user
--- PASS: TestGetUser (0.01s)
    --- PASS: TestGetUser/Get_existing_user (0.01s)
    --- PASS: TestGetUser/Get_non-existing_user (0.00s)
=== RUN   TestUpdateUser
=== RUN   TestUpdateUser/Update_existing_user
=== RUN   TestUpdateUser/Update_non-existing_user
=== RUN   TestUpdateUser/Update_with_non-existing_device_id
=== RUN   TestUpdateUser/Update_with_existing_device_id_and_set_firmware_version
=== RUN   TestUpdateUser/Update_with_device_already_registered_to_another_user
--- PASS: TestUpdateUser (0.04s)
    --- PASS: TestUpdateUser/Update_existing_user (0.01s)
    --- PASS: TestUpdateUser/Update_non-existing_user (0.00s)
    --- PASS: TestUpdateUser/Update_with_non-existing_device_id (0.01s)
    --- PASS: TestUpdateUser/Update_with_existing_device_id_and_set_firmware_version (0.01s)
    --- PASS: TestUpdateUser/Update_with_device_already_registered_to_another_user (0.01s)
=== RUN   TestDeleteUser
=== RUN   TestDeleteUser/Delete_existing_user
=== RUN   TestDeleteUser/Delete_non-existing_user
--- PASS: TestDeleteUser (0.01s)
    --- PASS: TestDeleteUser/Delete_existing_user (0.01s)
    --- PASS: TestDeleteUser/Delete_non-existing_user (0.00s)
=== RUN   TestExistUser
=== RUN   TestExistUser/User_exists
=== RUN   TestExistUser/User_does_not_exist
--- PASS: TestExistUser (0.01s)
    --- PASS: TestExistUser/User_exists (0.00s)
    --- PASS: TestExistUser/User_does_not_exist (0.00s)
=== RUN   TestCreateUserPhrase
=== RUN   TestCreateUserPhrase/Create_new_user_phrase
--- PASS: TestCreateUserPhrase (0.00s)
    --- PASS: TestCreateUserPhrase/Create_new_user_phrase (0.00s)
=== RUN   TestGetUserPhrase
=== RUN   TestGetUserPhrase/Get_specific_user_phrase
--- PASS: TestGetUserPhrase (0.01s)
    --- PASS: TestGetUserPhrase/Get_specific_user_phrase (0.01s)
=== RUN   TestGetUserPhrases
=== RUN   TestGetUserPhrases/Get_all_user_phrases
--- PASS: TestGetUserPhrases (0.01s)
    --- PASS: TestGetUserPhrases/Get_all_user_phrases (0.01s)
=== RUN   TestCopyPublicPhrase
=== RUN   TestCopyPublicPhrase/Copy_public_phrase
=== RUN   TestCopyPublicPhrase/Copy_non-existent_public_phrase
--- PASS: TestCopyPublicPhrase (0.02s)
    --- PASS: TestCopyPublicPhrase/Copy_public_phrase (0.01s)
    --- PASS: TestCopyPublicPhrase/Copy_non-existent_public_phrase (0.00s)
=== RUN   TestUpdateUserPhrase
=== RUN   TestUpdateUserPhrase/Update_existing_user_phrase
--- PASS: TestUpdateUserPhrase (0.01s)
    --- PASS: TestUpdateUserPhrase/Update_existing_user_phrase (0.01s)
=== RUN   TestDeleteUserPhrase
=== RUN   TestDeleteUserPhrase/Delete_existing_user_phrase
--- PASS: TestDeleteUserPhrase (0.01s)
    --- PASS: TestDeleteUserPhrase/Delete_existing_user_phrase (0.01s)
PASS
2024/07/13 20:14:49 mysql container end🐳
ok      github.com/EarEEG-dev/clocky_be 14.257s

summary

The basic steps of test-driven development using the Go language, how to create SQL queries, and how to run tests are described.

In this example, we only had to Create and Get methods for user phrases, so I will also include Update and Delete methods, as well as a method to copy from public phrases and test code.

Copied title and URL