sb

package module
v0.8.0 Latest Latest
Warning

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

 Go to latest Published: Dec 1, 2024 License: MIT Imports: 17 Imported by: 36

README

SB Open in Gitpod

tests Go Report Card PkgGoDev

A simplified SQL builder (with limited functionality).

For a full SQL builder functionality check: https://doug-martin.github.io/goqu

Includes a wrapper for the mainstream DB package to allow transparent working with transactions.

Installation

go get -u github.com/gouniverse/sb

Example Create Table SQL

import "github.com/gouniverse/sb"

sql := sb.NewBuilder(DIALECT_MYSQL).
	Table("users").
	Column(Column{
		Name:       "id",
		Type:       COLUMN_TYPE_STRING,
		Length:     40,
		PrimaryKey: true,
	}).
	Column(Column{
		Name: "image",
		Type: COLUMN_TYPE_BLOB,
	}).
	Column(Column{
		Name: "price_default",
		Type: COLUMN_TYPE_DECIMAL,
	}).
	Column(Column{
		Name:     "price_custom",
		Type:     COLUMN_TYPE_DECIMAL,
		Length:   12,
		Decimals: 10,
	}).
	Column(Column{
		Name: "created_at",
		Type: COLUMN_TYPE_DATETIME,
	}).
	Column(Column{
		Name:     "deleted_at",
		Type:     COLUMN_TYPE_DATETIME,
		Nullable: true,
	}).
	Create()

Example Table Drop SQL

sql := NewBuilder(DIALECT_MYSQL).
	Table("users").
	Drop()

Example Insert SQL

sql := sb.NewBuilder(DIALECT_MYSQL).
	Table("cache").
	Insert(map[string]string{
		"ID":         uid.NanoUid(),
		"CacheKey":   token,
		"CacheValue": string(emailJSON),
		"ExpiresAt":  expiresAt.Format("2006-01-02T15:04:05"),
		"CreatedAt":  time.Now().Format("2006-01-02T15:04:05"),
		"UpdatedAt":  time.Now().Format("2006-01-02T15:04:05"),
	})

Example Delete SQL

sql := sb.NewBuilder(DIALECT_MYSQL).
	Table("user").
	Where(sb.Where{
		Column: "id",
		Operator: "==",
		Value: "1",
	}).
	Limit(1).
	Delete()

Initiating Database Instance

  1. From existing Go DB instance
myDb := sb.NewDatabaseFromDb(sqlDb, DIALECT_MYSQL)
  1. From driver
myDb = sql.NewDatabaseFromDriver("sqlite3", "test.db")

Example SQL Execute

myDb := sb.NewDatabaseFromDb(sqlDb, DIALECT_MYSQL)
err := myDb.Exec(sql)

Example Transaction

import _ "modernc.org/sqlite"

myDb = sb.NewDatabaseFromDriver("sqlite3", "test.db")

myDb.BeginTransaction()

err := Database.Exec(sql1)

if err != nil {
	myDb.RollbackTransaction()
	return err
}

err := Database.Exec(sql2)

if err != nil {
	myDb.RollbackTransaction()
	return err
}

myDB.CommitTransaction()

Example Create View SQL

selectSQL := sb.NewBuilder(DIALECT_POSTGRES).
	Table("users").
	Select([]string{"FirstName", "LastName"})

createViewSql := NewBuilder(DIALECT_POSTGRES).
	View("v_users").
	ViewColumns([]string{"first_name", "last_name"}).
	ViewSQL(selectSQL).
	Create()

Example Create View If Not Exists SQL

selectSQL := sb.NewBuilder(DIALECT_POSTGRES).
	Table("users").
	Select([]string{"FirstName", "LastName"})

createViewSql := NewBuilder(DIALECT_POSTGRES).
	View("v_users").
	ViewColumns([]string{"first_name", "last_name"}).
	ViewSQL(selectSQL).
	CreateIfNotExists()

Example Drop View SQL

dropiewSql := ab.NewBuilder(DIALECT_POSTGRES).
	View("v_users").
	Drop()

Example Select as Map

Executes a select query and returns map[string]any


mapAny := myDb.SelectToMapAny(sql)

Executes a select query and returns map[string]string


mapString := myDb.SelectToMapAny(sql)

Developers

podman run -it --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=test -e MYSQL_USER=test -e MYSQL_PASSWORD=test mysql:latest

podman run -it --rm -p 5432:5432 -e POSTGRES_PASSWORD=test -e POSTGRES_DB=test -e POSTGRES_USER=test postgres:latest

Similar

TODO

  • github.com/stapelberg/postgrestest

Documentation

Index

Constants

View Source 
const ASC = "asc"

Sortable

View Source 
const COLUMN_ATTRIBUTE_AUTO = "auto"

Column Attributes

View Source 
const COLUMN_ATTRIBUTE_DECIMALS = "decimals"
View Source 
const COLUMN_ATTRIBUTE_LENGTH = "length"
View Source 
const COLUMN_ATTRIBUTE_NULLABLE = "nullable"
View Source 
const COLUMN_ATTRIBUTE_PRIMARY = "primary"
View Source 
const COLUMN_TYPE_BLOB = "blob"

Column Types

View Source 
const COLUMN_TYPE_DATE = "date"
View Source 
const COLUMN_TYPE_DATETIME = "datetime"
View Source 
const COLUMN_TYPE_DECIMAL = "decimal"
View Source 
const COLUMN_TYPE_FLOAT = "float"
View Source 
const COLUMN_TYPE_INTEGER = "integer"
View Source 
const COLUMN_TYPE_LONGTEXT = "longtext"
View Source 
const COLUMN_TYPE_STRING = "string"
View Source 
const COLUMN_TYPE_TEXT = "text"
View Source 
const DESC = "desc"
View Source 
const DIALECT_MSSQL = "mssql"

Dialects

View Source 
const DIALECT_MYSQL = "mysql"
View Source 
const DIALECT_POSTGRES = "postgres"
View Source 
const DIALECT_SQLITE = "sqlite"
View Source 
const MAX_DATE = "9999-12-31"
View Source 
const MAX_DATETIME = "9999-12-31 23:59:59"
View Source 
const NO = "no"
View Source 
const NULL_DATE = "0002-01-01"

Null time (earliest valid date in Gregorian calendar is 1AD, no year 0)

View Source 
const NULL_DATETIME = "0002-01-01 00:00:00"
View Source 
const YES = "yes"

Common

Variables

This section is empty.

Functions

func DatabaseDriverName 

func DatabaseDriverName(db *sql.DB) string

DatabaseDriverName finds the driver name from database

func TableCreate added in v0.7.0 

func TableCreate(db *sql.DB, tableName string, columns []Column) error

func TableCreateSql added in v0.7.0 

func TableCreateSql(db *sql.DB, tableName string, columns []Column) string

func TableDrop added in v0.7.0 

func TableDrop(ctx database.QueryableContext, tableName string) error

func TableDropIfExists added in v0.7.0 

func TableDropIfExists(ctx database.QueryableContext, tableName string) error

func TableDropIfExistsSql added in v0.7.0 

func TableDropIfExistsSql(ctx database.QueryableContext, tableName string) (string, error)

func TableDropSql added in v0.7.0 

func TableDropSql(ctx database.QueryableContext, tableName string) (string, error)

Types

type Builder 

type Builder struct {
	Dialect string
	// contains filtered or unexported fields
}

func NewBuilder 

func NewBuilder(dialect string) *Builder

func (*Builder) Column 

func (b *Builder) Column(column Column) BuilderInterface

func (*Builder) Create 

func (b *Builder) Create() string

*

  • The create method creates new database or table.
  • If the database or table can not be created it will return false.
  • False will be returned if the database or table already exist.
  • <code>
  • // Creating a new database
  • $database->create(); *
  • // Creating a new table
  • $database->table("STATES")
  • ->column("STATE_NAME","STRING")
  • ->create();
  • </code>
  • @return boolean true, on success, false, otherwise
  • @access public

func (*Builder) CreateIfNotExists 

func (b *Builder) CreateIfNotExists() string

func (*Builder) CreateIndex added in v0.6.0 

func (b *Builder) CreateIndex(indexName string, columnName ...string) string

func (*Builder) Delete 

func (b *Builder) Delete() string

*

  • The delete method deletes a row in a table. For deleting a database
  • or table use the drop method.
  • <code>
  • // Deleting a row
  • sql := builder.Table("STATES").Where("STATE_NAME","=","Alabama").Delete();
  • </code>
  • @return string
  • @access public

Drop deletes a table

func (*Builder) Drop 

func (b *Builder) Drop() string

Drop deletes a table or a view

func (*Builder) DropIfExists 

func (b *Builder) DropIfExists() string

func (*Builder) GroupBy 

func (b *Builder) GroupBy(groupBy GroupBy) BuilderInterface

func (*Builder) Insert 

func (b *Builder) Insert(columnValuesMap map[string]string) string

*

  • The <b>update</b> method updates the values of a row in a table.
  • <code>
  • $updated_user = array("USER_MANE"=>"Mike");
  • $database->table("USERS")->where("USER_NAME","==","Peter")->update($updated_user);
  • </code>
  • @param Array an associative array, where keys are the column names of the table
  • @return int 0 or 1, on success, false, otherwise
  • @access public

func (*Builder) Limit 

func (b *Builder) Limit(limit int64) BuilderInterface

func (*Builder) Offset 

func (b *Builder) Offset(offset int64) BuilderInterface

func (*Builder) OrderBy 

func (b *Builder) OrderBy(columnName, direction string) BuilderInterface

func (*Builder) Select 

func (b *Builder) Select(columns []string) string

* The <b>select</b> method selects rows from a table, based on criteria.

  • <code>
  • // Selects all the rows from the table
  • $db->table("USERS")->select(); *
  • // Selects the rows where the column NAME is different from Peter, in descending order
  • $db->table("USERS")
  • ->where("NAME","!=","Peter")
  • ->orderby("NAME","desc")
  • ->select();
  • </code>
  • @return mixed rows as associative array, false on error
  • @access public

func (*Builder) Table 

func (b *Builder) Table(tableName string) BuilderInterface

func (*Builder) TableColumnAdd added in v0.7.0 

func (b *Builder) TableColumnAdd(tableName string, column Column) (sql string, err error)

func (*Builder) TableColumnRename added in v0.7.0 

func (b *Builder) TableColumnRename(tableName, oldColumnName, newColumnName string) (sql string, err error)

func (*Builder) TableRename added in v0.7.0 

func (b *Builder) TableRename(oldTableName, newTableName string) (sql string, err error)

Rename renames a table or a view

func (*Builder) Truncate added in v0.7.0 

func (b *Builder) Truncate() string

func (*Builder) Update 

func (b *Builder) Update(columnValues map[string]string) string

*

  • The <b>update</b> method updates the values of a row in a table.
  • <code>
  • $updated_user = array("USER_MANE"=>"Mike");
  • $database->table("USERS")->where("USER_NAME","==","Peter")->update($updated_user);
  • </code>
  • @param Array an associative array, where keys are the column names of the table
  • @return int 0 or 1, on success, false, otherwise
  • @access public

func (*Builder) View 

func (b *Builder) View(viewName string) BuilderInterface

func (*Builder) ViewColumns 

func (b *Builder) ViewColumns(columns []string) BuilderInterface

func (*Builder) ViewSQL 

func (b *Builder) ViewSQL(sql string) BuilderInterface

func (*Builder) Where 

func (b *Builder) Where(where Where) BuilderInterface

type BuilderInterface added in v0.7.0 

type BuilderInterface interface {
	Column(column Column) BuilderInterface
	Create() string
	CreateIfNotExists() string
	CreateIndex(indexName string, columnName ...string) string
	Delete() string
	Drop() string
	DropIfExists() string
	Insert(columnValuesMap map[string]string) string
	GroupBy(groupBy GroupBy) BuilderInterface
	Limit(limit int64) BuilderInterface
	Offset(offset int64) BuilderInterface
	OrderBy(columnName string, sortDirection string) BuilderInterface

	Select(columns []string) string
	Table(name string) BuilderInterface
	Update(columnValues map[string]string) string
	View(name string) BuilderInterface
	ViewColumns(columns []string) BuilderInterface
	ViewSQL(sql string) BuilderInterface
	Where(where Where) BuilderInterface

	TableRename(oldTableName string, newTableName string) (string, error)
	TableColumnAdd(tableName string, column Column) (string, error)
	TableColumnRename(tableName, oldColumnName, newColumnName string) (string, error)
}

type Column 

type Column struct {
	Name          string
	Type          string
	Length        int
	Decimals      int
	AutoIncrement bool
	PrimaryKey    bool
	Nullable      bool
	Unique        bool
	Default       string
}

func TableColumns added in v0.7.0 

func TableColumns(ctx database.QueryableContext, tableName string, commonize bool) (columns []Column, err error)

TableColumns returns a list of columns for a given table name

type Database 

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

func (*Database) BeginTransaction 

func (d *Database) BeginTransaction() (err error)

func (*Database) BeginTransactionWithContext 

func (d *Database) BeginTransactionWithContext(ctx context.Context, opts *sql.TxOptions) (err error)

func (*Database) Close 

func (d *Database) Close() (err error)

func (*Database) CommitTransaction 

func (d *Database) CommitTransaction() (err error)

func (*Database) DB 

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

func (*Database) DebugEnable 

func (d *Database) DebugEnable(debug bool)

func (*Database) Exec 

func (d *Database) Exec(sqlStr string, args ...any) (sql.Result, error)

func (*Database) ExecInTransaction 

func (d *Database) ExecInTransaction(fn func(d *Database) error) (err error)

func (*Database) IsMssql added in v0.7.0 

func (d *Database) IsMssql() bool

func (*Database) IsMysql added in v0.7.0 

func (d *Database) IsMysql() bool

func (*Database) IsPostgres added in v0.7.0 

func (d *Database) IsPostgres() bool

func (*Database) IsSqlite added in v0.7.0 

func (d *Database) IsSqlite() bool

func (*Database) Open 

func (d *Database) Open() (err error)

func (*Database) Query 

func (d *Database) Query(sqlStr string, args ...any) (*sql.Rows, error)

func (*Database) RollbackTransaction 

func (d *Database) RollbackTransaction() (err error)

func (*Database) SelectToMapAny 

func (d *Database) SelectToMapAny(sqlStr string, args ...any) ([]map[string]any, error)

func (*Database) SelectToMapString 

func (d *Database) SelectToMapString(sqlStr string, args ...any) ([]map[string]string, error)

func (*Database) SqlLog 

func (d *Database) SqlLog() []map[string]string

func (*Database) SqlLogEmpty 

func (d *Database) SqlLogEmpty()

func (*Database) SqlLogEnable 

func (d *Database) SqlLogEnable(enable bool)

func (*Database) SqlLogLen 

func (d *Database) SqlLogLen() int

func (*Database) SqlLogShrink 

func (d *Database) SqlLogShrink(leaveLast int)

func (*Database) Tx added in v0.7.0 

func (d *Database) Tx() *sql.Tx

func (*Database) Type 

func (d *Database) Type() string

type DatabaseInterface added in v0.7.0 

type DatabaseInterface interface {
	// DB the database connection
	DB() *sql.DB

	// Type the database type, i.e. "mssql", "mysql", "postgres", "sqlite"
	Type() string

	// BeginTransaction starts a transaction
	BeginTransaction() (err error)

	// BeginTransactionWithContext starts a transaction with context
	BeginTransactionWithContext(ctx context.Context, opts *sql.TxOptions) (err error)

	// Close closes the database
	Close() (err error)

	// CommitTransaction commits the transaction
	CommitTransaction() (err error)

	// DebugEnable enables or disables debug
	DebugEnable(debug bool)

	// ExecInTransaction executes a function in a transaction
	ExecInTransaction(fn func(d *Database) error) (err error)

	// Exec executes a query
	Exec(sqlStr string, args ...any) (sql.Result, error)

	IsMssql() bool
	IsMysql() bool
	IsPostgres() bool
	IsSqlite() bool
	SqlLog() []map[string]string
	SqlLogEmpty()
	SqlLogLen() int
	SqlLogEnable(enable bool)
	SqlLogShrink(leaveLast int)
	Open() (err error)
	Query(sqlStr string, args ...any) (*sql.Rows, error)
	RollbackTransaction() (err error)
	SelectToMapAny(sqlStr string, args ...any) ([]map[string]any, error)
	SelectToMapString(sqlStr string, args ...any) ([]map[string]string, error)

	// Tx the transaction
	Tx() *sql.Tx
}

func NewDatabase 

func NewDatabase(db *sql.DB, databaseType string) DatabaseInterface

func NewDatabaseFromDriver 

func NewDatabaseFromDriver(driverName, dataSourceName string) (DatabaseInterface, error)

type GroupBy 

type GroupBy struct {
	Column string
}

type OrderBy 

type OrderBy struct {
	Column    string
	Direction string
}

type Where 

type Where struct {
	Raw      string
	Column   string
	Operator string
	Type     string
	Value    string
}

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.