[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", "john@example.com")
    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 = ?", "johndoe@example.com", 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("john.doe@example.com", insertedID)
    if err != nil {
        log.Fatal(err)
    }
}

Disclaimer

• [X] 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);