db

package
v1.10.0 Latest Latest
Warning

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

 Go to latest Published: Jan 27, 2022 License: Apache-2.0 Imports: 15 Imported by: 7

README

GoDoc

DB

DB is a package with helpers and wrappers for interacting with PostgreSQL using the squirrel.Builder.

Package types

  • Transactional is the interface for representing a db connector/query builder that support database transactions.
  • SQLConn is a connector for execution of queries to the PostgreSQL database.
  • PageQuery is the structure for building query with pagination
  • Table is the basis for implementing Querier for some model or table.

SQLConn

This is a database connector. SQLConn supports database transactions.

  • Clone returns sanitized copy of SQLConn instance backed by the same context and db. The result will not be bound to any transaction that the source is currently within.

  • Get casts given squirrel.Sqlizer to raw SQL string with arguments and execute GetRaw.

  • Exec casts given squirrel.Sqlizer to raw SQL string with arguments and execute ExecRaw

  • Select casts given squirrel.SelectBuilder to raw SQL string with arguments and execute SelectRaw.

  • GetRaw executes given SQL Select and returns only one record.

  • ExecRaw executes any given SQL query.

  • SelectRaw executes given SQL Select and returns all records.

PageQuery

Table

Usage

  • Connect and execute some query:
package main

import (
    "log"

    "github.com/lancer-kit/armory/db"
)

func main() {
    // initialize SQLConn singleton
    err := db.Init("postgres://user:user@localhost/user?sslmode=disable", nil)
    if err != nil {
        panic(err)
    }
    sqlConn := db.GetConnector()
    err = sqlConn.ExecRaw(`CREATE TABLE IF NOT EXIST users(
    id SERIAL, name VARCHAR(64), email VARCHAR(64), age INTEGER)`, nil)
    if err != nil {
        panic(err)
    }
}

Documentation

Overview

Example 
package main

import (
	"database/sql"

	sq "github.com/Masterminds/squirrel"
)

type User struct {
	ID    int64  `db:"id" json:"id"`
	Name  string `db:"name" json:"name"`
	Email string `db:"email" json:"email"`
	Age   int    `db:"age" json:"age"`

	RowCount int64 `db:"row_count" json:"-"`
}

// UserQ is an interface for
// interacting with the `users` table.
type UserQ struct {
	*SQLConn
	Table
}

// NewUserQ returns the new instance of the `UserQ`.
func NewUserQ(conn *SQLConn) *UserQ {
	return &UserQ{
		SQLConn: conn.Clone(),
		Table: Table{
			Name:     "users",
			QBuilder: sq.Select("*").From("users"),
		},
	}
}

// Insert adds new row into the `users` table.
func (q *UserQ) Insert(user *User) error {
	query := sq.Insert(q.Name).SetMap(map[string]interface{}{
		"name":  user.Name,
		"email": user.Email,
		"age":   user.Age,
	})

	idi, err := q.SQLConn.Insert(query)
	user.ID = idi.(int64)
	return err
}

// ByAge adds in the query filter by the `age` column.
func (q *UserQ) ByAge(age int) *UserQ {
	q.QBuilder = q.QBuilder.Where("age = ?", age)
	return q
}

// SetPage sets the limitation of select
// by the parameters from `PageQuery`.
func (q *UserQ) SetPage(pq *PageQuery) *UserQ {
	q.Table.SetPage(pq)
	return q
}

// Select gets all records
func (q *UserQ) Select() ([]User, error) {
	dest := make([]User, 0, 1)
	q.ApplyPage("id")

	err := q.SQLConn.Select(q.QBuilder, &dest)
	if err == sql.ErrNoRows {
		return dest, nil
	}

	return dest, err
}

func main() {
	// initialize SQLConn
	sqlConn, err := NewConnector(
		Config{ConnURL: "postgres://postgres:postgres@localhost/postgres?sslmode=disable"}, nil)
	if err != nil {
		panic(err)
	}

	err = sqlConn.ExecRaw(`CREATE TABLE IF NOT EXIST users(
    id SERIAL, name VARCHAR(64), email VARCHAR(64), age INTEGER)`, nil)
	if err != nil {
		panic(err)
	}
	user := &User{
		Name:  "Mike",
		Email: "mike@example.com",
		Age:   42,
	}

	q := NewUserQ(sqlConn)
	err = q.Insert(user)
	if err != nil {
		panic(err)
	}
}

Output:

Index

Examples

Constants

View Source 
const (
	DriverPostgres = "postgres"
	DriverMySQL    = "mysql"
)
View Source 
const (
	// DefaultPageSize - the standard number of records per page
	DefaultPageSize uint64 = 20
	// MaxPageSize - the maximum number of records per page,
	// if you need more, then use selection without a page.
	MaxPageSize uint64 = 1000
	// OrderAscending specifies the sort order in ascending direction.
	OrderAscending = "asc"
	// OrderDescending specifies the sort order in descending direction.
	OrderDescending = "desc"
)

Variables

This section is empty.

Functions

func Init 

func Init(dbConnStr string, logger *logrus.Entry) error

Init initializes new connector with database.

func MigrateMultiSet added in v1.9.0 

func MigrateMultiSet(connStr, driver string, dir MigrateDir, sets ...Migrations) (int, error)

MigrateMultiSet applies the given list of sets of migrations to the SQL database. Some cases for the multiset: 

— separate sets of migrations for different database schemas;
— separate sets of migrations for different purposes (ex. one for tables, another for functions & procedures).

Each set bounded to own table with migration history. When applying multiset UP, executor begins from the first set to last. When applying multiset DOWN, executor begins from the last set to first.

func MigrateSet added in v1.9.0 

func MigrateSet(connStr, driver string, dir MigrateDir, set Migrations) (int, error)

MigrateSet applies given set of migrations to SQL database.

Types

type Config 

type Config struct {
	ConnURL     string            `json:"conn_url" yaml:"conn_url"` // The database connection string.
	InitTimeout int               `json:"dbInitTimeout" yaml:"init_timeout"`
	AutoMigrate bool              `json:"auto_migrate" yaml:"auto_migrate"`
	WaitForDB   bool              `json:"wait_for_db" yaml:"wait_for_db"`
	Params      *ConnectionParams `json:"params" yaml:"params"`
}

func (*Config) URL added in v1.7.0 

func (cfg *Config) URL() string

func (Config) Validate added in v1.7.0 

func (cfg Config) Validate() error

Validate is an implementation of Validatable interface from ozzo-validation.

type ConnectionParams added in v1.7.0 

type ConnectionParams struct {
	MaxIdleConns int `json:"max_idle" yaml:"max_idle"`
	MaxOpenConns int `json:"max_open" yaml:"max_open"`
	// MaxLifetime time.Duration in Millisecond
	MaxLifetime int64 `json:"max_lifetime" yaml:"max_lifetime"`
}

type Count added in v1.8.0 

type Count struct {
	Count int64 `db:"count"`
}

Count is a model for the count select.

type ErrInvalidOrder 

type ErrInvalidOrder string

func (ErrInvalidOrder) Error added in v1.9.0 

func (e ErrInvalidOrder) Error() string

type ErrTooBigPage 

type ErrTooBigPage uint64

func (ErrTooBigPage) Error added in v1.9.0 

func (e ErrTooBigPage) Error() string

type MigrateDir 

type MigrateDir string

MigrateDir represents a direction in which to perform schema migrations. 

const (
	// MigrateUp causes migrations to be run in the "up" direction.
	MigrateUp MigrateDir = "up"
	// MigrateDown causes migrations to be run in the "down" direction.
	MigrateDown MigrateDir = "down"
)

type Migrations added in v1.9.0 

type Migrations struct {
	// Table name of the table used to store migration info.
	Table string
	// Schema that the migration table be referenced.
	Schema string
	// EnablePatchMode enables patch mode for migrations
	// Now it requires a new migration name format: 0001_00_name.sql and new table structure for save migrations
	EnablePatchMode bool
	// IgnoreUnknown skips the check to see if there is a migration
	// ran in the database that is not in MigrationSource.
	IgnoreUnknown bool
	// Assets are sql-migrate.MigrationSource assets. Ex.:
	// 	- migrate.HttpFileSystemMigrationSource
	// 	- migrate.FileMigrationSource
	// 	- migrate.AssetMigrationSource
	// 	- migrate.PackrMigrationSource
	Assets migrate.MigrationSource
}

Migrations is a configuration of migration set.

type MigrationsExecutor added in v1.9.0 

type MigrationsExecutor struct {
	Migrations
	// contains filtered or unexported fields
}

MigrationsExecutor is a helper that initializes database connection and applies migrations to the database.

func NewExecutor added in v1.9.0 

func NewExecutor(connStr, driver string) (*MigrationsExecutor, error)

NewExecutor returns new MigrationsExecutor.

func (*MigrationsExecutor) Migrate added in v1.9.0 

func (executor *MigrationsExecutor) Migrate(dir MigrateDir) (int, error)

Migrate connects to the database and applies migrations.

func (*MigrationsExecutor) SetMigrations added in v1.9.0 

func (executor *MigrationsExecutor) SetMigrations(set Migrations) *MigrationsExecutor

SetMigrations sets Migrations for executor.

type PageQuery 

type PageQuery struct {
	Order    string `json:"order" schema:"order"`
	Page     uint64 `json:"page" schema:"page"`
	PageSize uint64 `json:"pageSize" schema:"pageSize"`
	OrderBy  string `json:"orderBy" schema:"orderBy"`
}

PageQuery is the structure for building query with pagination.

func ParsePageQuery 

func ParsePageQuery(values url.Values) (pq PageQuery, err error)

ParsePageQuery extracts `PageQuery` from the url Query Values.

func (*PageQuery) Apply 

func (pq *PageQuery) Apply(query sq.SelectBuilder, orderColumn string) sq.SelectBuilder

Apply sets limit and ordering params to SelectBuilder. DEPRECATED: use ApplyByOrderColumn instead

func (*PageQuery) ApplyByOrderColumn added in v1.8.0 

func (pq *PageQuery) ApplyByOrderColumn(query sq.SelectBuilder) sq.SelectBuilder

ApplyByOrderColumn sets limit and ordering params to SelectBuilder.

func (*PageQuery) FromRQuery 

func (pq *PageQuery) FromRQuery(query url.Values) error

FromRQuery extracts `PageQuery` from the url Query Values and validates.

func (*PageQuery) Offset 

func (pq *PageQuery) Offset() uint64

Offset calculates select offset.

func (*PageQuery) Validate 

func (pq *PageQuery) Validate() error

Validate checks is correct values and sets default values if `PageQuery` empty. WARN: the receiver MUST be a pointer so that the default values works

type SQLConn 

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

SQLConn is a connector for interacting with the database.

func GetConnector 

func GetConnector() *SQLConn

GetConnector returns an instance of the SQLConn.

func NewConnector added in v1.7.0 

func NewConnector(cfg Config, logger *logrus.Entry) (*SQLConn, error)

NewConnector returns a new instance of the SQLConn.

func NewSQLConn 

func NewSQLConn(db *sqlx.DB, logger *logrus.Entry) *SQLConn

NewSQLConn create new connector by passed connection params

func (*SQLConn) Begin 

func (conn *SQLConn) Begin() error

Begin binds this SQLConn to a new transaction.

func (*SQLConn) Clone 

func (conn *SQLConn) Clone() *SQLConn

Clone clones the receiver, returning a new instance backed by the same context and db. The result will not be bound to any transaction that the source is currently within.

func (*SQLConn) Commit 

func (conn *SQLConn) Commit() error

Commit commits the current transaction.

func (*SQLConn) Exec 

func (conn *SQLConn) Exec(sqq sq.Sqlizer) error

Exec compile `sqq` to SQL and runs query.

func (*SQLConn) ExecRaw 

func (conn *SQLConn) ExecRaw(query string, args ...interface{}) error

ExecRaw runs `query` with `args`.

func (*SQLConn) Get 

func (conn *SQLConn) Get(sqq sq.Sqlizer, dest interface{}) error

Get compile `sqq` to raw sql query, executes it and write first row into the `dest`.

func (*SQLConn) GetRaw 

func (conn *SQLConn) GetRaw(dest interface{}, query string, args ...interface{}) error

GetRaw executes a raw sql query and write first row into the `dest`.

func (*SQLConn) InTx added in v1.6.0 

func (conn *SQLConn) InTx() bool

InTx checks is transaction started. Return true if it is a transaction, and false if it is not a transaction

func (*SQLConn) Insert 

func (conn *SQLConn) Insert(sqq sq.InsertBuilder) (id interface{}, err error)

Insert compile `sqq` to SQL and runs query. Return last inserted id

func (*SQLConn) Rollback 

func (conn *SQLConn) Rollback() error

Rollback rolls back the current transaction

func (*SQLConn) Select 

func (conn *SQLConn) Select(sqq sq.Sqlizer, dest interface{}) error

Select compile `sqq` to raw sql query, executes it, and write each row into dest, which must be a slice.

func (*SQLConn) SelectRaw 

func (conn *SQLConn) SelectRaw(dest interface{}, query string, args ...interface{}) error

SelectRaw executes a raw sql query, and write each row into dest, which must be a slice.

func (*SQLConn) SetConnMaxLifetime 

func (conn *SQLConn) SetConnMaxLifetime(d int64)

SetConnMaxLifetime changes `ConnMaxLifetime` of the database connector.

func (*SQLConn) SetConnParams added in v1.7.0 

func (conn *SQLConn) SetConnParams(params *ConnectionParams)

SetConnParams configures `MaxIdleConns`, `MaxOpenConns` and `ConnMaxLifetime` of the database connector.

func (*SQLConn) SetMaxIdleConns 

func (conn *SQLConn) SetMaxIdleConns(n int)

SetMaxIdleConns changes `MaxIdleConns` of the database connector.

func (*SQLConn) SetMaxOpenConns 

func (conn *SQLConn) SetMaxOpenConns(n int)

SetMaxOpenConns changes `MaxOpenConns` of the database connector.

func (*SQLConn) SetTx 

func (conn *SQLConn) SetTx(tx *sqlx.Tx)

SetTx set new sqlx.Tx

func (*SQLConn) Stats 

func (conn *SQLConn) Stats() sql.DBStats

Stats returns database stats.

func (*SQLConn) Transaction 

func (conn *SQLConn) Transaction(fn func() error) (err error)

Transaction is generic helper method for specific Q's to implement Transaction capabilities

type SecureConfig added in v1.9.0 

type SecureConfig struct {
	Driver      string            `yaml:"driver" json:"driver"`
	Name        string            `yaml:"name" json:"name"`
	Host        string            `yaml:"host" json:"host"`
	Port        int               `yaml:"port"  json:"port"`
	User        noble.Secret      `yaml:"user" json:"user"`
	Password    noble.Secret      `yaml:"password" json:"password"`
	SSL         bool              `yaml:"ssl" json:"ssl"`
	MySQLParams string            `yaml:"my_sql_params" json:"my_sql_params,omitempty"`
	InitTimeout int               `yaml:"init_timeout" json:"init_timeout"`
	AutoMigrate bool              `yaml:"auto_migrate" json:"auto_migrate"`
	WaitForDB   bool              `yaml:"wait_for_db" json:"wait_for_db"`
	Params      *ConnectionParams `yaml:"params" json:"params"`
}

SecureConfig configuration with secrets support nolint:maligned

func (SecureConfig) Config added in v1.9.0 

func (d SecureConfig) Config() Config

Config returns lancer db Config

func (SecureConfig) ConnectionString added in v1.9.0 

func (d SecureConfig) ConnectionString() string

ConnectionString returns Connection String for selected driver

func (SecureConfig) Validate added in v1.9.0 

func (d SecureConfig) Validate() error

Validate is an implementation of Validatable interface from ozzo-validation.

type Table 

type Table struct {
	Name    string
	Alias   string
	Columns string

	DB        sq.BaseRunner
	QBuilder  sq.SelectBuilder
	GQBuilder sq.SelectBuilder
	IQBuilder sq.InsertBuilder
	UQBuilder sq.UpdateBuilder
	DQBuilder sq.DeleteBuilder
	Page      *PageQuery
}

Table is the basis for implementing QueryModel for some model or table.

func NewTable added in v1.8.0 

func NewTable(table, alias, columns string) Table

NewTable initializes new table query helper.

func (Table) AliasedName 

func (t Table) AliasedName() string

AliasedName returns table name with the alias postfix.

func (*Table) ApplyPage 

func (t *Table) ApplyPage(orderColumn string)

ApplyPage adds limit/offset and/or order to the queryBuilder.

func (*Table) CountQuery added in v1.8.0 

func (t *Table) CountQuery() (string, []interface{}, error)

CountQuery sanitizes query and replaces regular select to count select. Returns SQL statement and arguments for query.

func (*Table) GetCount added in v1.8.0 

func (t *Table) GetCount(sqlConn *SQLConn) (int64, error)

GetCount sanitizes query, replaces regular select to count select and run it. Returns total number of records for query.

func (*Table) SelectWithCount added in v1.8.0 

func (t *Table) SelectWithCount(sqlConn *SQLConn, dest interface{}, orderCol string, query *PageQuery) (int64, error)

SelectWithCount this method provides the ability to make a selection for a pagination request. How it works: - apply paging parameters to SQL query with given `PageQuery` and `orderColumn`; - make a request to the database without fetching data, only get the total number of records for the passed request; - make a selection of all records with the specified filters, limit and offset (i.e. the necessary page).

func (*Table) SetPage 

func (t *Table) SetPage(pq *PageQuery)

SetPage is a setter for Page field.

type Transactional 

type Transactional interface {
	// Begin starts a database transaction.
	Begin() error
	// Commit commits the transaction.
	Commit() error
	// Rollback aborts the transaction.
	Rollback() error
	// InTx checks is transaction started. Return true if it is a transaction, and false if it is not a transaction
	InTx() bool
}

Transactional is the interface for representing a db connector/query builder that support database transactions.

type WBase 

type WBase struct {
	DB     Transactional
	Logger *logrus.Entry
	Ctx    context.Context
	Err    error
}

WBase is a base structure for worker which uses database and need transactions support.

func (*WBase) DBTxRollback 

func (s *WBase) DBTxRollback()

func (*WBase) Recover 

func (s *WBase) Recover()

Source Files

View all Source files

Directories

Path Synopsis

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.