Documentation
¶
Overview ¶
Package mysql provides utilities for establishing and managing connections to MySQL databases. It abstracts common database operations, allowing for consistent interactions across different parts of an application.
The package defines a Database interface that can be used for various database operations like querying, executing commands, and managing connections. This makes it easier to swap out actual database implementations or to mock the database during testing.
Usage:
1. Initialize the MySqlDbConnector and call the Open method to establish a connection. 2. Perform database operations using the MySqlDb methods that wrap standard sql.DB functions. 3. Use the Database interface for more abstract interactions or when mocking for tests.
It's essential to manage database connections carefully, ensuring they are closed when no longer needed, and to be aware of the configuration concerning connection lifetimes and pool sizes.
Index ¶
- Constants
- func CastSQLStringToFloat(value interface{}) float64
- func CastSQLStringToInt(value interface{}) int64
- func CastSQLStringToString(value interface{}) string
- func CastSQLStringToTime(value interface{}) time.Time
- func ConvertQueryAndNamedParams(query string, params ...map[string]interface{}) (string, []interface{})
- func DeleteByPrimaryKey(dbctx *DBContext, tableName, pkColumn string, pkValue interface{}) (int64, error)
- func ExecuteReadQuery(dbctx *DBContext, queryInput ReadQueryInput) ([]map[string]interface{}, error)
- func ExecuteWriteQuery(dbctx *DBContext, query string, params []interface{}) (int64, int64, error)
- func GetParameterizedInClause[T any](columnName string, columnValueArray []T) (string, map[string]interface{})
- func HashKey(query string, args map[string]interface{}) (string, error)
- func InsertFromMap(dbctx *DBContext, tableName string, data map[string]interface{}) (int64, int64, error)
- func InsertFromStruct(dbctx *DBContext, tableName string, data interface{}) (int64, int64, error)
- func MultiInsertFromStructsArray[T any](dbctx *DBContext, tableName string, data []T) (int64, error)
- func SetConnectionConfig(clusterName string, config *ConnectionConfig)
- func SoftDeleteByPrimaryKey(dbctx *DBContext, tableName, deleteCol, pkCol string, value interface{}) (int64, error)
- func UpdateFromMap(dbctx *DBContext, tableName string, data map[string]interface{}, where string, ...) (int64, error)
- type ConnectionConfig
- type DBContext
- type MySqlDb
- func (mdb *MySqlDb) Begin() (*sql.Tx, error)
- func (mdb *MySqlDb) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
- func (mdb *MySqlDb) Close() error
- func (mdb *MySqlDb) Conn(ctx context.Context) (*sql.Conn, error)
- func (mdb *MySqlDb) Driver() driver.Driver
- func (mdb *MySqlDb) Exec(query string, args ...interface{}) (sql.Result, error)
- func (mdb *MySqlDb) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
- func (mdb *MySqlDb) Ping() error
- func (mdb *MySqlDb) PingContext(ctx context.Context) error
- func (mdb *MySqlDb) Prepare(query string) (*sql.Stmt, error)
- func (mdb *MySqlDb) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
- func (mdb *MySqlDb) Query(query string, args ...interface{}) (*sql.Rows, error)
- func (mdb *MySqlDb) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
- func (mdb *MySqlDb) QueryRow(query string, args ...interface{}) *sql.Row
- func (mdb *MySqlDb) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
- func (mdb *MySqlDb) SetConnMaxIdleTime(d time.Duration)
- func (mdb *MySqlDb) SetConnMaxLifetime(d time.Duration)
- func (mdb *MySqlDb) SetMaxIdleConns(n int)
- func (mdb *MySqlDb) SetMaxOpenConns(n int)
- func (mdb *MySqlDb) Stats() sql.DBStats
- type MySqlDbConnector
- type MySqlDbConnectorInterface
- type MySqlDbInterface
- type ReadQueryInput
- type Tx
Constants ¶
const DriverTypeMySQL = "mysql"
*sql.DB Driver type for MYSQL
Variables ¶
This section is empty.
Functions ¶
func CastSQLStringToFloat ¶
func CastSQLStringToFloat(value interface{}) float64
func CastSQLStringToInt ¶
func CastSQLStringToInt(value interface{}) int64
func CastSQLStringToString ¶
func CastSQLStringToString(value interface{}) string
func CastSQLStringToTime ¶
func ConvertQueryAndNamedParams ¶
func ConvertQueryAndNamedParams(query string, params ...map[string]interface{}) (string, []interface{})
ConvertQueryAndNamedParams replaces named parameters in a query with positional placeholders (`?`) and returns the ordered list of parameters as expected by sql.DB.
Parameters:
- query: SQL query with named parameters (e.g., :userId).
- params: one or more maps of named parameter values.
Returns:
- modified query string
- slice of parameter values in correct order
func DeleteByPrimaryKey ¶
func DeleteByPrimaryKey(dbctx *DBContext, tableName, pkColumn string, pkValue interface{}) (int64, error)
DeleteByPrimaryKey deletes a single record by primary key.
Parameters:
- dbctx: DB context.
- tableName: name of the table.
- pkColumn: primary key column name.
- pkValue: value of the primary key.
Returns:
- number of rows deleted
- error if any.
func ExecuteReadQuery ¶
func ExecuteReadQuery(dbctx *DBContext, queryInput ReadQueryInput) ([]map[string]interface{}, error)
ExecuteReadQuery runs a read-only SELECT query on a given cluster, returning results as a slice of maps where each map represents a row. If CapitaliseColumns is set to true in the input, column names in the result will be capitalized.
Parameters:
- cluster: name of the database cluster.
- queryInput: struct containing the query string, parameters, and capitalisation flag.
Returns:
- []map[string]interface{}: list of result rows.
- error: any error encountered while querying.
func ExecuteWriteQuery ¶
ExecuteWriteQuery executes a SQL write query (INSERT/UPDATE/DELETE) using the provided DB context. It supports transactional or direct DB execution based on the DBContext.
Parameters:
- dbctx: context containing transaction, connection, or cluster.
- query: SQL query string.
- params: query parameters.
Returns:
- rows affected
- last insert ID (0 if not applicable)
- error if any.
func GetParameterizedInClause ¶
func GetParameterizedInClause[T any](columnName string, columnValueArray []T) (string, map[string]interface{})
GetParameterizedInClause generates a named parameterized IN clause from a slice of values. Returns the SQL-safe IN clause and map of parameter keys to values.
Parameters:
- columnName: name of the column for the IN clause.
- columnValueArray: slice of values to include in the clause.
Returns:
- IN clause string
- map of named parameters
func HashKey ¶
GenerateHashKey creates a unique hash key based on the provided query and its arguments. It first extracts the keys from the argument map and sorts them. It then concatenates the query string and the sorted arguments to generate a hash key. The key is then hashed using SHA-1 and encoded to a base64 URL-safe string.
Parameters:
- query: The SQL query string.
- args: A map of argument names to their values.
Returns:
- A base64 URL-encoded string representation of the SHA-1 hash.
- An error if there's a failure during the hashing process.
func InsertFromMap ¶
func InsertFromMap(dbctx *DBContext, tableName string, data map[string]interface{}) (int64, int64, error)
InsertFromMap inserts a single record into the given table using a key-value map of column names and their corresponding values.
Parameters:
- dbctx: DB context.
- tableName: target table.
- data: map of column names to values.
Returns:
- rows affected
- last insert ID
- error if any.
func InsertFromStruct ¶
InsertFromStruct inserts a single record into the given table using field tags from a struct. Supports "omitempty" and "marshaljson" in struct tags.
Parameters:
- dbctx: DB context.
- tableName: target table.
- data: struct with db-tagged fields.
Returns:
- rows affected
- last insert ID
- error if any.
func MultiInsertFromStructsArray ¶
func MultiInsertFromStructsArray[T any](dbctx *DBContext, tableName string, data []T) (int64, error)
MultiInsertFromStructsArray performs a bulk insert operation using a slice of structs. Fields are inferred from struct tags, and default values are handled using `omitempty`. Supports JSON marshaling via tag option "marshaljson".
Parameters:
- dbctx: database context.
- tableName: target table.
- data: slice of structs to insert.
Returns:
- rows affected
- error if any.
func SetConnectionConfig ¶
func SetConnectionConfig(clusterName string, config *ConnectionConfig)
func SoftDeleteByPrimaryKey ¶
func SoftDeleteByPrimaryKey(dbctx *DBContext, tableName, deleteCol, pkCol string, value interface{}) (int64, error)
SoftDeleteByPrimaryKey sets a soft-delete flag column to 1 for the given primary key.
Parameters:
- dbctx: DB context.
- tableName: name of the table.
- deleteCol: soft delete column name.
- pkCol: primary key column name.
- value: value of the primary key.
Returns:
- number of rows updated
- error if any.
func UpdateFromMap ¶
func UpdateFromMap(dbctx *DBContext, tableName string, data map[string]interface{}, where string, params ...interface{}) (int64, error)
UpdateFromMap updates rows in a table using a map of column-value pairs and a WHERE clause.
Parameters:
- dbctx: DB context.
- tableName: name of the table.
- data: map of columns to new values.
- where: WHERE clause string (without "WHERE").
- params: parameters for WHERE clause.
Returns:
- number of rows affected
- error if any.
Types ¶
type ConnectionConfig ¶
type ConnectionConfig struct {
Host string
Port string
UserName string
Password string
DbName string
MaxOpenConn int64
MaxIdleConn int64
MaxConnLifetime int64
ConnMaxIdleTime int64
}
ConnectionConfig represents the configuration settings for a MySQL database. It provides details about the database's connection parameters as well as various connection pool settings that can be used to optimize the database connections for a specific use case or environment.
Fields:
- Host: The host name or IP address of the MySQL server.
- Port: The port number on which the MySQL server is listening.
- UserName: The username to use when connecting to the MySQL database.
- Password: The password to use when connecting to the MySQL database.
- DbName: The name of the specific MySQL database to connect to.
- MaxOpenConn: The maximum number of open connections to the database. This can be used to control the size of the connection pool.
- MaxIdleConn: The maximum number of idle connections that can be maintained in the connection pool.
- MaxConnLifetime: The maximum duration in seconds a connection can remain open. After this duration, the connection will be closed and removed from the pool.
- ConnMaxIdleTime: The maximum duration in seconds a connection can remain idle before it's closed and removed from the pool.
func GetConnectionConfig ¶
func GetConnectionConfig(clusterName string) *ConnectionConfig
type DBContext ¶
type DBContext struct {
// Tx represents an optional active SQL transaction. If set, all operations
// will be executed using this transaction.
Tx Tx
// Conn is an optional MySQL connection interface. If set and Tx is nil,
// this will be used to execute operations.
Conn MySqlDbInterface
// Cluster is a named MySQL cluster identifier. If both Tx and Conn are nil,
// this name is used to fetch a new database connection via helperMySqlConnector.
Cluster string
// ExecFn is an optional override for executing SQL statements.
// If set, it is invoked instead of the default internal logic.
ExecFn func(query string, args ...any) (sql.Result, error)
// PrepareFn is an optional override for preparing SQL statements.
PrepareFn func(query string) (*sql.Stmt, error)
// QueryFn is an optional override for executing SQL queries that return rows.
QueryFn func(query string, args ...any) (*sql.Rows, error)
}
DBContext defines a contextual wrapper around SQL execution logic. It allows execution via an existing transaction (Tx), a manually provided database connection (Conn), or, if neither is set, establishes a connection using the provided cluster name.
Custom execution behaviors can be injected via ExecFn, PrepareFn, and QueryFn to facilitate mocking or alternative behaviors (e.g., instrumentation).
func (*DBContext) Exec ¶
Exec executes a SQL statement (e.g., INSERT, UPDATE) within the context.
Priority:
- ExecFn (if provided)
- Tx.Exec() (if Tx is set)
- Conn.Exec() (if Conn is set)
- Connects via Cluster and uses Exec()
type MySqlDb ¶
type MySqlDb struct {
// DB is the underlying instance of sql.DB to which MySqlDb delegates its calls.
DB *sql.DB
}
MySqlDb serves as a wrapper around sql.DB, exposing a set of methods for MySQL database operations.
func (*MySqlDb) Begin ¶
Begin starts and returns a new transaction. If an error occurs while initializing the transaction, it will be returned.
Returns:
- *sql.Tx: A pointer to the new transaction.
- error: An error object detailing any issues starting the transaction.
func (*MySqlDb) BeginTx ¶
BeginTx starts a new transaction with the provided context and options. It allows for custom transaction isolation levels and readonly settings.
Parameters:
- ctx: Context for the transaction, which can be used to cancel it.
- opts: Transaction options, such as isolation level.
Returns:
- *sql.Tx: A pointer to the new transaction.
- error: An error object detailing any issues starting the transaction.
func (*MySqlDb) Close ¶
Close terminates the database connection. It delegates the call to the underlying sql.DB's Close method.
Returns: - An error if any issues were encountered while trying to close the connection.
func (*MySqlDb) Conn ¶
Conn returns a single connection from the connection pool. The provided context can be used to cancel or time out the connection request.
Parameters:
- ctx: Context for obtaining the connection.
Returns:
- *sql.Conn: A single database connection.
- error: An error object detailing any issues obtaining the connection.
func (*MySqlDb) Driver ¶
Driver returns the database's underlying driver.
Returns:
- driver.Driver: The database's underlying driver.
func (*MySqlDb) Exec ¶
Exec runs an SQL statement which doesn't return rows, like an INSERT, DELETE, or UPDATE.
Parameters:
- query: The SQL query string to execute.
- args: Parameters for the SQL query.
Returns:
- Result of the executed query.
- Any error encountered during execution.
func (*MySqlDb) ExecContext ¶
func (mdb *MySqlDb) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
ExecContext behaves like Exec but allows for a provided context.
Parameters:
- ctx: Context to use for the execution.
- query: The SQL query string to execute.
- args: Parameters for the SQL query.
Returns:
- Result of the executed query.
- Any error encountered during execution.
func (*MySqlDb) Ping ¶
Ping checks the database connection for liveness. This is useful for ensuring that the connection to the database is still active and operational.
Returns: - An error if the connection isn't alive or any other issues are encountered.
func (*MySqlDb) PingContext ¶
PingContext checks the database connection for liveness, with the given context. Useful when you want to provide a timeout or cancel the operation.
Parameters:
- ctx: Context to use for the Ping operation.
Returns:
- An error if the connection isn't alive, context times out, or any other issues arise.
func (*MySqlDb) Prepare ¶
Prepare creates a prepared statement for later queries or executions. The provided query may contain placeholders for binding parameters.
Parameters:
- query: SQL query string possibly containing placeholders.
Returns:
- *sql.Stmt: A statement object which can be executed with different parameters.
- error: An error object detailing any issues preparing the statement.
func (*MySqlDb) PrepareContext ¶
PrepareContext creates a prepared statement using the provided context. This method allows for the cancellation of statement preparation.
Parameters:
- ctx: Context for the statement preparation.
- query: SQL query string possibly containing placeholders.
Returns:
- *sql.Stmt: A statement object which can be executed with different parameters.
- error: An error object detailing any issues preparing the statement.
func (*MySqlDb) Query ¶
Query executes a provided SQL query with the given arguments and returns a set of rows from the database. The rows should be closed after usage. If no rows are found, it doesn't return an error but `rows.Next` will return `false`.
Parameters:
- query: The SQL query string to execute.
- args: Parameters for the SQL query.
Returns:
- A pointer to the retrieved rows.
- Any error encountered during execution.
func (*MySqlDb) QueryContext ¶
func (mdb *MySqlDb) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
QueryContext behaves like Query but allows for a provided context. The context can be used to cancel or time out the executed SQL query.
Parameters:
- ctx: Context to use for the execution.
- query: The SQL query string to execute.
- args: Parameters for the SQL query.
Returns:
- A pointer to the retrieved rows.
- Any error encountered during execution.
func (*MySqlDb) QueryRow ¶
QueryRow executes a provided SQL query with the given arguments and returns a single row from the database. If no rows are found, it doesn't return an error. If the executed query returns more than one row, only the first row is returned.
Parameters:
- query: The SQL query string to execute.
- args: Parameters for the SQL query.
Returns:
- A pointer to the retrieved single row.
func (*MySqlDb) QueryRowContext ¶
func (mdb *MySqlDb) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
QueryRowContext behaves like QueryRow but allows for a provided context. The context can be used to cancel or time out the executed SQL query.
Parameters:
- ctx: Context to use for the execution.
- query: The SQL query string to execute.
- args: Parameters for the SQL query.
Returns:
- A pointer to the retrieved single row.
func (*MySqlDb) SetConnMaxIdleTime ¶
SetConnMaxIdleTime sets the maximum amount of time a connection may be idle before being closed. Idle connections which exceed this duration will be closed lazily on their next use. If the value is zero, then idle connections are not closed based on the idle time.
Parameters:
- d: Duration value representing the maximum idle time of a connection.
func (*MySqlDb) SetConnMaxLifetime ¶
SetConnMaxLifetime sets the maximum amount of time a connection may be reused. If a connection is older than this duration, it'll be closed when it's returned to the pool. Expired connections may be closed lazily before reuse.
Parameters:
- d: Duration value representing the maximum lifetime of a connection.
func (*MySqlDb) SetMaxIdleConns ¶
func (*MySqlDb) SetMaxOpenConns ¶
SetMaxOpenConns sets the maximum number of open connections to the database. If this limit is exceeded, new connections will be blocked until one of the existing connections is returned to the pool. If non-positive, there's no limit on the number of open connections.
Parameters:
- n: Maximum number of open connections to the database.
type MySqlDbConnector ¶
type MySqlDbConnector struct {
// DB represents a wrapped instance of sql.DB for MySQL database operations.
DB *MySqlDb
}
MySqlDbConnector serves as a wrapper around the MySqlDb type, providing an interface for establishing a connection to a MySQL database.
func (*MySqlDbConnector) Open ¶
func (mdbc *MySqlDbConnector) Open(driverName string, dataSourceName string) (MySqlDbInterface, error)
Open establishes a new connection to a MySQL database using the specified driver and data source name. The function will create an instance of MySqlDb and assigns it to the DB field of the MySqlDbConnector.
Parameters:
- driverName: Name of the driver used for the database connection.
- dataSourceName: Connection string that contains information about the database.
Returns:
- A pointer to the MySqlDbInterface instance which wraps the sql.DB.
- An error if any issues were encountered while trying to establish the connection.
type MySqlDbConnectorInterface ¶
type MySqlDbConnectorInterface interface {
Open(driverName string, dataSourceName string) (MySqlDbInterface, error)
}
MySqlDbConnectorInterface defines the behavior required for establishing connections with a MySQL database. Implementers of this interface are expected to provide the specific logic to open a connection based on the provided driver and data source name.
type MySqlDbInterface ¶
type MySqlDbInterface interface {
Ping() error
PingContext(ctx context.Context) error
Exec(query string, args ...interface{}) (sql.Result, error)
ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
Query(query string, args ...interface{}) (*sql.Rows, error)
QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
QueryRow(query string, args ...interface{}) *sql.Row
QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
SetConnMaxLifetime(d time.Duration)
SetMaxIdleConns(n int)
SetMaxOpenConns(n int)
SetConnMaxIdleTime(d time.Duration)
Stats() sql.DBStats
Begin() (*sql.Tx, error)
BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
Prepare(query string) (*sql.Stmt, error)
PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
Driver() driver.Driver
Conn(ctx context.Context) (*sql.Conn, error)
Close() error
}
MySqlDbInterface represents a generalized interface for interacting with a database. It abstracts common database operations, making it easier to work with different database systems and allowing for easier mocking in tests.
func Connect ¶
func Connect(connector MySqlDbConnectorInterface, clusterName string) (MySqlDbInterface, error)
Connect establishes a connection to a database cluster specified by the given clusterName. If a connection to the cluster already exists, the existing connection is reused. This method uses a singleton pattern to ensure only one connection instance per clusterName.
Parameters:
- clusterName: The name of the database cluster to connect to.
Returns:
- *MySqlDb: A pointer to the established database connection. If a connection to the given clusterName already exists, the existing connection will be returned.
- error: An error object that describes the reason for any connection failures. It returns nil if the connection was successful.
Note: This method is thread-safe and uses mutexes to handle concurrent access.
func ConnectContext ¶
func ConnectContext(ctx context.Context, connector MySqlDbConnectorInterface, clusterName string) (MySqlDbInterface, error)
ConnectContext establishes or reuses a MySQL connection for clusterName. The supplied context is honored while opening and pinging a new connection.
type ReadQueryInput ¶
type Tx ¶
type Tx interface {
Exec(query string, args ...any) (sql.Result, error)
Prepare(query string) (*sql.Stmt, error)
Query(query string, args ...any) (*sql.Rows, error)
}
TxF defines the interface for executing SQL statements within a transaction context. It mirrors the Exec method from *sql.Tx to allow mocking in unit tests.
Source Files