bob

package module
Version: v0.0.3 Latest Latest
Warning

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

Go to latest
Published: Jul 31, 2021 License: MIT Imports: 6 Imported by: 0

README

Bob - SQL Query Builder

Go Reference Go Report Card GitHub CodeFactor codecov Codacy Badge Build test Test and coverage

Think of this as an extension of Squirrel with functionability like Knex. I still use Squirrel for other types of queries (insert, select, and all that), but I needed some SQL builder for create table and some other stuffs. Including database creation & upsert.

Oh, and of course, heavily inspired by Bob the Builder.

import "github.com/aldy505/bob"

Usage

It's not ready for large-scale production yet (although I've already using it on one of my projects). But, the API is probably close to how you'd do things on Squirrel.

Create a table
import "github.com/aldy505/bob"

func main() {
  // Note that CREATE TABLE doesn't returns args params.
  sql, _, err := bob.
    CreateTable("tableName").
    // The first parameter is the column's name.
    // The second parameters and so on forth are extras.
    StringColumn("id", "NOT NULL", "PRIMARY KEY", "AUTOINCREMENT").
    StringColumn("email", "NOT NULL", "UNIQUE").
    // See the list of available column definition types through pkg.go.dev or scroll down below.
    TextColumn("password").
    // Or add your custom type.
    AddColumn(bob.ColumnDef{Name: "tableName", Type: "customType", Extras: []string{"NOT NULL"}}).
    ToSql()
  if err != nil {
    // handle your error
  }
}

Available column definition types (please be aware that some only works on certain database):

  • StringColumn() - Default to VARCHAR(255)
  • TextColumn() - Default to TEXT
  • UUIDColumn() - Defaults to UUID
  • BooleanColumn() - Defaults to BOOLEAN
  • IntegerColumn() - Defaults to INTEGER. Postgres and SQLite only.
  • IntColumn() - Defaults to INT. MySQL and MSSQL only.
  • RealColumn() - Defaults to REAL. Postgres, MSSQL, and SQLite only.
  • FloatColumn() - Defaults to FLOAT. Postgres and SQLite only.
  • DateTimeColumn() - Defaults to DATETIME.
  • TimeStampColumn() - Defaults to TIMESTAMP.
  • TimeColumn() - Defaults to TIME.
  • DateColumn() - Defaults to DATE.
  • JSONColumn() - Dafults to JSON. MySQL and Postgres only.
  • JSONBColumn() - Defaults to JSONB. Postgres only.
  • BlobColumn() - Defaults to BLOB. MySQL and SQLite only.

For any other types, please use AddColumn().

Another builder of bob.CreateTableIfNotExists() is also available.

Check if a table exists
func main() {
  sql, args, err := bob.HasTable("users").ToSql()
  if err != nil {
    log.Fatal(err)
  }
}
Check if a column exists
func main() {
  sql, args, err := bob.HasColumn("email").ToSql()
  if err != nil {
    log.Fatal(err)
  }
}
Drop table
func main() {
  sql, _, err := bob.DropTable("users").ToSql()
  if err != nil {
    log.Fatal(err)
  }
}

You could also do bob.DropTableIfExists("users") to output a DROP TABLE IF EXISTS "users" query.

Truncate table
func main() {
  sql, _, err := bob.Truncate("users").ToSql()
  if err != nil {
    log.Fatal(err)
  }
}
Rename table
func main() {
  sql, _, err := bob.RenameTable("users", "people").ToSql()
  if err != nil {
    log.Fatal(err)
  }
}
Upsert
func main() {
  sql, args, err := bob.
    // Notice that you should give database dialect on the second params.
    // Available database dialect are MySQL, PostgreSQL, SQLite, and MSSQL.
    Upsert("users", bob.MySQL).
    Columns("name", "email", "age").
    // You could do multiple Values() call, but I'd suggest to not do it.
    // Because this is an upsert function, not an insert one.
    Values("Thomas Mueler", "tmueler@something.com", 25).
    Replace("age", 25).
    PlaceholderFormat(bob.Question).
    ToSql()

  // Another example for PostgreSQL
  sql, args, err = bob.
    Upsert("users", bob.PostgreSQL).
    Columns("name", "email", "age").
    Values("Billy Urtha", "billu@something.com", 30).
    Key("email").
    Replace("age", 40).
    PlaceholderFormat(bob.Dollar).
    ToSql()
  
  // One more time, for MSSQL / SQL Server.
  sql, args, err = bob.
    Upsert("users", bob.MSSQL).
    Columns("name", "email", "age").
    Values("George Rust", "georgee@something.com", 19).
    Key("email", "georgee@something.com").
    Replace("age", 18).
    PlaceholderFormat(bob.AtP).
    ToSql()
}
Placeholder format / Dialect

Default placeholder is a question mark (MySQL-like). If you want to change it, simply use something like this:

func main() {
  // Option 1
  sql, args, err := bob.HasTable("users").PlaceholderFormat(bob.Dollar).ToSql()
  if err != nil {
    log.Fatal(err)
  }

  // Option 2
  sql, args, err = bob.HasTable("users").ToSql()
  if err != nil {
    log.Fatal(err)
  }
  correctPlaceholder := bob.ReplacePlaceholder(sql, bob.Dollar)
}

Available placeholder formats:

  • bob.Question - INSERT INTO "users" (name) VALUES (?)
  • bob.Dollar - INSERT INTO "users" (name) VALUES ($1)
  • bob.Colon - INSERT INTO "users" (name) VALUES (:1) (Yes, I know this is kinda wrong. I'm thinking of removing it.)
  • bob.AtP - INSERT INTO "users" (name) VALUES (@p1)
With pgx (PostgreSQL)
import (
  "context"
  "log"
  "strings"

  "github.com/aldy505/bob"
  "github.com/jackc/pgx/v4"
)

func main() {
  db := pgx.Connect()

  // Check if a table is exists
  sql, args, err = bob.HasTable("users").PlaceholderFormat(bob.Dollar).ToSql()
  if err != nil {
    log.Fatal(err)
  }

  var hasTableUsers bool
  err = db.QueryRow(context.Background(), sql, args...).Scan(&hasTableUsers)
  if err != nil {
    if err == bob.ErrEmptyTablePg {
      hasTableUsers = false
    } else {
      log.Fatal(err)
    }
  }

  if !hasTableUsers {
    // Create "users" table
    sql, _, err := bob.
      CreateTable("users").
      IntegerColumn("id", "PRIMARY KEY", "SERIAL").
      StringColumn("name", "NOT NULL").
      TextColumn("password", "NOT NULL").
      DateColumn("created_at").
      ToSql()
    if err != nil {
      log.Fatal(err)
    }

    _, err = db.Query(context.Background(), splitQuery[i])
    if err != nil {
      log.Fatal(err)
    }

    // Create another table, this time with CREATE TABLE IF NOT EXISTS
    sql, _, err := bob.
      CreateTableIfNotExists("inventory").
      UUIDColumn("id", "PRIMARY KEY").
      IntegerColumn("userID", "FOREIGN KEY REFERENCES users(id)").
      JSONColumn("items").
      IntegerColumn("quantity").
      ToSql()
    if err != nil {
      log.Fatal(err)
    }
    
    _, err = db.Query(context.Background(), inventoryQuery[i])
    if err != nil {
      log.Fatal(err)
    }
  }
}

Features

  • bob.CreateTable(tableName) - Basic SQL create table
  • bob.CreateTableIfNotExists(tableName) - Create table if not exists
  • bob.HasTable(tableName) - Checks if column exists (return error if false, check example above for error handling)
  • bob.HasColumn(columnName) - Check if a column exists on current table
  • bob.DropTable(tableName) - Drop a table (drop table "users")
  • bob.DropTableIfExists(tableName) - Drop a table if exists (drop table if exists "users")
  • bob.RenameTable(currentTable, desiredName) - Rename a table (rename table "users" to "people")
  • bob.Truncate(tableName) - Truncate a table (truncate "users")
  • bob.Upsert(tableName, dialect) - UPSERT function (insert into "users" ("name", "email") values (?, ?) on duplicate key update email = ?)
TODO

Meaning these are some ideas for the future development of Bob.

  • bob.ExecWith() - Just like Squirrel's ExecWith
  • bob.Count(tableName, columnName) - Count query (select count("active") from "users")

Contributing

Contributions are always welcome! As long as you add a test for your changes.

License

Bob is licensed under MIT license

Documentation

Index

Constants

View Source
const (
	MySQL int = iota
	PostgreSQL
	SQLite
	MSSQL
)
View Source
const (
	// Question is the format used in MySQL
	Question = "?"
	// Dollar is the format used in PostgreSQL
	Dollar = "$"
	// Colon is the format used in Oracle Database, but here I implemented it wrong.
	// I will either fix it or remove it in the future.
	Colon = ":"
	// AtP comes in the documentation of Squirrel but I don't know what database uses it.
	AtP = "@p"
)

Variables

BobStmtBuilder is the parent builder for BobBuilderType

View Source
var ErrDialectNotSupported = errors.New("provided database dialect is not supported")

ErrDialectNotSupported tells you whether the dialect is supported or not.

View Source
var ErrEmptyTable = errors.New("sql: no rows in result set")

ErrEmptyTable is a common database/sql error if a table is empty or no rows is returned by the query.

View Source
var ErrEmptyTablePgx = errors.New("no rows in result set")

ErrEmptyTable is a common pgx error if a table is empty or no rows is returned by the query.

Functions

func ReplacePlaceholder

func ReplacePlaceholder(sql string, format string) string

ReplacePlaceholder converts default placeholder format to a specific format.

Types

type BobBuilder

type BobBuilder interface {
	ToSql() (string, []interface{}, error)
}

BobBuilder interface wraps the ToSql method

type BobBuilderType

type BobBuilderType builder.Builder

BobBuilderType is the type for BobBuilder

func (BobBuilderType) CreateTable

func (b BobBuilderType) CreateTable(table string) CreateBuilder

CreateTable creates a table with CreateBuilder interface

func (BobBuilderType) CreateTableIfNotExists

func (b BobBuilderType) CreateTableIfNotExists(table string) CreateBuilder

CreateTableIfNotExists creates a table with CreateBuilder interface, if the table doesn't exists.

func (BobBuilderType) DropTable added in v0.0.3

func (b BobBuilderType) DropTable(table string) DropBuilder

DropTable drops (delete contents & remove) a table from the database.

func (BobBuilderType) DropTableIfExists added in v0.0.3

func (b BobBuilderType) DropTableIfExists(table string) DropBuilder

DropTable drops (delete contents & remove) a table from the database if the table exists.

func (BobBuilderType) HasColumn

func (b BobBuilderType) HasColumn(column string) HasBuilder

HasColumn checks if a column exists with HasBuilder interface

func (BobBuilderType) HasTable

func (b BobBuilderType) HasTable(table string) HasBuilder

HasTable checks if a table exists with HasBuilder interface

func (BobBuilderType) RenameTable added in v0.0.3

func (b BobBuilderType) RenameTable(from, to string) RenameBuilder

RenameTable simply renames an exisisting table.

func (BobBuilderType) Truncate added in v0.0.3

func (b BobBuilderType) Truncate(table string) TruncateBuilder

Truncate performs TRUNCATE function. It deletes all contents from a table but not deleting the table.

func (BobBuilderType) Upsert added in v0.0.3

func (b BobBuilderType) Upsert(table string, dialect int) UpsertBuilder

type ColumnDef added in v0.0.2

type ColumnDef struct {
	Name   string
	Type   string
	Extras []string
}

type CreateBuilder

type CreateBuilder builder.Builder

func CreateTable

func CreateTable(table string) CreateBuilder

CreateTable creates a table with CreateBuilder interface. Refer to README for available column definition types.

// Note that CREATE TABLE doesn't returns args params.
sql, _, err := bob.
  CreateTable("tableName").
  // The first parameter is the column's name.
  // The second parameters and so on forth are extras.
  StringColumn("id", "NOT NULL", "PRIMARY KEY", "AUTOINCREMENT").
  StringColumn("email", "NOT NULL", "UNIQUE").
  // See the list of available column definition types through pkg.go.dev or README.
  TextColumn("password").
  // Or add your custom type.
  AddColumn(bob.ColumnDef{Name: "tableName", Type: "customType", Extras: []string{"NOT NULL"}}).
  ToSql()
if err != nil {
// handle your error
}

func CreateTableIfNotExists

func CreateTableIfNotExists(table string) CreateBuilder

CreateTableIfNotExists creates a table with CreateBuilder interface, if the table doesn't exists.

func (CreateBuilder) AddColumn added in v0.0.2

func (b CreateBuilder) AddColumn(column ColumnDef) CreateBuilder

AddColumn sets custom columns

func (CreateBuilder) BlobColumn added in v0.0.2

func (b CreateBuilder) BlobColumn(name string, extras ...string) CreateBuilder

BlobColumn only available for MySQL and SQLite. For PostgreSQL and MSSQL, please use AddColumn(bob.ColumnDef{Name: "name", Type: "BYTEA"}).

func (CreateBuilder) BooleanColumn added in v0.0.2

func (b CreateBuilder) BooleanColumn(name string, extras ...string) CreateBuilder

BooleanColumn only available for PostgreSQL

func (CreateBuilder) DateColumn added in v0.0.2

func (b CreateBuilder) DateColumn(name string, extras ...string) CreateBuilder

func (CreateBuilder) DateTimeColumn added in v0.0.2

func (b CreateBuilder) DateTimeColumn(name string, extras ...string) CreateBuilder

func (CreateBuilder) FloatColumn added in v0.0.2

func (b CreateBuilder) FloatColumn(name string, extras ...string) CreateBuilder

FloatColumn only available for MySQL and MSSQL. For PostgreSQL and SQLite, please refer to RealColumn.

func (CreateBuilder) IntColumn added in v0.0.2

func (b CreateBuilder) IntColumn(name string, extras ...string) CreateBuilder

IntColumn only available for MySQL and MSSQL. For PostgreSQL and SQLite please refer to IntegerColumn.

func (CreateBuilder) IntegerColumn added in v0.0.2

func (b CreateBuilder) IntegerColumn(name string, extras ...string) CreateBuilder

IntegerColumn only available for PostgreSQL and SQLite. For MySQL and MSSQL, please refer to IntColumn,

func (CreateBuilder) JSONBColumn added in v0.0.2

func (b CreateBuilder) JSONBColumn(name string, extras ...string) CreateBuilder

JSONBColumn only available for PostgreSQL. For MySQL please refer to JSONColumn.

func (CreateBuilder) JSONColumn added in v0.0.2

func (b CreateBuilder) JSONColumn(name string, extras ...string) CreateBuilder

JSONColumn only available for MySQL and PostgreSQL. For MSSQL please use AddColumn(bob.ColumnDef{Name: "name", Type: "NVARCHAR(1000)"}). Not supported for SQLite.

func (CreateBuilder) RealColumn added in v0.0.2

func (b CreateBuilder) RealColumn(name string, extras ...string) CreateBuilder

RealColumn only available for MSSQL, PostgreSQL, and SQLite. For MySQL, please refer to FloatColumn, or create your own with AddColumn() with Type: "DOUBLE".

func (CreateBuilder) StringColumn added in v0.0.2

func (b CreateBuilder) StringColumn(name string, extras ...string) CreateBuilder

StringColumn creates a column with VARCHAR(255) data type. For SQLite please refer to TextColumn.

func (CreateBuilder) TextColumn added in v0.0.2

func (b CreateBuilder) TextColumn(name string, extras ...string) CreateBuilder

TextColumn creates a column with TEXT data type

func (CreateBuilder) TimeColumn added in v0.0.2

func (b CreateBuilder) TimeColumn(name string, extras ...string) CreateBuilder

func (CreateBuilder) TimeStampColumn added in v0.0.2

func (b CreateBuilder) TimeStampColumn(name string, extras ...string) CreateBuilder

func (CreateBuilder) ToSql

func (b CreateBuilder) ToSql() (string, []interface{}, error)

ToSql returns 3 variables filled out with the correct values based on bindings, etc.

func (CreateBuilder) UUIDColumn added in v0.0.2

func (b CreateBuilder) UUIDColumn(name string, extras ...string) CreateBuilder

UUIDColumn only available for PostgreSQL

func (CreateBuilder) WithSchema

func (b CreateBuilder) WithSchema(name string) CreateBuilder

WithSchema specifies the schema to be used when using the schema-building commands.

type DropBuilder added in v0.0.3

type DropBuilder builder.Builder

func DropTable added in v0.0.3

func DropTable(table string) DropBuilder

DropTable drops (delete contents & remove) a table from the database.

func DropTableIfExists added in v0.0.3

func DropTableIfExists(table string) DropBuilder

DropTable drops (delete contents & remove) a table from the database if the table exists.

func (DropBuilder) ToSql added in v0.0.3

func (b DropBuilder) ToSql() (string, []interface{}, error)

ToSql returns 3 variables filled out with the correct values based on bindings, etc.

type HasBuilder

type HasBuilder builder.Builder

func HasColumn

func HasColumn(col string) HasBuilder

HasColumn checks if a column exists with HasBuilder interface.

func HasTable

func HasTable(table string) HasBuilder

HasTable checks if a table exists with HasBuilder interface.

func (HasBuilder) HasColumn

func (h HasBuilder) HasColumn(column string) HasBuilder

HasColumn checks if a column exists in the current table, resolves the promise with a boolean, true if the column exists, false otherwise.

func (HasBuilder) HasTable

func (h HasBuilder) HasTable(table string) HasBuilder

HasTable checks for a table's existence by tableName, resolving with a boolean to signal if the table exists.

func (HasBuilder) PlaceholderFormat

func (h HasBuilder) PlaceholderFormat(f string) HasBuilder

PlaceholderFormat changes the default placeholder (?) to desired placeholder.

func (HasBuilder) ToSql

func (h HasBuilder) ToSql() (string, []interface{}, error)

ToSql returns 3 variables filled out with the correct values based on bindings, etc.

func (HasBuilder) WithSchema

func (h HasBuilder) WithSchema(schema string) HasBuilder

WithSchema specifies the schema to be used when using the schema-building commands.

type PlaceholderFormat

type PlaceholderFormat interface {
	ReplacePlaceholders(sql string) (string, error)
}

PlaceholderFormat is an interface for placeholder formattings.

type RenameBuilder added in v0.0.3

type RenameBuilder builder.Builder

func RenameTable added in v0.0.3

func RenameTable(from, to string) RenameBuilder

RenameTable simply renames an exisisting table.

func (RenameBuilder) ToSql added in v0.0.3

func (b RenameBuilder) ToSql() (string, []interface{}, error)

ToSql returns 3 variables filled out with the correct values based on bindings, etc.

type TruncateBuilder added in v0.0.3

type TruncateBuilder builder.Builder

func Truncate added in v0.0.3

func Truncate(table string) TruncateBuilder

Truncate performs TRUNCATE function. It deletes all contents from a table but not deleting the table.

func (TruncateBuilder) ToSql added in v0.0.3

func (b TruncateBuilder) ToSql() (string, []interface{}, error)

ToSql returns 3 variables filled out with the correct values based on bindings, etc.

type UpsertBuilder added in v0.0.3

type UpsertBuilder builder.Builder

func Upsert added in v0.0.3

func Upsert(table string, dialect int) UpsertBuilder

Upsert performs a UPSERT query with specified database dialect. Supported database includes MySQL, PostgreSQL, SQLite and MSSQL.

// MySQL example:
sql, args, err := bob.
  // Notice that you should give database dialect on the second params.
  // Available database dialect are MySQL, PostgreSQL, SQLite, and MSSQL.
  Upsert("users", bob.MySQL).
  Columns("name", "email", "age").
  // You could do multiple Values() call, but I'd suggest to not do it.
  // Because this is an upsert function, not an insert one.
  Values("Thomas Mueler", "tmueler@something.com", 25).
  Replace("age", 25).
  PlaceholderFormat(bob.Question).
  ToSql()

// Another example for PostgreSQL:
sql, args, err = bob.
  Upsert("users", bob.PostgreSQL).
  Columns("name", "email", "age").
  Values("Billy Urtha", "billu@something.com", 30).
  Key("email").
  Replace("age", 40).
  PlaceholderFormat(bob.Dollar).
  ToSql()

// One more time, for MSSQL / SQL Server:
sql, args, err = bob.
  Upsert("users", bob.MSSQL).
  Columns("name", "email", "age").
  Values("George Rust", "georgee@something.com", 19).
  Key("email", "georgee@something.com").
  Replace("age", 18).
  PlaceholderFormat(bob.AtP).
  ToSql()

func (UpsertBuilder) Columns added in v0.0.3

func (u UpsertBuilder) Columns(columns ...string) UpsertBuilder

Columns sets the columns for the data to be inserted.

func (UpsertBuilder) Key added in v0.0.3

func (u UpsertBuilder) Key(key ...interface{}) UpsertBuilder

Key specifies which key to be checked on conflict. Must be used on PostgreSQL and SQLite.

func (UpsertBuilder) PlaceholderFormat added in v0.0.3

func (u UpsertBuilder) PlaceholderFormat(f string) UpsertBuilder

PlaceholderFormat changes the default placeholder (?) to desired placeholder.

func (UpsertBuilder) Replace added in v0.0.3

func (u UpsertBuilder) Replace(column interface{}, value interface{}) UpsertBuilder

Replace sets the column and value respectively for the data to be changed on a specific row.

func (UpsertBuilder) ToSql added in v0.0.3

func (u UpsertBuilder) ToSql() (string, []interface{}, error)

ToSql returns 3 variables filled out with the correct values based on bindings, etc.

func (UpsertBuilder) Values added in v0.0.3

func (u UpsertBuilder) Values(values ...interface{}) UpsertBuilder

Values sets the values in relation with the columns. Please not that only string, int, and bool type are supported. Inputting other types other than those might result in your SQL not working properly.

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
t or T : Toggle theme light dark auto
y or Y : Canonical URL