Automating SQL execution with Go: development experience and setting up notifications

In today's world, automating routine processes helps save time and avoid mistakes. In one of my projects, I had a task to automatically execute SQL scripts that appear in a certain folder, and then send a full report on the execution to the administrator's email. To solve this task, I decided to write a specialized microservice in Go language, which constantly monitors the specified directory, executes the found SQL-files and notifies the responsible persons about the results of the work. Next I will detail the steps of implementation, the use of an external configuration file and the peculiarities of the approach.

Step 1: Project initialization and application configuration

The first step was to develop the project structure and create a configuration file. For flexibility of work all settings - database connection parameters, information for SMTP-notifications and paths to working folders - are placed in a separate config.yml file. This makes it easy to change the parameters without changing the source code.

database:
  host: localhost
  port: 3306
  user: root
  password: secret
  name: mydb

smtp:
  server: smtp.example.com
  port: 587
  username: user@example.com
  password: smtp-password
  from: noreply@example.com
  to: admin@example.com

paths:
  input: ./sql/input
  output: ./sql/processed
  error: ./sql/errors

Step 2: Connecting to the database

After loading the settings from the file, you need to establish a connection to the database. For this purpose I used a package for working with MySQL. It is necessary to establish a connection to the database and check the correctness of interaction with the database.

func initDB() {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true&multiStatements=true",
		config.Database.User,
		config.Database.Password,
		config.Database.Host,
		config.Database.Port,
		config.Database.Name)

	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Error connecting to database: %v", err)
	}

	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %v", err)
	}
}

Step 3: Folder monitoring and file processing

The main functionality of the script is to constantly monitor the specified directory. Every few seconds the application checks for new SQL files, and if they are detected, it reads the contents of the file and tries to execute SQL queries in the database.

Features of this stage:

• Checking each file for extra characters (e.g., BOM) - this is important for correct query execution.
• Logging errors in reading or executing SQL queries for crash analysis.
• Separating files into successfully executed and those that caused an error by moving them to the appropriate folders (done and failed).

func processFiles() {
	files, err := os.ReadDir(config.Paths.Input)
	if err != nil {
		log.Printf("Error reading input directory: %v", err)
		return
	}

	for _, file := range files {
		if file.IsDir() {
			continue
		}

		filePath := filepath.Join(config.Paths.Input, file.Name())
		processFile(filePath)
	}
}

func processFile(filePath string) {
	data, err := os.ReadFile(filePath)
	if err != nil {
		log.Printf("Error reading file %s: %v", filePath, err)
		return
	}

	err = executeSQL(string(data))
	if err != nil {
		handleError(filePath, err)
	} else {
		handleSuccess(filePath)
	}
}

Step 4: Realization of SQL query execution logic

Each SQL file is checked before execution, after which a SQL query is executed using db.Exec. If the query is successfully executed, the file is moved to the archived scripts folder; otherwise, it is moved to the errors folder. This approach avoids repeated execution of the same query and helps to track which files need repeated attention.

func executeSQL(query string) error {
	if strings.Contains(query, "\ufeff") {
		query = strings.ReplaceAll(query, "\ufeff", "")
	}

	_, err := db.Exec(query)
	return err
}

Step 5: Sending the progress report to email

At the end of the script operation, the important point is to send a report to the administrator. Each time an operation is executed (successfully or with an error), a corresponding notification is sent via SMTP. The use of a secure TLS connection ensures the security of data transmission. This allows you to quickly receive feedback on the script operation and prevent possible problems in the system operation.

func handleError(filePath string, err error) {
	log.Printf("Error executing SQL: %v", err)
	sendEmail("SQL Execution Error", fmt.Sprintf("Error: %v\nFile: %s", err, filePath))
	moveFile(filePath, config.Paths.Failed)
}

Advantages and disadvantages of the approach

Advantages:

• Automation of routine tasks can significantly reduce the time to complete operations and reduce the risk of errors.
• Flexibility of settings via an external configuration file makes it possible to easily adapt the script to different environments.
• Sending reports via e-mail ensures that responsible persons are informed promptly.

Disadvantages:

• Constant disk folder checking can put a strain on the system with a large number of files if an optimal caching or queuing strategy is not implemented.
• Synchronization issues may arise in case of large volume of SQL queries, especially if the queries require significant resources.
• The presence of minimal error handling in sprint queries requires additional monitoring for timely response.

Conclusion

Developing this microservice was a unique experience for me in automating routine tasks using Go. Proper separation of logic into configuration, database connectivity, file monitoring and notifications makes the system flexible and scalable. This approach not only minimizes the risks associated with manual execution of SQL scripts, but also allows you to quickly respond to issues through reporting.

If you are facing similar challenges, this example can be a good starting point for creating a reliable and easy-to-use solution.

package main

import (
	"crypto/tls"
	"database/sql"
	"fmt"
	_ "github.com/octoper/go-ray"
	"log"
	"net/smtp"
	"os"
	"path/filepath"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"gopkg.in/yaml.v3"
)

type Config struct {
	Database struct {
		Host     string `yaml:"host"`
		Port     int    `yaml:"port"`
		User     string `yaml:"user"`
		Password string `yaml:"password"`
		Name     string `yaml:"name"`
	} `yaml:"database"`

	SMTP struct {
		Server   string `yaml:"server"`
		Port     int    `yaml:"port"`
		Username string `yaml:"username"`
		Password string `yaml:"password"`
		From     string `yaml:"from"`
		To       string `yaml:"to"`
	} `yaml:"smtp"`

	Paths struct {
		Input  string `yaml:"input"`
		Done   string `yaml:"done"`
		Failed string `yaml:"failed"`
	} `yaml:"paths"`
}

var (
	config Config
	db     *sql.DB
)

func main() {
	loadConfig("config.yml")

	initDB()
	defer db.Close()

	for {
		processFiles()
		time.Sleep(5 * time.Second) // Проверка каждые 5 секунд
	}
}

func loadConfig(filename string) {
	data, err := os.ReadFile(filename)
	if err != nil {
		log.Fatalf("Error reading config file: %v", err)
	}

	err = yaml.Unmarshal(data, &config)
	if err != nil {
		log.Fatalf("Error parsing config file: %v", err)
	}
}

func initDB() {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true&multiStatements=true",
		config.Database.User,
		config.Database.Password,
		config.Database.Host,
		config.Database.Port,
		config.Database.Name)

	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Error connecting to database: %v", err)
	}

	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %v", err)
	}
}

func processFiles() {
	files, err := os.ReadDir(config.Paths.Input)
	if err != nil {
		log.Printf("Error reading input directory: %v", err)
		return
	}

	for _, file := range files {
		if file.IsDir() {
			continue
		}

		filePath := filepath.Join(config.Paths.Input, file.Name())
		processFile(filePath)
	}
}

func processFile(filePath string) {
	data, err := os.ReadFile(filePath)
	if err != nil {
		log.Printf("Error reading file %s: %v", filePath, err)
		return
	}

	err = executeSQL(string(data))
	if err != nil {
		handleError(filePath, err)
	} else {
		handleSuccess(filePath)
	}
}

func executeSQL(query string) error {
	if strings.Contains(query, "\ufeff") {
		query = strings.ReplaceAll(query, "\ufeff", "")
	}

	_, err := db.Exec(query)
	return err
}

func handleError(filePath string, err error) {
	log.Printf("Error executing SQL: %v", err)
	sendEmail("SQL Execution Error", fmt.Sprintf("Error: %v\nFile: %s", err, filePath))
	moveFile(filePath, config.Paths.Failed)
}

func handleSuccess(filePath string) {
	log.Println("SQL executed successfully")
	sendEmail("SQL Execution Success", fmt.Sprintf("File: %s", filePath))
	moveFile(filePath, config.Paths.Done)
}

func moveFile(source, destDir string) {
	fileName := filepath.Base(source)
	destPath := filepath.Join(destDir, fileName)

	err := os.Rename(source, destPath)
	if err != nil {
		log.Printf("Error moving file: %v", err)
	}
}

func sendEmail(subject, body string) {
	tlsConfig := &tls.Config{
		ServerName:         config.SMTP.Server,
		InsecureSkipVerify: false,
	}

	conn, err := tls.Dial("tcp", fmt.Sprintf("%s:%d", config.SMTP.Server, config.SMTP.Port), tlsConfig)
	if err != nil {
		log.Printf("Error creating TLS connection: %v", err)
		return
	}
	defer conn.Close()

	client, err := smtp.NewClient(conn, config.SMTP.Server)
	if err != nil {
		log.Printf("Error creating SMTP client: %v", err)
		return
	}
	defer client.Close()

	auth := smtp.PlainAuth("", config.SMTP.Username, config.SMTP.Password, config.SMTP.Server)
	if err := client.Auth(auth); err != nil {
		log.Printf("SMTP auth error: %v", err)
		return
	}

	if err := client.Mail(config.SMTP.From); err != nil {
		log.Printf("Mail command error: %v", err)
		return
	}
	if err := client.Rcpt(config.SMTP.To); err != nil {
		log.Printf("Rcpt command error: %v", err)
		return
	}

	wc, err := client.Data()
	if err != nil {
		log.Printf("Data command error: %v", err)
		return
	}
	defer wc.Close()

	msg := fmt.Sprintf("From: %s\r\nTo: %s\r\nSubject: %s\r\n\r\n%s",
		config.SMTP.From,
		config.SMTP.To,
		subject,
		body,
	)

	if _, err = fmt.Fprint(wc, msg); err != nil {
		log.Printf("Error writing message: %v", err)
		return
	}
}