blockysql

README

The Go database/sql wrapper (Blocky SQL)

Write once, use with any SQL driver, without driver specific types

The Golang SQL wrapper with driver-specific parameters.

This implementation allows to use the same code for different SQL drivers. It also provides a way to get driver-specific information, such as error codes and details.

The drivers could be opened using the OpenDB function, which returns a *blockysql.DB.

The *blockysql.DB is simply a wrapper around the *sql.DB, which provides the same interface. It also provides a way to get driver-specific information.

What's more it allows to handle query errors without casting the error to the driver-specific error type.

The *blockysql.DB also provides a way to run a function in a transaction,

Usage

package main

import (
    "context"
	
    "github.com/blockysource/blockysql"
    _ "github.com/blockysource/blockysql/pgxblockysql"
)

func main() {
    ctx := context.Background()
    db, err := blockysql.OpenDB(ctx, "mysql://user:password@host:port/dbname?param=value")
    if err != nil {
        panic(err)
    }
    
    // use db as usual
    rows, err := db.QueryContext(ctx, "SELECT * FROM table")
    if err != nil {
        panic(err)
    }
	defer rows.Close()
    
    // ...
    
    // close db
    db.Close()    
}

Getting driver information.

package main 

import (
	"context"
	"fmt"
	
	"github.com/blockysource/blockysql"
    _ "github.com/blockysource/blockysql/pgxblockysql"      
)

func main() {
    ctx := context.Background()
    db, err := blockysql.OpenDB(ctx, "mysql://user:password@host:port/dbname?param=value")
    if err != nil {
        panic(err)
    }
    
    // Get the name of the driver.
	// Prints: 'pgx'
    fmt.Println(db.DriverName()) 

	
    // Get the dialect name from the driver.
	// It could be used to switch implementations based on the database family.
	// I.e. postgres specific queries.
    // Prints: 'postgres' or 'cockroach' (if database is cockroachdb).
    fmt.Println(db.Dialect())
    
    // close db
    db.Close()    
}

Generic error codes and details.

package main

import (
    "context"
    "fmt"
	
    "github.com/blockysource/blockysql"
    "github.com/blockysource/blockysql/bserr"
    _ "github.com/blockysource/blockysql/pgxblockysql"
)

func main() {
    ctx := context.Background()
    db, err := blockysql.OpenDB(ctx, "mysql://user:password@host:port/dbname?param=value")
    if err != nil {
        panic(err)
    }
    
    // use db as usual
    _, err = db.ExecContext(ctx, "INSERT INTO table (id, name) VALUES ($1, $2)", 1, "name")
    if err != nil {
        if db.ErrorCode(err) == bserr.UniqueViolation {
            // handle unique violation
            // i.e. return error to the user.
            if db.HasErrorDetails() {
                // get error details
                // i.e. get the column name that violated the unique constraint.                
                fmt.Println(db.ErrorColumn(err))
            }
        }        
    }  
    
    // close db
    db.Close()    
}

Getting *sql.DB from *blockysql.DB

package main

import (
	"context"
	
	"github.com/blockysource/blockysql"
	_ "github.com/blockysource/blockysql/pgxblockysql"
)

func main() {
    ctx := context.Background()
    db, err := blockysql.OpenDB(ctx, "mysql://user:password@host:port/dbname?param=value")
    if err != nil {
        panic(err)
    }
    
    // get *sql.DB
    sqlDB := db.DB()
    
    // use sqlDB as usual
	
	// close DB
    db.Close() 	
}

Auto Commit/Rollback of transactions.

package main

import (
	"context"
	"database/sql"
	
	"github.com/blockysource/blockysql"
	_ "github.com/blockysource/blockysql/pgxblockysql"
)

func main() {
    ctx := context.Background()
    db, err := blockysql.OpenDB(ctx, "mysql://user:password@host:port/dbname?param=value")
    if err != nil {
        panic(err)
    }
    
    // The execFn function will be executed in a transaction.
	// It could alternatively be a closure, or a method.
    err = db.RunInTransaction(ctx, nil, execFn)
    if err != nil {
        // An error occurred, it can be handled here.
        // The transaction is already rolled back.
        // i.e. db.ErrorCode(err) == bserr.UniqueViolation...
    }
    
    // close db
    db.Close()
}

// execFn is the function that will be executed in a transaction.
func execFn(ctx context.Context, tx *sql.Tx) error {
    // use tx as usual
    _, err := tx.ExecContext(ctx, "INSERT INTO table (id, name) VALUES ($1, $2)", 1, "name")
    if err != nil {
        // Do not handle the transaction as rollback or commit will be called automatically.
        return err
    }
    
    // ...
    return nil
}

Documentation

Index

• Variables
• type DB
  • func OpenDB(ctx context.Context, urlstr string) (*DB, error)
  • func (d *DB) Begin() (*sql.Tx, error)
  • func (d *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
  • func (d *DB) Close() error
  • func (d *DB) Conn(ctx context.Context) (*sql.Conn, error)
  • func (d *DB) DB() *sql.DB
  • func (d *DB) Dialect() string
  • func (d *DB) DriverName() string
  • func (d *DB) ErrorCode(err error) bserr.Code
  • func (d *DB) ErrorColumn(err error) string
  • func (d *DB) ErrorConstraint(err error) string
  • func (d *DB) ErrorTable(err error) string
  • func (d *DB) Exec(query string, args ...any) (sql.Result, error)
  • func (d *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
  • func (d *DB) HasErrorDetails() bool
  • func (d *DB) Ping() error
  • func (d *DB) PingContext(ctx context.Context) error
  • func (d *DB) Prepare(query string) (*sql.Stmt, error)
  • func (d *DB) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
  • func (d *DB) Query(query string, args ...any) (*sql.Rows, error)
  • func (d *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
  • func (d *DB) QueryRow(query string, args ...any) *sql.Row
  • func (d *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
  • func (d *DB) RunInTransaction(ctx context.Context, opts *sql.TxOptions, ...) error
  • func (d *DB) SQLDriver() sqldriver.Driver
  • func (d *DB) SetConnMaxLifetime(dur time.Duration)
  • func (d *DB) SetMaxIdleConns(n int)
  • func (d *DB) SetMaxOpenConns(n int)
  • func (d *DB) Stats() sql.DBStats
• type DBURLOpener
• type URLMux
  • func DefaultURLMux() *URLMux
  • func (mux *URLMux) DBSchemes() []string
  • func (mux *URLMux) OpenDB(ctx context.Context, urlstr string) (*DB, error)
  • func (mux *URLMux) RegisterDB(scheme string, opener DBURLOpener)
  • func (mux *URLMux) ValidDBScheme(scheme string) bool

Variables

var NewDB = newDB

NewDB creates a new instance of DB.

Types

type DB

type DB struct {
	// contains filtered or unexported fields
}

DB is a driver specific wrapper over the database/sql.DB.

func OpenDB

func OpenDB(ctx context.Context, urlstr string) (*DB, error)

OpenDB opens the database identified by the URL given. i.e. "mysql://user:password@localhost:3306/database" OpenDB is safe to call from multiple goroutines.

func (*DB) Begin

func (d *DB) Begin() (*sql.Tx, error)

Begin starts a transaction.

func (*DB) BeginTx

func (d *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

BeginTx starts a transaction with the provided options.

func (*DB) Close

func (d *DB) Close() error

Close closes the database and prevents new queries from starting.

func (*DB) Conn

func (d *DB) Conn(ctx context.Context) (*sql.Conn, error)

Conn returns a single connection by either opening a new connection or returning an existing connection from the connection pool.

func (*DB) DB

func (d *DB) DB() *sql.DB

DB returns the underlying database/sql.DB.

func (*DB) Dialect

func (d *DB) Dialect() string

Dialect returns the dialect of the database connection. For the same driver (i.e.: postgres) a dialect may vary depending on the database server (i.e.: postgres, cockroachdb, yugabyte).

func (*DB) DriverName

func (d *DB) DriverName() string

DriverName returns the name of the driver.

func (*DB) ErrorCode

func (d *DB) ErrorCode(err error) bserr.Code

ErrorCode returns the error code of the given error if the driver supports it.

func (*DB) ErrorColumn

func (d *DB) ErrorColumn(err error) string

ErrorColumn returns the column name of the given error if the driver doesn't support it, it should return an empty string.

func (*DB) ErrorConstraint

func (d *DB) ErrorConstraint(err error) string

ErrorConstraint returns the constraint name of the given error if the driver doesn't support it, it should return an empty string.

func (*DB) ErrorTable

func (d *DB) ErrorTable(err error) string

ErrorTable returns the table name of the given error if the driver doesn't support it, it should return an empty string.

func (*DB) Exec

func (d *DB) Exec(query string, args ...any) (sql.Result, error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*DB) ExecContext

func (d *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*DB) HasErrorDetails

func (d *DB) HasErrorDetails() bool

HasErrorDetails returns true if the driver supports error details, such as column, table and constraint name.

func (*DB) Ping

func (d *DB) Ping() error

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (*DB) PingContext

func (d *DB) PingContext(ctx context.Context) error

PingContext verifies a connection to the database is still alive, establishing a connection if necessary.

func (*DB) Prepare

func (d *DB) Prepare(query string) (*sql.Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

func (*DB) PrepareContext

func (d *DB) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

func (*DB) Query

func (d *DB) Query(query string, args ...any) (*sql.Rows, error)

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryContext

func (d *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)

QueryContext executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryRow

func (d *DB) QueryRow(query string, args ...any) *sql.Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

func (*DB) QueryRowContext

func (d *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called.

func (*DB) RunInTransaction

func (d *DB) RunInTransaction(ctx context.Context, opts *sql.TxOptions, fn func(ctx context.Context, tx *sql.Tx) error) error

RunInTransaction runs the given function in a transaction.

func (*DB) SQLDriver

func (d *DB) SQLDriver() sqldriver.Driver

SQLDriver returns the underlying database/sql/driver.Driver. This is useful for using the database/sql package directly.

func (*DB) SetConnMaxLifetime

func (d *DB) SetConnMaxLifetime(dur time.Duration)

SetConnMaxLifetime sets the maximum amount of time a connection may be reused.

func (*DB) SetMaxIdleConns

func (d *DB) SetMaxIdleConns(n int)

SetMaxIdleConns sets the maximum number of connections in the idle connection pool.

func (*DB) SetMaxOpenConns

func (d *DB) SetMaxOpenConns(n int)

SetMaxOpenConns sets the maximum number of open connections to the database.

func (*DB) Stats

func (d *DB) Stats() sql.DBStats

Stats returns database statistics.

type DBURLOpener

type DBURLOpener interface {
	// OpenDBURL opens a database connection for the given URL.
	OpenDBURL(ctx context.Context, u *url.URL) (*DB, error)
}

DBURLOpener is an interface that allows to open a database connection for a given URL.

type URLMux

type URLMux struct {
	// contains filtered or unexported fields
}

URLMux is URL opener multiplexer. It matches the scheme of the URLs against a set of registered schemes and calls the opener that matches the URL's scheme. See https://gocloud.dev/concepts/urls/ for more information.

func DefaultURLMux

func DefaultURLMux() *URLMux

DefaultURLMux returns the URLMux used by OpenSender.

Driver packages can use this to register their SenderURLOpener on the mux.

func (*URLMux) DBSchemes

func (mux *URLMux) DBSchemes() []string

DBSchemes returns a sorted slice of the registered DB schemes.

func (*URLMux) OpenDB

func (mux *URLMux) OpenDB(ctx context.Context, urlstr string) (*DB, error)

OpenDB calls OpenDBURL with the URL parsed from urlstr. OpenDB is safe to call from multiple goroutines.

func (*URLMux) RegisterDB

func (mux *URLMux) RegisterDB(scheme string, opener DBURLOpener)

RegisterDB registers the opener with the given scheme. If an opener already exists for the scheme, RegisterDB panics.

func (*URLMux) ValidDBScheme

func (mux *URLMux) ValidDBScheme(scheme string) bool

ValidDBScheme returns true iff scheme has been registered for MailProviders.