sb

package module
v0.23.0 Latest Latest
Warning

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

 Go to latest Published: Mar 27, 2026 License: MIT Imports: 16 Imported by: 34

README

SB Open in Gitpod

tests Go Report Card PkgGoDev

A simplified SQL builder with comprehensive database operations support and robust error handling.

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.

Features:

  • ✅ Parameterized Queries - SQL injection protection by default with dialect-specific placeholders
  • ✅ Subquery Support - IN, NOT IN, EXISTS, NOT EXISTS, and comparison subqueries
  • ✅ JOIN Operations - INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins with table aliases
  • ✅ Enhanced Index Management - Advanced index types (GIN, FULLTEXT, partial, covering) with database-specific options
  • ✅ Zero-Panic Error Handling - All errors returned gracefully, no panics anywhere
  • ✅ Fluent API with Error Collection - Chain operations with comprehensive error validation

Security

🔒 Parameterized Queries by Default: SB now generates parameterized queries with SQL injection protection. Values are separated from SQL and safely handled by database drivers.

// ✅ Secure - parameterized queries (default)
sql, params, err := builder.
    Where(&sb.Where{Column: "email", Operator: "=", Value: userEmail}).
    Select([]string{"*"})
// SQL: SELECT * FROM users WHERE email = ?
// Params: ["user@example.com"]

// ✅ Legacy mode - interpolated values (backward compatible)
sql, _, err := builder.
    Where(&sb.Where{Column: "email", Operator: "=", Value: userEmail}).
    WithInterpolatedValues().
    Select([]string{"*"})
// SQL: SELECT * FROM users WHERE email = "user@example.com"

See Security Guide for detailed safety information and best practices.

// ✅ Safe - values are properly quoted
sql := builder.
    Where(&sb.Where{Column: "email", Operator: "=", Value: userEmail}).
    Select([]string{"*"})

// ❌ Dangerous - never concatenate user input
sql := "SELECT * FROM users WHERE email = '" + userEmail + "'"

import "github.com/dracory/sb"

// Create a builder
builder := sb.NewBuilder(sb.DIALECT_MYSQL)

// Build parameterized queries with error handling
sql, params, err := builder.
    Table("users").
    Where(&sb.Where{Column: "status", Operator: "=", Value: "active"}).
    Select([]string{"name", "email"})

if err != nil {
    // Handle error gracefully
    log.Fatal("SQL generation error:", err)
}

// Execute with database (parameterized)
myDb := sb.NewDatabaseFromDriver("sqlite3", "test.db")
ctx := context.Background()
result, err := myDb.Exec(ctx, sql, params...)  // Pass params separately

Documentation

Core Operations
Advanced Features
Examples
Basic Query (Parameterized)
sql, params, err := sb.NewBuilder(sb.DIALECT_MYSQL).
    Table("users").
    Where(&sb.Where{Column: "status", Operator: "=", Value: "active"}).
    Select([]string{"name", "email"})

if err != nil {
    log.Fatal("SQL generation error:", err)
}

// Execute: db.Exec(sql, params...)
// SQL: SELECT `name`, `email` FROM `users` WHERE `status` = ?
// Params: ["active"]
JOIN Example (Parameterized)
sql, params, err := sb.NewBuilder(sb.DIALECT_MYSQL).
    Table("orders").
    InnerJoin("users", "orders.user_id = users.id").
    Where(&sb.Where{Column: "orders.status", Operator: "=", Value: "active"}).
    Select([]string{"orders.*", "users.name"})

if err != nil {
    log.Fatal("SQL generation error:", err)
}

// Execute: db.Query(sql, params...)
// SQL: SELECT `orders`.*, `users`.`name` FROM `orders` INNER JOIN `users` ON orders.user_id = users.id WHERE `orders`.`status` = ?
// Params: ["active"]
Subquery Example (Parameterized)
subquery := sb.NewBuilder(sb.DIALECT_MYSQL).
    Table("orders").
    Where(&sb.Where{Column: "total", Operator: ">", Value: "1000"})

sql, params, err := sb.NewBuilder(sb.DIALECT_MYSQL).
    Table("users").
    InSubquery(subquery).
    Select([]string{"name"})

if err != nil {
    log.Fatal("SQL generation error:", err)
}

// Execute: db.Query(sql, params...)
// SQL: SELECT `name` FROM `users` WHERE `id` IN (SELECT * FROM `orders` WHERE `total` > ?)
// Params: ["1000"]
Enhanced Index Support
// Simple unique index
sql, err := sb.NewBuilder(sb.DIALECT_MYSQL).
    Table("users").
    CreateUniqueIndex("idx_users_email", "email")

// PostgreSQL GIN index with covering columns
sql, err := sb.NewBuilder(sb.DIALECT_POSTGRES).
    Table("documents").
    CreateIndexWithOptions("idx_search", sb.IndexOptions{
        Unique:      true,
        IfNotExists: true,
        Using:       sb.INDEX_TYPE_GIN,
        Columns:     []sb.IndexColumn{{Name: "search_vector"}},
        Include:     []string{"title", "content"},
        Where:       "published = true",
        Storage:     "fillfactor=90",
    })

// MySQL FULLTEXT index with prefix and comment
sql, err := sb.NewBuilder(sb.DIALECT_MYSQL).
    Table("articles").
    CreateIndexWithOptions("idx_content", sb.IndexOptions{
        Using:   sb.INDEX_TYPE_FULLTEXT,
        Columns: []sb.IndexColumn{
            {Name: "title", Length: 100},
            {Name: "content", Length: 255},
        },
        Comment: "Full-text search index",
    })

// Enhanced DROP with schema support
sql, err := sb.NewBuilder(sb.DIALECT_POSTGRES).
    Table("users").
    DropIndexWithOptions("idx_users_email", sb.DropIndexOptions{
        IfExists: true,
        Schema:   "public",
    })
Error Handling Example
// All SQL generation methods return (string, []interface{}, error)
sql, params, err := builder.
    Table("users").
    Where(&sb.Where{Column: "email", Operator: "=", Value: "test@example.com"}).
    Select([]string{"*"})

if err != nil {
    switch err.Error() {
    case "ValidationError: no table specified":
        // Handle missing table error
    case "ValidationError: unsupported dialect: unknown":
        // Handle unsupported dialect error
    default:
        // Handle other errors
        log.Fatal("SQL error:", err)
    }
}

// Execute safely with parameters
rows, err := db.Query(sql, params...)

Migration Guide

Breaking Changes in v0.18.0

SB now uses parameterized queries by default for improved security. This is a breaking change that affects method signatures.

Before (v0.17.x)
sql, err := builder.
    Table("users").
    Where(&sb.Where{Column: "status", Operator: "=", Value: "active"}).
    Select([]string{"*"})

db.Exec(sql)  // Direct execution
After (v0.18.0)
sql, params, err := builder.
    Table("users").
    Where(&sb.Where{Column: "status", Operator: "=", Value: "active"}).
    Select([]string{"*"})

db.Exec(sql, params...)  // Pass parameters separately
Migration Strategies

Update your code to handle the new 3-value return:

// Before
sql, err := builder.Select([]string{"*"})
db.Exec(sql)

// After  
sql, params, err := builder.Select([]string{"*"})
db.Exec(sql, params...)
2. Gradual Migration

Use WithInterpolatedValues() for backward compatibility:

// Legacy mode - no changes needed
sql, _, err := builder.
    WithInterpolatedValues().
    Select([]string{"*"})
db.Exec(sql)  // Same as before
3. Database-Specific Placeholders
Database Placeholder Format Example
MySQL ? WHERE id = ?
PostgreSQL $1, $2, $3 WHERE id = $1
SQLite ? WHERE id = ?
MSSQL @p1, @p2, @p3 WHERE id = @p1
Benefits of Migration

✅ Security: SQL injection protection by default
✅ Performance: Better query plan caching
✅ Compatibility: Works with all major Go database drivers
✅ Flexibility: Optional legacy mode available

Need Help?
Transaction Example
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
})

Error Handling

SB uses a zero-panic error handling strategy. All errors are collected during fluent chaining and returned when SQL is generated.

Error Types
  • ValidationError - Invalid parameters (missing table, empty conditions, etc.)
  • ArgumentError - Invalid arguments (nil subqueries, etc.)
Error Collection Pattern
// Errors are collected during chaining
builder := sb.NewBuilder("unknown") // Unsupported dialect error collected
builder.InnerJoin("users", "")    // Empty JOIN condition error collected

// All errors returned when SQL is generated
sql, err := builder.Select([]string{"*"})
// Returns: "ValidationError: unsupported dialect: unknown"
Best Practices
// ✅ Always check for errors
sql, err := builder.Select([]string{"*"})
if err != nil {
    return fmt.Errorf("failed to generate SQL: %w", err)
}

// ✅ Handle specific error cases
if strings.Contains(err.Error(), "unsupported dialect") {
    // Handle dialect error
}

// ✅ Use structured error handling
switch {
case strings.Contains(err.Error(), "no table specified"):
    // Handle missing table
case strings.Contains(err.Error(), "ON condition cannot be empty"):
    // Handle empty JOIN condition
default:
    return err
}

Database Support

Database Dialect Constant Status
MySQL sb.DIALECT_MYSQL ✅ Full Support
PostgreSQL sb.DIALECT_POSTGRES ✅ Full Support
SQLite sb.DIALECT_SQLITE ✅ Full Support
MSSQL sb.DIALECT_MSSQL ✅ Full Support

Recently Implemented Features

  • ✅ Parameterized Queries - SQL injection protection by default with dialect-specific placeholders
  • ✅ Zero-Panic Error Handling - Complete error collection system, no panics anywhere
  • ✅ Subquery Support - IN, NOT IN, EXISTS, NOT EXISTS, and comparison subqueries with correlation
  • ✅ JOIN Support - INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins with table aliases
  • ✅ Index Management - Complete CREATE INDEX and DROP INDEX functionality
  • ✅ Table Truncation - TRUNCATE TABLE support with database-specific options
  • ✅ Multi-dialect Support - MySQL, PostgreSQL, SQLite, and MSSQL compatibility

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 Projects

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 INDEX_TYPE_BRIN = "BRIN"
View Source 
const INDEX_TYPE_BTREE = "BTREE"

Index Types

View Source 
const INDEX_TYPE_FULLTEXT = "FULLTEXT"
View Source 
const INDEX_TYPE_GIN = "GIN"
View Source 
const INDEX_TYPE_GIST = "GIST"
View Source 
const INDEX_TYPE_HASH = "HASH"
View Source 
const INDEX_TYPE_SPATIAL = "SPATIAL"
View Source 
const JOIN_CROSS = "CROSS"
View Source 
const JOIN_FULL = "FULL"
View Source 
const JOIN_INNER = "INNER"

Join Types

View Source 
const JOIN_LEFT = "LEFT"
View Source 
const JOIN_RIGHT = "RIGHT"
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

View Source 
var (
	// Validation errors
	ErrEmptyTableName             = &BuilderError{Type: "ValidationError", Message: "table name cannot be empty"}
	ErrEmptyColumnName            = &BuilderError{Type: "ValidationError", Message: "column name cannot be empty"}
	ErrEmptyIndexName             = &BuilderError{Type: "ValidationError", Message: "index name cannot be empty"}
	ErrEmptyColumns               = &BuilderError{Type: "ValidationError", Message: "columns cannot be empty"}
	ErrEmptyOnCondition           = &BuilderError{Type: "ValidationError", Message: "ON condition cannot be empty"}
	ErrInvalidJoinType            = &BuilderError{Type: "ValidationError", Message: "invalid join type"}
	ErrOffsetWithoutLimit         = &BuilderError{Type: "ValidationError", Message: "SQLite requires LIMIT when using OFFSET"}
	ErrMSSQLOffsetRequiresOrderBy = &BuilderError{Type: "ValidationError", Message: "MSSQL requires ORDER BY when using OFFSET"}

	// Configuration errors
	ErrInvalidDialect = &BuilderError{Type: "ConfigurationError", Message: "invalid database dialect"}
	ErrMissingTable   = &BuilderError{Type: "ValidationError", Message: "no table specified"}

	// Subquery errors
	ErrNilSubquery     = &BuilderError{Type: "ArgumentError", Message: "subquery cannot be nil"}
	ErrSubqueryColumns = &BuilderError{Type: "SubqueryError", Message: "subquery columns validation failed"}
)

Common error types

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, error)

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)

func TestMSSQLOffsetBug added in v0.23.0 

func TestMSSQLOffsetBug(t *testing.T)

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, error)

*

  • 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) CreateCompositeIndex added in v0.19.0 

func (b *Builder) CreateCompositeIndex(name string, columns []IndexColumn) (string, error)

CreateCompositeIndex creates an index on multiple columns with explicit ordering.

func (*Builder) CreateCoveringIndex added in v0.19.0 

func (b *Builder) CreateCoveringIndex(name string, include []string, columns ...string) (string, error)

CreateCoveringIndex creates a covering index using the INCLUDE clause (PostgreSQL, MSSQL).

func (*Builder) CreateIfNotExists 

func (b *Builder) CreateIfNotExists() (string, error)

func (*Builder) CreateIndex 

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

func (*Builder) CreateIndexWithOptions added in v0.19.0 

func (b *Builder) CreateIndexWithOptions(name string, opts IndexOptions) (string, error)

CreateIndexWithOptions generates a CREATE INDEX statement with full dialect-aware options.

Example (PostgreSQL partial unique index): 

sql, err := sb.NewBuilder(sb.DIALECT_POSTGRES).
    Table("users").
    CreateIndexWithOptions("idx_users_active_email", sb.IndexOptions{
        Unique: true,
        Columns: []sb.IndexColumn{{Name: "email"}},
        Where:  "deleted_at IS NULL",
    })
// CREATE UNIQUE INDEX IF NOT EXISTS "idx_users_active_email"
//   ON "users" ("email") WHERE deleted_at IS NULL;

func (*Builder) CreatePartialIndex added in v0.19.0 

func (b *Builder) CreatePartialIndex(name string, where string, columns ...string) (string, error)

CreatePartialIndex creates an index with a WHERE predicate (PostgreSQL, SQLite, MSSQL).

func (*Builder) CreateUniqueIndex added in v0.19.0 

func (b *Builder) CreateUniqueIndex(name string, columns ...string) (string, error)

CreateUniqueIndex creates a UNIQUE index on one or more columns.

func (*Builder) Delete 

func (b *Builder) Delete() (string, []interface{}, error)

*

  • 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

Delete deletes rows from a table

func (*Builder) Drop 

func (b *Builder) Drop() (string, error)

Drop deletes a table or a view

func (*Builder) DropIfExists 

func (b *Builder) DropIfExists() (string, error)

func (*Builder) DropIndex added in v0.16.0 

func (b *Builder) DropIndex(indexName string) (string, error)

DropIndex removes an index from a table. The method generates database-specific SQL:

  • MySQL: DROP INDEX `index_name` ON `table_name`;
  • PostgreSQL: DROP INDEX "index_name";
  • SQLite: DROP INDEX "index_name";
  • MSSQL: DROP INDEX [index_name] ON [table_name];

Example: 

sql := sb.NewBuilder(sb.DIALECT_MYSQL).Table("users").DropIndex("idx_users_email")
// Returns: "DROP INDEX `idx_users_email` ON `users`;"

func (*Builder) DropIndexIfExists added in v0.16.0 

func (b *Builder) DropIndexIfExists(indexName string) (string, error)

DropIndexIfExists removes an index from a table if it exists. Uses IF EXISTS clause where supported by the database dialect.

Example: 

sql := sb.NewBuilder(sb.DIALECT_SQLITE).Table("users").DropIndexIfExists("idx_users_email")
// Returns: "DROP INDEX IF EXISTS \"idx_users_email\";"

func (*Builder) DropIndexWithOptions added in v0.19.0 

func (b *Builder) DropIndexWithOptions(name string, opts DropIndexOptions) (string, error)

DropIndexWithOptions generates a DROP INDEX statement with full dialect-aware options.

func (*Builder) DropIndexWithSchema added in v0.16.0 

func (b *Builder) DropIndexWithSchema(indexName string, schema string) (string, error)

DropIndexWithSchema removes an index from a specific schema (PostgreSQL only). For other dialects, falls back to regular DropIndex behavior.

Example: 

sql := sb.NewBuilder(sb.DIALECT_POSTGRES).Table("users").
  DropIndexWithSchema("idx_users_email", "public")
// Returns: "DROP INDEX IF EXISTS \"public\".\"idx_users_email\";"

func (*Builder) Exists added in v0.17.0 

func (b *Builder) Exists(subquery BuilderInterface) (BuilderInterface, error)

Exists adds an EXISTS subquery condition to the query. The subquery parameter is the subquery to check for existence.

Example: 

activeOrders := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("orders").
  Where(&sb.Where{Column: "status", Operator: "=", Value: "active"})

sql := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("users").
  Exists(activeOrders).
  Select([]string{"name"})

func (*Builder) GroupBy 

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

func (*Builder) InSubquery added in v0.17.0 

func (b *Builder) InSubquery(subquery BuilderInterface) (BuilderInterface, error)

InSubquery adds an IN subquery condition to the query. The subquery parameter is the subquery to use for the IN clause.

Example: 

highValueUsers := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("orders").
  Select([]string{"user_id"}).
  Where(&sb.Where{Column: "total", Operator: ">", Value: 1000})

sql := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("users").
  InSubquery(highValueUsers).
  Select([]string{"name"})

func (*Builder) InnerJoin added in v0.17.0 

func (b *Builder) InnerJoin(table string, onCondition string) BuilderInterface

InnerJoin adds an INNER JOIN clause to the query. The table parameter specifies the table to join. The onCondition parameter specifies the join condition.

Example: 

sql := sb.NewBuilder(sb.DIALECT_MYSQL).Table("orders").
  InnerJoin("users", "orders.user_id = users.id").
  Select([]string{"orders.*", "users.name"})
// Returns: "SELECT orders.*, users.name FROM orders INNER JOIN users ON orders.user_id = users.id;"

func (*Builder) Insert 

func (b *Builder) Insert(columnValuesMap map[string]string) (string, []interface{}, error)

Insert inserts a row into a table.

NOTE: This method inserts a single record. For bulk inserts, be aware of database parameter limits:

  • SQLite: 999 parameters per statement (e.g., 124 records with 8 columns each)
  • MySQL: ~65,535 parameters (limited by max_allowed_packet setting)
  • PostgreSQL: ~32,767 parameters for prepared statements
  • MSSQL: 2,100 parameters

For large bulk inserts, split data into batches to avoid "too many SQL variables" errors.

Example: 

sql, params, err := sb.NewBuilder(sb.DIALECT_SQLITE).
  Table("users").
  Insert(map[string]string{"name": "John", "email": "john@example.com"})

Returns the SQL statement and parameters for execution.

func (*Builder) Join added in v0.17.0 

func (b *Builder) Join(joinType JoinType, table string, onCondition string) BuilderInterface

Join adds a JOIN clause to the query. The joinType parameter specifies the type of join (INNER, LEFT, RIGHT, FULL, CROSS). The table parameter specifies the table to join. The onCondition parameter specifies the join condition.

Example: 

sql := sb.NewBuilder(sb.DIALECT_MYSQL).Table("orders").
  Join(sb.JOIN_INNER, "users", "orders.user_id = users.id").
  Select([]string{"orders.*", "users.name"})
// Returns: "SELECT orders.*, users.name FROM orders INNER JOIN users ON orders.user_id = users.id;"

func (*Builder) JoinWithAlias added in v0.17.0 

func (b *Builder) JoinWithAlias(joinType JoinType, table string, alias string, onCondition string) BuilderInterface

JoinWithAlias adds a JOIN clause with table alias to the query. The joinType parameter specifies the type of join (INNER, LEFT, RIGHT, FULL, CROSS). The table parameter specifies the table to join. The alias parameter specifies the alias for the joined table. The onCondition parameter specifies the join condition.

Example: 

sql := sb.NewBuilder(sb.DIALECT_POSTGRES).Table("orders").
  JoinWithAlias(sb.JOIN_LEFT, "profiles", "p", "orders.user_id = p.user_id").
  Select([]string{"orders.*", "p.avatar"})
// Returns: "SELECT orders.*, p.avatar FROM orders LEFT JOIN profiles AS p ON orders.user_id = p.user_id;"

func (*Builder) LeftJoin added in v0.17.0 

func (b *Builder) LeftJoin(table string, onCondition string) BuilderInterface

LeftJoin adds a LEFT JOIN clause to the query. The table parameter specifies the table to join. The onCondition parameter specifies the join condition.

Example: 

sql := sb.NewBuilder(sb.DIALECT_SQLITE).Table("orders").
  LeftJoin("users", "orders.user_id = users.id").
  Select([]string{"orders.*", "users.name"})
// Returns: "SELECT orders.*, users.name FROM orders LEFT JOIN users ON orders.user_id = users.id;"

func (*Builder) Limit 

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

func (*Builder) NotExists added in v0.17.0 

func (b *Builder) NotExists(subquery BuilderInterface) (BuilderInterface, error)

NotExists adds a NOT EXISTS subquery condition to the query. The subquery parameter is the subquery to check for non-existence.

Example: 

activeOrders := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("orders").
  Where(&sb.Where{Column: "status", Operator: "=", Value: "active"})

sql := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("users").
  NotExists(activeOrders).
  Select([]string{"name"})

func (*Builder) NotInSubquery added in v0.17.0 

func (b *Builder) NotInSubquery(subquery BuilderInterface) (BuilderInterface, error)

NotInSubquery adds a NOT IN subquery condition to the query. The subquery parameter is the subquery to use for the NOT IN clause.

Example: 

inactiveUsers := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("orders").
  Select([]string{"user_id"}).
  Where(&sb.Where{Column: "status", Operator: "=", Value: "inactive"})

sql := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("users").
  NotInSubquery(inactiveUsers).
  Select([]string{"name"})

func (*Builder) Offset 

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

func (*Builder) OrderBy 

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

func (*Builder) RightJoin added in v0.17.0 

func (b *Builder) RightJoin(table string, onCondition string) BuilderInterface

RightJoin adds a RIGHT JOIN clause to the query. The table parameter specifies the table to join. The onCondition parameter specifies the join condition.

Example: 

sql := sb.NewBuilder(sb.DIALECT_MSSQL).Table("orders").
  RightJoin("users", "orders.user_id = users.id").
  Select([]string{"orders.*", "users.name"})
// Returns: "SELECT orders.*, users.name FROM orders RIGHT JOIN users ON orders.user_id = users.id;"

func (*Builder) Select 

func (b *Builder) Select(columns []string) (string, []interface{}, error)

* 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) Subquery added in v0.17.0 

func (b *Builder) Subquery() BuilderInterface

Subquery creates a subquery builder for use in WHERE clauses. Returns a new Builder instance with the same dialect for building subqueries.

Example: 

subquery := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("orders").
  Select([]string{"user_id"}).
  Where(&sb.Where{Column: "total", Operator: ">", Value: 1000})

sql := sb.NewBuilder(sb.DIALECT_MYSQL).
  Table("users").
  InSubquery(subquery).
  Select([]string{"name"})

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, error)

Truncate removes all data from a table. The method generates database-specific SQL:

  • MySQL: TRUNCATE TABLE `table_name`;
  • PostgreSQL: TRUNCATE TABLE "table_name";
  • SQLite: DELETE FROM "table_name";
  • MSSQL: TRUNCATE TABLE [table_name];

Example: 

sql := sb.NewBuilder(sb.DIALECT_MYSQL).Table("users").Truncate()
// Returns: "TRUNCATE TABLE `users`;"

func (*Builder) TruncateWithOptions added in v0.16.0 

func (b *Builder) TruncateWithOptions(opts TruncateOptions) (string, error)

TruncateWithOptions removes all data from a table with additional options. The opts parameter provides database-specific behavior:

  • Cascade (PostgreSQL): adds CASCADE to handle foreign key constraints
  • ResetIdentity (MSSQL): resets identity column seed value after truncation

Examples: 

// PostgreSQL with CASCADE for foreign key constraints
sql := sb.NewBuilder(sb.DIALECT_POSTGRES).Table("orders").
  TruncateWithOptions(sb.TruncateOptions{Cascade: true})
// Returns: "TRUNCATE TABLE \"orders\" CASCADE;"

// MSSQL with identity reset
sql := sb.NewBuilder(sb.DIALECT_MSSQL).Table("users").
  TruncateWithOptions(sb.TruncateOptions{ResetIdentity: true})
// Returns: "TRUNCATE TABLE [users]; DBCC CHECKIDENT ('users', RESEED, 0)"

func (*Builder) Update 

func (b *Builder) Update(columnValues map[string]string) (string, []interface{}, error)

Update updates the values of rows in a table.

Example: 

sql := sb.NewBuilder(sb.DIALECT_MYSQL).Table("users").
  Where(sb.Where{Column: "id", Operator: "=", Value: "1"}).
  Update(map[string]string{"name": "John", "email": "john@example.com"})

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

Where adds a WHERE clause to the query.

Security Note: Values are properly quoted to prevent SQL injection. Avoid using the Raw field with user input. Use Column/Operator/Value instead.

Example: 

// Safe - values are properly quoted
sql := builder.Where(&sb.Where{Column: "email", Operator: "=", Value: userEmail})

// Dangerous - avoid using Raw with user input
sql := builder.Where(&sb.Where{Raw: "email = '" + userEmail + "'"})

func (*Builder) WithInterpolatedValues added in v0.18.0 

func (b *Builder) WithInterpolatedValues() BuilderInterface

WithInterpolatedValues enables legacy mode with interpolated values instead of parameterized queries. WARNING: This mode is insecure and vulnerable to SQL injection. Use only for backward compatibility.

type BuilderError added in v0.17.0 

type BuilderError struct {
	Type    string
	Message string
	Cause   error
}

BuilderError represents a structured error from the SQL builder

func NewConfigurationError added in v0.17.0 

func NewConfigurationError(message string) *BuilderError

NewConfigurationError creates a new configuration error

func NewSubqueryError added in v0.17.0 

func NewSubqueryError(message string, cause error) *BuilderError

NewSubqueryError creates a new subquery error with optional cause

func NewValidationError added in v0.17.0 

func NewValidationError(message string) *BuilderError

NewValidationError creates a new validation error

func (*BuilderError) Error added in v0.17.0 

func (e *BuilderError) Error() string

Error returns the error message

func (*BuilderError) Unwrap added in v0.17.0 

func (e *BuilderError) Unwrap() error

Unwrap returns the underlying cause

type BuilderInterface 

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

	// Create creates a table
	Create() (string, error)

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

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

	// DropIndex removes an index from the table
	DropIndex(indexName string) (string, error)

	// DropIndexIfExists removes an index from the table if it exists
	DropIndexIfExists(indexName string) (string, error)

	// DropIndexWithSchema removes an index from a specific schema (PostgreSQL only)
	DropIndexWithSchema(indexName string, schema string) (string, error)

	// CreateIndexWithOptions creates an index with advanced options
	CreateIndexWithOptions(name string, opts IndexOptions) (string, error)

	// CreateUniqueIndex creates a UNIQUE index on one or more columns
	CreateUniqueIndex(name string, columns ...string) (string, error)

	// CreateCompositeIndex creates an index on multiple columns with explicit ordering
	CreateCompositeIndex(name string, columns []IndexColumn) (string, error)

	// CreatePartialIndex creates an index with a WHERE predicate (PostgreSQL, SQLite, MSSQL)
	CreatePartialIndex(name string, where string, columns ...string) (string, error)

	// CreateCoveringIndex creates a covering index using the INCLUDE clause (PostgreSQL, MSSQL)
	CreateCoveringIndex(name string, include []string, columns ...string) (string, error)

	// DropIndexWithOptions drops an index with advanced options
	DropIndexWithOptions(name string, opts DropIndexOptions) (string, error)

	// Join adds a JOIN clause to the query
	Join(joinType JoinType, table string, onCondition string) BuilderInterface

	// JoinWithAlias adds a JOIN clause with table alias to the query
	JoinWithAlias(joinType JoinType, table string, alias string, onCondition string) BuilderInterface

	// LeftJoin adds a LEFT JOIN clause to the query
	LeftJoin(table string, onCondition string) BuilderInterface

	// RightJoin adds a RIGHT JOIN clause to the query
	RightJoin(table string, onCondition string) BuilderInterface

	// InnerJoin adds an INNER JOIN clause to the query
	InnerJoin(table string, onCondition string) BuilderInterface

	// Delete deletes a table
	Delete() (string, []interface{}, error)

	// Drop drops a table
	Drop() (string, error)

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

	// Insert inserts a row into the table
	Insert(columnValuesMap map[string]string) (string, []interface{}, error)

	// 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, []interface{}, error)

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

	// Update updates a row in the table
	Update(columnValues map[string]string) (string, []interface{}, error)

	// WithInterpolatedValues enables legacy mode with interpolated values
	WithInterpolatedValues() BuilderInterface

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

	// Subquery creates a subquery builder for use in WHERE clauses
	Subquery() BuilderInterface

	// Exists adds an EXISTS subquery condition
	Exists(subquery BuilderInterface) (BuilderInterface, error)

	// NotExists adds a NOT EXISTS subquery condition
	NotExists(subquery BuilderInterface) (BuilderInterface, error)

	// InSubquery adds an IN subquery condition
	InSubquery(subquery BuilderInterface) (BuilderInterface, error)

	// NotInSubquery adds a NOT IN subquery condition
	NotInSubquery(subquery BuilderInterface) (BuilderInterface, error)

	// Truncate truncates a table (removes all data)
	Truncate() (string, error)

	// TruncateWithOptions truncates a table with additional options
	TruncateWithOptions(opts TruncateOptions) (string, error)

	// 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 DropIndexOptions added in v0.19.0 

type DropIndexOptions struct {
	IfExists bool   // emit IF EXISTS where supported
	Schema   string // schema-qualify the index name (PostgreSQL only)
}

DropIndexOptions controls advanced index removal behaviour.

type GroupBy 

type GroupBy struct {
	Column string
}

type IndexColumn added in v0.19.0 

type IndexColumn struct {
	Name      string // column name
	Direction string // "ASC" or "DESC" — defaults to ASC if empty
	Length    int    // prefix length for MySQL text/blob columns (ignored on other dialects)
}

IndexColumn defines a single column in an index, with optional direction and prefix length.

type IndexOptions added in v0.19.0 

type IndexOptions struct {
	Unique      bool          // emit UNIQUE keyword
	IfNotExists bool          // emit IF NOT EXISTS where supported
	Columns     []IndexColumn // columns with optional direction / prefix length
	Using       string        // index type: BTREE, HASH, GIN, GIST, BRIN, FULLTEXT, SPATIAL
	Include     []string      // PostgreSQL / MSSQL covering-index columns (INCLUDE clause)
	Where       string        // partial index predicate (PostgreSQL, SQLite, MSSQL)
	Storage     string        // PostgreSQL WITH (...) storage parameters, e.g. "fillfactor=90"
	Comment     string        // MySQL COMMENT on index
}

IndexOptions controls advanced index creation behaviour.

type Join added in v0.17.0 

type Join struct {
	Type        JoinType
	Table       string
	Alias       string
	OnCondition string
	Columns     []string // Optional: for specific column selection
}

Join represents a database JOIN operation

type JoinType added in v0.17.0 

type JoinType string

JoinType represents the type of JOIN operation

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 TruncateOptions added in v0.16.0 

type TruncateOptions struct {
	Cascade       bool // For PostgreSQL: adds CASCADE to handle foreign key constraints
	ResetIdentity bool // For MSSQL: resets identity column seed value after truncation
}

TruncateOptions provides database-specific options for table truncation

type Where 

type Where struct {
	Raw      string   // Raw SQL - use with caution, allows arbitrary SQL
	Column   string   // Column name - safe for validated column names
	Operator string   // SQL operator - safe for validated operators
	Type     string   // Logic type (AND/OR) - safe
	Value    string   // Column value - safely quoted
	Subquery *Builder // Subquery - safely handled with validation
	IsNot    bool     // NOT operator flag - safe
	Children []Where  // Nested conditions - safe
}

Where represents a WHERE clause condition.

Security Note: The Raw field allows arbitrary SQL and can be dangerous with user input. Prefer using Column/Operator/Value for safe parameter handling.

Example: 

// Safe - values are properly quoted
&sb.Where{Column: "email", Operator: "=", Value: userEmail}

// Dangerous - allows arbitrary SQL
&sb.Where{Raw: "email = '" + userInput + "'"}

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.