sb

package module
v0.15.0 Latest Latest
Warning

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

 Go to latest Published: Nov 21, 2025 License: MIT Imports: 15 Imported by: 31

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/dracory/sb

Example Create Table SQL

import "github.com/dracory/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.NewDatabase(sqlDb, sb.DIALECT_MYSQL)
  1. From driver
myDb, err := sb.NewDatabaseFromDriver("sqlite3", "test.db")

Example SQL Execute

myDb := sb.NewDatabase(sqlDb, sb.DIALECT_MYSQL)

ctx := context.Background()

_, err := myDb.Exec(ctx, sql)

Example Transaction (using ExecInTransaction)

import _ "modernc.org/sqlite"

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

ctx := context.Background()

err := myDb.ExecInTransaction(ctx, func(tx *sb.Database) error {
	if _, err := tx.Exec(ctx, sql1); err != nil {
		return err
	}

	if _, err := tx.Exec(ctx, sql2); err != nil {
		return err
	}

	return nil
})

if err != nil {
	// handle error
}

Example Transaction (using BeginTransaction, CommitTransaction and RollbackTransaction)

import _ "modernc.org/sqlite"

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

myDb.BeginTransaction()

ctx := context.Background()

_, err := myDb.Exec(ctx, sql1)

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

_, err := myDb.Exec(ctx, 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

ctx := context.Background()

mapAny := myDb.SelectToMapAny(ctx, sql)

Executes a select query and returns map[string]string


ctx := context.Background()

mapString := myDb.SelectToMapString(ctx, 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 TableColumnExists 

func TableColumnExists(ctx database.QueryableContext, tableName, columnName string) (exists bool, err error)

TableColumnExists checks if a column exists in a table for various database types.

func TableCreate 

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

func TableCreateSql 

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

func TableDrop 

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

func TableDropIfExists 

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

func TableDropIfExistsSql 

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

func TableDropSql 

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 

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 

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

TableColumnAdd adds a column to the table

func (*Builder) TableColumnChange 

func (b *Builder) TableColumnChange(tableName string, column Column) (sqlString string, err error)

TableColumnChange changes a column in the table

func (*Builder) TableColumnDrop 

func (b *Builder) TableColumnDrop(tableName, columnName string) (sqlString string, err error)

TableColumnDrop drops a column from the table

func (*Builder) TableColumnExists 

func (b *Builder) TableColumnExists(tableName, columnName string) (sql string, params []interface{}, err error)

TableColumnExists checks if a column exists in a table for various database types 

Example:
b := NewBuilder(DIALECT_MYSQL)
sqlString, sqlParams, err := b.TableColumnExists("test_table", "test_column")

Params: - tableName: The name of the table to check. - columnName: The name of the column to check.

Returns: - sql: The SQL statement to check for the existence of the column. - params: An array of parameters to be bound to the statement. - err: An error object, if any.

func (*Builder) TableColumnRename 

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

func (*Builder) TableRename 

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

Rename renames a table or a view

func (*Builder) Truncate 

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 

type BuilderInterface interface {
	// Column adds a column to the table
	Column(column Column) BuilderInterface

	// Create creates a table
	Create() string

	// CreateIfNotExists creates a table if it doesn't exist
	CreateIfNotExists() string

	// CreateIndex creates an index on the table
	CreateIndex(indexName string, columnName ...string) string

	// Delete deletes a table
	Delete() string

	// Drop drops a table
	Drop() string

	// DropIfExists drops a table if it exists
	DropIfExists() string

	// Insert inserts a row into the table
	Insert(columnValuesMap map[string]string) string

	// GroupBy groups the results by a column
	GroupBy(groupBy GroupBy) BuilderInterface

	// Limit limits the number of results
	Limit(limit int64) BuilderInterface

	// Offset offsets the results
	Offset(offset int64) BuilderInterface

	// OrderBy orders the results by a column
	OrderBy(columnName string, sortDirection string) BuilderInterface

	// Select selects the columns from the table
	Select(columns []string) string

	// Table sets the table name
	Table(name string) BuilderInterface

	// Update updates a row in the table
	Update(columnValues map[string]string) string

	// View sets the view name
	View(name string) BuilderInterface

	// ViewColumns sets the view columns
	ViewColumns(columns []string) BuilderInterface

	// ViewSQL sets the view SQL
	ViewSQL(sql string) BuilderInterface

	// Where sets the where clause
	Where(where *Where) BuilderInterface

	// TableColumnAdd adds a column to the table
	TableColumnAdd(tableName string, column Column) (sqlString string, err error)

	// TableColumnChange changes a column in the table
	TableColumnChange(tableName string, column Column) (sqlString string, err error)

	// Table column drop drops a column
	TableColumnDrop(tableName string, columnName string) (sqlString string, err error)

	// TableColumnExists checks if a column exists in a table
	TableColumnExists(tableName, columnName string) (sqlString string, sqlParams []any, err error)

	// TableColumnRename renames a column in a table
	TableColumnRename(tableName, oldColumnName, newColumnName string) (sqlString string, err error)

	// TableRename renames a table
	TableRename(oldTableName string, newTableName string) (sqlString string, err 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 

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 ColumnSQLGenerator 

type ColumnSQLGenerator interface {
	GenerateSQL(column Column) string
}

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(ctx context.Context, sqlStr string, args ...any) (sql.Result, error)

func (*Database) ExecInTransaction 

func (d *Database) ExecInTransaction(ctx context.Context, fn func(d *Database) error) (err error)

func (*Database) IsMssql 

func (d *Database) IsMssql() bool

func (*Database) IsMysql 

func (d *Database) IsMysql() bool

func (*Database) IsPostgres 

func (d *Database) IsPostgres() bool

func (*Database) IsSqlite 

func (d *Database) IsSqlite() bool

func (*Database) Open 

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

func (*Database) Query 

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

func (*Database) RollbackTransaction 

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

func (*Database) SelectToMapAny 

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

func (*Database) SelectToMapString 

func (d *Database) SelectToMapString(ctx context.Context, 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 

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

func (*Database) Type 

func (d *Database) Type() string

type DatabaseInterface 

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(ctx context.Context, fn func(d *Database) error) (err error)

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

	// IsMssql checks if the database is MSSQL
	IsMssql() bool

	// IsMysql checks if the database is MySQL
	IsMysql() bool

	// IsPostgres checks if the database is PostgreSQL
	IsPostgres() bool

	// IsSqlite checks if the database is SQLite
	IsSqlite() bool

	// SqlLog returns the SQL log
	SqlLog() []map[string]string

	// SqlLogEmpty clears the SQL log
	SqlLogEmpty()

	// SqlLogLen returns the length of the SQL log
	SqlLogLen() int

	// SqlLogEnable enables or disables the SQL log
	SqlLogEnable(enable bool)

	// SqlLogShrink shrinks the SQL log to the last n entries
	SqlLogShrink(leaveLast int)

	// Open opens the database
	Open() (err error)

	// Query queries the database
	Query(ctx context.Context, sqlStr string, args ...any) (*sql.Rows, error)

	// RollbackTransaction rolls back the transaction
	RollbackTransaction() (err error)

	// SelectToMapAny selects rows from the database and returns them
	// as a map of any
	SelectToMapAny(ctx context.Context, sqlStr string, args ...any) ([]map[string]any, error)

	// SelectToMapString selects rows from the database and returns them
	// as a map of strings
	SelectToMapString(ctx context.Context, 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 MSSQLColumnSQLGenerator 

type MSSQLColumnSQLGenerator struct{}

func (MSSQLColumnSQLGenerator) GenerateSQL 

func (g MSSQLColumnSQLGenerator) GenerateSQL(column Column) string

type MySQLColumnSQLGenerator 

type MySQLColumnSQLGenerator struct{}

func (MySQLColumnSQLGenerator) GenerateSQL 

func (g MySQLColumnSQLGenerator) GenerateSQL(column Column) string

type OrderBy 

type OrderBy struct {
	Column    string
	Direction string
}

type PostgreSQLColumnSQLGenerator 

type PostgreSQLColumnSQLGenerator struct{}

func (PostgreSQLColumnSQLGenerator) GenerateSQL 

func (g PostgreSQLColumnSQLGenerator) GenerateSQL(column Column) string

type SQLiteColumnSQLGenerator 

type SQLiteColumnSQLGenerator struct{}

func (SQLiteColumnSQLGenerator) GenerateSQL 

func (g SQLiteColumnSQLGenerator) GenerateSQL(column Column) string

type Where 

type Where struct {
	Raw      string
	Column   string
	Operator string
	Type     string
	Value    string
	Children []Where
}

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.