[Feature Request] Implement Better Database Interaction for MySQL

[H0llyW00dzZ]

Tell us about your feature request

As Currently there is no interacting method, I would like to request this also. It's safe from SQL injection, and if anyone agrees, I can submit a PR.

the method:

// Service represents a service that interacts with a database.
type Service interface {
	// Health returns a map of health status information.
	// The keys and values in the map are service-specific.
	Health() map[string]string

	// Exec executes a SQL query with the provided arguments and returns the result.
	// It is safe against SQL injection when used with parameter placeholders.
	Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	// BeginTx starts a new database transaction with the specified options.
	// The transaction is bound to the context passed.
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

	// QueryRow executes a query that is expected to return at most one row.
	// The result is scanned into the provided destination variables.
	QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row

	// Query executes a query that returns multiple rows.
	// It is safe against SQL injection when used with parameter placeholders.
	Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

	// Prepare creates a prepared statement for repeated use.
	// A prepared statement takes parameters and is safe against SQL injection.
	Prepare(ctx context.Context, query string) (*sql.Stmt, error)
}

// Exec executes a SQL query with the given arguments within the provided context.
// It returns the result of the execution, such as the number of affected rows.
func (s *service) Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
	return s.db.ExecContext(ctx, query, args...)
}

// BeginTx starts a new transaction with the given transaction options within the provided context.
// It returns a transaction handle to be used for executing statements and committing or rolling back.
func (s *service) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) {
	return s.db.BeginTx(ctx, opts)
}

// QueryRow executes a SQL query that is expected to return at most one row,
// scanning the result into the provided destination variables.
func (s *service) QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row {
	return s.db.QueryRowContext(ctx, query, args...)
}

// Query executes a SQL query with the given arguments within the provided context.
// It returns a result set containing multiple rows, which must be iterated over.
func (s *service) Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	return s.db.QueryContext(ctx, query, args...)
}

// Prepare creates a new prepared statement for the given query within the provided context.
// Prepared statements can be reused and are safe against SQL injection.
func (s *service) Prepare(ctx context.Context, query string) (*sql.Stmt, error) {
	return s.db.PrepareContext(ctx, query)
}

Example Usage:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

// Service represents a service that interacts with a database.
type Service interface {
	// Health returns a map of health status information.
	// The keys and values in the map are service-specific.
	Health() map[string]string

	// Exec executes a SQL query with the provided arguments and returns the result.
	// It is safe against SQL injection when used with parameter placeholders.
	Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	// BeginTx starts a new database transaction with the specified options.
	// The transaction is bound to the context passed.
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

	// QueryRow executes a query that is expected to return at most one row.
	// The result is scanned into the provided destination variables.
	QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row

	// Query executes a query that returns multiple rows.
	// It is safe against SQL injection when used with parameter placeholders.
	Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

	// Prepare creates a prepared statement for repeated use.
	// A prepared statement takes parameters and is safe against SQL injection.
	Prepare(ctx context.Context, query string) (*sql.Stmt, error)
}

// service implements the Service interface with a connection to a SQL database.
type service struct {
	db *sql.DB
}

// Health reports the current status of the service.
func (s *service) Health() map[string]string {
	return map[string]string{"status": "ok"}
}

// Exec executes a SQL query with the given arguments within the provided context.
// It returns the result of the execution, such as the number of affected rows.
func (s *service) Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
	return s.db.ExecContext(ctx, query, args...)
}

// BeginTx starts a new transaction with the given transaction options within the provided context.
// It returns a transaction handle to be used for executing statements and committing or rolling back.
func (s *service) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) {
	return s.db.BeginTx(ctx, opts)
}

// QueryRow executes a SQL query that is expected to return at most one row,
// scanning the result into the provided destination variables.
func (s *service) QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row {
	return s.db.QueryRowContext(ctx, query, args...)
}

// Query executes a SQL query with the given arguments within the provided context.
// It returns a result set containing multiple rows, which must be iterated over.
func (s *service) Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	return s.db.QueryContext(ctx, query, args...)
}

// Prepare creates a new prepared statement for the given query within the provided context.
// Prepared statements can be reused and are safe against SQL injection.
func (s *service) Prepare(ctx context.Context, query string) (*sql.Stmt, error) {
	return s.db.PrepareContext(ctx, query)
}

func main() {
	// Create a new instance of the service
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydatabase")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	svc := &service{db: db}

	// Check the health of the service
	health := svc.Health()
	fmt.Println("Service health:", health)

	// Execute a query with parameter placeholders
	result, err := svc.Exec(context.Background(), "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "[email protected]")
	if err != nil {
		log.Fatal(err)
	}
	insertedID, _ := result.LastInsertId()
	fmt.Println("Inserted user with ID:", insertedID)

	// Begin a transaction
	tx, err := svc.BeginTx(context.Background(), nil)
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback()

	// Execute a query within the transaction using parameter placeholders
	_, err = tx.Exec("UPDATE users SET email = ? WHERE id = ?", "[email protected]", insertedID)
	if err != nil {
		log.Fatal(err)
	}

	// Commit the transaction
	err = tx.Commit()
	if err != nil {
		log.Fatal(err)
	}

	// Query a single row using parameter placeholders
	var name, email string
	err = svc.QueryRow(context.Background(), "SELECT name, email FROM users WHERE id = ?", insertedID).Scan(&name, &email)
	if err != nil {
		if err == sql.ErrNoRows {
			fmt.Println("User not found")
		} else {
			log.Fatal(err)
		}
	} else {
		fmt.Printf("User: %s, Email: %s\n", name, email)
	}

	// Execute a query with multiple rows using parameter placeholders
	rows, err := svc.Query(context.Background(), "SELECT id, name, email FROM users WHERE id > ?", 0)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	fmt.Println("Users:")
	for rows.Next() {
		var id int64
		var name, email string
		err := rows.Scan(&id, &name, &email)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("ID: %d, Name: %s, Email: %s\n", id, name, email)
	}

	// Prepare a statement for multiple executions
	stmt, err := svc.Prepare(context.Background(), "UPDATE users SET email = ? WHERE id = ?")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	// Execute the prepared statement with different parameters
	_, err = stmt.Exec("[email protected]", insertedID)
	if err != nil {
		log.Fatal(err)
	}
}

Disclaimer

• I agree

[H0llyW00dzZ]

Note

Also For instance, with the ExecContext method, you can utilize the SQL clause ON DUPLICATE KEY UPDATE to prevent duplicate entries.

example query

INSERT INTO user (email, password, date_added)
VALUES (?, ?, NOW())
ON DUPLICATE KEY UPDATE
email = VALUES(email),
password = VALUES(password);