database

package
v0.19.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 12, 2025 License: AGPL-3.0 Imports: 8 Imported by: 0

README

Database Package

This package provides database interaction functionalities for the Dracory framework. It offers a set of tools for interacting with various database systems.

Usage

This package provides functionalities for opening database connections, executing queries, inserting data, managing transactions, and more. It can be used to interact with various database systems.

Core Functions

Context Functions Hierarchy

The database package provides a complete hierarchy of functions for working with database contexts:

Low-level Constructor Functions

  • NewQueryableContext: Core constructor that creates a new queryable context

    // Always creates a new QueryableContext with the given database connection
qCtx := database.NewQueryableContext(context.Background(), db)

  • NewQueryableContextOr: Core implementation that preserves existing queryable contexts

    // If ctx is already a QueryableContext, returns it as is
// Otherwise creates a new one
qCtx := database.NewQueryableContextOr(ctx, db)
User-friendly Shortcut Aliases

The following functions are simply shortcut aliases for the core functions above. They provide the same functionality with shorter, more memorable names:

  • Context: Direct alias for NewQueryableContext - always creates a new context

    // These two lines are functionally identical:
dbCtx := database.Context(context.Background(), db)
dbCtx := database.NewQueryableContext(context.Background(), db)

  • ContextOr: Direct alias for NewQueryableContextOr - preserves existing contexts

    // These two lines are functionally identical:
qCtx := database.ContextOr(ctx, db)
qCtx := database.NewQueryableContextOr(ctx, db)

These aliases are provided for convenience and to make code more readable. The underlying implementation is in the core functions.

Example

  • Example of opening a database connection
db, err := database.Open(database.Options().
     SetDatabaseType(DbDriver).
     SetDatabaseHost(DbHost).
     SetDatabasePort(DbPort).
     SetDatabaseName(DbName).
     SetCharset(`utf8mb4`).
     SetUserName(DbUser).
     SetPassword(DbPass))

if err != nil {
     return err
}

if db == nil {
     return errors.New("db is nil")
}

defer db.Close()
  • Example of executing a raw query
// using DB
dbCtx := Context(context.Background(), db)
rows, err := Query(dbCtx, "SELECT * FROM users")
if err != nil {
     log.Fatalf("Failed to execute query: %v", err)
}
defer rows.Close()

// using transaction
txCtx := Context(context.Background(), tx)
rows, err := Query(txCtx, "SELECT * FROM users")
if err != nil {
     log.Fatalf("Failed to execute query: %v", err)
}
defer rows.Close()
  • Example of inserting data with DB connection
dbCtx := Context(context.Background(), db)
result, err := Execute(dbCtx, "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "john@example.com")
if err != nil {
     log.Fatalf("Failed to insert data: %v", err)
}
  • Example of inserting data with transaction
// Begin a transaction
tx, err := db.BeginTx(context.Background(), nil)
if err != nil {
     log.Fatalf("Failed to begin transaction: %v", err)
}

// Create a queryable context with the transaction
txCtx := Context(context.Background(), tx)

// Execute the query within the transaction
result, err := Execute(txCtx, "INSERT INTO users (name, email) VALUES (?, ?)", "Jane Doe", "jane@example.com")
if err != nil {
     // With transactions, you typically want to roll back on error
     tx.Rollback()
     log.Fatalf("Failed to insert data: %v", err)
}

// If successful, commit the transaction
err = tx.Commit()
if err != nil {
     tx.Rollback()
     log.Fatalf("Failed to commit transaction: %v", err)
}
  • Select rows (as map[string]string)
mappedRows, err := database.SelectToMapString(store.toQuerableContext(ctx), sqlStr, params...)
if err != nil {
     log.Fatalf("Failed to select rows: %v", err)
}
  • Select rows (as map[string]any)
mappedRows, err := database.SelectToMapAny(store.toQuerableContext(ctx), sqlStr, params...)
if err != nil {
     log.Fatalf("Failed to select rows: %v", err)
}

Transactions

The database package supports transactions through the standard Go database/sql package. The QueryableInterface can work with *sql.DB, *sql.Conn, or *sql.Tx (transaction) objects through a context-based approach.

Starting a Transaction
// Get a database connection
db, err := database.Open(database.Options().
     SetDatabaseType(DbDriver).
     SetDatabaseHost(DbHost).
     SetDatabasePort(DbPort).
     SetDatabaseName(DbName).
     SetCharset(`utf8mb4`).
     SetUserName(DbUser).
     SetPassword(DbPass))

if err != nil {
     return err
}
defer db.Close()

// Begin a transaction
tx, err := db.BeginTx(context.Background(), nil)
if err != nil {
     return err
}

// Create a queryable context with the transaction
txCtx := database.Context(context.Background(), tx)
Using Transactions

Once you have a transaction context, you can use it with any of the database functions:

// Execute a query within the transaction
result, err := database.Execute(txCtx, "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "john@example.com")
if err != nil {
     // Roll back the transaction if there's an error
     tx.Rollback()
     return err
}

// Query data within the transaction
rows, err := database.Query(txCtx, "SELECT * FROM users WHERE name = ?", "John Doe")
if err != nil {
     tx.Rollback()
     return err
}
defer rows.Close()

// Process the query results...
Committing or Rolling Back Transactions
// If all operations are successful, commit the transaction
err = tx.Commit()
if err != nil {
     // If commit fails, try to roll back
     tx.Rollback()
     return err
}

// If an error occurs during any operation, roll back the transaction
// tx.Rollback()
Using ContextOr with Transactions

The ContextOr function provides a convenient way to work with contexts that may or may not already be queryable contexts. This is especially useful in functions that might receive either a regular context or a transaction context:

// Function that can work with either a regular context or a transaction context
func GetUserByID(ctx context.Context, db *sql.DB, userID int) (map[string]any, error) {
    // Convert the context to a queryable context if it isn't already one
    qCtx := database.ContextOr(ctx, db)
    
    // If ctx was already a transaction context, it will be used as is
    // If not, a new queryable context with db will be created
    return database.SelectToMapAny(qCtx, "SELECT * FROM users WHERE id = ?", userID)
}

This allows you to write functions that can participate in larger transactions when needed, but can also work independently with a direct database connection.

Using ContextOr in Data Stores

In the Dracory framework, data stores are kept in independent packages with public interfaces and private implementations. The ContextOr function is particularly useful in these store implementations:

// UserStore interface in the users package
type UserStoreInterface interface {
    FindByID(ctx context.Context, id int) (*User, error)
    Create(ctx context.Context, user *User) error
    // Other methods...
}

// Private implementation
type userStore struct {
    db *sql.DB
}

// Implementation using ContextOr to support both regular and transaction contexts
func (store *userStore) FindByID(ctx context.Context, id int) (*User, error) {
    // Convert to queryable context, preserving any existing transaction
    qCtx := database.ContextOr(ctx, store.db)
    
    // Use the queryable context for database operations
    rows, err := database.Query(qCtx, "SELECT * FROM users WHERE id = ?", id)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    // Process results...
}

This pattern allows store methods to be called either with a regular context for independent operations or with a transaction context when multiple operations need to be atomic.

Transaction Best Practices

  1. Error Handling: Always check for errors after each database operation and roll back the transaction if an error occurs.

  2. Defer Rollback: Consider using a deferred rollback that is ignored if the transaction is committed successfully:

    tx, err := db.BeginTx(context.Background(), nil)
if err != nil {
     return err
}

// Defer a rollback in case anything fails
defer func() {
     // The rollback will be ignored if the tx has been committed
     tx.Rollback()
}()

// Perform transaction operations...

// If successful, commit
return tx.Commit()

  3. Transaction Isolation: Be aware of the default transaction isolation level of your database. You can specify a different isolation level when beginning a transaction:

    tx, err := db.BeginTx(context.Background(), &sql.TxOptions{
     Isolation: sql.LevelSerializable,
     ReadOnly: false,
})

  4. Keep Transactions Short: Long-running transactions can cause performance issues and deadlocks. Keep transactions as short as possible.

Documentation

Overview

Package database contains functions to interact with the database.

The package is imported like this: 

import "github.com/gouniverse/base/database"

Index

Constants

View Source 
const DATABASE_TYPE_MSSQL = "mssql"
View Source 
const DATABASE_TYPE_MYSQL = "mysql"
View Source 
const DATABASE_TYPE_POSTGRES = "postgres"
View Source 
const DATABASE_TYPE_SQLITE = "sqlite"

Variables

This section is empty.

Functions

func DatabaseType 

func DatabaseType(q QueryableInterface) string

DatabaseType finds the driver name from database

It returns the type of the database in the following way:

  • "mysql" for MySQL
  • "postgres" for PostgreSQL
  • "sqlite" for SQLite
  • "mssql" for Microsoft SQL Server
  • the full name of the driver otherwise

The function is useful when you want to find the type of the database, without knowing it during compilation.

Note that the implementation uses reflection to get the private fields of sql.Tx and sql.Conn. This is done to support the use of sql.Tx and sql.Conn as QueryableInterface.

Parameters: - q QueryableInterface: the database connection or transaction or connection

Returns: - string: the type of the database

#nosec G103 - we use unsafe deliberately to get private fields of sql.Tx and sql.Conn

func Execute 

func Execute(ctx QueryableContext, sqlStr string, args ...any) (sql.Result, error)

Execute executes a SQL query in the given context and returns a sql.Result containing information about the execution, or an error if the query failed.

The context is used to control the execution of the query, allowing for cancellation and timeout control. It also allows to be used with DB, Tx, and Conn.

Example usage:

result, err := Execute(context.Background(), "UPDATE users SET name = ? WHERE id = ?", "John Doe", 1)

Parameters: - ctx (context.Context): The context to use for the query execution. - sqlStr (string): The SQL query to execute. - args (any): Optional arguments to pass to the query.

Returns: - sql.Result: A sql.Result object containing information about the execution. - error: An error if the query failed.

func IsQueryableContext 

func IsQueryableContext(ctx context.Context) bool

IsQueryableContext checks if the given context is a QueryableContext.

Parameters: - ctx: The context to check.

Returns: - bool: True if the context is a QueryableContext, false otherwise.

func Open 

func Open(options openOptionsInterface) (*sql.DB, error)

Open opens the database

Note:

  • drivers are not included to this package to prevent size bloat
  • you must add only the required database driver

Drivers: - sqlite add the following includes: ``` _ "modernc.org/sqlite" ``` - mysql add the following includes: ``` _ "github.com/go-sql-driver/mysql" ``` - postgres add the following includes: ``` _ "github.com/lib/pq" ```

Business logic:

  • opens the database based on the driver name
  • each driver has its own set of parameters

Parameters: - options openOptionsInterface

Returns: - *sql.DB: the database connection - error: the error if any

func Options 

func Options() openOptionsInterface

func Query 

func Query(ctx QueryableContext, sqlStr string, args ...any) (*sql.Rows, error)

Query executes a SQL query in the given context and returns a *sql.Rows object containing the query results.

The context is used to control the execution of the query, allowing for cancellation and timeout control. It also allows to be used with DB, Tx, and Conn.

Example usage:

rows, err := Query(context.Background(), "SELECT * FROM users")

Parameters: - ctx (context.Context): The context to use for the query execution. - sqlStr (string): The SQL query to execute. - args (any): Optional arguments to pass to the query.

Returns: - *sql.Rows: A *sql.Rows object containing the query results. - error: An error if the query failed.

func SelectToMapAny 

func SelectToMapAny(ctx QueryableContext, sqlStr string, args ...any) ([]map[string]any, error)

SelectToMapAny executes a SQL query in the given context and returns a slice of maps, where each map represents a row of the query results. The keys of the map are the column names of the query, and the values are the values of the columns.

The context is used to control the execution of the query, allowing for cancellation and timeout control. It also allows to be used with DB, Tx, and Conn.

If the query returns no rows, the function returns an empty slice.

Example usage:

listMap, err := SelectToMapAny(context.Background(), "SELECT * FROM users")

Parameters: - ctx (context.Context): The context to use for the query execution. - sqlStr (string): The SQL query to execute. - args (any): Optional arguments to pass to the query.

Returns: - []map[string]any: A slice of maps containing the query results. - error: An error if the query failed.

func SelectToMapString 

func SelectToMapString(ctx QueryableContext, sqlStr string, args ...any) ([]map[string]string, error)

SelectToMapString executes a SQL query in the given context and returns a slice of maps, where each map represents a row of the query results. The keys of the map are the column names of the query, and the values are the values of the columns as strings.

The context is used to control the execution of the query, allowing for cancellation and timeout control. It also allows to be used with DB, Tx, and Conn.

If the query returns no rows, the function returns an empty slice.

Example usage:

listMap, err := SelectToMapString(context.Background(), "SELECT * FROM users")

Parameters: - ctx (context.Context): The context to use for the query execution. - sqlStr (string): The SQL query to execute. - args (any): Optional arguments to pass to the query.

Returns: - []map[string]string: A slice of maps containing the query results. - error: An error if the query failed.

Types

type QueryableContext 

type QueryableContext struct {
	context.Context
	// contains filtered or unexported fields
}

QueryableContext extends the context.Context interface with a queryable field. The queryable field may be of type *sql.DB, *sql.Conn, or *sql.Tx.

func Context 

func Context(ctx context.Context, queryable QueryableInterface) QueryableContext

Context returns a new context with the given QueryableInterface. This is a direct alias/shortcut for NewQueryableContext.

Example: 

ctx := database.Context(context.Background(), tx)
// is identical to
ctx := database.NewQueryableContext(context.Background(), tx)

Parameters: - ctx: The parent context. - queryable: The QueryableInterface to be associated with the context.

Returns: - QueryableContext: A new context with the given QueryableInterface.

func ContextOr added in v0.18.0 

func ContextOr(ctx context.Context, queryable QueryableInterface) QueryableContext

ContextOr returns the existing QueryableContext if the provided context is already a QueryableContext, or creates a new one with the given QueryableInterface. This is a direct alias/shortcut for NewQueryableContextOr.

Example: 

// This will use the existing QueryableContext if ctx is already one,
// or create a new one with db if it's not
qCtx := database.ContextOr(ctx, db)
// is identical to
qCtx := database.NewQueryableContextOr(ctx, db)

Parameters: - ctx: The parent context, which may or may not be a QueryableContext. - queryable: The QueryableInterface to be associated with the context if a new one is created.

Returns: - QueryableContext: Either the existing QueryableContext or a new one.

func NewQueryableContext 

func NewQueryableContext(ctx context.Context, queryable QueryableInterface) QueryableContext

NewQueryableContext returns a new context with the given QueryableInterface.

Note: For convenience, a shortcut alias function 'Context' is provided in funcs.go that calls this function with the same parameters.

func NewQueryableContextOr added in v0.18.0 

func NewQueryableContextOr(ctx context.Context, queryable QueryableInterface) QueryableContext

NewQueryableContextOr returns the existing QueryableContext if the provided context is already a QueryableContext, or creates a new one with the given QueryableInterface.

Note: For convenience, a shortcut alias function 'ContextOr' is provided in funcs.go that calls this function with the same parameters.

func (QueryableContext) IsConn 

func (ctx QueryableContext) IsConn() bool

func (QueryableContext) IsDB 

func (ctx QueryableContext) IsDB() bool

func (QueryableContext) IsTx 

func (ctx QueryableContext) IsTx() bool

func (QueryableContext) Queryable 

func (ctx QueryableContext) Queryable() QueryableInterface

type QueryableInterface 

type QueryableInterface interface {
	// ExecContext executes a SQL query in the given context.
	// It returns a sql.Result object containing information about the execution,
	// or an error if the query failed.
	//
	// The context is used to control the execution of the query, allowing for
	// cancellation and timeout control.
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	// PrepareContext creates a prepared statement for use within a transaction.
	//
	// The returned statement operates within the transaction and will be closed
	// when the transaction has been committed or rolled back.
	//
	// To use an existing prepared statement on this transaction, see [Tx.Stmt].
	//
	// The provided context will be used for the preparation of the context, not
	// for the execution of the returned statement. The returned statement
	// will run in the transaction context.
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

	// QueryContext executes a SQL query in the given context and returns a
	// *sql.Rows object containing the query results.
	//
	// The context is used to control the execution of the query, allowing for
	// cancellation and timeout control.
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

	// QueryRowContext executes a SQL query in the given context and returns a
	// *sql.Row object containing a single row of results.
	//
	// The context is used to control the execution of the query, allowing for
	// cancellation and timeout control.
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Queryable is an interface that defines a set of methods for executing SQL queries on a database or data source.

It can be one of the following: - *sql.DB - *sql.Conn - *sql.Tx

Implementations of this interface provide a way to execute queries in a context, allowing for cancellation and timeout control.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.