database

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, and more. It can be used to interact with various database systems.

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

// using DB
dbCtx := Context(context.Background(), db)
_, err := Execute(dbCtx, "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "a4lGw@example.com")
if err != nil {
     log.Fatalf("Failed to insert data: %v", err)
}

// using transaction
txCtx := Context(context.Background(), tx)
_, err := Execute(txCtx, "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "a4lGw@example.com")
if err != nil {
     log.Fatalf("Failed to insert data: %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)
}

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
• func DatabaseType(q QueryableInterface) string
• func Execute(ctx QueryableContext, sqlStr string, args ...any) (sql.Result, error)
• func IsQueryableContext(ctx context.Context) bool
• func Open(options openOptionsInterface) (*sql.DB, error)
• func Options() openOptionsInterface
• func Query(ctx QueryableContext, sqlStr string, args ...any) (*sql.Rows, error)
• func SelectToMapAny(ctx QueryableContext, sqlStr string, args ...any) ([]map[string]any, error)
• func SelectToMapString(ctx QueryableContext, sqlStr string, args ...any) ([]map[string]string, error)
• type QueryableContext
  • func Context(ctx context.Context, queryable QueryableInterface) QueryableContext
  • func NewQueryableContext(ctx context.Context, queryable QueryableInterface) QueryableContext
  • func (ctx QueryableContext) IsConn() bool
  • func (ctx QueryableContext) IsDB() bool
  • func (ctx QueryableContext) IsTx() bool
  • func (ctx QueryableContext) Queryable() QueryableInterface
• type QueryableInterface

Constants

const DATABASE_TYPE_MSSQL = "mssql"

const DATABASE_TYPE_MYSQL = "mysql"

const DATABASE_TYPE_POSTGRES = "postgres"

const DATABASE_TYPE_SQLITE = "sqlite"

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. It is a shortcut for NewQueryableContext.

Example:

ctx := database.Context(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 NewQueryableContext

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

NewQueryableContext returns a new context with the given QueryableInterface.

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.